Lock events for DB2 for Linux, UNIX, and Windows, Part 3: Use the lock event monitor in DB2 9.7 to solve concurrency issues

An integrated solution to analyze deadlocks, lock-timeouts, and lock waits

This is the third in a series of articles about the DB2® for Linux®, UNIX®, and Windows® options to analyze database concurrency issues. DB2 9.7 offers an integrated monitoring solution for deadlock, lock-timeout, and lock-wait analysis: the new lock event monitor. This article introduces the new lock event monitor and shows how you can use it to analyze concurrency issues.

Dirk Fechner (fechner@de.ibm.com), IT Services Specialist, IBM

Author photoDirk Fechner works as an IT Specialist for IBM Software Group Services. His area of expertise is design, implementation, and administration of DB2 databases on distributed platforms as well as database application development. He has ten years of experience with DB2 for Linux, UNIX, and Windows and is an IBM Certified Advanced Database Administrator and an IBM Certified Application Developer.


developerWorks Contributing author
        level

22 April 2010

Also available in Russian

Introduction

The article introduces the new lock event monitor and shows how you can use it to analyze concurrency issues. The other articles in the series (see Resources) explain the options available in DB2 8.2/9.1 and DB2 9.5, respectively. Those options have been further enhanced. DB2 9.7 offers an integrated monitoring solution for deadlock, lock-timeout, and lock-wait analysis: the new lock event monitor. This lock event monitor replaces the older mechanisms and utilities. Those mechanisms and utilities are deprecated starting with DB2 9.7, and they will be discontinued in a future DB2 release. The article introduces the new lock event monitor and shows how you can use it to analyze concurrency issues.

Creating a sample database

This article describes the lock event monitor with two sample concurrency issues. For the first example, create the DB2 sample database and set the DB CFG parameter LOCKTIMEOUT to a value of 10 seconds, as shown in Listing 1.

Listing 1. Creating the DB2 sample database and setting the LOCKTIMEOUT value
db2sampl
db2 "UPDATE DB CFG FOR SAMPLE USING LOCKTIMEOUT 10"

Information collected by the lock event monitor is written to an unformatted event table. It is called that way because data is stored in a binary format in the table. The unformatted event table should be placed in a separate tablespace. This prevents a tablespace storing table or index data from filling up unexpectedly because of too much monitor data. With DB2 automatic storage, it is easy to create an additional tablespace for the unformatted event table, because tablespace creation does not require any explicit tablespace container definitions. Listing 2 connects to the SAMPLE database and creates a new tablespace TBSPACE_LOCKING.

Listing 2. Creating a tablespace for lock event monitor data
db2 "CONNECT TO SAMPLE"
db2 "CREATE TABLESPACE TBSPACE_LOCKING"

Next, create the lock event monitor. Select a name of the lock event monitor itself and of its corresponding unformatted event table. In the example, the lock event monitor is called EVMON_LOCKING. The unformatted event table is called TAB_LOCKING in schema EMDATA. Specifying the IN-clause, the table is placed in its own tablespace TBSPACE_LOCKING.

Listing 3. Creating the lock event monitor
db2 "CREATE EVENT MONITOR EVMON_LOCKING FOR LOCKING WRITE TO UNFORMATTED EVENT TABLE
    (TABLE EMDATA.TAB_LOCKING IN TBSPACE_LOCKING)"

By default, a lock event monitor is created with the AUTOSTART option, so it is automatically started whenever the database is activated. To initially start the lock event monitor without re-activating the database, start it explicitly by setting its state to a value of 1, which is active, as shown in Listing 4.

Listing 4. Activating the lock event monitor
db2 "SET EVENT MONITOR EVMON_LOCKING STATE 1"

Defining the scope to generate lock events

Although the lock event monitor is now active, it does not start to collect information about concurrency issues for every session. Instead you need to specify the sessions that the lock event monitor should monitor. Specify the sessions at two levels:

DB2 Workload Manager (WLM) concepts

