Default multi-threaded DBPURGE operation on IBM DB2 database might not always work in a large environment

Multi-threaded DBPurge operation might fail with deadlock in database systems even though all optimization steps are followed. This scenario and the workaround apply to IBM Tivoli Identity Manager, version 5.1 and later versions of IBM Security Identity Manager.

The IBM Security Identity Manager DBPurge operation, by default, uses four threads for the IBM DB2 database. We can run the DBPurge operation with one thread by specifying the -threads 1 argument in the DBPurge command. If you run the DBPurge operation without the -threads 1 option, the operation might fail with errors similar to the following one:

DB2 SQL Error: SQLCODE=-1476, SQLSTATE=40506,SQLERRMC=-911

The error indicates that either a database timeout or deadlock occurred.

The issue resulted from a deadlock condition between the multiple threads of the DBPurge operation. Tables that have defined foreign key constraint and have no defined index on the foreign key column might cause a deadlock or a lock timeout in the database system.

See the IBM DB2 documentation (http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.perf.doc/doc/c0004121.html) for this scenario.

Example.

The ACTIVITY_LOCK table has a foreign key constraint defined with the PROCESS_ID and ACTIVITY_ID fields of the PROCESS and ACTIVITY tables. The ACTIVITY_LOCK table does not have an index for a foreign key ACTIVITY_ID column. The DBPurge utility refers to the following tables which have no index entries defined in foreign key column:

• The ACTIVITY_LOCK table does not have an index entry for the foreign key ACTIVITY_ID column.

• The PENDING and PENDING_REQUESTS tables do not have index entries defined on the foreign key column. However, this table has the foreign key and primary key defined on the same column, PROCESS_ID. The database creates the index internally for the PROCESS_ID column.

• The PROCESSDATA and RECONCILIATION_INFO tables have index entries that include the foreign key column. However, the tables do not have index that contains only the foreign key columns. The DB2 documentation specifies that we must create an index that contains only the foreign key columns, to resolve the deadlock issue.

We can resolve this problem by creating the following extra index entries in the ISIM database:

• CREATE INDEX ENROLE.ACTIVITY_LOCK_AIDX ON ENROLE.ACTIVITY_LOCK (ACTIVITY_ID ASC) MINPCTUSED 10 ALLOW REVERSE SCANS;
• CREATE INDEX ENROLE.PROCESSDATA_PIDX ON ENROLE.PROCESSDATA (PROCESS_ID ASC) MINPCTUSED 10 ALLOW REVERSE SCANS;
• CREATE INDEX ENROLE.RECONCILIATION_INFO_RIDX ON ENROLE.RECONCILIATION_INFO (RECONID ASC) MINPCTUSED 10 ALLOW REVERSE SCANS;

Creating the additional index entries ensures that DBPurge operation completes without a deadlock on a DB2 database when multiple threads of DBPurge operation run simultaneously.

Parent topic: Troubleshooting database problems