|
Problem IC94438
|
Status: Closed |
RUNNING MULTIPLE CONCURRENT SAMPLED DETAILED INDEX RUNSTATS MIGHT CAUSE A SERVER ABEND OR INACCURATE INDEX STATISTICS |
| product: |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 |
| Problem description: |
If you execute multiple RUNSTATS commands with the SAMPLED
DETAILED index option concurrently, you might observe a server
abend, or inaccurate index statistics.
This problem can occur only with the SAMPLED DETAILED index
option. In DB2 V10.1, when the DETAILED index option is
indicated without explicit specification of SAMPLED or
UNSAMPLED, SAMPLED is the default. RUNSTATS with the UNSAMPLED
DETAILED index option does not observe the problem. RUNSTATS
where the DETAILED index option is not specified does not
observe the problem.
Concurrent runstats executions can be common in environments
where real time statistics (AUTO_STMT_STATS) is enabled. In
environments where AUTO_RUNSTATS is enabled, and AUTO_STMT_STATS
is disabled, only one automatic runstats can be executing at any
given point in time. Unless otherwise directed by statistics
profiles, automatic runstats uses the SAMPLED DETAILED index
option when collecting statistics. As such, environments
enabled for real time statistics is vulnerable to this problem.
While real times statistics is used prevalently in DB2 10
customer environments, to date, the server abend symptom has
been reported only in one installation. The installation was a
zLinux server, but other platforms are vulnerable as well.
When the abend symptom occurs, the stack trace for the failing
EDU might look like this:
sqloEDUCodeTrapHandler
sqldHashZValue32
sqlis_touch_page
sqliProcessColStats
sqlischs
sqlistat
sqldIndexStats
sqlrLocalRunstats
When the inaccurate statistics symptom occurs, the inaccuracy is
likely to suggest that an index is less clustered than it is.
This inaccuracy can affect the query optimizer.
Aside from automatic statistics collections performed by DB2,
runstats activity can occur from user actions:
- RUNSTATS command, or db2Runstats API
- CREATE INDEX with statistics collection
- CREATE INDEX, when registry variable
DB2_CREATE_INDEX_COLLECT_STATS is set
- LOAD with statistics collection
- REDISTRIBUTE with statistics collection
- REORGCK with update statistics
You can check in-progress runstats executions using the LIST
UTILITIES command. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All users *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 version 10.5.0.3. *
**************************************************************** |
| Local Fix: |
Disable AUTO_STMT_STATS if enabled. Avoid user runstats
activity that can lead to multiple concurrent RUNSTATS with the
SAMPLED DETAILED index option.
- disable automatic runstats, by setting AUTO_RUNSTATS (or one
of its parents AUTO_TBL_MAINT or AUTO_MAINT) to off
- do not use the SAMPLED keyword on the "index statistics
clause"
- do not execute runstats command concurrently
- do not invoke runstats via other means (reorgchk, create
index, load, redistribute) |
| available fix packs: |
DB2 Version 10.5 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows
|
| Solution |
The problem is first fixed in DB2 version 10.5.0.3. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 29.07.2013
02.12.2013
02.12.2013 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.5.0.3
|
 |
| 10.5.0.3
|
|