Workload, service class, work class, threshold, and so on are concepts related to the DB2 WLM that was first introduced in DB2 9.5. In this context, workloads are database objects that route new sessions to service classes according to session attributes such as application name, authorization ID, and so on. Even if there are no user-defined workloads, the DB2 default workload SYSDEFAULTUSERWORKLOAD exists, which contains all sessions not belonging to any other workload. DB2 routes those sessions to the DB2 default service class SYSDEFAULTUSERCLASS. So, even when DB2 WLM is not explicitly used, DB2's default WLM objects always exist. For overview and in-depth information about DB2 WLM, see Resources.

Database level
Starting with DB2 9.7, the DB CFG uses three new parameters: MON_DEADLOCK, MON_TIMEOUT, and MON_LOCKWAIT. When activating any of these parameters, the lock event monitor observes all database sessions independently of application name, authorization ID, and other parameters for the occurrence of the corresponding lock event: deadlock, lock-timeout, or lock-wait.
Workload-level
Alternatively, you can configure the lock event monitor to observe only sessions belonging to a certain workload. In this case, collection of lock event data is activated by setting a specific workload option.

Generally speaking, collection of data related to deadlock or lock-timeout events should be activated at the database level because those events are normally unexpected by applications. Therefore, a restriction to certain workloads in advance is usually not possible or even not desired. On the other hand, lock-wait events always occur to a certain degree. Thus limiting data collection for lock-wait events to a certain workload makes sense. The guidelines given here are very general, so check both options before deciding on the best monitoring strategy for your specific concurrency issue.


Collecting lock-timeout event data for a certain workload

In this example, use the lock event monitor to collect lock-timeout information only for a certain workload. To keep it simple, do not create a user-defined workload. Instead work with the DB2 WLM default workload SYSDEFAULTUSERWORKLOAD that contains all sessions that do not belong to a user-defined workload. For SYSDEFAULTUSERWORKLOAD, the lock event monitor captures all lock-timeout events. Because you already created and activated the lock event monitor EVMON_LOCKING, there is nothing more to do than to start lock event data collection for the desired workload, as shown in Listing 5.

Listing 5. Starting lock-timeout data collection for the default user workload
db2 "ALTER WORKLOAD SYSDEFAULTUSERWORKLOAD COLLECT LOCK TIMEOUT DATA WITH HISTORY"

Because of the WITH HISTORY option, the lock event monitor gathers:

  • The victim SQL statement of a lock-timeout event
  • All other SQL statements executed within the transactions involved in the lock-timeout event

Now, intentionally cause a lock-timeout by trying to make changes in the EMPLOYEE table using two different sessions. In the first session, execute a transaction that gives each employee a 2% raise and then queries all employees' names and their new salaries. Because this transaction is not committed (auto-commit is deactivated through the DB2 CLP option +c), it holds an X-lock (exclusive lock) on each row in the EMPLOYEE table, as shown in Listing 6.

Listing 6. First transaction - Giving all employees a 2% raise
db2 "CONNECT TO SAMPLE"
db2 +c "UPDATE EMPLOYEE SET SALARY = SALARY * 1.02"
db2 +c "SELECT LASTNAME, FIRSTNME, SALARY FROM EMPLOYEE ORDER BY LASTNAME ASC"

In another session, execute another transaction to give each manager a 10% bonus based on her or his current salary, as shown in Listing 7.

Listing 7. Second transaction: give all managers a 10% bonus
db2 "CONNECT TO SAMPLE"
db2 +c "UPDATE EMPLOYEE SET BONUS = SALARY * 0.1 WHERE JOB = 'MANAGER'"

Because the first transaction locks all the manager and other employees' rows, a lock-timeout occurs after 10 seconds (remember the setting of the LOCKTIMEOUT parameter), and the UPDATE statement in the second transaction fails.

Listing 8. Lock-timeout error returned by DB2
SQL0911N  The current transaction has been rolled back because of a deadlock or timeout
    Reason code "68".  SQLSTATE=40001

