Support Article
Transaction log full error when AES 3.4 is run on PRPC 6.1 SP2
SA-14264
Summary
Transaction log full error when AES 3.4 is run on PRPC 6.1 SP2. The following SQL query is responsible for the issue.
DELETE FROM pegaam_alert where GeneratedDateTime < ( select
(max(GeneratedDateTime) - MIDNIGHT_SECONDS (max(GeneratedDateTime))
SECONDS - 21 DAYS ) from pegaam_alert)
Error Messages
Caused by: com.ibm.db2.jcc.am.SqlTransactionRollbackException: DB2 SQL Error: SQLCODE=-911, SQLSTATE=40001, SQLERRMC=68, DRIVER=3.62.56
at com.ibm.db2.jcc.am.fd.a(fd.java:663)
at com.ibm.db2.jcc.am.fd.a(fd.java:60)
at com.ibm.db2.jcc.am.fd.a(fd.java:127)
at com.ibm.db2.jcc.am.jn.c(jn.java:2614)
at com.ibm.db2.jcc.t4.cb.s(cb.java:983)
at com.ibm.db2.jcc.t4.cb.k(cb.java:405)
at com.ibm.db2.jcc.t4.cb.a(cb.java:62)
at com.ibm.db2.jcc.t4.q.a(q.java:50)
at com.ibm.db2.jcc.t4.sb.b(sb.java:220)
at com.ibm.db2.jcc.am.kn.lc(kn.java:3077)
at com.ibm.db2.jcc.am.kn.b(kn.java:4034)
at com.ibm.db2.jcc.am.kn.dc(kn.java:746)
at com.ibm.db2.jcc.am.kn.executeUpdate(kn.java:729)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at com.pega.pegarules.data.internal.access.DatabasePreparedStatementImpl.addBatch(DatabasePreparedStatementImpl.java:567)
Steps to Reproduce
Not Applicable
Root Cause
A defect or configuration issue in the operating environment. The rate at which records are added in pegaam_alert table is high enough for the out-of-the-box DELETE SQL to cause DB transaction log space overrun.
Resolution
Make the following change to the operating environment:
- Modify the DELETE SQL to remove records from pegaam_alert table in smaller intervals.
- Increase the threshold of ALERTs that are not important so that the overall count is less and hence the table does not grow at the current rate.
- Reduce the retention days System Settings for AES so that less amount of data gets accumulated for DELETE SQL to remove.
Published September 23, 2015 - Updated December 2, 2021
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.