Running DB2 8.2 on aix.
Similar problem to this thread.
http://groups.google.com/group/comp....9ffd1e725f0380
May have something to do with the sql package cache. The snapshots
show it's not a buffer pool issue.
The SP uses it's arguments instead of parameter markers in the sql.
eg.g i_last_name
SET sql_where = sql_where CONCAT ' PNAM.LAST_NAME LIKE
UPPER(REPLACE( ' ;
SET sql_where = sql_where CONCAT '''' CONCAT i_last_name CONCAT ''''
CONCAT ', '' '', '''')) AND ' ;
Stripping out the SQL from the SP and testing the statement from the
command line................
PNAM.LAST_NAME LIKE UPPER(REPLACE( 'Murphy', ' ', ''))
The first time the statement runs with "Murphy" it takes 20 seconds.
Subsequent executions take 24 milleseconds. If I change Murphy to
"Finnegan" the sql takes 20 seconds. Subsequent executions take 24
milleseconds. And so on. So I am wondering if DB2 thinks the SQL
statement is brand new when the argument changes i.e. "Murphy" changes
to "Finnegan" and it recompiles the SQL statemennt.
Any ideas?