I hope a lot of you use the DB2 SQL EXPLAIN statement before issuing any SQL that you suspect would result in poor performance. When your SQL statement is dynamically executed the SQL EXPLAIN of this statement will give you the exact access path. But how do you EXPLAIN a single static SQL statement embedded in a program using host variables? Then you have to replace your host variable with something else. Either you use the exact value or you can use a ? instead.

The following static SQL statement:

EXEC SQL
  SELECT MAX(a), MAX(b)
  INTO :a, :b
  FROM TABLEX
  WHERE n > :n

can be rewritten to:

EXPLAIN PLAN FOR
  SELECT MAX(a), MAX(b)
  FROM TABLEX
  WHERE n > ?

We have to remove the INTO clause because it is only valid in static SQL and EXPLAIN only works with dynamic SQL. This example is reasonably simple but the point is, that if you supply a value instead of ? then you might end up with a different access path, than your static SQL statement will be assigned. The ? will give you the same access path.

To complicate matters DB2 introduced a BIND option called REOPT(VARS) in DB2 version 5 and even more complications were introduced in version 6 with OPTHINT. REOPT(VARS) will re-evaluate the access path chosen on bind time for static SQL statements based on the contents of host variables. You must take this into account when using the method described in this tip. REOPT(VARS) is a good idea when your static SQL statement is executed only once or twice, but a very bad idea if your static SQL statement is executed a lot of times. It is worth noticing that REOPT(VARS) is used at package or plan level but not statement level.

I don't want to go into details about OPTHINT, but if it is used for the static SQL statement you apply the method in this tip on, you might end up with a different result. With REOPT(VARS) you can adjust your EXPLAIN, but with OPTHINT you might get a completely different access path for your static SQL statement compared with the result from your EXPLAIN, because the access path in the static SQL is taken from a previosly explained SQL statement.