SAPTechno

Note 1502194 - DB6: Automatic Statistics Collection (Automatic RUNSTATS)

Header
Version / Date 4 / 2010-11-16
Priority Recommendations/additional info
Category FAQ
Primary Component SV-BO-DB-IBM Performance Problems IBM Databases
Secondary Components

Summary
Symptom

You are looking for information about DB2's automatic RUNSTATS function in an SAP environment.

Other terms

FAQ, auto-runstats, autonomic features, TCO, zero admin

Solution

This SAP Note provides the following information:

    1. Why do database statistics need to be collected?
    2. What is automatic statistics collection (ASC)?
    3. Since when has automatic staticstics collection (ASC) been available?
    4. How to enable automatic statistics collection?
    5. How does automatic RUNSTATS work?
    6. How to monitor automatic RUNSTATS?
    7. How does automatic RUNSTATS appear on operating system level?
    8. What are real-time statistics (RTS) and why are they needed?
    9. How to check real-time statistics and statistics cache?
    10. How to enable real-time statistics?
    11. What types of automatic statistics collection are there?
    12. How is RUNSTATS utility triggered by automatic RUNSTATS?
    13. What is a statistics profile?
    14. What is the performance overhead of automatic RUNSTATS?
    15. How does automatic statistics collection work in a partitioned database environment (DPF)?
    16. What is not covered by automatic RUNSTATS?
    17. How does the online maintenance window affect automatic RUNSTATS?
    18. SAP Recommendations
    19. Known DB2 Issues and Corrections
    20. Known SAP Issues and Corrections




1. Why do database statististics need to be collected?


To determine the most efficient access plan for any given query, the DB2 optimizer uses catalog statistics about the content of tables and indexes. If there are out-of-date or incomplete statistics for a table or an index, the optimizer might select a plan that is not optimal, which results in increased query execution time. If there have been many changes, it is necessary to collect new statistics for tables and indexes.

2. What is automatic statistics collection (ASC)?


With automatic statistics collection, you can let the DB2 database manager determine whether database statistics need to be updated. Automatic statistics collection can run synchronously at statement compilation time using the real-time statistics (RTS) feature, or they can run in the background for asynchronous collection to ensure most current database statistics.

3. Since when has automatic statistics collection been available?


Automatic statistics collection (ASC), also known as automatic RUNSTATS, was introduced in DB2 UDB Version 8.2 (which is equivalent to version 8.1, FixPak 7) as part of DB2's automated table maintenance feature. You can check your current database version by using the db2level command. The following is an example of the output of this command:
lu0208:db2aps 51> db2level
DB21085I  Instance "db2aps" uses "64" bits and DB2 code release "SQL09052" with level identifier "03030107".
Informational tokens are "DB2 v9.5.0.2", "s080911", "MI00260", and Fix Pack "2a".
Product is installed at "/db2/db2aps/db2_v95".

4. How to enable automatic statistics collection?


You can enable or disable automatic statistics collection using the AUTO_RUNSTATS database configuration parameter that is part of a hierarchy of automatic maintenance database configuration parameters. To view these parameters, use the GET DB CONFIG command on the command line (CLP). The following is an example of the output of this command:
...
...
Automatic maintenance                       (AUTO_MAINT) = ON
   Automatic database backup            (AUTO_DB_BACKUP) = OFF
   Automatic table maintenance          (AUTO_TBL_MAINT) = ON
     Automatic runstats                  (AUTO_RUNSTATS) = ON
     Automatic statistics profiling    (AUTO_STATS_PROF) = OFF
       Automatic profile updates         (AUTO_PROF_UPD) = OFF
     Automatic reorganization               (AUTO_REORG) = OFF
...
...

Note that the AUTO_RUNSTATS parameter is part of a hierarchy of automatic maintenance parameters. To enable automatic statistics collection, the AUTO_MAINT, AUTO_TBL_MAINT, and AUTO_RUNSTATS parameters must all be set to ON.

5. How does automatic RUNSTATS work?


The asynchronous check whether statistics need to be collected starts the first time after the database has been activated. The check runs then approx. every two hours to identify database objects that require new statistics collection. This time frame is hard coded and cannot be changed.

