Under rare circumstances, automatic statistics collection for a
table might repeatedly log sqlcode -901 errors due to an invalid
entry in the statistics cache.
The following conditions must exist for the problem to occur:
- The table has at least one index.
- The table has a statistics profile which specifies the "FOR
INDEXES" clause. The clause may specify one or more or all of
the
indexes of the table.
- The table's current state has statistics for at least one
column which is not a leading column in the index, or any of the
indexes, indicated by the statistics profile.
- Automatic statistics collection, which is enabled via
AUTO_STMT_STATS or AUTO_RUNSTATS (and their parent automatic
maintenance database configuration parameters), is collecting
statistics for the table.
- The automatic statistics collection is interrupted within a
narrow window of time after it has completed the statistics
collection phase of processing but before it has completed
updating the catalogs with the new statistics.
When these conditions are met, an invalid entry exists in the
statistics cache which will cause a sqlcode -901 error to be
reported when background processes later try to process the
statistics cache entry to complete the interrupted catalog
update.
The DB2 diagnostic log will show a db2agent logging a sqlcode
-901 error with token "No statistics for column i" where i is a
number.
For example:
2011-02-10-13.57.52.467545-300 I17558E848 LEVEL: Severe
PID : 13988 TID : 47344489851200PROC :
db2sysc
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-30 APPID:
*LOCAL.db2inst1.110210185756
AUTHID : DB2INST1
EDUID : 36 EDUNAME: db2agent (SAMPLE)
FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc,
probe:300
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -901 sqlerrml: 26
sqlerrmc: No statistics for column 0
sqlerrp : SQLRR016
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3)
0x00000000
(4) 0x00000000 (5) 0xFFFFB4E1 (6)
0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
Following almost immediately, the optstats log will show the
same -901 error logged by db2acd. For example, this is an entry
from the real time statistics daemon:
2011-02-10-13.57.52.504450-300 I14638E1131 LEVEL:
Warning
PID : 13999 TID : 1107310912 PROC : db2acd
INSTANCE: db2inst1 NODE : 000
APPID : *LOCAL.db2inst1.110210185756
FUNCTION: DB2 UDB, Automatic Table Maintenance,
JitsDaemon::runstats, probe:60
MESSAGE : SQL0901N The SQL statement failed because of a
non-severe system
error. Subsequent SQL statements can be processed.
(Reason "".)
DATA #1 : String, 215 bytes
AutoStats: Non zero code from Runstats
SQL0901N The SQL statement failed because of a non-severe
system error.
Subsequent SQL statements can be processed. (Reason "No
statistics for column
0".) SQLSTATE=58004
DATA #2 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -901 sqlerrml: 26
sqlerrmc: No statistics for column 0
sqlerrp : SQLRR016
sqlerrd : (1) 0x00000385 (2) 0x801A006D (3)
0x00000000
(4) 0x00000000 (5) 0xFFFFB4E1 (6)
0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate: 58004
2011-02-10-13.57.52.514932-300 E15770E536 LEVEL: Event
PID : 13999 TID : 1107310912 PROC : db2acd
INSTANCE: db2inst1 NODE : 000
APPID : *LOCAL.db2inst1.110210185756
FUNCTION: DB2 UDB, Automatic Table Maintenance,
JitsDaemon::runstats, probe:80
WRITE : TABLE AND INDEX STATS : Object name with schema : AT
"2011-02-10-13.57.52.514897" : BY "Asynchronous" : failure
OBJECT : Object name with schema, 11 bytes
<schema.table>
IMPACT : None
DATA #1 : String, 26 bytes
ZRC=0x90000002=-1879048190
Automatic statistics collection will continue to try to process
the invalid entry as part of background activities. The
background daemon runs approximately every 5 minutes for real
time statistics (AUTO_STMT_STATS). The background daemon runs
approximately every 2 hours for automatic runstats
(AUTO_RUNSTATS).
The -901 error logging will continue until the invalid entry is
evicted from cache, or until the statistics cache is released
which occurs when the database shuts down after the last
connection disconnects from the database in the absence of a
prior explicit database activation. The continued logging of
sqlcode -901 may lead to a filesystem full condition to the
diagnostic data directory path (DIAGPATH).
This problem was introduced in DB2 Version 9.7 Fix Pack 1. |
To avoid the problem, do not create statistics profiles with the
"FOR INDEXES" clause. Use the "AND INDEXES" clause instead.
Using "FOR INDEXES" in the statistics profile is a poor practice
and should be generally avoided as it will lead to continued
refresh of index statistics by automatic statistics collection,
without refresh of table statistics.
If the problem has occurred, to stop the cycle of -901 logging,
the recommended action is to deactivate the database (if it was
activated) and have all applications terminate their
connections.
This serves to shut down the database, releasing the catalog
cache in which the statistics cache resides. |