Information about this lock-timeout event is written to the unformatted event table EMDATA.TAB_LOCKING: the one you specified when executing the CREATE EVENT MONITOR statement. To read and format the information the lock event monitor collects, use a utility named db2evmonfmt, which is written in Java. Before using that utility for the first time, you must compile it. You can find the source code of the utility (including a corresponding XML style sheet) in the subdirectory samples of the DB2 install directory. Before compiling the Java source code, copy the two files (source code + style sheet) to your current working directory, as shown in Listing 9.

Listing 9. Copying source files for db2evmonfmt utility
copy "C:\Program Files\IBM\SQLLIB\samples\java\jdbc\db2evmonfmt.java" .
copy "C:\Program Files\IBM\SQLLIB\samples\java\jdbc\DB2EvmonLocking.xsl" .

A JDK (Java Development Kit) is required to compile the db2evmonfmt utility. Because a JDK is installed with each DB2 server product, you can use the Java compiler (javac) of the DB2 installation. For the operating system to find the Java compiler, the javac binary must be included in the PATH-variable before calling it, as shown in Listing 10.

Listing 10. Compiling the db2evmonfmt utility
set PATH=C:\Program Files\IBM\SQLLIB\java\jdk\bin;%PATH%
javac db2evmonfmt.java

To read and format the information about the lock-timeout event, the db2evmonfmt utility is called with the options in Listing 11. By default, the output of the utility is written to standard out, so redirect its output into a text file.

Listing 11. Calling the db2evmonfmt utility to format the data collected for the lock-timeout event
java db2evmonfmt -d sample -ue emdata.tab_locking -ftext -u username -p password
    >locktimeout.txt

where the code options are as follows:

-d
Database name
-ue
Fully qualified path (including the schema) of the unformatted event table
-ftext
Output of lock-event information in plain text format (or, output can be in XML format by specifying the option -fxml)
-u
User ID for database access
-p
Password for database access

For the lock-timeout event, db2evmonfmt produces the output shown in Listing 12.

Listing 12. db2evmonfmt output for the lock-timeout event
-------------------------------------------------------
Event ID               : 1
Event Type             : LOCKTIMEOUT
Event Timestamp        : 2010-02-16-18.27.38.102302
Partition of detection : 0
-------------------------------------------------------

Participant No 1 requesting lock 
----------------------------------
Lock Name            : 0x02000600040000000000000052
Lock wait start time : 2010-02-16-18.27.28.086228
Lock wait end time   : 2010-02-16-18.27.38.102302
Lock Type            : ROW
Lock Specifics       : ROWID=4,DATA_PARTITION_ID=0,PAGEID=0
Lock Attributes      : 00000000
Lock mode requested  : Update
Lock mode held       : Exclusive
Lock Count           : 1
Lock Hold Count      : 0
Lock rrIID           : 0
Lock Status          : Waiting
Lock release flags   : 40000000
Tablespace TID       : 2
Tablespace Name      : USERSPACE1
Table FID            : 6
Table Schema         : FECHNER 
Table Name           : EMPLOYEE


Attributes            Requester                       Owner                           
--------------------- ------------------------------  ------------------------------  
Participant No        1                               2                               
Application Handle    0342                            0333                            
Application ID        *LOCAL.DB2.100216172719         *LOCAL.DB2.100216172702         
Application Name      db2bp.exe                       db2bp.exe                       
Authentication ID     FECHNER                         FECHNER                         
Requesting AgentID    2172                            6320                            
Coordinating AgentID  2172                            6320                            
Agent Status          UOW Executing                   UOW Waiting                     
Application Action    No action                       No action                       
Lock timeout value    10                              0                               
Lock wait value       0                               0                               
Workload ID           1                               1                               
Workload Name         SYSDEFAULTUSERWORKLOAD          SYSDEFAULTUSERWORKLOAD          
Service subclass ID   13                              13                              
Service subclass      SYSDEFAULTSUBCLASS              SYSDEFAULTSUBCLASS              
Current Request       Execute Immediate               Close Cursor                    
TEntry state          1                               2                               
TEntry flags1         00000000                        00000000                        
TEntry flags2         00000200                        00000200                        
Lock escalation       no                              no                              
Client userid                                                                         
Client wrkstnname                                                                     
Client applname                                                                       
Client acctng                                                                         


