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.
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.