SA-1076

Summary

Administrator has reported ‘lock exhaustion exceptions’ in the database caused by SPPR_PURGE_TABLE stored procedure while deleting large amounts of data from the table PR_PERF_STATS.

Error Messages

Messages were written to database console:
10.49.45 STC24484 *DXR142E IRN2002 THE LOCK STRUCTURE DB2N_LOCK1 IS 080% I
10.49.51 STC24484 *DXR142E IRN2002 THE LOCK STRUCTURE DB2N_LOCK1 IS 100% I

Steps to Reproduce

Unknown

Root Cause

System Cleaner agent was not working in the issue-reported environment and it was not deleting the records.

Resolution

By default, Pega automatically purges records older than 30 days. If you do not need older rows for performance analysis, debugging, or other reporting, you can purge them by date (known as trimming) without affecting other system capabilities. 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.

To set a retention period longer or 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.

Perform the following local-change:

1. Back up the PR_PERF_STATS table from the behaviour reported environment.
2. Delete a large number of records from this table: Truncate the PR_PERF_STATS table.
3. Set the appropriate usage retention period by adding a prconfig.xml entry, <env name="usage/retentionperiod " value="nnn" />.
4. Restart the server.

See also this Help topic:
https://community.pega.com/sites/default/files/help_v62/procomhelpmain.htm#howto/howto2/logusagetable.htm