Current Activities of Participant No 1
----------------------------------------
Activity ID        : 1
Uow ID             : 1
Package Name       : SQLC2H20
Package Schema     : NULLID  
Package Version    : 
Package Token      : AAAAAZBZ
Package Sectno     : 203
Reopt value        : none
Incremental Bind   : no
Eff isolation      : CS
Eff degree         : 0
Eff locktimeout    : 10
Stmt unicode       : no
Stmt query ID      : 0
Stmt nesting level : 0
Stmt invocation ID : 0
Stmt source ID     : 0
Stmt pkgcache ID   : 3783866187777
Stmt type          : Dynamic
Stmt operation     : DML, Insert/Update/Delete
Stmt text          : UPDATE EMPLOYEE SET BONUS = SALARY * 0.1 WHERE JOB = 'MANAGER'



Past Activities of Participant No 1
-------------------------------------
Activities not available


Current Activities of Participant No 2
----------------------------------------
Activities not available


Past Activities of Participant No 2
-------------------------------------
Past Activities wrapped: no

Activity ID        : 2
Uow ID             : 2
Package Name       : SQLC2H20
Package Schema     : NULLID  
Package Version    : 
Package Token      : AAAAAZBZ
Package Sectno     : 201
Reopt value        : none
Incremental Bind   : no
Eff isolation      : CS
Eff degree         : 0
Eff locktimeout    : 10
Stmt unicode       : no
Stmt query ID      : 0
Stmt nesting level : 0
Stmt invocation ID : 0
Stmt source ID     : 0
Stmt pkgcache ID   : 721554505729
Stmt type          : Dynamic
Stmt operation     : DML, Select (blockable)
Stmt text          : SELECT LASTNAME, FIRSTNME, SALARY FROM EMPLOYEE ORDER BY LASTNAME ASC

Activity ID        : 1
Uow ID             : 2
Package Name       : SQLC2H20
Package Schema     : NULLID  
Package Version    : 
Package Token      : AAAAAZBZ
Package Sectno     : 203
Reopt value        : none
Incremental Bind   : no
Eff isolation      : CS
Eff degree         : 0
Eff locktimeout    : 10
Stmt unicode       : no
Stmt query ID      : 0
Stmt nesting level : 0
Stmt invocation ID : 0
Stmt source ID     : 0
Stmt pkgcache ID   : 3835405795329
Stmt type          : Dynamic
Stmt operation     : DML, Insert/Update/Delete
Stmt text          : UPDATE EMPLOYEE SET SALARY = SALARY * 1.02

The output contains all the information required to determine the cause of the lock-timeout event, including:

  • General information about the applications participating in the lock-timeout event, such as application name, authentication ID, and so on.
  • Information about the lock that was requested by the waiting application, such as row or table lock, shared or exclusive lock, table name, and so on.
  • Information about the SQL statement blocked.
  • Information about all SQL statements executed before the lock-timeout event in the participating transactions, such as statement text, isolation level used, order of execution, and so on.

The event monitor gathered more than just the last SQL statements executed within the participating transactions. This is helpful for lock-analysis, because those statements might not be the reason for the concurrency issue. For the example, it is not the SELECT statement that caused the problematic lock, but the UPDATE statement executed just before the SELECT statement in the same transaction. Information about previous SQL statements is collected only if you specify the WITH HISTORY option with the ALTER WORKLOAD...COLLECT LOCK TIMEOUT DATA statement.

After you determine the cause for the lock-timeout event, deactivate collecting lock-timeout information for the default workload, as shown in Listing 13.

Listing 13. Stopping lock-timeout data collection for the default user workload
db2 "ALTER WORKLOAD SYSDEFAULTUSERWORKLOAD COLLECT LOCK TIMEOUT DATA NONE"

To prepare the next example scenario, reset the DB CFG parameter LOCKTIMEOUT to a value of -1, meaning that no more lock-timeouts can occur because a transaction can wait infinitely for another transaction to release a required lock. Because the LOCKTIMEOUT parameter is not configurable online, the database has to be deactivated and activated again for the change to take effect, as shown in Listing 14.

