I just read your Jan 5, 2010 blog about volatile tables. Are you aware of the problems addressed by APARs IZ58997 (DB2 9.1), IZ58996 (DB2 9.5), and IC62645 (DB2 9.7)? If tables do not have statistics, DB2 will try to fabricate them. With DB2 9.5 and DB2 9.7, this can lead to a database crash.
I have personally experienced such crashes with a volatile table when running SAP with DB2 9.5 FP4. The available workarounds are:
Below are excerpts from a technical note on this topic.
Cause and Prerequisites
The crashes are caused by a problem in the database product. The following database releases are affected: DB2 V9.1, DB2 V9.5, DB2 9.7. The error can occur if real time statistics are fabricated, or statistics information is gathered for a table for that no statistic data had been collected so far.
Solution:
o DB2 V9.1 for LUW: APAR IZ58997
o DB2 V9.5 for LUW: APAR IZ58996 going to Fix Pack 6
o DB2 V9.7 for LUW: APAR IC62645
Workaround
o DB2 V9.5 and DB2 9.7:
Avoid real time statistics by setting the database configuration parameter AUTO_STMT_STATS to OFF. If statistical data for tables is up-to-date, real time statistics are also avoided.
o Since the problem can also occur because of non-existent table statistics, we recommend that you update the statistics on these tables.
You may also be interested in APARs IZ35882 and IZ35881. They state:
DB2 Optimizer respects statistics that is collected on
Volatile tables and makes plan choices based on that.
Purpose of this APAR is for the DB2 compiler to ignore
statistics collected on the table(s) makrked as volatile and
instead fabricate statistics assuming none has been collected.
NOTE :-
This will be done only under the control of registry variable
DB2_WORKLOAD=SAP
Regards,
Rick
Rick - thanks for sharing your experience and knowledge with me and the DB2 community.
Best regards,
Scott
PS - Have you signed up for The DB2Night Show Episode #11 yet on DB2 9.7 Optimizer updates with special guest John Hornibrook, IBM Lab? www.db2nightshow.com