SA-5924

Summary

Pega provides a stored procedure(SPPR_AGGREGATE_USAGE) in the PegaRULES database which  summarizes hourly data, in pr_hourly_usage and daily usage in pr_daily_usage table.

The stored procedure is not populating the hourly data and daily usage statics into the tables specified.

Error Messages

The error message from Apptune tool from DBA's:

SQL ERROR TIMESTAMP. 2014-12-24-00.01.09.905371
SUBSYS.............. DDH2 USER............. PEGD201E
PLAN................ DISTSERV CORRELATION ID... db2jcc_appli
PROGRAM............. SPPR_AGG CONNECT ID....... SERVER
PROGRAM TYPE........ NSP LOCATION......... DDH2
COLLECTION ID....... PEGDEV02 CONTOKEN......... 194051610D6ACADC
VERSION... V1

STATEMENT TYPE...... DYNAMIC STATEMENT NUMBER. 1
CALL TYPE........... SQLPL SECTION NUMBER... 1

DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN
UNAVAILABLE RESOURCE. REASON 00C90096, TYPE OF RESOURCE
00000304, AND RESOURCE NAME DPEGD02 .PRHOURUS.X'0002C2' '.X'14'
DSNT418I SQLSTATE = 57011 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXRSDL SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -190 13172746 0 13228501 -404549623 0 SQL
DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF42' X'00C9000A' X'00000000' X'00C9D9D5'
X'E7E31009' X'00000000' SQL DIAGNOSTIC INFORMATION

Steps to Reproduce

1. Run the listview for Log-License-Usage-Daily class and the data is same from DEC 10th.
2. Stored procedure(SPPR_AGGREGATE_USAGE) is not inserting data into table because the total number of page locks has exceeded the level allowed by the DB2 subsystem.
3. 5000 page locks is the current setting at the DB2 subsystem level.

Root Cause

The root cause of this problem is defect/misconfiguration in the PRPC operating environment. The no. of page locks is less than the default page locks of DB2.

Resolution

This issue is resolved through the following local change:

The no. of page locks are is less than the default page locks of DB2 which is 10000. So unable to populate the statistics after the no of the page locks exhausted. We suggested the customer to increase the page locks from 5000 to 10000.