Listing 14. Resetting the database LOCKTIMEOUT setting
db2 "TERMINATE"
db2 "DEACTIVATE DB SAMPLE"
db2 "UPDATE DB CFG FOR SAMPLE USING LOCKTIMEOUT -1"
db2 "ACTIVATE DB SAMPLE"

Collecting deadlock event data for the whole database

The lock event monitor can be configured to collect information about lock events for only certain workloads or for the whole database. The previous example showed how to gather information about lock-timeout events for a single workload. In this example, see how to configure the lock event monitor so that it collects information about deadlock events for the whole database. Regardless of the level of collecting lock event data (workload or database), lock event data is gathered only when a lock event monitor is active.

Collecting deadlock event data for the whole database is activated through the DB CFG parameter MON_DEADLOCK (the corresponding parameters for lock-timeouts and lock-waits are MON_TIMEOUT and MON_LOCKWAIT respectively). By default, MON_DEADLOCK is set to a value of WITHOUT_HIST, which means the lock event monitor already collects information about deadlock events but without history data. Because you also want to collect history data, update this parameter with a value of HISTORY. In contrast to the LOCKTIMEOUT parameter, MON_DEADLOCK is configurable online, which means that deactivating or activating the database is not necessary. Nevertheless, to change a DB CFG parameter online, a connection to the database is mandatory, and the UPDATE DB CFG command must include the option IMMEDIATE, as shown in Listing 15.

Listing 15. Configuring deadlock data collection at the database-level
db2 "CONNECT TO SAMPLE"
db2 "UPDATE DB CFG USING MON_DEADLOCK HISTORY IMMEDIATE"

Next, execute a series of SQL statements in two different sessions that finally lead to a deadlock. In the sessions, try to update location data and department names in the table DEPARTMENT in parallel. In the first session, execute the UPDATE statement shown in Listing 16, which places an exclusive lock on the row with department number B01.

Listing 16. First transaction: Updating location data for a certain department
db2 "CONNECT TO SAMPLE"
db2 +c "UPDATE DEPARTMENT SET LOCATION = 'New York' WHERE DEPTNO = 'B01'"

In a second session, update another row with department number E11 before trying to update the row for department B01, too, as shown in Listing 17.

Listing 17. Second transaction: Updating the names of certain departments
db2 "CONNECT TO SAMPLE"
db2 +c "UPDATE DEPARTMENT SET DEPTNAME = 'OPERATIONS 1' WHERE DEPTNO = 'E11'"
db2 +c "UPDATE DEPARTMENT SET DEPTNAME = 'PLANNING 1' WHERE DEPTNO = 'B01'"

The second UPDATE statement in the second session is blocked by the exclusive lock hold by the transaction in the first session, thus it has to wait. Now execute an UPDATE in the first session that tries to change the row locked by the transaction in the second session (row for department E11), as shown in Listing 18. The database recognizes a deadlock event.

Listing 18. First transaction: Updating location data for another department
db2 +c "UPDATE DEPARTMENT SET LOCATION = 'Los Angeles' WHERE DEPTNO = 'E11'"

The database manager automatically rolls back one of the transactions, either the one in the first session or the one in the second session. The corresponding error message is returned for the UPDATE statement in the victim transaction, as shown in Listing 19.

Listing 19. Deadlock error returned by DB2
SQL0911N  The current transaction has been rolled back because of a deadlock or timeout.
    Reason code "2".  SQLSTATE=40001

To format the data about the deadlock event that the lock event monitor collects and write it to a plain text file, call the db2evmonfmt utility again. Because you compiled the source code for the first example, the db2evmonfmt Java class file already exists. This time, to restrict the utility's output to deadlock events, specify the additional option -type with a value of deadlock, as shown in Listing 20.

Listing 20. Calling the db2evmonfmt utility to format the data collected for the deadlock event
java db2evmonfmt -d sample -ue emdata.tab_locking -type deadlock -ftext
    -u username -p password >deadlock.txt

The output format of db2evmonfmt is nearly identical to the one described in the first example. Based on the information about applications, locks, and SQL statements, you can easily determine the cause of the deadlock event, as shown in Listing 21.

