The error indicates that a deadlock occurred in the DB2 database server. Following is the deadlock data that is captured from the DB2 database. This data comes from the formatted output of the DB2DETAILDEADLOCK event monitor, which is created and active by default for every DB2 database:

Application 1 requests a lock that is held by application 2 and vice versa. To release the lock, the DB2 database selects a target, which is rolled back. Both applications run the same query:SELECT * from PERSISTENTLOCK WHERE LOCKID = ? AND SEQUENCEID = ?

Cause

On the Database server runstats script was not run on regular intervals. The following lines in the explanation show the last statistics update: Name: PK_PERSISTENTLOCK Type: Index Time of creation: 2009-01-14-11.58.52.612756 Last statistics update: 2009-01-20-10.36.40.039020 Name: PERSISTENTLOCK Type: Table Time of creation: 2009-01-14-11.58.52.582210 Last statistics update: 2009-01-20-10.36.40.039020

Another way to check the statistics timestamp is to run a simple query from the system catalog views using the relevant tables that are shown by the SQL in the deadlock event monitor output::

select TABSCHEMA, TABNAME, CREATE_TIME, STATS_TIME from SYSCAT.TABLES where TABSCHEMA like 'DB2INST1' and TABNAME like 'PERSISTENTLOCK'

Resolving the problem

Run the runstats script, which is a DB2 script, at regular intervals and set the script to gather RUNSTATS WITH DISTRIBUTION AND DETAILED INDEXES ALL.

In addition to running the runstats scripts regularly, you can perform the following tasks to avoid the problem:

Use REOPT ONCE or REOPT ALWAYS with the command-line interface (CLI ) packages to change the query optimization behavior.

In the DB2 database, change the table to make it volatile. Volatile tables indicate to the DB2 optimizer that the table cardinality can change significantly at run time (from empty to large and vice versa). Therefore, DB2 uses an index to access a table rather than a table scan.