DB2 FOR LUW / DB2FORLUW / 970 - DB2

The automatics statistics collection facility uses UDI 
(update/delete/insert) activity information when deciding 
whether to perform runstats against a table.  Under rare 
conditions, the in-memory UDI data for a table might be lost, 
which in turn might lead to a failure to collect new statistics 
for the table as the automatics statistics collection facility 
does not think the table has had significant UDI activity, which 
in turn might lead to the query optimizer choosing a poor access 
plan. 
 
When the problem occurs, the statistics logs might show that a 
SQL1224 error, suggested an automatic runstats was forced. 
 
The fix for APAR IC91562 is a prerequisite when applying the fix 
for IC91713.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All DB2 V9.7 on LUW users.                                   * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 V9.7 Fix Pack 9 or higher                     * 
****************************************************************

Workaround: 
 
While this problem is generic in nature, it is more likely to be 
observed for tables which grow and shrink significantly and 
frequently, for example, tables which are truncated and then 
re-populated.  Consider an example of a daily job where a table 
is populated, queries are executed, and then the table is 
truncated.  Several workarounds are available in this situation: 
- Change the job so that the table truncation step starts the 
job.  This reduces the probability that the auto_runstats daemon 
might perform runstats on the table when it is empty, which 
reduces the probability that the optimizer might think the table 
is empty when compiling the queries, should the in-memory UDI 
information be lost leading the automatics statistics collection 
facility to not take action. 
- Alter the table to have the VOLATILE attribute.  This informs 
the query optimizer to take into consideration that the 
cardinality of the table can vary significantly at run time, 
from empty to large, whereby certain access methods will be 
preferred. 
- Change the job to include performing a runstats after 
re-populating the table. 
- Perform a one-time runstats against the table when it contains 
data that is representative of its daily re-populated state, and 
exclude the table from automatic statistics maintenance. 
 
In general situations where a poor access plan is identified and 
a better access plan is known, direct the optimizer to the 
better access plan via an optimization guideline.

DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Fixed in DB2 V9.7 Fix Pack 9

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC91955 IC95284 
follow-up : 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

22.04.2013
25.08.2014
25.08.2014

9.7.FP9