Deadlocks due to IBM DB2 utilites executing against Pega DB
Admin wishes to run third party utilities but cannot find a suitable time when there is no contention.
• Reorganizing a table compresses the data file that contains the table by physically reclaiming unusable space create by deletes and updates. Each Online Reorg requires a few second of lock on the respective table to go through a step referred to as a ‘Switch step’ through which a shadow copy of the table is renamed and becomes active. Due to high transaction volumes with some of the Pega tables such as PR_Sys_Locks and Clipboard cache the jobs have not successfully executed at times for these tables.
This utility takes a quiet point from all the WSP tables on daily basis to be used in case a Recovery of the DB is required. Quiesce may not need an outage for most of the applications and it does not cause an outage since quiesce only waits for the next commit point. Problem is when there are long running threads without commits or too many active threads then quiesce can cause an outage.
[7/22/14 18:45:51:782 MST] 00043cbc PRBootstrap E com.pega.pegarules.internal.bootstrap.PRBootstrap Problem during method invocation (unlock)
javax.ejb.EJBException: nested exception is: com.pega.pegarules.pub.database.DatabaseException: Problem executing quick lock delete: DB2 SQL Error: SQLCODE=-913, SQLSTATE=57033, SQLERRMC=00C900BA;00002008;PSYSLOCK.00000000, DRIVER=3.64.133: code: -913 SQLState: 57033 Message: DB2 SQL Error: SQLCODE=-913, SQLSTATE=57033, SQLERRMC=00C900BA;00002008;PSYSLOCK.00000000, DRIVER=3.64.133
Steps to Reproduce
Execute the IBM DB2 reorg and Quiesce utilities
The root cause of this problem is in a third-party product integrated with PRPC.
Here are some details on the pr_sys_locks table that is involved in one of the deadlock issues.
While the system is up 24 x 7 if there are times when the system is not as heavily used that is the time that these type of utilities should be run. They should never be run during high volume times as they will hinder performance. Also if possible it may be better if you can configure these tools to not run against certain tables, such as the system tables that you are running into issues with.