Support Article
SystemCleaner timeout on PR_LOG_REPORTSTATS
SA-18909
Summary
Admin reported that the stored Procedure(SP) PW3.SPPR_PURGE_TABLE gets called for PW3.PR_LOG_REPORTSTATS. However, for the last three days this SP fails with a timeout.
Error Messages
from log: (note non-standard log formatting]
type=ERROR~ctgy_cd=com.pega.pegarules.data.internal.access.ReportStatisticsImpl~log_prty_no=ERROR~rcv_ts=2015-12-21 01:01:34,924~rec_type_cd=~user_site_nm=~req_user_id=~user_id=~serv_nm=lppza271~call_appl_nm=~appl_own_nm=WSP~thread_nm=WorkManager.PRPCWorkManager : 0~pega_thread_val_tx=STANDARD~pega_appl_val_tx=PegaRULES:07.10~serverid=5c3d79ebb061bc4ec1118ece7c5d68f5~requestorid=BF904B46FDB6C8BA85BAF1F5166D4235C~interaction=~act_nm=~step_tx=~appl_error_cd=~error_cd_ds=~info_type_cd=~info_dec=~prcs_strt_tm=~prcs_end_tm=~tot_resp_no=~srvc_type_cd=~srvc_nm=~func_nm=~rule_set_nm=~appl_msg_tx=Database Error occured while calling the sppr_purge_table store proc~stack_trc_ds=~time_from_started=2601302889~com.pega.pegarules.pub.database.DatabaseException: Problem encountered executing a stored procedure: code: -913 SQLState: 57033 Message: DB2 SQL Error: SQLCODE=-913, SQLSTATE=57033, SQLERRMC=00C9008E;00000302;D3643PW3.PGTSP072.X'000044', DRIVER=4.18.60
From: (BF904B46FDB6C8BA85BAF1F5166D4235C)
SQL: {call PW3.sppr_purge_table (?,?,?)}
SQL Inserts: <PW3.pr_log_reportstats> <pxCreateDateTime> <30>
Caused by SQL Problems.
Problem #1, SQLState 57033, Error code -913: com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-913, SQLSTATE=57033, SQLERRMC=00C9008E;00000302;D3643PW3.PGTSP072.X'000044', DRIVER=4.18.60
Steps to Reproduce
1) Run the agent "system cleaner".
Root Cause
The Pega-RULES: Core Engine Processing Agent SystemCleaner is a standard agent that runs once per day (every 86400 seconds). When it runs, it will go through and clean up items that are older than 30 days by calling call sppr_purge_table({PurgeParams.TableName},{PurgeParams.DateColumnName},{PurgeParams.RententionDays Integer})\ and passing in the parameters (table, date, retention days).
There are 3 tables in this process that are cleaned up:
pr_perf_stats
pr4_log_rule_usage
pr4_log_rule_usage_details
In a production setting, the pr_perf_stats table can grow to contain millions of rows. By default, the system automatically purges records older than 30 days. Once each day, the Pega-RULES agent starts a stored procedure that purges older rows of this table, passing the value of the pxProcess property as the number of days to retain.
If older rows are noy needed for performance analysis, debugging, or other reporting, they can be purged by date without affecting other system capabilities.
Resolution
To set a retention period shorter than the 30 days, add or revise the following element to your prconfig.xml file and restart the system:
<env name="usage/retentionperiod " value="nnn" /> where nnn is the number of days to retain.PEGA0005 alerts for Tables pr_perf_stats and pr4_log_rule_usage
Published February 26, 2016 - 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.