Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

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

 
Suggest Edit

Published February 26, 2016 - Updated December 2, 2021

Did you find this content helpful? Yes No

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us