Team with the best
Db2® LUW Performance Tools
company in the World

DB2 LUW Performance: a VOLATILE topic - more volatile than I thought

January 14, 2010, 7:15 pm
Posted by Scott in DB2 Performance How-To
Very few DB2 LUW performance blog posts have delivered so many emails to my Inbox as the prior posting on VOLATILE tables. One of our blog readers brought some very important information to my attention that I feel an obligation to share...
Scott,

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:

  • Issue RUNSTATS against the volatile table; or
  • Set AUTO_STMT_STATS to OFF.
BTW: We obtained a special build of DB2 9.5 FP4, which addresses this issue.

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

Printer friendly