The following describes roughly how automatic RUNSTATS works for an SAP system on DB2 Version 9.1 or higher:

  • Checks if table has statistics. If statistics have never been collected for this table,  RUNSTATS on the table is issued. No further checks are performed.
  • If the UDI ratio UDI / Cardinality (# of rows in a table) is larger than 50%, RUNSTATS is issued.
  • If the UDI ratio is less than 10%, no steps are performed.
  • If the ratio is between 10% and 50%, a more sophisticated decision process takes place:
    • If a table is smaller than 4000 pages, a RUNSTATS is issued.
    • If a table is larger than 4000 pages, DB2 does a sampling of the table and compares the sampling results against the current statistics. If there is no significant change, DB2 does not issue a RUNSTATS. If there are significant differences between current and sampled statistics, DB2 issues a RUNSTATS.

For more information about the check process, refer to the following IBM Web page:
http://www.ibm.co m/developerworks/data/library/techarticle/dm-0706tang/#udi

Refer to SAP Note 1306308 for more about UDI counter.

6. How to monitor automatic RUNSTATS?


Up to DB2 Version 9.1, all activities are recorded in the db2diag.log. You can extract activities that are related to automatic RUNSTATS from the db2diag.log as shown in the following command (including an example of the output):
db2diag -gi "funcname:= runstats"
2010-04-02-09.20.21.438602+480 I159584A364        LEVEL: Event
PID     : 7053324              TID  : 1360        PROC : db2acd 0
INSTANCE: db2egp               NODE : 000
APPID   : *LOCAL.db2egp.100402012022
FUNCTION: DB2 UDB, Automatic Table Maintenance, AtmTable::runstats, probe:500
START   : Automatic Runstats: runstats has started on table "SAPAPS  "."VBAK"
...
...
2010-04-02-09.20.21.560270+480 I159949A375        LEVEL: Event
PID     : 7053324              TID  : 1360        PROC : db2acd 0
INSTANCE: db2egp               NODE : 000
APPID   : *LOCAL.db2egp.100402012022
FUNCTION: DB2 UDB, Automatic Table Maintenance, AtmTable::runstats, probe:900
STOP    : Automatic Runstats: runstats completed successfully on table "SAPAPS  "."VBAK"

As of DB2 Version 9.5, all related information has been moved to a series of dedicated log files for automatic RUNSTATS that are located in the $DIAGPATH/events directory. The default name of the statistics log is db2optstats.<number>.log. It is located in the $DIAGPATH/events directory. The statistics log is a rotating log (the default are 5 logs). The behaviour of statistics log is controlled by the DB2_OPTSTATS_LOG registry variable.

You can view the contents of the statistics log directly or you can query the content using the SYSPROC.PD_GET_DIAG_HIST table function. In the following SQL example, the query returns statistics log records for events up to one year using the SYSPROC.PD_GET_DIAG_HIST table function.
SELECT
  pid,
  tid,
  substr(eventtype, 1,10),
  substr(objtype,1,30) as objtype,
  substr(objname_qualifier,1,20) as objschema,
  substr(objname,1,10) as objname,
  substr(first_eventqualifier,1,26) as event1,
  substr(second_eventqualifiertype,1,2) as event2_type,
  substr(second_eventqualifier,1,20) event2,
  substr(third_eventqualifiertype,1,6) event3_type,
  substr(third_eventqualifier,1,15) event3,
  substr(eventstate,1,20) as eventstate
FROM
  TABLE( SYSPROC.PD_GET_DIAG_HIST
       ( 'optstats', 'EX', 'NONE', CURRENT_TIMESTAMP - 1 year, CAST( NULL AS TIMESTAMP ))) as sl
order by
  timestamp(varchar(substr(first_eventqualifier,1,26),26))

For more information, see the following information in the IBM DB2 Version 9.5 Information Center at:

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.admin.perf.doc/doc/c0052970.html


If the automtic RUNSTATS utility is running, you can monitor it by using the LIST UTILITIES SHOW DETAIL CLP command. The following is an example of the output of this command:
ID                               = 36464
Type                             = RUNSTATS
Database Name                    = EGP
Partition Number                 = 0
Description                      = SAPR3.VBAK
Start Time                       = 03/13/2010 17:13:19.933221
State                            = Executing
Invocation Type                  = Automatic
Throttling:
   Priority                      = 40

Alternatively, you can also use the "db2pd -util" command.

7. How does automatic RUNSTATS appear on operating system level?


The engine evaluating the collection of automatic statistics is a thread that is running in the health monitor process on the catalog node. In DB2 UDB V8.2, the health monitor process was called db2hmon. With DB2 V9.1, however, it was renamed to db2acd.

There is one automatic statistics collection thread per database, and it maintains a database connection for the duration of the evaluation.

8. What are real-time statistics (RTS) and why are they needed?


Real-time statistics (in the following referred to as RTS) are also called "just-in-time" statistics (JIT). Since DB2 Version 9.5, RTS  has been the enhancement for automatic RUNSTATS. The gathering of RTS closes the gap in automatic statistics collection that only collect and maintain statistics periodicially. With RTS - when you submit a query to the compiler - the optimizer determines whether the statistics for the referenced tables are accurate. If there are no statistics or if the tables have changed significantly since statistics were last collected, the statistics are collected again during statement compilation if possible. The default time to collect statistics at statement-compilation time is limited to 5 seconds by default. This value can be controlled by the RTS optimization guideline.

There are two optional attributes for RTS in the optimization guideline:

  • The OPTION attribute is used to enable or disable RTS collection. It can have the values ENABLE or DISABLE. If no option is specified, ENABLE is the default.
  • The TIME attribute specifies the maximum amount of time in milliseconds to be spent on RTS collection at statement compilation time for a single statement.


If ENABLE is specified for the OPTION attribute, automatic statistics collection and RTS must be enabled by their corresponding configuration parameters (see section "10. How to enable real-time statistics"). Otherwise, the optimization guideline is not applied and you receive warning message SQL0437W (reason code 13).

Example:
The following RTS request enables RTS collection and limits RTS collection time to 3.5 seconds:

<RTS OPTION="ENABLE" TIME="3500" />

If synchronous collection exceeds the time limit, statistics are fabricated and an asynchronous collection request is submitted (see section "11. Types or Automatic Statistics Collection").

9. How to check real-time statistics and the statistics cache?


With DB2 Version 9.5, a statistics cache was introduced to make synchronously-collected statistics available to all queries. This cache is part of the catalog cache. In a partitioned database environment (DPF), the cache resides only on the catalog database partition.

To check the current status of the statistics cache, use the following command:
db2pd -db <DBNAME> -statisticscache [summary | detail]


The following is an example of the output of this command:
lu0208:db2aps 74> db2pd -db aps -statisticscache summary

Database Partition 0 -- Database APS -- Active -- Up 1 days 03:22:55

Statistics Cache:
Current Size            95056

Entries in Statistics Cache:
Address            Schema   Name               LastRefID  LastStatsTime              Sts
0x00002B62BBB4DD20 DB2APS   EXPLAIN_STATEMENT  63         2010-04-21-14.34.05.932233 V
0x00002B62BBF1DD60 SAPR3    TUCON              47         2010-04-22-03.03.22.348917 V
0x00002B62BBA6DE20 DB2APS   EXPLAIN_STREAM     63         2010-04-21-14.34.05.995790 V
0x00002B62BBB5DCC0 DB2APS   EXPLAIN_ARGUMENT   63         2010-04-21-14.34.05.963943 V
0x00002B62BD25DE00 SAPR3    ALALERTDB          47         2010-04-21-21.18.21.738768 V
0x00002B62BBACDC00 DB2APS   EXPLAIN_OBJECT     63         2010-04-21-14.34.06.034182 V
0x00002B62BBACDDE0 DB2APS   EXPLAIN_PREDICATE  63         2010-04-21-14.34.06.076604 V
0x00002B62BBA6DC40 DB2APS   EXPLAIN_OPERATOR   63         2010-04-21-14.34.05.972085 V


You can opbtain the current size of the statistics cache also from a database snapshot as shown in the following example:
              Database Snapshot
...
Catalog database partition number          = 0
Catalog network node name                  =
Catalog cache lookups                      = 223234
Catalog cache inserts                      = 541
Catalog cache overflows                    = 0
Catalog cache high water mark              = 4849664
Catalog cache statistics size              = 95056
...


There is another group of database monitoring elements in the database snapshot that count the various RTS activities and summarize their time as shown in the following example:
              Database Snapshot
...
...
Statistic fabrications                     = 10
Statistic fabrication time (sec.ms)        = 5
Synchronous runstats                       = 6
Synchronous runstats time (sec.ms),,       = 340
Asynchronous runstats                      = 20
...
...

10. How to enable real-time statistics (RTS)?


As of DB2 Version 9.5, parameter AUTO_STMT_STATS was introduced as a new automatic maintenance database configuration parameter to enable or disable the collection of RTS. To enable RTS collection, this parameter must be set to ON as shown in the following example output:

Automatic maintenance                      (AUTO_MAINT) = ON
   Automatic database backup            (AUTO_DB_BACKUP) = OFF
   Automatic table maintenance          (AUTO_TBL_MAINT) = ON
     Automatic runstats                  (AUTO_RUNSTATS) = ON
       Automatic statement statistics  (AUTO_STMT_STATS) = ON
     Automatic statistics profiling    (AUTO_STATS_PROF) = OFF
       Automatic profile updates         (AUTO_PROF_UPD) = OFF
     Automatic reorganization               (AUTO_REORG) = ON

11. What types of automatic statistics collection are there?


Automatic statistics can be collected synchronously or asynchronously by running the RUNSTATS utility. Asynchronous collection takes place in the background. If the RTS feature is enabled, statistics can also be collected synchronously at statement compilation time. If RTS collection is enabled, statistics can also be fabricated using meta-data that is maintained by the index and data manager. Fabrication means deriving or creating statistics rather than collecting them as part of normal RUNSTATS activity. Statistics can be fabricated in the following scenario:

If multiple agents discover that an object needs statistics, DB2 ensures that only one agent collects statistics synchronously. The other agents can try to improve their local copies of the statistics by fabricating them (using online information collected by DB2) before optimizing and running the query, but they do not perform synchronous statistic collection. Once the agent collecting the statistics finishes, the agent makes the statistics visible to all subsequent queries in the system.

If RTS collection is enabled, the asynchronous check for statistics collection still takes place at two-hour intervals. Real-time statistics collection also initiates asynchronous collection requests if the following applies:

  • Table activity is not high enough to require synchronous collection but it is high enough to require asynchronous collection.
  • Synchronous statistics collection used sampling because the table was large.
  • Synchronous statistics were fabricated.
  • Synchronous statistics collection failed because the collection time was exceeded.


At most, two asynchronous requests can be processed at the same time but only for different tables. One request was initiated by RTS and the other was initiated by the asynchronous check for statistics collection.
In total, 3 distinct types of automatic statistics collection exist, the log files are located  in the $DIAGPATH/events directory, with default name: db2optstats.number.log.


The following is an example of synchronous statistics collection - triggered by RTS:

2010-04-09-08.20.23.024488+000 E16615A604         LEVEL: Event
PID     : 11698366             TID  : 31356       PROC : db2sysc 0
INSTANCE: db2ecp               NODE : 000         DB   : ECP
APPHDL  : 0-14786              APPID: 148.175.216.102.39893.100409051
AUTHID  : SAPECC
EDUID   : 31356                EDUNAME: db2agent (ECP) 0
FUNCTION: DB2 UDB, SW- optimizer, sqlno_runstats_or_fabrication, probe:100
COLLECT : TABLE AND INDEX STATS : Object name with schema : AT "2010-04-09-08.20.23.024037" : BY "Synchronous" : start
OBJECT  : Object name with schema, 13 bytes
SAPECC  .SOSC
IMPACT  : None
2010-04-09-08.20.23.048061+000 E17220A750         LEVEL: Event
PID     : 11698366             TID  : 31356       PROC : db2sysc 0
INSTANCE: db2ecp               NODE : 000         DB   : ECP
APPHDL  : 0-14786              APPID: 148.175.216.102.39893.100409051
AUTHID  : SAPECC
EDUID   : 31356                EDUNAME: db2agent (ECP) 0
FUNCTION: DB2 UDB, SW- optimizer, sqlno_runstats_or_fabrication, probe:1000
COLLECT : TABLE AND INDEX STATS : Object name with schema : AT "2010-04-09-08.20.23.047754" : BY "Synchronous" : success
OBJECT  : Object name with schema, 13 bytes
SAPECC  .SOSC
IMPACT  : None
DATA #1 : String, 114 bytes
RUNSTATS ON TABLE "SAPECC"."SOSC" ON ALL COLUMNS WITH DISTRIBUTION ON ALL COLUMNS AND SAMPLED DETAILED INDEXES ALL

The following is an example of asynchronous statistics collection - triggered by RTS or asychronous statistics collection checking every 2 hours:

(Triggered by asynchronous statistics collection)
2010-04-09-09.21.21.686379+000 E263716A537        LEVEL: Event
PID     : 14479552             TID  : 1177        PROC : db2acd 0
INSTANCE: db2ecp               NODE : 000
APPID   : *LOCAL.db2ecp.100409092123
EDUID   : 1177                 EDUNAME: db2acd 0
FUNCTION: DB2 UDB, Automatic Table Maintenance, AtmTable::runstats, probe:30
COLLECT : TABLE AND INDEX STATS : Object name with schema : AT "2010-04-09-09.21.21.685958" : BY "Asynchronous" : start
OBJECT  : Object name with schema, 23 bytes
SAPECC  .ZTMP0000256040
IMPACT  : None
...
2010-04-09-09.21.21.752777+000 E264254A655        LEVEL: Event
PID     : 14479552             TID  : 1177        PROC : db2acd 0
INSTANCE: db2ecp               NODE : 000
APPID   : *LOCAL.db2ecp.100409092123
EDUID   : 1177                 EDUNAME: db2acd 0
FUNCTION: DB2 UDB, Automatic Table Maintenance, AtmTable::runstats, probe:60
COLLECT : TABLE AND INDEX STATS : Object name with schema : AT "2010-04-09-09.21.21.752355" : BY "Asynchronous" : success
OBJECT  : Object name with schema, 23 bytes
SAPECC  .ZTMP0000256040
IMPACT  : None
DATA #1 : String, 89 bytes
Automatic Runstats: runstats completed successfully on table "SAPECC  "."ZTMP0000256040"

(Triggered by real-time statistics)
2010-04-09-08.19.21.084236+000 E5938A533          LEVEL: Event
PID     : 14479552             TID  : 1151        PROC : db2acd 0
INSTANCE: db2ecp               NODE : 000
APPID   : *LOCAL.db2ecp.100409081924
EDUID   : 1151                 EDUNAME: db2acd 0
FUNCTION: DB2 UDB, Automatic Table Maintenance, JitsDaemon::runstats, probe:50
COLLECT : TABLE AND INDEX STATS : Object name with schema : AT "2010-04-09-08.19.21.083849" : BY "Asynchronous" : start
OBJECT  : Object name with schema, 17 bytes
SAPECC  .SDOKPROF
IMPACT  : None
...
2010-04-09-08.19.21.120346+000 E6472A535          LEVEL: Event
PID     : 14479552             TID  : 1151        PROC : db2acd 0
INSTANCE: db2ecp               NODE : 000
APPID   : *LOCAL.db2ecp.100409081924
EDUID   : 1151                 EDUNAME: db2acd 0
FUNCTION: DB2 UDB, Automatic Table Maintenance, JitsDaemon::runstats, probe:80
COLLECT : TABLE AND INDEX STATS : Object name with schema : AT "2010-04-09-08.19.21.119938" : BY "Asynchronous" : success
OBJECT  : Object name with schema, 17 bytes
SAPECC  .SDOKPROF
IMPACT  : None


The following is a an example of statistics fabrication - triggered by RTS:

2010-04-09-08.34.14.436854+000 E41345A602         LEVEL: Event
PID     : 11698366             TID  : 29814       PROC : db2sysc 0
INSTANCE: db2ecp               NODE : 000         DB   : ECP
APPHDL  : 0-16501              APPID: 148.175.216.102.33889.100409025
AUTHID  : SAPECC
EDUID   : 29814                EDUNAME: db2agent (ECP) 0
FUNCTION: DB2 UDB, SW- optimizer, sqlno_runstats_or_fabrication, probe:100
COLLECT : TABLE AND INDEX STATS : Object name with schema : AT "2010-04-09-08.34.14.436386" : BY "Fabricate" : start
OBJECT  : Object name with schema, 13 bytes
SAPECC  .SOSC
IMPACT  : None
...
2010-04-09-08.34.14.438151+000 E41948A663         LEVEL: Event
PID     : 11698366             TID  : 29814       PROC : db2sysc 0
INSTANCE: db2ecp               NODE : 000         DB   : ECP
APPHDL  : 0-16501              APPID: 148.175.216.102.33889.100409025
AUTHID  : SAPECC
EDUID   : 29814                EDUNAME: db2agent (ECP) 0
FUNCTION: DB2 UDB, SW- optimizer, sqlno_runstats_or_fabrication, probe:1000
COLLECT : TABLE AND INDEX STATS : Object name with schema : AT "2010-04-09-08.34.14.437897" : BY "Fabricate" : success
OBJECT  : Object name with schema, 13 bytes
SAPECC  .SOSC
IMPACT  : None
DATA #1 : String, 30 bytes
RUNSTATS command not available

12. How is RUNSTATS Utility Triggered by Automatic RUNSTATS?


If a RUNSTATS utility is triggered by automatic RUNSTATS, the following RUNSTATS command is executed:
RUNSTATS ON TABLE <SCHEMA>.<TABLE NAME> WITH DISTRIBUTION AND SAMPLED DETAILED INDEXES ALL

The "WITH DISTRIBUTION" clause specifies that except for basic statistics, distribution statistics are to be collected on columns as well.

You can use the following options to determine how distribution statistics on columns are collected:

  • NUM_FREQVALUES:

    Defines the maximum number of frequency values to collect
  • NUM_QUANTILES:

    Defines the maximum number of distribution quantile values to collect


If either option is not specified in the command, the values of NUM_FREQVALUES and NUM_QUANTILES database configuration parameters will be taken respectively. In an SAP environment, the default value for these 2 database parameters are as follows:
Number of frequent values retained     (NUM_FREQVALUES) = 10
Number of quantiles retained            (NUM_QUANTILES) = 20

Normally, the default settings are sufficient and you do not have to change the values manually.

13. What is a statistics profile?


The RUNSTATS utility provides an option to register and to use a statistics profile. A statistics profile allows you to store options that you like to specify when issuing a RUNSTATS command so that you can collect the specific statistics repeatedly on a table without having to re-type the command options.

The statistics profile is saved in the SYSCAT.TABLES catalog table. Automatic statistics collection respects the provided RUNSTATS profile if the automatic maintenance database configuration parameter AUTO_STATS_PROF is set to ON.

For a detailed description about statistics profile, see the IBM DB2 Version 9.5 Information Center at:

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.admin.perf.doc/doc/c0011392.html

14. What is the performance overhead of automatic RUNSTATS and RTS?


Automatic RUNSTATS always runs in throttled mode  and tries not to have any severe impact on your current workload. The throttling is controlled by a fixed impact rate and cannot be controlled by the available external switches. Automatic RUNSTATS needs certain locks to update the system catalog but these are all low-level locks. If there is a locking conflict, automatic RUNSTATS always is the victim of a lock conflict. If automatic RUNSTATS cannot acquire a lock, it does not wait; it processes the next table in the queue.

However, statistics that are collected for a table are cached and materialized during the next asynchronous check for statistics collection if they cannot be materialized immediately due to lock situations on system catalog tables.

Since RTS jumps in synchronously, it can - in some cases - have a negative impact on your current workload. In particular, volatile tables that are modified very often can lead to ongoing RTS activities, and therefore, to higher I/O and CPU consumption. In these cases, setting the volatile flag for these tables on database level can be an option to avoid heavy RTS activities. Whether a table can be flagged as volatile, however, requires a detailed workload analysis.

For a list of tables that are delivered with a volatile flag by SAP, see SAP Note 1425488.

Except the way mentioned in SAP Note 1425488, you can use following command to switch a table to be volatile:

db2 "ALTER TABLE <table schema>.<table name> VOLATILE"

In general, we do not recommend to manually switch on table volatile other than those mentioned in SAP Note 1425488.

15. How does automatic statistics collection work in a Partitioned Database (DPF) Environment?


In a DPF environment, statistics are collected on a single database partition and then the collected statistics are extrapolated assuming uniformity across partitions. The database manager always collects statistics (both synchronous and asynchronous) on the first database partition of the database partition group.

Example:
Consider a SAP NetWeaver BW system on a DB2 database with 16 partitions:Partition group 1 contains master data tables (for example, /BI0/YEXAMPLEY), which are normally very small, on partition 0, normally this is also the coordinator partition which SAP applications are connecting to. Partition group 2 contains big fact tables (for example, /BI0/F0DB6_C05H) evenly distributed on partition 1-15.

Automatic statistics collection on table /BI0/YEXAMPLEY runs on partition 0, and automatic statistics collection on table /BI0/F0DB6_C05H will run on partition 1 (first partition with table data) and extrapolate to other partitions in partition group 2.

16. When do automatic RUNSTATS and RTS not work?

Automatic statistics collection and RTS do not work for:

  • Statistical views
  • Tables that are marked volatile (tables with the VOLATILE flag set in the SYSCAT.TABLES catalog view)
  • Tables whose statistics were manually updated by UPDATE statements that were issued directly against SYSSTAT catalog views


If you modify the statistics manually for tables, the database manager assumes that you are now responsible for maintaining these table statistics. To allow the database manager to reconsider and maintain statistics for a table  whose statistics were manually updated, collect statistics using the RUNSTATS command or specify to collect statistics when the LOAD command is used.

Tables that were created prior to DB2 V9.5 and that had their statistics updated manually prior to migration are not affected and their statistics are automatically maintained by the database manager until you manually update their statistics. With DB2 9.7 and higher, a special DB2 registry variable under DB2_WORKLOAD=SAP retains the asynchronous check for automatic statistics collection and RTS, even though statistics are updated manually - as shown in the following example:
[i] DB2_ATM_CMD_LINE_ARGS=-include-manual-tables [DB2_WORKLOAD]


17. How does the online maintenance window affect automatic RUNSTATS?


A maintenance window is a time period that you define for running automatic maintenance activities, for example, backups, statistics collection, statistics profiling, and reorganizations.

Online maintenance activities (automatic statistics collection and profiling, online index reorganizations, and online database backups) are maintenance activities that can take place  only in the online maintenance window.

If online maintenance activities are running, any currently connected application is allowed to remain connected, and new connections can be established. To minimize the impact on the system, online database backups and automatic statistics collection and profiling are throttled by the adaptive utility throttling mechanism.

You can check and change your current setting for the  online maintenance window setting with the DBA Cockpit as follows:

In your SAP system, call transaction DBACOCKPIT and choose -> Configuration-> Automatic Maintenance Settings in the navigation frame of the DBA Cockpit (SAP GUI-based user interface).

With the online maintenance window enabled, asynchronous automatic statistics collection only starts in the defined timeframe. However, if an automatic RUNSTATS starts, it runs to completion even if it goes beyond the specified window.

18. SAP Recommendations


We recommend that you enable DB2's automatic RUNSTATS function instead of manually scheduling RUNSTATS jobs for DB2 UDB Version 8.2 and higher. If your database has been upgraded from a previous version, read SAP Note 860724 for information about how to enable automatic RUNSTATS and RTS.

Note that since the statistics cache is part of catalog cache, the catalog cache might need to be increased with RTS switched on.

19. Known DB2 Issues and Corrections

The following are known APARs of DB2 that are related to automatic statistics collection:

  • DB2 Version 9.1:
    • IZ57365: ERROR SQL0429N ENCOUNTERED WHEN RUNNING AUTO RUNSTATS ON OVER 32KTABLES WITH SET STATISTICS PROFILE OPTION.
    • IZ53532: SQL0901 with token "Potential corruption in statistics.", from sqlno_prds_is_NPK_adjust_eligible()
    • JR32131: SQL2025N WHEN STATISTICS PROFILE FEATURE IS ENABLED
    • IZ26298: DATABASE CRASHES IF THERE IS A FILE ERROR AND AUTONOMIC STATISTICS PROFILING IS ENABLED.
    • LI73125: DETAILED INDEX STATISTICS MAY CAUSE OPTIMIZER TO FAVOUR HSJN PLAN OVER A NLJN PLAN WHERE NLJN PLAN IS MORE FAVOURABLE
    • IZ02697: AUTOMATIC STATISTICS PROFILING DOES NOT POPULATE PROFILE IF NONEINITIALLY EXISTS
    • IY95975: AUTO RUNSTATS DOES NOT ISSUE RUNSTATS ON TABLES WITHOUT STATISTICS IN DPF
    • IY97452: OPTIMIZER DOES NOT USE COLUMN STATISTICS CORRECTLY FOR REAL COLUMNS.
    • IY84169: INCONSISTENT STATISTICS WITH 'CREATE INDEX ... COLLECT STATISTICS' COMMAND AND 'RUNSTATS' COMMAND WHEN VIEWING THE CATALOG
  • DB2 Version 9.5:
    • LI73953: MEMORY LEAK WITH STAT_HEAP_SZ WHILE EXECUTING REDISTRIBUTE STATISTICS USE PROFILE
    • IZ56136: SQL0901 with token "Potential corruption in statistics.", from sqlno_prds_is_NPK_adjust_eligible()
    • JR32132: SQL2025N WHEN STATISTICS PROFILE FEATURE IS ENABLED
    • IZ37191: AUTOMATIC STATISTICS COLLECTION MAY SOMETIMES SLOW DOWN TRANSACTION PROCESSING
    • IZ40984: Instance abend during Auto-Runstats or Real Time Statistics collection due to data is re-distributed in DPF system.
    • IZ35881: IGNORE STATISTICS COLLECTED ON TABLES MARKED AS VOLATILE WHEN DB2_WORKLOAD=SAP IS ENABLED
    • IZ31655: TABLE PACK DESCRIPTOR CORRUPTION BY STATISTICS FABRICATION IN REAL-TIME-STATISTICS FEATURE.
    • IZ26790: DATABASE CRASHES IF THERE IS A FILE ERROR AND AUTONOMIC STATISTICS PROFILING IS ENABLED.
    • IZ31409: AUTOMATIC STATISTICS COLLECTION FAILS WITH AN INTERNAL ERROR.
    • LI73413: TABLE OR INDEX STATISTICS CORRUPTION MIGHT CAUSE A DB2 SERVER ABEND
    • IZ07009: STATS FABRICATION BEHAVIOR INCONSISTENCE WHEN TABLE IS EMPTY.
    • IZ13767: WHEN REAL-TIME STATISTICS FABRICATED STATS, IT MIGHT CORRUPT THE COLUMN PACKED DESCRIPTOR
    • LI73220: REAL-TIME STATISTICS ON TABLE WITH DEFAULT VALUE MAY RESULT IN -901


20. Known SAP Issues and Corrections


The following SAP Notes contain information related to issues and corrections of automatic statistics collection:

  • Note 533589 - DB6: SQL2314W Some statistics are in an inconsistent state
  • Note 1381591 - DB6: Database crashes when fabricating statistics
  • Note 1309705 - DB6: Log space not released by statistics update
  • Note 1160813 - DB6: Accessing a table returns SQL0901N
  • Note 1425488 - DB6: Performance problems with volatile tables
  • Note 966970 - DB6: New tables remain VOLATILE for AutoRunstats
  • Note 1472292 - DB6: Slow performance due to wrong stats for BW temp tables

Related Notes
1425488DB6: Performance problems with volatile tables
1381591DB6: Database crashes when fabricating statistics
1309705DB6: Log space not released by statistics update
1160813DB6: Accessing a table returns SQL0901N
966970DB6: New tables remain VOLATILE for AutoRunstats
533589DB6: SQL2314W Some statistics are in an inconsistent state