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 WL1 COLLECT ACTIVITY DATA 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.