Support Article

Unable to add records to Data type showing error

SA-25478

Summary



A systems administrator has been contacted by the business and notified of poor system performance. 

This poor performance is observed at anytime during the processing day. 

Logged in users observe very slow performance, that is 10+ seconds screen to screen times, and new users logging in complain of long delays more than 10 seconds to login.


Error Messages



No error messages are displayed on screen; the Pega logfile contains multiple errors indicating database timeouts:

2016-07-06 00:00:07,468 [ttp-nio-8080-exec-14] [TABTHREAD1] [                    ] [       SLCM:01.01.01] (l.access.ConnectionManagerImpl) ERROR <HOSTNAME>|<IPADDRESS> <NAME> - Not returning connection 6 for database "pegarules" to the pool as it previously encountered the following error
User ID: <OPERATORID>
Last SQL: WITH <REFERENCE> AS ( SELECT "r".pzInsKey, "r".pyRuleAvailable, "r".pyClassName, "r".pyRuleSet, RANK() OVER (PARTITION BY "r".pxInsId, "r".pyClass, "r".pyClassName ORDER BY "i"."pzRuleSetIndex", "r".pyRuleSetVersion DESC) AS "rank_idx", DENSE_RANK() OVER (ORDER BY "r".pxInsId, "r".pyClass, "r".pyClassName) AS "group_idx" FROM RULES.pr4_rule_vw "r" INNER JOIN ( SELECT "i".pzRuleSetName AS "pzRuleSetName", "i".pzRuleSetIndex AS "pzRuleSetIndex", "i".pzRuleSetVersionMajor AS "pzRuleSetVersionMajor", "i".pzRuleSetVersionMinor AS "pzRuleSetVersionMinor", "i".pzRuleSetVersionPatch AS "pzRuleSetVersionPatch" , "i".pzRuleSetVersionMinorPatch AS "pzRuleSetVersionMinorPatch" FROM RULES.pr_sys_ruleset_index "i" WHERE "i".pzRuleSetListHash = ?  UNION ALL SELECT CAST(?  AS VARCHAR(128)) AS "pzRuleSetName", -1 AS "pzRuleSetIndex", 1 AS "pzRuleSetVersionMajor", 1 AS "pzRuleSetVersionMinor", 1 AS "pzRuleSetVersionPatch" , '01-01' AS "pzRuleSetVersionMinorPatch" ) "i" ON ("r".pyRuleSet = "i"."pzRuleSetName") AND ("r".pzRuleSetVersionMajor = "i"."pzRuleSetVersionMajor" AND "r".pzRuleSetVersionMinorPatch <= "i"."pzRuleSetVersionMinorPatch") WHERE "r".pyClass = ?  AND "r".pyRuleAvailable != 'No' AND "r".pyCircumstanceType IS NULL AND "r".pyRuleStarts IS NULL AND "r".pyRuleEnds IS NULL AND "r".pzClassType = 0 ) , bc_inheritance_pc0 AS ( SELECT "bc".pzInsKey FROM ( SELECT "tmp".pzInsKey, "tmp".pyRuleAvailable FROM ( SELECT "second".pzInsKey, "second".pyRuleAvailable, RANK() OVER (PARTITION BY "second"."group_idx" ORDER BY "second"."rank_idx") AS "finalRank" FROM rr_inheritance_pc0 "first" RIGHT OUTER JOIN rr_inheritance_pc0 "second" ON ( "first"."group_idx" = "second"."group_idx" AND "first".pyClassName = "second".pyClassName AND "first".pyRuleSet = "second".pyRuleSet AND "first".pyRuleAvailable = 'Withdrawn' AND coalesce("first"."rank_idx", 0) <= "second"."rank_idx" ) WHERE "first"."rank_idx" IS NULL ) "tmp" WHERE "tmp"."finalRank" = 1 ) "bc" WHERE "bc".pyRuleAvailable != 'Blocked'  ) , rr_wo_inheritance_pc0 AS ( SELECT "r".pzInsKey, "r".pyRuleAvailable, "r".pyClassName, "r".pyRuleSet, RANK() OVER (PARTITION BY "r".pxInsId, "r".pyClass ORDER BY "i"."pzRuleSetIndex", "r".pyRuleSetVersion DESC) AS "rank_idx", DENSE_RANK() OVER (ORDER BY "r".pxInsId, "r".pyClass) AS "group_idx" FROM RULES.pr4_rule_vw "r" INNER JOIN ( SELECT "i".pzRuleSetName AS "pzRuleSetName", "i".pzRuleSetIndex AS "pzRuleSetIndex", "i".pzRuleSetVersionMajor AS "pzRuleSetVersionMajor", "i".pzRuleSetVersionMinor AS "pzRuleSetVersionMinor", "i".pzRuleSetVersionPatch AS "pzRuleSetVersionPatch" , "i".pzRuleSetVersionMinorPatch AS "pzRuleSetVersionMinorPatch" FROM RULES.pr_sys_ruleset_index "i" WHERE "i".pzRuleSetListHash = ?  UNION ALL SELECT CAST(?  AS VARCHAR(128)) AS "pzRuleSetName", -1 AS "pzRuleSetIndex", 1 AS "pzRuleSetVersionMajor", 1 AS "pzRuleSetVersionMinor", 1 AS "pzRuleSetVersionPatch" , '01-01' AS "pzRuleSetVersionMinorPatch" ) "i" ON ("r".pyRuleSet = "i"."pzRuleSetName") AND ("r".pzRuleSetVersionMajor = "i"."pzRuleSetVersionMajor" AND "r".pzRuleSetVersionMinorPatch <= "i"."pzRuleSetVersionMinorPatch") WHERE "r".pyClass = ?  AND "r".pyRuleAvailable != 'No' AND "r".pyCircumstanceType IS NULL AND "r".pyRuleStarts IS NULL AND "r".pyRuleEnds IS NULL AND "r".pzClassType = 1 ) , bc_wo_inheritance_pc0 AS ( SELECT "bc".pzInsKey FROM ( SELECT "tmp".pzInsKey, "tmp".pyRuleAvailable FROM ( SELECT "second".pzInsKey, "second".pyRuleAvailable, RANK() OVER (PARTITION BY "second"."group_idx" ORDER BY "second"."rank_idx") AS "finalRank" FROM rr_wo_inheritance_pc0 "first" RIGHT OUTER JOIN rr_wo_inheritance_pc0 "second" ON ( "first"."group_idx" = "second"."group_idx" AND "first".pyRuleSet = "second".pyRuleSet AND "first".pyRuleAvailable = 'Withdrawn' AND coalesce("first"."rank_idx", 0) <= "second"."rank_idx" ) WHERE "first"."rank_idx" IS NULL ) "tmp" WHERE "tmp"."finalRank" = 1 ) "bc" WHERE "bc".pyRuleAvailable != 'Blocked'  ) , non_rr_rules_pc0 AS ( SELECT "r".pzInsKey FROM RULES.pr4_rule_vw "r" WHERE COALESCE("r".pyRuleAvailable, 'Yes') != 'No' AND "r".pyCircumstanceType IS NULL AND "r".pyRuleStarts IS NULL AND "r".pyRuleEnds IS NULL AND "r".pzClassType = 2  ) , resolved_rules_pc0 AS ( SELECT pzInsKey FROM bc_inheritance_pc0 UNION ALL SELECT pzInsKey FROM bc_wo_inheritance_pc0 UNION ALL SELECT pzInsKey FROM non_rr_rules_pc0  ) SELECT "PC0".pyDescription AS "pyDescription" , "PC0".pyCategoryName AS "pyCategoryName" , "PC0".pyOwner AS "pyOwner" , "PC0".pyOwnerType AS "pyOwnerType" , "PC0".pzInsKey AS "pzInsKey" , "PC0".pyVisibleWhen AS "pyVisibleWhen" , "PC0".pxInsName AS "pxInsName" FROM RULES.pr4_rule_shortcut "PC0" INNER JOIN resolved_rules_pc0 "resolved_rules_pc0" ON ("PC0".pzInsKey = "resolved_rules_pc0".pzInsKey)  WHERE  ( "PC0".pyOwnerType = ?  )  AND "PC0".pxObjClass = ?
com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.
 at com.microsoft.sqlserver.jdbc.TDSCommand.checkForInterrupt(IOBuffer.java:5918)
 at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:70)
 at com.microsoft.sqlserver.jdbc.SQLServerResultSet.<init>(SQLServerResultSet.java:311)
...


A matching error is observed in the Pega ALERT logfile.


Steps to Reproduce



Unknown. No specific steps were found to reproduce this error.


Root Cause



Missing Process Commander INDEX definitions.  Specifically, the standard Pega 7.2 release was missing the following required database INDEXes:
  • PR4_RULE_VW.PYCIRCUMSTANCETYPE
  • PR4_RULE_VW.PYRULESTARTS
  • PR4_RULE_VW.PYRULEENDS


Resolution



User is advised to implement an INDEX on the above mentioned table.property values.
 

Published July 11, 2016 - Updated August 11, 2016

Have a question? Get answers now.

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