Identifying how a statement affects database objects
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.
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 BASEEnabling 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
SET EVENT MONITOR ACTEVMON STATE 0