Now this might sound like a James Bond style bad guy’s death threat but actually this has happened at a shop I was at, and so I thought I would share the grisly details with you all!
In DB2 10 IBM increased the intelligence of the Optimizer again and so they introduced the dreaded “index probe”. It is a kind of double check – Is the data really empty?
Index probing is used in three distinct circumstances in DB2 10 any mode:
First is:
1) RUNSTATS reported an empty table
2) RUNSTATS reported empty qualifying parts
3) Catalog statistics are at default
4) A matching predicate is estimated to return zero rows
Second is:
1) Table has the VOLATILE attribute
Third is:
1) Table has fewer than ZPARM NPGTHRSH pages
Now the last two are basically the same – Don’t trust the RUNSTATS as it might have been run at an inopportune time, e.g. when the table was empty, so checking the index to see if there is data there really does make sense.
A quick solution in the past – A killer at present
Now what happened at our customer site was Dynamic SQL PREPAREs started taking up to 30 seconds for some SQL. What was weird was that the SQL was against many partitioned objects (Normally more than 250 and most actually at 999!) using the full partitioned key. The customer checked the statistics – everything fine, REORGed the DB2 Catalog – just in case! No change and then they got the answer from IBM – Remove the VOLATILE keyword!
Now we go back in time … When the customer migrated to DB2 V8 many, many moons ago the access to their, then only 254 partitions, partioned tables got much worse and IBM investigated and said “use the VOLATILE keyword”, and hey Presto! It worked! It also worked for DB2 9 but is a disaster in DB2 10. By the way – the 999 partitions were done “for future growth” and most of the partitions are, of course, empty.
IBM has said that a corrective PTF will come out in September.
Check the access path
Of course simply ALTERing the table to be NOT VOLATILE stopped the disastrous index probe of 999 datasets *but* it also, naturally enough, changed the access paths as well! The customer must now compare all the DB2 9 and DB2 10 dynamic SQL access paths to see what will now go pear shaped… Luckily they have our Bind ImpactExpert with Early Precheck Dynamic and Static so the task is simple to do but someone must still do it and then analyze the results!
Here’s a little query to show you if *you* have any of these little darlings waiting to go BOOM at your shop…
SELECT SUBSTR(STRIP(A.CREATOR) CONCAT '.'
CONCAT STRIP(A.NAME) , 1 , 32 ) AS TABLE
, MAX(B.PARTITION) AS MAX_PARTS
FROM SYSIBM.SYSTABLES A
,SYSIBM.SYSTABLEPART B
WHERE A.SPLIT_ROWS = 'Y'
AND A.TYPE = 'T'
AND A.DBNAME = B.DBNAME
AND A.TSNAME = B.TSNAME
AND B.PARTITION > 200
GROUP BY A.CREATOR, A.NAME
ORDER BY 1
;
As usual any questions or comments are welcome,
TTFN Roy Boxwell
Senior Software Architect