
Ved Renden 31 2870 Dyssegaard Tel. +45 23 34 54 43
| 
MainframeSupports tip week 32/2002:
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.
Previous tip in english
Sidste danske tip
Tip list
|