Skip to main content

This content is closed to future replies and is no longer being maintained or updated.
Links may no longer function. If you have a similar request, please write a new post.

Question

 PRADEEP CHOWDARY PYDI (PradeepPydi) Anthem Senior PEGA DEVOPS and Cloud Administrator
Anthem
US
PradeepPydi Member since 2017 240 posts
Anthem
Posted: Mar 15, 2021
Last activity: Jan 28, 2022
Posted: 15 Mar 2021 2:50 EDT
Last activity: 28 Jan 2022 6:15 EST
Closed

pr_sys_serviceregistry related PEGA0005 Alerts.

Hi Team,

We have upgraded recently to PRPC842 version and below mentioned SQL queries related to pr_sys_serviceregistry table haven been observed to be impacting the performance of application.

To start with, what does this pr_sys_serviceregistry do ? Is this related to any PEGA OOTB agent ? Do we need to implement any indexes ? or ay Hotfix or DSS ? I couldnt find any information regardng this table across PDN.

Queries for reference below :

1) SELECT "SKVS"."pyKey" AS "pyKey" , "SKVS"."pyParent" AS "pyParent" , "SKVS"."pyValue" AS "pyValue" , "SKVS"."pyVersion" AS "pyVersion" , "SKVS"."pyCreateDateTime" AS "pyCreateDateTime" , "SKVS"."pyUpdateDateTime" AS "pyUpdateDateTime" , "SKVS"."pyMode" AS "pyMode" , "SKVS"."pySessionId" AS "pySessionId" FROM dbo.pr_sys_serviceregistry_kvs "SKVS" WHERE  ( "SKVS"."pyKey" = ?  AND  ( "SKVS"."pySessionId" IS NULL OR  ( "SKVS"."pySessionId" IN (SELECT "NSRS"."pySessionId" AS "pySessionId" FROM dbo.pr_sys_serviceregistry "NSRS") AND "SKVS"."pySessionId" NOT IN (SELECT "FSRS"."pySessionId" AS "pySessionId" FROM dbo.pr_sys_serviceregistry "FSRS" WHERE  ( "FSRS"."pyLastSeenDateTime" <= (?  - "FSRS"."pyTimeout") ) ) )  )  )

Show More

Hi Team,

We have upgraded recently to PRPC842 version and below mentioned SQL queries related to pr_sys_serviceregistry table haven been observed to be impacting the performance of application.

To start with, what does this pr_sys_serviceregistry do ? Is this related to any PEGA OOTB agent ? Do we need to implement any indexes ? or ay Hotfix or DSS ? I couldnt find any information regardng this table across PDN.

Queries for reference below :

1) SELECT "SKVS"."pyKey" AS "pyKey" , "SKVS"."pyParent" AS "pyParent" , "SKVS"."pyValue" AS "pyValue" , "SKVS"."pyVersion" AS "pyVersion" , "SKVS"."pyCreateDateTime" AS "pyCreateDateTime" , "SKVS"."pyUpdateDateTime" AS "pyUpdateDateTime" , "SKVS"."pyMode" AS "pyMode" , "SKVS"."pySessionId" AS "pySessionId" FROM dbo.pr_sys_serviceregistry_kvs "SKVS" WHERE  ( "SKVS"."pyKey" = ?  AND  ( "SKVS"."pySessionId" IS NULL OR  ( "SKVS"."pySessionId" IN (SELECT "NSRS"."pySessionId" AS "pySessionId" FROM dbo.pr_sys_serviceregistry "NSRS") AND "SKVS"."pySessionId" NOT IN (SELECT "FSRS"."pySessionId" AS "pySessionId" FROM dbo.pr_sys_serviceregistry "FSRS" WHERE  ( "FSRS"."pyLastSeenDateTime" <= (?  - "FSRS"."pyTimeout") ) ) )  )  )

2) SELECT "SSR"."pyName" AS "pyName" , "SSR"."pyAddress" AS "pyAddress" , "SSR"."pyPort" AS "pyPort" , "SSR"."pyState" AS "pyState" , "SSR"."pyNodeId" AS "pyNodeId" , "SSR"."pyVersion" AS "pyVersion" , "SSR"."pyMeta" AS "pyMeta" , "SSR"."pyTags" AS "pyTags" , "SSR"."pyTimeout" AS "pyTimeout" , "SSR"."pyStartTime" AS "pyStartTime" , "SSR"."pySessionId" AS "pySessionId" FROM dbo.pr_sys_serviceregistry "SSR" WHERE  ( "SSR"."pyName" = ?  AND "SSR"."pyLastSeenDateTime" > (?  - "SSR"."pyTimeout") )  ORDER BY 5 ASC, 10 DESC

3) SELECT "SKVS"."pyKey" AS "pyKey" , "SKVS"."pyParent" AS "pyParent" , "SKVS"."pyValue" AS "pyValue" , "SKVS"."pyVersion" AS "pyVersion" , "SKVS"."pyCreateDateTime" AS "pyCreateDateTime" , "SKVS"."pyUpdateDateTime" AS "pyUpdateDateTime" , "SKVS"."pyMode" AS "pyMode" , "SKVS"."pySessionId" AS "pySessionId" FROM dbo.pr_sys_serviceregistry_kvs "SKVS" WHERE  (  ( "SKVS"."pyKey" = ?  OR "SKVS"."pyParent" = ?  OR  "SKVS"."pyParent" LIKE ?   )  AND  ( "SKVS"."pySessionId" IS NULL OR  ( "SKVS"."pySessionId" IN (SELECT "NSRS"."pySessionId" AS "pySessionId" FROM dbo.pr_sys_serviceregistry "NSRS") AND "SKVS"."pySessionId" NOT IN (SELECT "FSRS"."pySessionId" AS "pySessionId" FROM dbo.pr_sys_serviceregistry "FSRS" WHERE  ( "FSRS"."pyLastSeenDateTime" <= (?  - "FSRS"."pyTimeout") ) ) )  )  )