Listing 21. db2evmonfmt output for the deadlock event
-------------------------------------------------------
Event ID               : 1
Event Type             : DEADLOCK
Event Timestamp        : 2010-02-21-19.25.32.723805
Partition of detection : 0
-------------------------------------------------------

Deadlock Graph
--------------
Total number of deadlock participants : 2
Participant that was rolled back      : 2
Type of deadlock                      : local

Participant     Participant     Deadlock Member Application Handle 
Requesting Lock Holding Lock                                  
--------------- --------------- --------------- ------------------ 
1               2               0               044             
2               1               0               024             


Participant No 1 requesting lock 
----------------------------------
Lock Name            : 0x020005000B0000000000000052
Lock wait start time : 2010-02-21-19.25.26.208252
Lock wait end time   : 2010-02-21-19.25.32.723805
Lock Type            : ROW
Lock Specifics       : ROWID=11,DATA_PARTITION_ID=0,PAGEID=0
Lock Attributes      : 00000000
Lock mode requested  : Exclusive
Lock mode held       : Exclusive
Lock Count           : 1
Lock Hold Count      : 0
Lock rrIID           : 0
Lock Status          : Waiting
Lock release flags   : 40000000
Tablespace TID       : 2
Tablespace Name      : USERSPACE1
Table FID            : 5
Table Schema         : FECHNER 
Table Name           : DEPARTMENT

Participant No 2 requesting lock 
----------------------------------
Lock Name            : 0x02000500050000000000000052
Lock wait start time : 2010-02-21-19.25.13.381517
Lock wait end time   : 2010-02-21-19.25.32.723805
Lock Type            : ROW
Lock Specifics       : ROWID=5,DATA_PARTITION_ID=0,PAGEID=0
Lock Attributes      : 00000000
Lock mode requested  : Exclusive
Lock mode held       : Exclusive
Lock Count           : 1
Lock Hold Count      : 0
Lock rrIID           : 0
Lock Status          : Waiting
Lock release flags   : 40000000
Tablespace TID       : 2
Tablespace Name      : USERSPACE1
Table FID            : 5
Table Schema         : FECHNER 
Table Name           : DEPARTMENT


Attributes            Requester                       Requester                       
--------------------- ------------------------------  ------------------------------  
Participant No        1                               2                               
Application Handle    024                             044                             
Application ID        *LOCAL.DB2.100221182012         *LOCAL.DB2.100221182513         
Application Name      db2bp.exe                       db2bp.exe                       
Authentication ID     FECHNER                         FECHNER                         
Requesting AgentID    4148                            1016                            
Coordinating AgentID  4148                            1016                            
Agent Status          UOW Executing                   UOW Executing                   
Application Action    No action                       No action                       
Lock timeout value    0                               0                               
Lock wait value       0                               0                               
Workload ID           1                               1                               
Workload Name         SYSDEFAULTUSERWORKLOAD          SYSDEFAULTUSERWORKLOAD          
Service subclass ID   13                              13                              
Service subclass      SYSDEFAULTSUBCLASS              SYSDEFAULTSUBCLASS              
Current Request       Execute Immediate               Execute Immediate               
TEntry state          2                               2                               
TEntry flags1         00000000                        00000000                        
TEntry flags2         00000200                        00000200                        
Lock escalation       no                              no                              
Client userid                                                                         
Client wrkstnname                                                                     
Client applname                                                                       
Client acctng                                                                         


Current Activities of Participant No 1
----------------------------------------
Activity ID        : 2
Uow ID             : 4
Package Name       : SQLC2H20
Package Schema     : NULLID  
Package Version    : 
Package Token      : AAAAAZBZ
Package Sectno     : 203
Reopt value        : none
Incremental Bind   : no
Eff isolation      : CS
Eff degree         : 0
Eff locktimeout    : -1
Stmt unicode       : no
Stmt query ID      : 0
Stmt nesting level : 0
Stmt invocation ID : 0
Stmt source ID     : 0
Stmt pkgcache ID   : 631360192513
Stmt type          : Dynamic
Stmt operation     : DML, Insert/Update/Delete
Stmt text          : UPDATE DEPARTMENT SET LOCATION = 'Los Angeles' WHERE DEPTNO = 'E11'



