Use db2pd to analyze the locks in DB2forLinux, UNIX, and andWindows.

When multiple DB2? When a user accesses a database concurrently, the lock wait slows down the response. Lock wait is temporary and therefore difficult to capture. However, when a lock wait occurs, the Database Administrator is responsible for determining the cause of the lock wait. This document uses examples to demonstrate how to use the tool for DB2forLinux ?, UNIX ?, AndWindows? Db2pd and db

When multiple DB2? When a user accesses a database concurrently, the lock wait slows down the response. Lock wait is temporary and therefore difficult to capture. However, when a lock wait occurs, the Database Administrator is responsible for determining the cause of the lock wait. This article uses examples to demonstrate how to use DB2 for Linux ?, UNIX ?, And Windows? Db2pd and db

When multiple DB2? When a user accesses a database concurrently, the lock wait slows down the response. Lock wait is temporary and therefore difficult to capture. However, when a lock wait occurs, the Database Administrator is responsible for determining the cause of the lock wait. This article uses examples to demonstrate how to use DB2 for Linux ?, UNIX ?, And Windows? Of db2pdAnd db2pdcfgUtility to complete the task.

Used for Lock monitoringdb2pdOption

db2pdIs a utility for monitoring various DB2 database activities and troubleshooting. It is an independent utility released with the DB2 engine since DB2 V8.2. Its appearance and functions are similar to Informix.onstatUtility.db2pdIs executed in an optional interactive mode from the command line. The utility runs very fast because it does not need to obtain any locks and runs outside of the engine resources (which means it can even work on a suspended engine ). You can also collect snapshots through snapshot monitoring.db2pdProvides a lot of monitor data,db2pdThe output format is very different from that of snapshot monitoring. This allows DBAs to select an alternative monitoring method that better meets user needs. This article focuses ondb2pd. There is a developerWorks article by Sam Poon (see references) ondb2pdThe monitoring function is more widely introduced.

The following figure shows the lock monitoringdb2pdOption:

Figure 1. Used for Lock monitoringdb2pdOption

• TranHdl: Used to specify the transaction handle to monitor only the locks held by a specific transaction.
• showlocks: This sub-option extends the lock name to a meaningful explanation. For a row lock, this option displays the following information: tablespace ID, table ID, partition ID, page, and slot. By using the catalog ViewSYSCAT.TABLESYou can easily map the tablespace ID and table ID to the corresponding table name:
  
  Listing 1. ing tablespace ID and table ID to table mode and table name
  

  SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE TBSPACEID = tbspaceid AND TABLEID = tableid

  
  
• wait: If you specifywaitSub-optionsdb2pdOnly displays the locks that the transaction is currently waiting for and the locks that are responsible for the waiting situation. This sub-option greatly simplifies lock wait analysis because it limits the output to the lock involved in the lock wait situation.
• db2pd databaseAndfileThe options are not specific to lock monitoring, but apply to (almost) Alldb2pdCall.databaseOptiondb2pdThe returned monitoring data is limited to the monitoring data of a database. WhilefileYou can define a filedb2pdOutput to this file.

Lock wait analysis scenario

Next, we will start to usedb2pdTo analyze the lock wait situation. To this end, we create DB2SAMPLEDatabase:

List 2. CreateSAMPLEDatabase

db2sampl

User A executes transaction A to provide them with A 10% bonus based on the salary of each manager:

Listing 3. Update operations performed by transaction

UPDATE EMPLOYEE SET BONUS = SALARY * 0.1 WHERE JOB = 'MANAGER'

When transaction A is still running (because user A is not usingCOMMITOrROLLBACKWhen the transaction is terminated, user B executes transaction B to increase the salary of each employee by 2%:

Listing 4. Update operations performed by transaction B

UPDATE EMPLOYEE SET SALARY = SALARY * 0.02

Because transaction B is not completed, user B requests the DBA to determine the cause of the problem. Therefore, DBA callsdb2pdTo check whether there is a lock wait:

Listing 5. Check lock wait Conditions

db2pd -db sample -locks wait showlocks Database Partition 0 -- Database SAMPLE -- Active -- Up 3 days 08:33:05 Locks: Address TranHdl Lockname Type Mode Sts Owner Dur 0x050A0240 6 02000600050040010000000052 Row ..X W 2 1 0x050A0DB0 2 02000600050040010000000052 Row ..X G 2 1 HoldCount Att ReleaseFlg 0 0x00 0x40000000 TbspaceID 2 TableID 6 PartitionID 0 Page 320 Slot 5 0 0x00 0x40000000 TbspaceID 2 TableID 6 PartitionID 0 Page 320 Slot 5

db2pdIt is reported that a row lock exists in a table with ID 6 in the tablespace with ID 2. Pass CheckSYSCAT.TABLES, DBA determines the tableEMPLOYEEThere is indeed a lock wait.

Listing 6. Tables involved in determining lock wait Conditions

Click here to view the full text