Question

4
Replies
758
Views
Seri Charoensri (chars1)
PEGA
Sr. System Architect
Pegasystems Inc.
AU
chars1 Member since 2015 46 posts
PEGA
Posted: March 29, 2016
Last activity: June 4, 2016
Posted: 29 Mar 2016 2:49 EDT
Last activity: 4 Jun 2016 3:32 EDT
Closed

PRPC managed connections counter ?

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)

Show More

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

Show Less
Data Integration
Moderation Team has archived post, This thread is closed to future replies. Content and links will no longer be updated. If you have the same/similar Question, please write a new Question.