REOPT ALWAYS for v 8.2 SQL procedures: does it work?

[Ion]

Hi,

I have a sql procedure that (among others) deletes a row in a table
(very small). The table has a foreign key - ON DELETE NO ACTION that
points to a large table (child). There is an index defined on the child
table column, but the selectivity is very poor (actually in this
particular case all values are zero)
The delete statement looks like

delete from ParentTable_Small where primary_key = variable

Because the cascade rule is NO ACTION, DB2 will check the child table
for orphaned records (which there are none).

If I do a db2expln for a similar statement (delete from parent where
primary_key = ?) the access plan wil show a table scan of the large
table (and the deletion takes forever). However, if I replace the "?"
with a constant which is out of the child's values range, the access
plan will choose an index scan.

The stored procedure access plan shows table scan.
I tried to rebind the procedure with the REOPT ALWAYS option as well as
with the EXPLAIN option, but the explain tables show no change in the
access plan (actually the only compilation is the initial one)

Isn't this suppose to change the access plan to choose an index scan at
some point when i call the stored procedure with some actual values?
(same values for which db2expln chooses an index scan)
thanks

[Serge Rielau]

Ion wrote:

Hi,

I have a sql procedure that (among others) deletes a row in a table
(very small). The table has a foreign key - ON DELETE NO ACTION that
points to a large table (child). There is an index defined on the child
table column, but the selectivity is very poor (actually in this
particular case all values are zero)
The delete statement looks like

delete from ParentTable_Small where primary_key = variable

Because the cascade rule is NO ACTION, DB2 will check the child table
for orphaned records (which there are none).

If I do a db2expln for a similar statement (delete from parent where
primary_key = ?) the access plan wil show a table scan of the large
table (and the deletion takes forever). However, if I replace the "?"
with a constant which is out of the child's values range, the access
plan will choose an index scan.

The stored procedure access plan shows table scan.
I tried to rebind the procedure with the REOPT ALWAYS option as well as
with the EXPLAIN option, but the explain tables show no change in the
access plan (actually the only compilation is the initial one)

Isn't this suppose to change the access plan to choose an index scan at
some point when i call the stored procedure with some actual values?
(same values for which db2expln chooses an index scan)
thanks

Which fixpack are you on? Note that when you recompile the procedure you
won't see the reoptimized plan (yet).
It should show up when you execute the procedure assuming you have
EXPLAIN ALL set.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Ion]

Hi Serge,

DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08021"
with
level identifier "03020106".
Informational tokens are "DB2 v8.1.8.762", "s041221", "WR21348", and
FixPak "8".

After recompiling the stored procedure, explaining the package shows a
table scan.
When executing it, it obviously stays with the table scan as it takes
forever.
However, if in the procedure I replace the delete statement with
either:
delete from table where pk=666
or i make it dynamic sql with hardcode values instead of parameter
markers, the stored procedure executes instantly