Hi all,

My client is on PRPC 717.  At time, I observe PRPC Managed Connection counter in SMA/Advanced/Database Management - what is this counter and how to interpret the number.  User has compliant the system became very slow, could be impacted by this PRPC connection pool running out ?

We have seen 3k - 4k, which we know the physical JDBC managed connection has been set to 125 in Tomcat only.

How can PRPC managed connection counter comes up with such a  big number.  Do we really use that high number of connections ?

Please see the screenshot in the attached file and the log also showing crazy number of connection .... ????

2016-03-29 10:05:41,844 [    AsyncServices-91] [  STANDARD] [                    ] [        OAM:01.11.01] (l.access.ConnectionManagerImpl) ERROR tmcprdpga701.tmc.rta.nsw.gov.au|XX.XX.XX.XXX CMCSUser - Not returning connection 3220 for database "pegarules" to the pool as it previously encountered the following error

User ID: (unknown)

Last SQL: WITH app_rulesets_pc0 AS ( SELECT "PC0AR".pzRuleSetName AS "pzRuleSetName", "PC0AR".pzRuleSetVersionMajor AS "pzRuleSetVersionMajor", "PC0AR".pzRuleSetVersionMinor AS "pzRuleSetVersionMinor", "PC0AR".pzRuleSetVersionPatch AS "pzRuleSetVersionPatch" FROM ( SELECT "PC0AR".pzRuleSetName, "PC0AR".pzRuleSetVersionMajor, "PC0AR".pzRuleSetVersionMinor, "PC0AR".pzRuleSetVersionPatch, ROW_NUMBER() OVER (PARTITION BY "PC0AR".pzRuleSetName ORDER BY "PC0AH".pzAppHeight) AS "pzRuleSetOrder" FROM rules.pr_sys_app_hierarchy_flat "PC0AH" INNER JOIN rules.pr_sys_app_ruleset_index "PC0AR" ON ("PC0AH".pzAppHash = "PC0AR".pzAppHash) WHERE "PC0AH".pzTopAppHash = ?  AND "PC0AH".pzAppName != 'PegaRULES') "PC0AR" WHERE "PC0AR"."pzRuleSetOrder" = 1 UNION ALL SELECT CAST(?  AS VARCHAR(128)) AS "pzRuleSetName", 1 AS "pzRulesetVersionMajor", 1 AS "pzRulesetVersionMinor", 1 AS "pzRuleSetVersionPatch"  ) ,rr_inheritance_pc0 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" FROM rules.pr_sys_ruleset_index "i" WHERE "i".pzRuleSetListHash = ?  ) "i" ON ("r".pyRuleSet = "i"."pzRuleSetName") WHERE "r".pxObjClass = ?  AND "r".pyRuleAvailable != 'No' AND "r".pyCircumstanceType IS NULL AND "r".pyRuleStarts IS NULL AND "r".pyRuleEnds IS NULL AND ( ( "r".pzRuleSetVersionMajor IS NULL OR "i"."pzRuleSetVersionMajor" = -1 OR "r".pzRuleSetVersionMajor = "i"."pzRuleSetVersionMajor" ) AND ( "r".pzRuleSetVersionMinor IS NULL OR "i"."pzRuleSetVersionMinor" = -1 OR "r".pzRuleSetVersionMinor <= "i"."pzRuleSetVersionMinor" ) AND ( "r".pzRuleSetVersionPatch IS NULL OR "i"."pzRuleSetVersionPatch" = -1 OR "r".pzRuleSetVersionMinor < "i"."pzRuleSetVersionMinor" OR ( "r".pzRuleSetVersionMinor = "i"."pzRuleSetVersionMinor" AND "r".pzRuleSetVersionPatch <= "i"."pzRuleSetVersionPatch" ) ) ) AND ( "r".pyClass IN ( SELECT "prbase".pyClassName FROM rules.pr4_base "prbase" WHERE "prbase".pyClassInheritance = 'true' AND "prbase".pyRuleResolution = 'true' AND "prbase".pyClassType = 'Concrete' AND "prbase".pxObjClass = 'Rule-Obj-Class' AND "prbase".pyClassName LIKE 'Rule%%' ) )  ) , 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" FROM rules.pr_sys_ruleset_index "i" WHERE "i".pzRuleSetListHash = ?  ) "i" ON ("r".pyRuleSet = "i"."pzRuleSetName") WHERE "r".pxObjClass = ?  AND "r".pyRuleAvailable != 'No' AND "r".pyCircumstanceType IS NULL AND "r".pyRuleStarts IS NULL AND "r".pyRuleEnds IS NULL AND ( ( "r".pzRuleSetVersionMajor IS NULL OR "i"."pzRuleSetVersionMajor" = -1 OR "r".pzRuleSetVersionMajor = "i"."pzRuleSetVersionMajor" ) AND ( "r".pzRuleSetVersionMinor IS NULL OR "i"."pzRuleSetVersionMinor" = -1 OR "r".pzRuleSetVersionMinor <= "i"."pzRuleSetVersionMinor" ) AND ( "r".pzRuleSetVersionPatch IS NULL OR "i"."pzRuleSetVersionPatch" = -1 OR "r".pzRuleSetVersionMinor < "i"."pzRuleSetVersionMinor" OR ( "r".pzRuleSetVersionMinor = "i"."pzRuleSetVersionMinor" AND "r".pzRuleSetVersionPatch <= "i"."pzRuleSetVersionPatch" ) ) ) AND ( "r".pyClass IN ( SELECT "prbase".pyClassName FROM rules.pr4_base "prbase" WHERE "prbase".pyClassInheritance = 'false' AND "prbase".pyRuleResolution = 'true' AND "prbase".pyClassType = 'Concrete' AND "prbase".pxObjClass = 'Rule-Obj-Class' AND "prbase".pyClassName LIKE 'Rule%%' ) )  ) , 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".pyClass IN ( SELECT "prbase".pyClassName FROM rules.pr4_base "prbase" WHERE "prbase".pyClassInheritance = 'false' AND "prbase".pyRuleResolution = 'false' AND "prbase".pyClassType = 'Concrete' AND "prbase".pxObjClass = 'Rule-Obj-Class' AND "prbase".pyClassName LIKE 'Rule%%' ) )  ) , 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 DISTINCT "W".pxWarningSeverity AS "pxWarningSeverity", COUNT("PC0".pzInsKey) AS "pySummaryCount(1)" FROM rules.pr4_rule_vw "PC0" INNER JOIN app_rulesets_pc0 "PC0AR" ON ((( ("PC0".pzRuleSetVersionMajor IS NULL OR "PC0".pzRuleSetVersionMajor = -1) AND ("PC0".pyRuleSet = "PC0AR"."pzRuleSetName")) OR (("PC0".pzRuleSetVersionMajor IS NOT NULL AND "PC0".pzRuleSetVersionMajor != -1) AND ("PC0".pyRuleSet = "PC0AR"."pzRuleSetName") AND ("PC0".pzRuleSetVersionMajor = "PC0AR"."pzRuleSetVersionMajor") AND (("PC0AR"."pzRuleSetVersionMinor" != -1 AND "PC0AR"."pzRuleSetVersionPatch" != -1 AND (("PC0".pzRuleSetVersionMinor < "PC0AR"."pzRuleSetVersionMinor") OR ("PC0".pzRuleSetVersionMinor = "PC0AR"."pzRuleSetVersionMinor" AND "PC0".pzRuleSetVersionPatch <= "PC0AR"."pzRuleSetVersionPatch"))) OR ("PC0AR"."pzRuleSetVersionMinor" != -1 AND "PC0AR"."pzRuleSetVersionPatch" = -1 AND "PC0".pzRuleSetVersionMinor <= "PC0AR"."pzRuleSetVersionMinor") OR ("PC0AR"."pzRuleSetVersionMinor" = -1)))) )  INNER JOIN resolved_rules_pc0 "resolved_rules_pc0" ON ("PC0".pzInsKey = "resolved_rules_pc0".pzInsKey)  INNER JOIN rules.pr_index_warnings "W" ON ( ( "PC0".pzInsKey = "W".pxReferencingRuleInsKey )  AND "PC0".pxObjClass = ?  AND "W".pxObjClass = ? ) WHERE  ( "W".pxWarningCreateOperator = ?  AND "W".pxWarningCreatedTime BETWEEN ?  AND ?  AND  "PC0".pyRuleSet NOT LIKE ?   )  GROUP BY "W".pxWarningSeverity ORDER BY 1 ASC

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)

  at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1526)

  at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)

  at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)

  at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)

Regards

Seri