4) SELECT CURRENT_TIMESTAMP AS "CurrentDateTime" FROM dbo.pr_sys_serviceregistry "SSR"

5) SELECT "SSR"."pyName" AS "pyName" , "SSR"."pyAddress" AS "pyAddress" , "SSR"."pyPort" AS "pyPort" , "SSR"."pyState" AS "pyState" , "SSR"."pyNodeId" AS "pyNodeId" , "SSR"."pyVersion" AS "pyVersion" , "SSR"."pyMeta" AS "pyMeta" , "SSR"."pyTags" AS "pyTags" , "SSR"."pyTimeout" AS "pyTimeout" , "SSR"."pyStartTime" AS "pyStartTime" , "SSR"."pySessionId" AS "pySessionId" FROM dbo.pr_sys_serviceregistry "SSR" WHERE  ( "SSR"."pyLastSeenDateTime" > (?  - "SSR"."pyTimeout") )  ORDER BY 5 ASC, 10 DESC

 

Show Less
***Edited by Moderator: Pooja Gadige to add platform capability tag***
To see attachments, please log in.
Pega Platform 8.4.2 System Administration

Posted: 3 years ago
Updated: 3 years ago
Posted: 15 Mar 2021 5:39 EDT
Updated: 15 Mar 2021 5:41 EDT
PRADEEP CHOWDARY PYDI (PradeepPydi) Anthem Senior PEGA DEVOPS and Cloud Administrator
Anthem
US

@MarcLasserre_GCS How does a type and version of database matters here ? what does this pr_sys_serviceregistry do ? Is this related to any PEGA OOTB agent ? Do we need to implement any indexes ? or apply new Hotfix or DSS ? 

Driver Information
Database Product Name Microsoft SQL Server
Database Product Version 11.00.7493
Driver Name Microsoft JDBC Driver 6.0 for SQL Server
Driver Version 6.0.8112.100
To see attachments, please log in.
Posted: 2 years ago
Posted: 28 Jan 2022 2:15 EST
Yashvant Nagendra (Yashvant N) Senior Consultant Senior Consultant
Senior Consultant
CH

Hi @PradeepPydi, Did you got any response for this issue? Was this issue fixed? Can you please share any update on this issue, because in my account as well, am facing the very same issue. In PDC, am seeing the same query getting triggered and resulting to The connection is closed message. Any inputs to resolve this issue, is very helpful. Thanks in Advance

Show More

Hi @PradeepPydi, Did you got any response for this issue? Was this issue fixed? Can you please share any update on this issue, because in my account as well, am facing the very same issue. In PDC, am seeing the same query getting triggered and resulting to The connection is closed message. Any inputs to resolve this issue, is very helpful. Thanks in Advance

Type com.pega.pegarules.pub.database.ConnectionException
   
Message There was a problem getting a list: code: 0 SQLState: <none> Message: The connection is closed.DatabaseException caused by prior exception: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed. | SQL Code: 0 | SQL State: nullFrom: (BAQ8D0WF5XGOVCJOABWDV6J0RIA8ASBVMA) SQL: SELECT "SKVS"."pyKey" AS "pyKey", "SKVS"."pyParent" AS "pyParent", "SKVS"."pyValue" AS "pyValue", "SKVS"."pyVersion" AS "pyVersion", "SKVS"."pyCreateDateTime" AS "pyCreateDateTime", "SKVS"."pyUpdateDateTime" AS "pyUpdateDateTime", "SKVS"."pyMode" AS "pyMode", "SKVS"."pySessionId" AS "pySessionId" FROM pegadata.pr_sys_serviceregistry_kvs "SKVS" WHERE ("SKVS"."pyKey" = ? AND ("SKVS"."pySessionId" IS NULL OR ("SKVS"."pySessionId" IN (SELECT "NSRS"."pySessionId" AS "pySessionId" FROM pegadata.pr_sys_serviceregistry "NSRS") AND "SKVS"."pySessionId" NOT IN (SELECT "FSRS"."pySessionId" AS "pySessionId" FROM pegadata.pr_sys_serviceregistry "FSRS" WHERE ("FSRS"."pyLastSeenDateTime" <= (? - "FSRS"."pyTimeout"))))))
   
Stack 
at (:)
Show Less
To see attachments, please log in.
Posted: 2 years ago
Posted: 28 Jan 2022 6:15 EST
Yashvant Nagendra (Yashvant N) Senior Consultant Senior Consultant
Senior Consultant
CH

Just to keep Informed, we performed complete restart for all nodes and the above issue got resolved automatically.

During initial analysis, found that none of the nodes were not able to access the instance available in pr_sys_serviceregistry_kvs table and it was resulting in connection closed error. But after restart, this issue was not happening anymore. 

 

To see attachments, please log in.

We'd prefer it if you saw us at our best.

Pega Collaboration Center has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice