Question

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:10.81.10.96) .....

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***

**Moderation Team has archived post**

This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.

Group Tags

Correct Answer
April 25, 2017 - 1:44am

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

Comments

Keep up to date on this post and subscribe to comments

March 8, 2017 - 4:46am

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

March 9, 2017 - 4:17am
Response to Mahesh Midthuru

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.

March 9, 2017 - 6:19am
Response to RadhikaL

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

March 14, 2017 - 8:07am
Response to Mahesh Midthuru

Hi Mahesh,

I checked in Context.xml file, the values are as expected. Still the issue is there.

March 21, 2017 - 7:13am
Response to RadhikaL

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

April 25, 2017 - 1:44am

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

May 4, 2017 - 8:27am

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

 

Mod
May 4, 2017 - 8:30am
Response to JayForWeb

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!

Lochana | Community Moderator | Pegasystems Inc.