db2 9.7 Collecting lock event data and generating reports

You can use the lock event monitor to collect lock timeout, lock wait, and deadlock information to help identify and resolve locking problems. After the lock event data has been collected in an unreadable form in an unformatted event table, this task describes how to obtain a readable text report.

About this task

The lock event monitor collects relevant information that helps with the identification and resolution of locking problems. For example, some of the information the lock event monitor collects for a lock event is as follows:

• The lock that resulted in a lock event
• The applications requesting or holding the lock that resulted in a lock event
• What the applications were doing during the lock event

This task provides instructions for collecting lock event data for a given workload. You might want to collect lock event data under the following conditions:

• You notice that lock wait values are longer than usual when using the MON_GET_WORKLOAD table function.
• An application returns a -911 SQL return code with reason code 68 in the administration notification log, stating that "The transaction was rolled back due to a lock timeout." See also message SQL0911N for further details.
• You notice a deadlock event message in the administration notification log (-911 SQL return code with reason code 2, stating that "The transaction was rolled back due to a deadlock."). The log message indicates that the lock event occurred between two applications, for example, Application A and B, where A is part of workload FINANCE and B is part of workload PAYROLL. See also message SQL0911N for further details.

Restrictions

To view data values, you need the EXECUTE privilege on the EVMON_FORMAT_UE_* routines, which the SQLADM and DBADM authorities hold implicitly. You also need SELECT privilege on the unformatted event table table, which by default is held by users with the DATAACCESS authority and by the creator of the event monitor and the associated unformatted event table.

Before you begin

To create the locking event monitor and collect lock event monitor data, you must have DBADM, or SQLADM authority.

Procedure

To collect detailed information regarding potential future lock events, perform the following steps:

1. Create a lock event monitor called lockevmon by using the CREATE EVENT MONITOR FOR LOCKING statement, as shown in the following example:

   CREATE EVENT MONITOR lockevmon FOR LOCKING
      WRITE TO UNFORMATTED EVENT TABLE

   Note: The following lists important points to remember when creating an event monitor:

   • You can create event monitors ahead of time and not worry about using up disk space since nothing is written until you activate the data collection at the database or workload level
   • In a partitioned database environment, ensure that the event monitors are placed in a partitioned table space across all nodes. Otherwise, lock events will be missed at partitions where the partitioned table space is not present.
   • Ensure that you set up a table space and bufferpool to minimize the interference on high performance work caused by ongoing work during accesses to the tables to obtain data.
2. Activate the lock event monitor called lockevmon by running the following statement:

   SET EVENT MONITOR lockevmon STATE 1

3. To enable the lock event data collection at the workload level, issue the ALTER WORKLOAD statement with one of the following COLLECT clauses: COLLECT LOCK TIMEOUT DATA, COLLECT DEADLOCK DATA, or COLLECT LOCK WAIT DATA. Specify the WITH HISTORY option on the COLLECT clause. Setting the database configuration parameter affects the lock event data collection at the database level and all workloads are affected.

   For lock wait events

   To collect lock wait data for any lock acquired after 5 seconds for the FINANCE application and to collect lock wait data for any lock acquired after 10 seconds for the PAYROLL application, issue the following statements:

   ALTER WORKLOAD finance COLLECT LOCK WAIT DATA WITH HISTORY AND VALUES
      FOR LOCKS WAITING MORE THAN 5 SECONDS
   ALTER WORKLOAD payroll COLLECT LOCK WAIT DATA 
      FOR LOCKS WAITING MORE THAN 10 SECONDS WITH HISTORY

   To set the mon_lockwait database configuration parameter with HIST_AND_VALUES input data value for the SAMPLE database, and to set the mon_lw_thresh database configuration parameter for 10 seconds, issue the following commands:

   db2 update db cfg for sample using mon_lockwait hist_and_values
   db2 update db cfg for sample using mon_lw_thresh 10000000

   For lock timeout events

   To collect lock timeout data for the FINANCE and PAYROLL applications, issue the following statements:

   ALTER WORKLOAD finance COLLECT LOCK TIMEOUT DATA WITH HISTORY
   ALTER WORKLOAD payroll COLLECT LOCK TIMEOUT DATA WITH HISTORY

   To set the mon_locktimeout database configuration parameter with HIST_AND_VALUES input data value for the SAMPLE database, issue the following command:

   db2 update db cfg for sample using mon_locktimeout hist_and_values

   For deadlock events

   To collect data for the FINANCE and PAYROLL applications, issue the following statements:

   ALTER WORKLOAD finance COLLECT DEADLOCK DATA WITH HISTORY
   ALTER WORKLOAD payroll COLLECT DEADLOCK DATA WITH HISTORY

   To set the mon_deadlock database configuration parameter with HIST_AND_VALUES input data value for the SAMPLE database, issue the following command:

   db2 update db cfg for sample using mon_deadlock hist_and_values

4. Rerun the workload in order to receive another lock event notification.
5. Connect to the database.
6. Obtain the locking event report using one of the following approaches:
   1. Use the XML parser tool, db2evmonfmt, to produce a flat-text report based on the event data collected in the unformatted event table and using the default stylesheet, for example:

      java db2evmonfmt -d db_name -ue table_name -ftext -u user_id -p password

   2. Use the EVMON_FORMAT_UE_TO_XML table function to obtain an XML document.
   3. Use the EVMON_FORMAT_UE_TO_TABLES procedure to output the data into a relational table.
7. Analyze the report to determine the reason for the lock event problem and resolve it.
8. Turn OFF lock data collection for both FINANCE and PAYROLL applications by running the following statements or resetting the database configuration parameters:

   For lock wait events

   ALTER WORKLOAD finance COLLECT LOCK WAIT DATA NONE
   ALTER WORKLOAD payroll COLLECT LOCK WAIT DATA NONE

   To reset the mon_lockwait database configuration parameter with the default NONE input data value for the SAMPLE database, and to reset the mon_lw_thresh database configuration parameter back to its default value of 5 seconds, issue the following command:

   db2 update db cfg for sample using mon_lockwait none
   db2 update db cfg for sample using mon_lw_thresh 5000000

   For lock timeout events

   ALTER WORKLOAD finance COLLECT LOCK TIMEOUT DATA NONE
   ALTER WORKLOAD payroll COLLECT LOCK TIMEOUT DATA NONE

   To reset the mon_locktimeout database configuration parameter with the default NONE input data value for the SAMPLE database, issue the following command:

   db2 update db cfg for sample using mon_locktimeout none

   For deadlock events

   ALTER WORKLOAD finance COLLECT DEADLOCK DATA NONE
   ALTER WORKLOAD payroll COLLECT DEADLOCK DATA NONE

   To reset the mon_deadlock database configuration parameter with the default WITHOUT_HIST input data value for the SAMPLE database, issue the following command:

   db2 update db cfg for sample using mon_deadlock without_hist