DB2 10.5 for Linux, UNIX, and Windows

Automatic statistics collection activity logging

The statistics log is a record of all of the statistics collection activities (both manual and automatic) that have occurred against a specific database.

The default name of the statistics log is db2optstats.number.log. It resides in the $diagpath/events directory. The statistics log is a rotating log. Log behavior is controlled by the DB2_OPTSTATS_LOG registry variable.

The statistics log can be viewed directly or it can be queried using the SYSPROC.PD_GET_DIAG_HIST table function. This table function returns a number of columns containing standard information about any logged event, such as the timestamp, DB2® instance name, database name, process ID, process name, and thread ID. The log also contains generic columns for use by different logging facilities. The following table describes how these generic columns are used by the statistics log.

Table 1. Generic columns in the statistics log file
Column name	Data type	Description
OBJTYPE	VARCHAR(64)	The type of object to which the event applies. For statistics logging, this is the type of statistics to be collected. OBJTYPE can refer to a statistics collection background process when the process starts or stops. It can also refer to activities that are performed by automatic statistics collection, such as a sampling test, initial sampling, and table evaluation. Possible values for statistics collection activities are: TABLE STATS Table statistics are to be collected. INDEX STATS Index statistics are to be collected. TABLE AND INDEX STATS Both table and index statistics are to be collected. Possible values for automatic statistics collection are: EVALUATION The automatic statistics background collection process has begun an evaluation phase. During this phase, tables will be checked to determine if they need updated statistics, and statistics will be collected, if necessary. INITIAL SAMPLING Statistics are being collected for a table using sampling. The sampled statistics are stored in the system catalog. This allows automatic statistics collection to proceed quickly for a table with no statistics. Subsequent operations will collect statistics without sampling. Initial sampling is performed during the evaluation phase of automatic statistics collection. SAMPLING TEST Statistics are being collected for a table using sampling. The sampled statistics are not stored in the system catalog. The sampled statistics will be compared to the current catalog statistics to determine if and when full statistics should be collected for this table. The sampling is performed during the evaluation phase of automatic statistics collection. STATS DAEMON The statistics daemon is a background process used to handle requests that are submitted by real-time statistics processing. This object type is logged when the background process starts or stops.
OBJNAME	VARCHAR(255)	The name of the object to which the event applies, if available. For statistics logging, this is the table or index name. If OBJTYPE is STATS DAEMON or EVALUATION, OBJNAME is the database name and OBJNAME_QUALIFIER is NULL.
OBJNAME_QUALIFIER	VARCHAR(255)	For statistics logging, this is the schema of the table or index.
EVENTTYPE	VARCHAR(24)	The event type is the action that is associated with this event. Possible values for statistics logging are: COLLECT This action is logged for a statistics collection operation. START This action is logged when the real-time statistics background process (OBJTYPE = STATS DAEMON) or an automatic statistics collection evaluation phase (OBJTYPE = EVALUATION) starts. STOP This action is logged when the real-time statistics background process (OBJTYPE = STATS DAEMON) or an automatic statistics collection evaluation phase (OBJTYPE = EVALUATION stops. ACCESS This action is logged when an attempt has been made to access a table for statistics collection purposes. This event type is used to log an unsuccessful access attempt when the object is unavailable. WRITE This action is logged when previously collected statistics that are stored in the statistics cache are written to the system catalog.
FIRST_EVENTQUALIFIERTYPE	VARCHAR(64)	The type of the first event qualifier. Event qualifiers are used to describe what was affected by the event. For statistics logging, the first event qualifier is the timestamp for when the event occurred. For the first event qualifier type, the value is AT.
FIRST_EVENTQUALIFIER	CLOB(16k)	The first qualifier for the event. For statistics logging, the first event qualifier is the timestamp for when the statistics event occurred. The timestamp of the statistics event might be different than the timestamp of the log record, as represented by the TIMESTAMP column.
SECOND_EVENTQUALIFIERTYPE	VARCHAR(64)	The type of the second event qualifier. For statistics logging, the value can be BY or NULL. This field is not used for other event types.
SECOND_EVENTQUALIFIER	CLOB(16k)	The second qualifier for the event. For statistics logging, this represents how statistics were collected for COLLECT event types. Possible values are: User Statistics collection was performed by a DB2 user invoking the LOAD, REDISTRIBUTE, or RUNSTATS command, or issuing the CREATE INDEX statement. Synchronous Statistics collection was performed at SQL statement compilation time by the DB2 server. The statistics are stored in the statistics cache but not the system catalog. Synchronous sampled Statistics collection was performed using sampling at SQL statement compilation time by the DB2 server. The statistics are stored in the statistics cache but not the system catalog. Fabricate Statistics were fabricated at SQL statement compilation time using information that is maintained by the data and index manager. The statistics are stored in the statistics cache but not the system catalog. Fabricate partial Only some statistics were fabricated at SQL statement compilation time using information that is maintained by the data and index manager. In particular, only the HIGH2KEY and LOW2KEY values for certain columns were fabricated. The statistics are stored in the statistics cache but not the system catalog. Asynchronous Statistics were collected by a DB2 background process and are stored in the system catalog. This field is not used for other event types.
THIRD_EVENTQUALIFIERTYPE	VARCHAR(64)	The type of the third event qualifier. For statistics logging, the value can be DUE TO or NULL.
THIRD_EVENTQUALIFIER	CLOB(16k)	The third qualifier for the event. For statistics logging, this represents the reason why a statistics activity could not be completed. Possible values are: Timeout Synchronous statistics collection exceeded the time budget. Error The statistics activity failed due to an error. RUNSTATS error Synchronous statistics collection failed due to a RUNSTATS error. For some errors, SQL statement compilation might have completed successfully, even though statistics could not be collected. For example, if there was insufficient memory to collect statistics, SQL statement compilation will continue. Object unavailable Statistics could not be collected for the database object because it could not be accessed. Some possible reasons include: The object is locked in super exclusive (Z) mode The table space in which the object resides is unavailable The table indexes need to be recreated Conflict Synchronous statistics collection was not performed because another application was already collecting synchronous statistics. Check the FULLREC column or the db2diag log files for the error details.
EVENTSTATE	VARCHAR(255)	State of the object or action as a result of the event. For statistics logging, this indicates the state of the statistics operation. Possible values are: Start Success Failure

Example

In this example, the query returns statistics log records for events up to one year prior to the current timestamp by invoking PD_GET_DIAG_HIST.

   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) as event2,
       substr(third_eventqualifiertype, 1, 6) as event3_type,
       substr(third_eventqualifier, 1, 15) as 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));

