SA-94

Summary

PRPC system administrator reported following DB2 SQL Exception in the logs which was hindering user from logging in.

Error Messages

Following exception was seen in the PegaRULES log: 
Caused by: com.pega.pegarules.pub.database.DatabaseException: Database-General There was a problem getting a list -204 42704 DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=XA9935A.PR_SYS_APP_HIERARCHY_FLAT, DRIVER=3.64.133 From: (HFF12F4909A9F11525F1B09A90919FC91) SQL: select 1 from pr_sys_app_hierarchy_flat where pzTopAppHash = ? and pzAppHash = ? SQL Inserts: Caused by SQL Problems.
Problem #1, SQLState 42704, Error code -204: com.ibm.websphere.ce.cm.StaleConnectionException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=XA9935A.PR_SYS_APP_HIERARCHY_FLAT, DRIVER=3.64.133
Problem #2, SQLState 42704, Error code -204: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=XA9935A.PR_SYS_APP_HIERARCHY_FLAT, DRIVER=3.64.133
Problem #3, SQLState 26501, Error code -516: com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-516, SQLSTATE=26501, SQLERRMC=null, DRIVER=3.64.133 Problem #4, SQLState 26501, Error code -514: com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-514, SQLSTATE=26501, SQLERRMC=SQL_CURLN200C1, DRIVER=3.64.133 at com.pega.pegarules.data.internal.access.ExceptionInformation.createExceptionDueToDBFailure(ExceptionInformation.java:167) at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:6115) at com.pega.pegarules.data.internal.access.DatabaseImpl.executeRDB(DatabaseImpl.java:6483) at com.pega.pegarules.data.internal.access.DatabaseImpl.executeRDB(DatabaseImpl.java:6444) at com.pega.pegarules.data.internal.access.DatabaseImpl.executeRDB(DatabaseImpl.java:6425) at com.pega.pegarules.session.internal.authorization.context.GlobalContextCache.doesTopAppHashNeedToBeInsertedIntoFlattenedHierarchy(GlobalContextCache.java:1487) ... 97 more Caused by: com.ibm.websphere.ce.cm.StaleConnectionException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=XA9935A.PR_SYS_APP_HIERARCHY_FLAT, DRIVER=3.64.133 at com.ibm.ws.rsadapter.jdbc.WSJdbcUtil.handleStaleStatement(WSJdbcUtil.java:862) at com.ibm.ws.rsadapter.AdapterUtil.mapException(AdapterUtil.java:2126) at com.ibm.ws.rsadapter.jdbc.WSJdbcUtil.mapException(WSJdbcUtil.java:1047) at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.execute(WSJdbcPreparedStatement.java:628) at com.pega.pegarules.data.internal.access.DatabasePreparedStatementImpl.execute(DatabasePreparedStatementImpl.java:980) at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:6038) ... 101 more !en_us_!wsp:unauthenticated!02.38.01$37af3271647f3ca38172a1fd3c21a454>!en_us_!wsp:unauthenticated!02.38.01$37af3271647f3ca38172a1fd3c21a454>

Root Cause

The exception says StaleConnectionException which suggests database went through either recycle or the app server to database connectivity was broken. As a result, all connections in the datasource pool will be stale. To get past this issue either enable pretest connections under the datasource or recycle the JVM.

Resolution

Enable pretest connections under the WAS datasource for existing as well as new connections.