SA-39884

Summary

A developer is working on new development in the environment, and attempts to generate the Direct Capture of Objects (DCO) Design Document for their application.

The system appears to hang (developers are unable to continue working), and the document generation process appears to halt.

NOTE that this is a Microsoft SQL Server environment.

The SA has setup test environments using PostGRES and OraclePE, and was unable to reproduce this behavior out of the box.

Error Messages

No error messages on screen; no applicable error messages were found in the log. However, analysis of the ALERT log does identify two key SQL statements that appear to be related to the DCO document generation. These SQL statements are timing out the database.

NOTE that the SQL statements are too lengthy to post here. However, their first five lines are provided:

SQL STATEMENT ONE

WITH
rr_inheritance_pc0 AS
(
SELECT
"r".pzInsKey,
"r".pyRuleAvailable,
...

SQL STATEMENT TWO

WITH
app_rulesets_pc0 AS
(
SELECT
"PC0AR".pzRuleSetName AS "pzRuleSetName",
"PC0AR".pzRuleSetVersionMajor AS "pzRuleSetVersionMajor",
"PC0AR".pzRuleSetVersionMinor AS "pzRuleSetVersionMinor",
...

Steps to Reproduce

To reproduce this issue, the developer triggers the generation of the DCO document in the DEV environment.

Root Cause

The root cause was found to be database performance. Specifically, multiple Pega standard requisite database Indexes were found to be missing, and some tuning was required against the database.

Resolution

To resolve this issue, two steps were implemented:

1) At the database, update the database statistics on the following tables:

PR4_RULE_VW
PR_SYS_RULESET_INDEX
PR4_RULE_FLOW

2) Create the following additional database INDEXes:

CREATE INDEX
RULES.PR_SYS_CLASS_ANCEST_RR3
ON
RULES.PR_SYS_CLASS_ANCESTORS
(
PXLEAFCLASSNAME ASC,
PXCLASSNAME ASC,
PXUCCLASSNAME ASC,
PXHEIGHT ASC
)

CREATE INDEX
DATA.OPERATORS_IDX01
ON
DATA.PR_OPERATORS
(
PXOBJCLASS ASC,
PYACCESSGROUP ASC
)

CREATE INDEX
RULES.PCASSIGNWRKLST_IDX01
ON
RULES.PC_ASSIGN_WORKLIST
(
PXAPPLICATION ASC,
PXOBJCLASS ASC,
PXASSIGNEDOPERATORID ASC,
PXWORKGROUP ASC
)


CREATE INDEX
DATA.PR_LOG_RULE_NODE_IDX02
ON
DATA.PR4_LOG_RULE_USAGE
(
PXSYSTEMNODE ASC,
PYLABEL ASC,
PZINSKEY ASC,
PXLASTUSE ASC
)

CREATE INDEX
DATA.PR_DATA_RECENTS_IDX2
ON
DATA.PR_DATA_RECENTS
(
PXUPDATEOPNAME ASC,
PXOBJCLASS ASC
)

CREATE INDEX
RULES.PR4_RULE_VW_IDX5B
ON
RULES.PR4_RULE_VW
(
"PYRULESET" ASC,
"PZRULESETVERSIONMAJOR" ASC,
"PZRULESETVERSIONMINORPATCH" ASC,
"PYCLASS" ASC,
"PYRULEAVAILABLE" ASC,
"PZCLASSTYPE" ASC,
"PYRULESETVERSION ASC
)

CREATE INDEX
RULES.PR4_RULE_FLOW_IDX1
ON
RULES.PR4_RULE_FLOW
(
"PZINSKEY" ASC,
"PYRULESET" ASC,
"PYRULEAVAILABLE" ASC,
"PYCLASSNAME" ASC,
"PXOBJCLASS" ASC
)

CREATE INDEX
RULES.PR_SYS_APP_HIERARCHY_FLAT_IDX4
ON
RULES.PR_SYS_APP_HIERARCHY_FLAT
(
"PZAPPHASH" ASC,
"PZTOPAPPHASH" ASC,
"PZAPPNAME" ASC,
"PZAPPHEIGHT" ASC
)

CREATE INDEX
RULES.PR4_RULE_VW_IDX5C
ON
RULES.PR4_RULE_VW
(
"PYRULESET" ASC,
"PZRULESETVERSIONMAJOR" ASC,
"PZRULESETVERSIONMINORPATCH" ASC,
"PYCLASS" ASC,
"PYRULEAVAILABLE" ASC,
"PYCLASSNAME" ASC,
"PZCLASSTYPE" ASC,
"PYRULESETVERSION" ASC,
"PXOBJCLASS" ASC,
"PYCIRCUMSTANCETYPE" ASC,
"PYRULESTARTS" ASC,
"PYRULEENDS" ASC,
"PXINSID" ASC
)

CREATE INDEX
RULES.PR_REFERENCE_PAGE_IDX4
ON
RULES.PR_REFERENCE_PAGE
(
"PXINSINDEXEDKEY" ASC,
"PXINSINDEXEDCLASS" ASC,
"PXOBJCLASS" ASC,
"PXPAGECLASS" ASC,
"PXPAGENAME" ASC
)

CREATE INDEX
RULES.PR4_RULE_VW_IDX5D
ON
RULES.PR4_RULE_VW
(
"PYRULESET" ASC,
"PZRULESETVERSIONMAJOR" ASC,
"PZRULESETVERSIONMINORPATCH" ASC,
"PYCLASS" ASC,
"PYRULEAVAILABLE" ASC,
"PYCLASSNAME" ASC,
"PZCLASSTYPE" ASC,
"PYRULESETVERSION" ASC,
"PXINSID" ASC,
"PYCIRCUMSTANCEPROP" ASC,
"PYCIRCUMSTANCEVAL" ASC,
"PYCIRCUMSTANCEDATEPROP" ASC,
"PYCIRCUMSTANCEDATE" ASC,
"PYRULESTARTS" ASC,
"PYRULEENDS" ASC
)

CREATE INDEX
RULES.RULEFLOWRES_IDX2
ON
RULES.PR4_RULE_FLOW
(
"PZINSKEY" ASC,
"PYRULESET" ASC,
"PYRULEAVAILABLE" ASC,
"PYCLASSNAME" ASC,
"PXOBJCLASS" ASC
)