The results are ordered by the timestamp stored in the FIRST_EVENTQUALIFIER column, which represents the time of the statistics event.

PID   TID           EVENTTYPE OBJTYPE                OBJSCHEMA OBJNAME    EVENT1                     EVENT2_ EVENT2               EVENT3_ EVENT3   EVENTSTATE
                                                                                                     TYPE                         TYPE
----- ------------- --------- ---------------------  --------- ---------- -------------------------- ------- -------------------- ------- -------- ----------
28399    1082145120 START     STATS DAEMON           -         PROD_DB    2007-07-09-18.37.40.398905 -       -                    -       -        success
28389  183182027104 COLLECT   TABLE AND INDEX STATS  DB2USER   DISTRICT   2007-07-09-18.37.43.261222 BY      Synchronous          -       -        start
28389  183182027104 COLLECT   TABLE AND INDEX STATS  DB2USER   DISTRICT   2007-07-09-18.37.43.407447 BY      Synchronous          -       -        success
28399    1082145120 COLLECT   TABLE AND INDEX STATS  DB2USER   CUSTOMER   2007-07-09-18.37.43.471614 BY      Asynchronous         -       -        start
28399    1082145120 COLLECT   TABLE AND INDEX STATS  DB2USER   CUSTOMER   2007-07-09-18.37.43.524496 BY      Asynchronous         -       -        success
28399    1082145120 STOP      STATS DAEMON           -         PROD_DB    2007-07-09-18.37.43.526212 -       -                    -       -        success
28389  183278496096 COLLECT   TABLE STATS            DB2USER   ORDER_LINE 2007-07-09-18.37.48.676524 BY      Synchronous sampled  -       -        start
28389  183278496096 COLLECT   TABLE STATS            DB2USER   ORDER_LINE 2007-07-09-18.37.53.677546 BY      Synchronous sampled  DUE TO  Timeout  failure
28389    1772561034 START     EVALUATION             -         PROD_DB    2007-07-10-12.36.11.092739 -       -                    -       -        success
28389    8231991291 COLLECT   TABLE AND INDEX STATS  DB2USER   DISTRICT   2007-07-10-12.36.30.737603 BY      Asynchronous         -       -        start
28389    8231991291 COLLECT   TABLE AND INDEX STATS  DB2USER   DISTRICT   2007-07-10-12.36.34.029756 BY      Asynchronous         -       -        success
28389    1772561034 STOP      EVALUATION             -         PROD_DB    2007-07-10-12.36.39.685188 -       -                    -       -        success
28399    1504428165 START     STATS DAEMON           -         PROD_DB    2007-07-10-12.37.43.319291 -       -                    -       -        success
28399    1504428165 COLLECT   TABLE AND INDEX STATS  DB2USER   CUSTOMER   2007-07-10-12.37.43.471614 BY      Asynchronous         -       -        start
28399    1504428165 COLLECT   TABLE AND INDEX STATS  DB2USER   CUSTOMER   2007-07-10-12.37.44.524496 BY      Asynchronous         -       -        failure
28399    1504428165 STOP      STATS DAEMON           -         PROD_DB    2007-07-10-12.37.45.905975 -       -                    -       -        success
28399    4769515044 START     STATS DAEMON           -         PROD_DB    2007-07-10-12.48.33.319291 -       -                    -       -        success
28389    4769515044 WRITE     TABLE AND INDEX STATS  DB2USER   CUSTOMER   2007-07-10-12.48.33.969888 BY      Asynchronous         -       -        start
28389    4769515044 WRITE     TABLE AND INDEX STATS  DB2USER   CUSTOMER   2007-07-10-12.48.34.215230 BY      Asynchronous         -       -        success