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.
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:
Possible values for automatic statistics collection
are:
|
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:
|
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:
|
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:
|
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:
|
Examples
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));
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