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.
db2 update database configuration using section_actuals base
CALL WLM_SET_CONN_ENV(NULL,
'<collectactdata>WITH DETAILS, SECTION</collectactdata>
<collectsectionactuals>BASE</collectsectionactuals>
')
- 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
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
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.