Progressive increase in SQL failure count on pc_assign_worklist
After a v9.7 DB2 upgrade on PRPC 6.3 SP1, the user observed a significant and progressive increase in the failure count on the pc_assign_worklist table in a production environment. The user reported seeing this problem while executing any assignment flow in the application.
Error Messages[3/17/17 21:21:53:734 GMT] 000000b0 SystemErr R com.pega.pegarules.pub.database.DatabaseException: There was a database problem when performing an RDBExecute: code: -952 SQLState: 57014 Message: Processing was cancelled due to an interrupt.. SQLCODE=-952, SQLSTATE=57014, DRIVER=3.63.75
SQL: SELECT "PC0".PYASSIGNMENTSTATUS AS "pyAssignmentStatus" , "PC0".PXREFOBJECTINSNAME AS "pxRefObjectInsName" , "PC0".PZINSKEY AS "pzInsKey" FROM pc_assign_worklist "PC0" WHERE ( "PC0".PXCREATEDATETIME >= ? AND "PC0".CROSSSELLINDICATOR is null AND "PC0".PYASSIGNMENTSTATUS LIKE ? AND "PC0".PXCREATEOPERATOR NOT LIKE ? AND "PC0".PXWORKGROUP LIKE ? ) AND "PC0".pxObjClass = ?
SQL Inserts: <20161218T000000.000 GMT> <%Error%> <your_op%> <%your_WG%> <Assign-Worklist>
Caused by SQL Problems.
Problem #1, SQLState 57014, Error code -952: com.db2.jcc.am.SqlException: Processing was cancelled due to an interrupt.. SQLCODE=-952, SQLSTATE=57014, DRIVER=3.63.75
Steps to Reproduce
Process any assignment flow in an application using DB2 for Linux, UNIX, and Windows upgraded to version 9.7.
A defect or configuration issue in the operating environment
Indexes need to be created for the columns that are part of the SELECT query.
Perform the following local-change:
1. Note down the number of rows on the PYASSIGNMENTSTATUS table.
2. Run the indexes on columns that are part of the SELECT query, for example, CROSSSELLINDICATOR.
3. Observe the improved response time.
Published March 31, 2017 - Updated April 27, 2017