Analyze the cause of lock timeout in DB2 9.5 (1)

In DB29.5, the locking timeout analysis method has been greatly improved, making the locking timeout analysis easier. This article mainly explores these new lock timeout reporting functions and checks the collected additional information to determine the specific cause of lock timeout.

Review lock timeout Analysis in DB2 9.1

The following steps are taken to analyze the lock timeout using the db2pd tool and the db2cos script:

1. Use a special DB2 script named db2cos to execute a db2pd call each time the db2cos script is called. Db2pd calls collect information related to locking, transactions, applications, and statement caching, and store the information in a text file for analysis.

2. to automatically execute the db2cos script (including the db2pd command) when the lock timeout occurs, run the db2pdcfg command to register the lock timeout event.

3. In the lock timeout event, DBA can check the db2pd output generated by automatically calling the db2cos script. This allows the DBA to determine the reason for the lock contention, so as to avoid such situations in the future.

Although the methods described here provide a lot of information, making lock timeout analysis easier than the previous DB2 9 for Linux, Unix, and Windows versions, it still has some shortcomings:

◆ To use this method, you must manually rewrite the db2cos script and call db2pdcfg to register the lock timeout event. The two steps are not complex, but may be difficult for new users.

◆ Explain the db2pd output to identify the applications and SQL statements involved in lock timeout. This task is not easy and requires some attempts.

◆ If the lock timeout is caused by a transaction that contains multiple SQL commands, the information collected by db2pd may not be enough to determine the SQL statement that causes the lock.

New lock timeout report feature in DB2 9.5

In the lock timeout report function of DB2 9.5, a new feature is introduced to simplify the lock timeout analysis. To activate the lock timeout report, set the DB2 registration variable DB2_CAPTURE_LOCKTIMEOUT to ON and restart your DB2 instance:

Listing 1. Activating the lock timeout report in DB2 9.5

 
 

  
  
db2set DB2_CAPTURE_LOCKTIMEOUT=ON   
  
  
db2stop   
  
  
db2start  
 
 

Yes, it's that simple. When DB2_CAPTURE_LOCKTIMEOUT is set to ON, DB2 automatically creates a report file for each lock timeout event. The report file is saved in the directory to which the database administrator configuration (dbm cfg) parameter of DIAGPATH points. The following information is contained: lock the timeout date and time, problematic locking, locking the request program and locking the owner.

So does DB2 9.5 not use the db2cos script? This is not the case. It is widely used to combine the db2cos script with the db2pd tool. This means that the combination of these tools can still be used to capture any DB2 event information related to SQL code and DB2 internal return code, not just lock timeout information.

Now let's take a look at the new DB2 9.5 registration variable DB2_CAPTURE_LOCKTIMEOUT and view a locking timeout example scenario using the DB2 SAMPLE database. If the SAMPLE database does not exist, you can call the following command to create one:

Analyze the cause of lock timeout in DB2 9.5

Author: kaduo, source: IT expert network forum, responsible editor: Chen Ziqi

In DB29.5, the locking timeout analysis method has been greatly improved, making the locking timeout analysis easier. This article explores these new lock timeout reporting functions and checks the collected additional information to determine the cause of lock timeout.

In DB29.5, the locking timeout analysis method has been greatly improved, making the locking timeout analysis easier. This article explores these new lock timeout reporting functions and checks the collected additional information to determine the cause of lock timeout.

Review lock timeout Analysis in DB2 9.1

The following steps are taken to analyze the lock timeout using the db2pd tool and the db2cos script:

1. Use a special DB2 script named db2cos to execute a db2pd call each time the db2cos script is called. Db2pd calls collect information related to locking, transactions, applications, and statement caching, and store the information in a text file for analysis.

2. to automatically execute the db2cos script (including the db2pd command) when the lock timeout occurs, run the db2pdcfg command to register the lock timeout event.

3. In the lock timeout event, DBA can check the db2pd output generated by automatically calling the db2cos script. This allows the DBA to determine the reason for the lock contention, so as to avoid such situations in the future.

Although the methods described here provide a lot of information, making lock timeout analysis easier than the previous DB2 9 for Linux, Unix, and Windows versions, it still has some shortcomings:

◆ To use this method, you must manually rewrite the db2cos script and call db2pdcfg to register the lock timeout event. The two steps are not complex, but may be difficult for new users.

◆ Explain the db2pd output to identify the applications and SQL statements involved in lock timeout. This task is not easy and requires some attempts.

◆ If the lock timeout is caused by a transaction that contains multiple SQL commands, the information collected by db2pd may not be enough to determine the SQL statement that causes the lock.

New lock timeout report feature in DB2 9.5

In the lock timeout report function of DB2 9.5, a new feature is introduced to simplify the lock timeout analysis. To activate the lock timeout report, set the DB2 registration variable DB2_CAPTURE_LOCKTIMEOUT to ON and restart your DB2 instance:

Listing 1. Activating the lock timeout report in DB2 9.5

 
 

  
  
db2set DB2_CAPTURE_LOCKTIMEOUT=ON   
  
  
db2stop   
  
  
db2start  
 
 

Yes, it's that simple. When DB2_CAPTURE_LOCKTIMEOUT is set to ON, DB2 automatically creates a report file for each lock timeout event. The report file is saved in the directory to which the database administrator configuration (dbm cfg) parameter of DIAGPATH points. The following information is contained: lock the timeout date and time, problematic locking, locking the request program and locking the owner.

So does DB2 9.5 not use the db2cos script? This is not the case. It is widely used to combine the db2cos script with the db2pd tool. This means that the combination of these tools can still be used to capture any DB2 event information related to SQL code and DB2 internal return code, not just lock timeout information.

Now let's take a look at the new DB2 9.5 registration variable DB2_CAPTURE_LOCKTIMEOUT and view a locking timeout example scenario using the DB2 SAMPLE database. If the SAMPLE database does not exist, you can call the following command to create one: