Question
HCL
NL
Last activity: 4 May 2017 8:30 EDT
Getting DatabaseException : the Query has timed out, when trying to open any item from Data Explorer
I have created a new application in PEGA v7.1.9. While creating the application itself i added few data objects. So the corresponding data classes got created.
Now, when i am trying to open any data Item form the Data Explorer, i am getting following exception in logs and a distorted screen gets displayed on the screen (Attached the screenshot)
Can anybody please help me resolving this issue?
Exception:
An error occured on executing the query for the report definition - There was a problem getting a list: code: 0 SQLState: HY008 Message: The query has timed out.
com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: 0 SQLState: HY008 Message: The query has timed out.
From: (H8CA3901010CBAE280B6016C09DEC3A1E: Proprietary information hidden) .....
I have created a new application in PEGA v7.1.9. While creating the application itself i added few data objects. So the corresponding data classes got created.
Now, when i am trying to open any data Item form the Data Explorer, i am getting following exception in logs and a distorted screen gets displayed on the screen (Attached the screenshot)
Can anybody please help me resolving this issue?
Exception:
An error occured on executing the query for the report definition - There was a problem getting a list: code: 0 SQLState: HY008 Message: The query has timed out.
com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: 0 SQLState: HY008 Message: The query has timed out.
From: (H8CA3901010CBAE280B6016C09DEC3A1E: Proprietary information hidden) .....
Caused by SQL Problems.
Problem #1, SQLState HY008, Error code 0: com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.
at com.pega.pegarules.data.internal.access.ExceptionInformation.createExceptionDueToDBFailure(ExceptionInformation.java:257)
at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:6080)
at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:5785)
at com.pega.pegarules.data.internal.rd.queryexec.SqlQueryExecutor.executeQuery(SqlQueryExecutor.java:68)
at com.pega.pegarules.data.internal.rd.queryexec.SqlQueryExecutor.executeQuery(SqlQueryExecutor.java:43)
at com.pega.pegarules.data.internal.rd.SqlReportExecutor.executeQuery(SqlReportExecutor.java:284)
at com.pega.pegarules.data.internal.rd.SqlReportExecutor.executeMainReport(SqlReportExecutor.java:128)
at com.pega.pegarules.data.internal.rd.SqlReportExecutor.execute(SqlReportExecutor.java:90)
at com.pega.pegarules.data.internal.rd.SqlReportExecutor.execute(SqlReportExecutor.java:57)
at com.pega.pegarules.data.internal.PRDataProviderImpl.executeReport(PRDataProviderImpl.java:1203)
at com.pega.pegarules.session.internal.mgmt.Executable.executeReport(Executable.java:10716)
at ......
***** Detailed Exception is attached
***Updated by moderator: Lochan to add SR details***
Accepted Solution
HCL
NL
This issue is resolved and following is the fix.
Run sp_updatestats on SQL server. And test the issue.
If this does not bring any improvement then create the below index
i.e
USE [PEGA71]
GO
CREATE NONCLUSTERED INDEX [pr4_rule_vw_idx7]
ON [PEGA71].[pr4_rule_vw] ([pyClass],[pyCircumstanceType],[pyRuleStarts],[pyRuleEnds],[pzClassType],[pyRuleAvailable])
INCLUDE ([pxInsId],[pyRuleSet],[pyRuleSetVersion],[pzRuleSetVersionMajor],[pzRuleSetVersionMinorPatch])
GO
Note: replace PEGA71 by your
schema name.
You can also replace the name of
the index “pr4_rule_vw_idx7” by the name of your choice. Make sure you don't overwrite an existing index.
This solution was provided as part of a SR resolution
SR-B38828
Pegasystems Inc.
US
Hi Radhika,
As per the stack trace attached it looks like similar issue specified over here. Kindly look into this article so that it can be helpful in resolving your issue.
Regards
Mahesh
HCL
NL
Thanks Mahesh for the reference.
Can you please tell me how and where do we change this configuration?
I understand that, this is something that must have been done when they installed PEGA.
Pegasystems Inc.
US
Hi Radhika,
If you are using tomcat server then this information you can find in context.xml file in <Resource connectionProperties="...."/>. Hope this might be helpful to you.
Regards
Mahesh
Pegasystems Inc.
US
Hi Radhika,
Apologies for the delayed response. I would recommend you to raise an SR as it needs more investigation further to find the root cause of the issue. Thank you.
Regards
Mahesh
Accepted Solution
HCL
NL
This issue is resolved and following is the fix.
Run sp_updatestats on SQL server. And test the issue.
If this does not bring any improvement then create the below index
i.e
USE [PEGA71]
GO
CREATE NONCLUSTERED INDEX [pr4_rule_vw_idx7]
ON [PEGA71].[pr4_rule_vw] ([pyClass],[pyCircumstanceType],[pyRuleStarts],[pyRuleEnds],[pzClassType],[pyRuleAvailable])
INCLUDE ([pxInsId],[pyRuleSet],[pyRuleSetVersion],[pzRuleSetVersionMajor],[pzRuleSetVersionMinorPatch])
GO
Note: replace PEGA71 by your
schema name.
You can also replace the name of
the index “pr4_rule_vw_idx7” by the name of your choice. Make sure you don't overwrite an existing index.
This solution was provided as part of a SR resolution
SR-B38828
MeBank
AU
Hi there,
We use PEGA 7.1.9 and noticed random occurrences of the same SQLState HY008 error on the logs.
This PDN article contradicts the JDBC driver connection string property documentations at the following places.
https://docs.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties
https://docs.oracle.com/cd/E13157_01/wlevs/docs30/jdbc_drivers/mssqlserver.html#wp1074599
Should we raise a separate SR or is it advisable to go ahead with the accepted correcte answer?
Thanks,
Jay
This comment has been branched out as a new node and the new thread can be followed here
Pegasystems Inc.
IN
Hi Jay,
To increase the visibility for your question, we have branched your reply in this new post: [PEGA 7.1.9] Noticed random occurrences of the same SQLState HY008 error on the logs
Please update the new discussion to continue the discussion.
Thank you!