|
Problem IC91713
|
Status: Closed |
UNDER RARE CONDITIONS, AUTOMATIC STATISTICS COLLECTION MIGHT NOT OCCUR DUE TO LOST UDI INFORMATION |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
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. |
| Problem Summary: |
****************************************************************
* 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 *
**************************************************************** |
| Local Fix: |
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. |
| available fix packs: |
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
|
| Solution |
Fixed in DB2 V9.7 Fix Pack 9 |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC91955 IC95284
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 22.04.2013
25.08.2014
25.08.2014 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP9
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.9
|
 |
| 9.7.0.9
|
|
