Support Article
PEGA0005 alert for database operations (OOTB tables)
SA-22336
Summary
User's system has Pega 7.2 with Pega Marketing for financial services.
User is getting PEGA0005 alert in the log files for the query. This only occurs on call of the data page D_PZWARNINGSINTRODUCED.
Error Messages
Database operation took more than the threshold of 500 ms: 7,395 ms SQL: WITH app_rulesets_pc0 AS ( SELECT "PC0AR".pzRuleSetName AS "pzRuleSetName", "PC0AR".pzRuleSetVersionMajor AS "pzRuleSetVersionMajor", "PC0AR".pzRuleSetVersionMinor AS "pzRuleSetVersionMinor", "PC0AR".pzRuleSetVersionPatch AS "pzRuleSetVersionPatch" , "PC0AR"."pzRuleSetOverallOrder" AS "pzRuleSetOverallOrder", "PC0AR".pzRuleSetVersionMinorPatch AS "pzRuleSetVersionMinorPatch" FROM ( SELECT "PC0AR".pzRuleSetName, "PC0AR".pzRuleSetVersionMajor, "PC0AR".pzRuleSetVersionMinor, "PC0AR".pzRuleSetVersionPatch, ROW_NUMBER() OVER (PARTITION BY "PC0AR".pzRuleSetName ORDER BY "PC0AH".pzAppHeight) AS "pzRuleSetOrder" , ROW_NUMBER() OVER (ORDER BY "PC0AH".pzAppHeight, "PC0AR".pzRuleSetIndex) AS "pzRuleSetOverallOrder" , "PC0AR".pzRuleSetVersionMinorPatch FROM pega.pr_sys_app_hierarchy_flat "PC0AH" INNER JOIN pega.pr_sys_app_ruleset_index "PC0AR" ON ("PC0AH".pzAppHash = "PC0AR".pzAppHash) WHERE "PC0AH".pzTopAppHash = ? AND "PC0AH".pzAppName != 'PegaRULES') "PC0AR" WHERE "PC0AR"."pzRuleSetOrder" = 1 ) ,rr_inheritance_pc0 AS ( SELECT "r".pzInsKey, "r".pyRuleAvailable, "r".pyClassName, "r".pyRuleSet, "r".pyRuleSetVersion, "r".pxInsId, "r".pyClass, RANK() OVER (PARTITION BY "r".pxInsId, "r".pyClass, "r".pyClassName ORDER BY ...
Steps to Reproduce
On execution of D_PZWARNINGSINTRODUCED data page.
Root Cause
Database is responding poorly to PRPC SQL.
Specifically, the PEGA0005 alerts indicate that a specific SQL is causing the most mayhem. The source of the SQL has been traced back to standard Report Definition: <DATA-RULE-SUMMARY> PZWARNINGINTRODUCED. This standard stored procedure is called by each User Requestor Instance after user login, and is called repeatedly as the user updates rule instances.
Resolution
Following is the local change provided:
- Open @baseclass RuleFormHeaderMessagesInner and perform save as to operators ruleset.
- Delete the section in Dynamic Layout (Simple list) – 2 and Dynamic Layout (Simple list) – 3 and save.
- Open Data-Portal pyStudioHome and save as the rule into operators local ruleset.
- Delete the section Guardrail warnings and save.
- Logoff and stop the server. Clear temporary files and start the server.
Published April 30, 2016 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.