Past Activities of Participant No 1
-------------------------------------
Past Activities wrapped: no

Activity ID        : 1
Uow ID             : 4
Package Name       : SQLC2H20
Package Schema     : NULLID  
Package Version    : 
Package Token      : AAAAAZBZ
Package Sectno     : 203
Reopt value        : none
Incremental Bind   : no
Eff isolation      : CS
Eff degree         : 0
Eff locktimeout    : -1
Stmt unicode       : no
Stmt query ID      : 0
Stmt nesting level : 0
Stmt invocation ID : 0
Stmt source ID     : 0
Stmt pkgcache ID   : 2791728742402
Stmt type          : Dynamic
Stmt operation     : DML, Insert/Update/Delete
Stmt text          : UPDATE DEPARTMENT SET LOCATION = 'New York' WHERE DEPTNO = 'B01'



Current Activities of Participant No 2
----------------------------------------
Activity ID        : 2
Uow ID             : 1
Package Name       : SQLC2H20
Package Schema     : NULLID  
Package Version    : 
Package Token      : AAAAAZBZ
Package Sectno     : 203
Reopt value        : none
Incremental Bind   : no
Eff isolation      : CS
Eff degree         : 0
Eff locktimeout    : -1
Stmt unicode       : no
Stmt query ID      : 0
Stmt nesting level : 0
Stmt invocation ID : 0
Stmt source ID     : 0
Stmt pkgcache ID   : 4380866641921
Stmt type          : Dynamic
Stmt operation     : DML, Insert/Update/Delete
Stmt text          : UPDATE DEPARTMENT SET DEPTNAME = 'PLANNING 1' WHERE DEPTNO = 'B01'



Past Activities of Participant No 2
-------------------------------------
Past Activities wrapped: no

Activity ID        : 1
Uow ID             : 1
Package Name       : SQLC2H20
Package Schema     : NULLID  
Package Version    : 
Package Token      : AAAAAZBZ
Package Sectno     : 203
Reopt value        : none
Incremental Bind   : no
Eff isolation      : CS
Eff degree         : 0
Eff locktimeout    : -1
Stmt unicode       : no
Stmt query ID      : 0
Stmt nesting level : 0
Stmt invocation ID : 0
Stmt source ID     : 0
Stmt pkgcache ID   : 3173980831746
Stmt type          : Dynamic
Stmt operation     : DML, Insert/Update/Delete
Stmt text          : UPDATE DEPARTMENT SET DEPTNAME = 'OPERATIONS 1' WHERE DEPTNO = 'E11'

Dropping a lock event monitor

When the lock event monitor is no longer needed, you can deactivate it and drop it. If the lock event monitor might be needed to analyze future concurrency problems, it is sufficient to deactivate it. Dropping the lock event monitor does not implicitly drop the corresponding unformatted event table. If the collected lock event data should not be kept for further analysis purposes, the unformatted event table must be dropped manually using the DROP TABLE statement, as shown in Listing 22.

Listing 22. Dropping the lock event monitor
db2 "SET EVENT MONITOR EVMON_LOCKING STATE 0"
db2 "DROP EVENT MONITOR EVMON_LOCKING"
db2 "DROP TABLE EMDATA.TAB_LOCKING"

Conclusion

This article explained how to work with the new lock event monitor introduced in DB2 LUW 9.7. It showed, by example, how you can use the lock event monitor to collect information about any kind of concurrency issue: deadlocks, lock-timeouts, and lock-waits. Using the new formatter utility db2evmonfmt, the lock event information gathered can be formatted in a way that makes it easy to identify the cause of certain lock problems. DB2 LUW 9.7 offers an integrated approach to analyzing any kind of concurrency issue that replaces the lock analysis methods used in previous DB2 LUW releases.

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=484301
ArticleTitle=Lock events for DB2 for Linux, UNIX, and Windows, Part 3: Use the lock event monitor in DB2 9.7 to solve concurrency issues
publish-date=04222010