Use
a section explain that includes section actuals information to identify
how a statement affects database objects. You can use statistics about
how the statement section affected each table or index to determine
whether additional monitoring or tuning is required.
Before you begin
Do the following tasks:
- Identify a statement for which you want to view object usage statistics.
- Ensure that you migrated your explain tables to the database system.
- Ensure that automatic statistics profile generation is not enabled.
- Ensure that you have the privileges that are required to call
the EXPLAIN_FROM_ACTIVITY procedure.
About this task
After you identify a statement for which you want to view
object usage statistics, you can get a section explain that includes
section actuals information. Section actuals information indicates
how the statement affected each table or index that the statement
used when it executed.
Actuals information includes runtime
statistics about factors like locks and buffer pool usage for each
table or index. You can compare these statistics to baseline data
and use them to determine where additional monitoring or tuning might
be required.
Procedure
To determine how database objects are affected by a statement:
- Enable the collection of section actuals at the database
level by issuing the following command:
DB2 UPDATE DATABASE CONFIGURATION USING SECTION_ACTUALS BASE
- Create a workload to collect section actuals information
for activities that are submitted by the application that issues the
statement.
For example, to create the ACTWORKLOAD workload
for activities that are submitted by the TEST application and enable
collection for those activities, issue the following command:
CREATE WORKLOAD ACTWORKLOAD APPLNAME ('TEST')
COLLECT ACTIVITY DATA ON ALL WITH DETAILS,SECTION INCLUDE ACTUALS BASE
Enabling
collection of section actuals can also be accomplished in the following
ways:
- The CREATE SERVICE CLASS or ALTER SERVICE CLASS statement
- The CREATE WORK ACTION SET or ALTER WORK ACTION SET statement
- The WLM_SET_CONN_ENV procedure
- The section_actuals configuration parameter
- Create an activity event monitor by using the CREATE EVENT
MONITOR statement.
For example, to create the ACTEVMON
activity event monitor, issue the following command:
CREATE EVENT MONITOR ACTEVMON
FOR ACTIVITIES
WRITE TO TABLE
CONTROL (TABLE CONTROL_ACTEVMON ),
ACTIVITY (TABLE ACTIVITY_ACTEVMON ),
ACTIVITYSTMT (TABLE ACTIVITYSTMT_ACTEVMON ),
ACTIVITYVALS (TABLE ACTIVITYVALS_ACTEVMON ),
ACTIVITYMETRICS (TABLE ACTIVITYMETRICS_ACTEVMON )
- Activate the activity event monitor that you created by
using the SET EVENT MONITOR STATE statement.
For example,
to activate the ACTEVMON activity event monitor, issue the following
command:
SET EVENT MONITOR ACTEVMON STATE 1
- Run the application that issues the statement
for which you want to view object statistics.
- Find identifier information for the statement section by
using the following command to query the activity event monitor tables:
SELECT APPL_ID,
UOW_ID,
ACTIVITY_ID,
STMT_TEXT
FROM ACTIVITYSTMT_ACTEVMON
- Obtain a section explain with actuals by using the activity
identifier information as input for the EXPLAIN_FROM_ACTIVITY procedure.
For example, to obtain a section explain for a section with
an application ID of *N2.DB2INST1.0B5A12222841, a unit of work ID
of 16, and an activity ID of 4, issue the following command:
CALL EXPLAIN_FROM_ACTIVITY( '*N2.DB2INST1.0B5A12222841', 16, 4, 'ACTEVMON',
'MYSCHEMA', ?, ?, ?, ?, ? )
You
get output that looks like the following sample output:
Value of output parameters
--------------------------
Parameter Name : EXPLAIN_SCHEMA
Parameter Value : MYSCHEMA
Parameter Name : EXPLAIN_REQUESTER
Parameter Value : GSDBUSER3
Parameter Name : EXPLAIN_TIME
Parameter Value : 2010-11-23-10.51.09.631945
Parameter Name : SOURCE_NAME
Parameter Value : SQLC2J21
Parameter Name : SOURCE_SCHEMA
Parameter Value : NULLID
Parameter Name : SOURCE_VERSION
Parameter Value :
Return Status = 0
- Format the explain data by using the db2exfmt command.
Use the values of the explain_requester, explain_time, source_name, source_schema,
and source_version parameters in the output from
the EXPLAIN_FROM_ACTIVITY procedure as input for the command.
- View the explain output to determine how the
section affected the database objects that it used when it executed.
Statistics in the output might indicate that additional monitoring
or tuning is required.
For example, if a table that
the section uses has a high value for the lock_wait monitor
element, lock management might be required.
- If you tune the statement, repeat steps 5 through 9 to verify that performance is improved.
What to do next
Deactivate the activity event monitor by using the SET
EVENT MONITOR STATE statement. For example, to deactivate the ACTEVMON
activity event monitor, issue the following command:
SET EVENT MONITOR ACTEVMON STATE 0