DB2 10.5 for Linux, UNIX, and Windows

Capturing and accessing section actuals

Section actuals are runtime statistics collected during the execution of the section for an access plan. To capture a section with actuals, you use the activity event monitor. To access the section actuals, you perform a section explain using the EXPLAIN_FROM_ACTIVITY stored procedure.

To be able to view section actuals, you must perform a section explain on a section for which section actuals were captured (that is, both the section and the section actuals are the inputs to the explain facility). Information about enabling, capturing, and accessing section actuals is provided here.

Enabling section actuals

Section actuals will only be updated at runtime if they have been enabled. Enable section actuals for the entire database using the section_actuals database configuration parameter or for a specific application using the WLM_SET_CONN_ENV procedure.

Section actuals will only be updated at runtime if they have been enabled. Enable section actuals using the section_actuals database configuration parameter. To enable section actuals, set the parameter to BASE (the default value is NONE). For example:

db2 update database configuration using section_actuals base

To enable section actuals for a specific application, use the WLM_SET_CONN_ENV procedure and specify BASE for the section_actuals element. For example:

CALL WLM_SET_CONN_ENV(NULL, 
     '<collectactdata>WITH DETAILS, SECTION</collectactdata> 
      <collectsectionactuals>BASE</collectsectionactuals>
     ')

Note:

The setting of the section_actuals database configuration parameter that was in effect at the start of the unit of work is applied to all statements in that unit of work. When the section_actuals database configuration parameter is changed dynamically, the new value will not be seen by an application until the next unit of work.
The section_actuals setting specified by the WLM_SET_CONN_ENV procedure for an application takes effect immediately. Section actuals will be collected for the next statement issued by the application.
Section actuals cannot be enabled if automatic statistics profile generation is enabled (SQLCODE -5153).

Capturing section actuals

The mechanism for capturing a section, with section actuals, is the activity event monitor. An activity event monitor writes out details of an activity when the activity completes execution, if collection of activity information is enabled. Activity information collection is enabled using the COLLECT ACTIVITY DATA clause on a workload, service class, threshold, or work action. To specify collection of a section and actuals (if the latter is enabled), the SECTION option of the COLLECT ACTIVITY DATA clause is used. For example, the following statement indicates that any SQL statement, issued by a connection associated with the WL1 workload, will have information (including section and actuals) collected by any active activity event monitor when the statement completes:

ALTER WORKLOAD W1 COLLECT ACTIVITY DATA on coordinator WITH
    DETAILS,SECTION

In a partitioned database environment, section actuals are captured by an activity event monitor on all partitions where the activity was executed, if the statement being executed has a COLLECT ACTIVITY DATA clause applied to it and the COLLECT ACTIVITY DATA clause specifies both the SECTION keyword and the ON ALL DATABASE PARTITIONS clause. If the ON ALL DATABASE PARTITIONS clause is not specified, then actuals are captured on only the coordinator partition. In addition, besides the COLLECT ACTIVITY DATA clause on a workload, service class, threshold, or work action, activity collection can be enabled (for an individual application) using the WLM_SET_CONN_ENV procedure with a second argument that includes the collectactdata tag with a value of "WITH DETAILS, SECTION".

Limitations

The limitations, with respect to the capture of section actuals, are the following:

Section actuals will not be captured when the WLM_CAPTURE_ACTIVITY_IN_PROGRESS stored procedure is used to send information about a currently executing activity to an activity event monitor. Any activity event monitor record generated by the WLM_CAPTURE_ACTIVITY_IN_PROGRESS stored procedure will have a value of 1 in its partial_record column.
When a reactive threshold has been violated, section actuals will be captured on only the coordinator partition.
Explain tables must be migrated to DB2® Version 9.7 Fix Pack 1, or later, before section actuals can be accessed using a section explain. If the explain tables have not been migrated, the section explain will work, but section actuals information will not be populated in the explain tables. In this case, an entry will be written to the EXPLAIN_DIAGNOSTIC table.
Existing DB2 V9.7 activity event monitor tables (in particular, the activity table) must be recreated before section actuals data can be captured by the activity event monitor. If the activity logical group does not contain the SECTION_ACTUALS column, a section explain may still be performed using a section captured by the activity event monitor, but the explain will not contain any section actuals data.

Accessing section actuals

Section actuals can be accessed using the EXPLAIN_FROM_ACTIVITY procedure. When you perform a section explain on an activity for which section actuals were captured, the EXPLAIN_ACTUALS explain table will be populated with the actuals information.

Note: Section actuals are only available when a section explain is performed using the EXPLAIN_FROM_ACTIVITY procedure.

The EXPLAIN_ACTUALS table is the child table of the existing EXPLAIN_OPERATOR explain table. When EXPLAIN_FROM_ACTIVITY is invoked, if the section actuals are available, the EXPLAIN_ACTUALS table will be populated with the actuals data. If the section actuals are collected on multiple database partitions, there is one row per database partition for each operator in the EXPLAIN_ACTUALS table.