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.

To determine how database objects are affected by a statement:

1. Enable the collection of section actuals at the database level by issuing the following command:

   DB2 UPDATE DATABASE CONFIGURATION USING SECTION_ACTUALS BASE

2. 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
3. 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 )

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

5. Run the application that issues the statement for which you want to view object statistics.
6. 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

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

8. 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.
9. 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.
10. If you tune the statement, repeat steps 5 through 9 to verify that performance is improved.

SET EVENT MONITOR ACTEVMON STATE 0