Solution : https://service.sap.com/sap/support/notes/1502194 (SAP Service marketplace login required)
Summary :
This SAP Note addresses Automatic RUNSTATS in DB2 within an SAP environment, a critical component for maintaining efficient database performance. The note describes the necessity of Automatic Statistics Collection (ASC) to keep the DB2 optimizer informed for optimal query plans. It details the enabling of AUTO_RUNSTATS and monitoring through specific DB2 commands and logs. This includes a comprehensive explanation of how ASC is implemented, such as the conditions under which RUNSTATS are triggered, including the UDI/cardinality ratio for specific tables. It further covers historical context, setup procedures, and considerations for both regular and partitioned environments, ensuring updated statistics for efficient database operations.
Key words :
=catalog cache lookups = 223234catalog cache inserts = 541catalog cache overflows = 0catalog cache high water mark = 4849664catalog cache statistics size = 95056, db2aps 74> db2pd -db aps -statisticscache summarydatabase partition 0 -- database aps -- active --, /developerworks/data/library/techarticle/dm-0706tang/#udirefer, catalog database partition number = 0catalog network node, list utilities show detail clp command, db2pd -db <dbname> -statisticscache [summary, lastrefid laststatstime sts0x00002b62bbb4dd20 db2aps explain_statement 63 2010-04-21-14, online maintenance window affect automatic runstats, sap gui-based user interface, ] db2_atm_cmd_line_args=-include-manual-tables [db2_workload] 17
Related Notes :
| 1425488 | DB6: Performance problems with volatile tables |
| 1381591 | DB6: Database crashes when fabricating statistics |
| 1309705 | DB6: Log space not released by statistics update |
| 1160813 | DB6: Accessing a table returns SQL0901N |
| 966970 | DB6: New tables remain VOLATILE for AutoRunstats |
| 533589 | DB6: SQL2314W Some statistics are in an inconsistent state |