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

 Anandh Palanisamy (Anandh Palanisamy)
Ford Motor Company

Ford Motor Company
US
Anandh Palanisamy Member since 2013 62 posts
Ford Motor Company
Posted: Jun 22, 2018
Last activity: Jul 6, 2018
Posted: 22 Jun 2018 10:19 EDT
Last activity: 6 Jul 2018 9:34 EDT
Closed
Solved

Getting DatabaseException caused by prior exception: com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out. | SQL Code: 0 | SQL State: HY008

Hi,

We are facing DB locktime out error while opening view from Case type, its keep getting the below exception.

Show More

Hi,

We are facing DB locktime out error while opening view from Case type, its keep getting the below exception.

2018-06-22 09:18:24,018 [       TP_http : 436] [TABTHREAD1] [                    ] [   TestJawa:01.01.01] ( rd.queryexec.SqlQueryExecutor) ERROR wwwdev.tracklite.ford.com|xx.xx.xx.xx [email protected] - 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.
DatabaseException caused by prior exception: com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.
 | SQL Code: 0 | SQL State: HY008

com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: 0 SQLState: HY008 Message: The query has timed out.
DatabaseException caused by prior exception: com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.
 | SQL Code: 0 | SQL State: HY008

	at com.pega.pegarules.data.internal.access.ExceptionInformation.createAppropriateExceptionDueToDBFailure(ExceptionInformation.java:379) ~[prprivate.jar:?]
	at com.pega.pegarules.data.internal.access.ExceptionInformation.createExceptionDueToDBFailure(ExceptionInformation.java:358) ~[prprivate.jar:?]

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.

2018-06-22 09:18:24,021 [       TP_http : 436] [TABTHREAD1] [                    ] [   TestJawa:01.01.01] ( rd.queryexec.SqlQueryExecutor) ERROR wwwdev.tracklite.ford.com|xx.xx.xx.xx [email protected] - Query: WITH rr_inheritance_pc0 AS ( SELECT "r".pzInsKey, "r".pyRuleAvailable, "r".pyClassName, "r".pyRuleSet, RANK() OVER (PARTITION BY "r".pxInsId, "r".pyClass ORDER BY "c".pxHeight, "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 Rules.pr_sys_class_ancestors "c" ON ("r".pyClassName = "c".pxClassName) INNER JOIN ( SELECT "i".pzRuleSetName AS "pzRuleSetName", "i".pzRuleSetIndex AS "pzRuleSetIndex", "i".pzRuleSetVersionMajor AS "pzRuleSetVersionMajor", "i".pzRuleSetVersionMinor AS "pzRuleSetVersionMinor", "i".pzRuleSetVersionPatch AS "pzRuleSetVersionPatch" , "i".pzRuleSetVersionMinorPatch AS "pzRuleSetVersionMinorPatch" FROM Rules.pr_sys_ruleset_index "i" WHERE "i".pzRuleSetListHash = ?  UNION ALL SELECT CAST(?  AS VARCHAR(128)) AS "pzRuleSetName", -1 AS "pzRuleSetIndex", 1 AS "pzRuleSetVersionMajor", 1 AS "pzRuleSetVersionMinor", 1 AS "pzRuleSetVersionPatch" , '01-01' AS "pzRuleSetVersionMinorPatch" ) "i" ON ("r".pyRuleSet = "i"."pzRuleSetName") AND ("r".pzRuleSetVersionMajor = "i"."pzRuleSetVersionMajor" AND "r".pzRuleSetVersionMinorPatch <= "i"."pzRuleSetVersionMinorPatch") WHERE "r".pyClass = ?  AND "c".pxLeafClassName = ?  AND "r".pyRuleAvailable != 'No' AND "r".pyCircumstanceType IS NULL AND "r".pyRuleStarts IS NULL AND "r".pyRuleEnds IS NULL AND "r".pzClassType = 0 ) , 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" , "i".pzRuleSetVersionMinorPatch AS "pzRuleSetVersionMinorPatch" FROM Rules.pr_sys_ruleset_index "i" WHERE "i".pzRuleSetListHash = ?  UNION ALL SELECT CAST(?  AS VARCHAR(128)) AS "pzRuleSetName", -1 AS "pzRuleSetIndex", 1 AS "pzRuleSetVersionMajor", 1 AS "pzRuleSetVersionMinor", 1 AS "pzRuleSetVersionPatch" , '01-01' AS "pzRuleSetVersionMinorPatch" ) "i" ON ("r".pyRuleSet = "i"."pzRuleSetName") AND ("r".pzRuleSetVersionMajor = "i"."pzRuleSetVersionMajor" AND "r".pzRuleSetVersionMinorPatch <= "i"."pzRuleSetVersionMinorPatch") WHERE "r".pyClass = ?  AND "r".pyRuleAvailable != 'No' AND "r".pyCircumstanceType IS NULL AND "r".pyRuleStarts IS NULL AND "r".pyRuleEnds IS NULL AND "r".pzClassType = 1 ) , 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".pzClassType = 2  ) , 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 "PC0"."pyPropertyName" AS "pyPropertyName" , "PC0"."pxInsId" AS "pxInsId" , "PC0"."pyLabel" AS "pyLabel" , "PC0"."pyRuleSet" AS "pyRuleSet" , "PC0"."pzInsKey" AS "pzInsKey" FROM Rules.pr4_rule_property "PC0" INNER JOIN resolved_rules_pc0 "resolved_rules_pc0" ON ("PC0".pzInsKey = "resolved_rules_pc0".pzInsKey)  WHERE  ( "PC0"."pyClassName" = ?  )  AND "PC0"."pxObjClass" = ?  ORDER BY 3 ASC
2018-06-22 09:18:56,881 [       TP_http : 443] [TABTHREAD1] [                    ] [   TestJawa:01.01.01] ( rd.queryexec.SqlQueryExecutor) ERROR wwwdev.tracklite.ford.com|xx.xx.xx.xx [email protected] - 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.
DatabaseException caused by prior exception: com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.
 | SQL Code: 0 | SQL State: HY008

com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: 0 SQLState: HY008 Message: The query has timed out.
DatabaseException caused by prior exception: com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.
 | SQL Code: 0 | SQL State: HY008

***Edited by Moderator Marissa to update SR Details***


Show Less
To see attachments, please log in.
Dev/Designer Studio
Support Case Exists

Accepted Solution

Posted: 6 years ago
Posted: 29 Jun 2018 14:09 EDT
Anandh Palanisamy (Anandh Palanisamy)
Ford Motor Company

Ford Motor Company
US

Hi All,

This is SR-C41959 and HFix DL-88518 and DSS needs to be done.

PRPC restart is required and the following DSS must be created for the changes to take effect: 
1. 
Purpose: reporting/useForceOrderHint 
Ruleset: Pega-RULES 
Value: true 
for ac 
2. 
reporting/useForceOrderHintForRRandAC 
ruleset: PEGA-RULES 
value: true 
3. 
Purpose: reporting/useMergeHintForRRquery 
Ruleset: Pega-Reporting 
Value: true

 Regards,

Anandh

To see attachments, please log in.
Posted: 6 years ago
Posted: 22 Jun 2018 13:38 EDT
Ujjwal Kumar Shukla (UjjwalShukla_GCS)
PEGA
Senior Manager, Technical Support
Pegasystems Inc.
IN

Hi There,

The timeout setting on Data Access tab for Report definition must be very low and hence the operation cancelled (HY008) is being thrown.

Below URL should help in understanding

https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlcancel-function?view=sql-server-2017

Regards,
Ujjwal

To see attachments, please log in.
Posted: 6 years ago
Posted: 25 Jun 2018 17:59 EDT
Sowndar Kamalakannan (kamas1)
PEGA
Senior System Architect
Pegasystems Inc.
GB

I'm using Pega 74 installed on MS Azure and MS SQL Server. I'm having the same issue all the time. As you mentioned it is pega ootb activities which post this SQL and causing slowness. I tried recreating index but didn't help.

 

exec sp_updatestats
 
USE PegaRULES
 
GO
 
CREATE NONCLUSTERED INDEX [pr4_rule_vw]
 
ON [PegaRULES].[pr4_rule_vw] ([pyClass],[pyCircumstanceType],[pyRuleStarts],[pyRuleEnds],[pzClassType],[pyRuleAvailable])
 
INCLUDE ([pxInsId],[pyRuleSet],[pyRuleSetVersion],[pzRuleSetVersionMajor],[pzRuleSetVersionMinorPatch])
 
GO
To see attachments, please log in.
Posted: 6 years ago
Posted: 25 Jun 2018 11:42 EDT
Arun Kumar Mahanty (Arun_Mahanty)
Pegasystems Inc.
Principal Technical Solutions Engineer
Pegasystems Inc.
IN

I didn’t find the activity name just wondering if you miss to copy the activity name in the stack trace  ,let us know the activity name .

As query timeout exception occurs did you involve your DBA if the query can be tuned by applying indexing on the columns or through any other technique .

To see attachments, please log in.
Posted: 6 years ago
Posted: 25 Jun 2018 14:11 EDT
Anandh Palanisamy (Anandh Palanisamy)
Ford Motor Company

Ford Motor Company
US

here some more details.

Show More

here some more details.

2018-06-25 10:58:51,033 [       TP_http : 673] [TABTHREAD0] [                    ] [   SampleAp:01.01.01] ( rd.queryexec.SqlQueryExecutor) ERROR wwwdev.tracklite.ford.com| Proprietary information hidden apalanis - 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.
DatabaseException caused by prior exception: com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.
 | SQL Code: 0 | SQL State: HY008

com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: 0 SQLState: HY008 Message: The query has timed out.
DatabaseException caused by prior exception: com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.
 | SQL Code: 0 | SQL State: HY008

	at com.pega.pegarules.data.internal.access.ExceptionInformation.createAppropriateExceptionDueToDBFailure(ExceptionInformation.java:379) ~[prprivate.jar:?]
	at com.pega.pegarules.data.internal.access.ExceptionInformation.createExceptionDueToDBFailure(ExceptionInformation.java:358) ~[prprivate.jar:?]
	at com.pega.pegarules.data.internal.access.Lister.convertSqlExceptionToDatabaseException(Lister.java:577) ~[prprivate.jar:?]
	at com.pega.pegarules.data.internal.access.Lister.listWithResultPresenter(Lister.java:260) ~[prprivate.jar:?]
	at com.pega.pegarules.data.internal.rd.queryexec.SqlQueryExecutor.executeQuery(SqlQueryExecutor.java:99) ~[prprivate.jar:?]
	at com.pega.pegarules.data.internal.rd.queryexec.SqlQueryExecutor.executeQuery(SqlQueryExecutor.java:50) ~[prprivate.jar:?]
	at com.pega.pegarules.data.internal.rd.SQLExecutorResultsCP.executeQuery(SQLExecutorResultsCP.java:112) ~[prprivate.jar:?]
	at com.pega.pegarules.data.internal.rd.SqlReportExecutor.executeResolvedReport(SqlReportExecutor.java:189) ~[prprivate.jar:?]
	at com.pega.pegarules.data.internal.rd.SqlReportExecutor.executeMainResolvedReport(SqlReportExecutor.java:171) ~[prprivate.jar:?]
	at com.pega.pegarules.data.internal.rd.SqlReportExecutor.executeResolved(SqlReportExecutor.java:132) ~[prprivate.jar:?]
	at com.pega.pegarules.data.internal.rd.SqlReportExecutor.executeResolved(SqlReportExecutor.java:57) ~[prprivate.jar:?]
	at com.pega.pegarules.data.internal.rd.query.resolution.rdbms.RDBMSExecutable.call(RDBMSExecutable.java:49) ~[prprivate.jar:?]
	at com.pega.pegarules.data.internal.rd.query.resolution.rdbms.RDBMSExecutable.call(RDBMSExecutable.java:28) ~[prprivate.jar:?]
	at com.pega.platform.query.LogicalQueryExecutor$1.call(LogicalQueryExecutor.java:60) ~[logicaldatastore.jar:?]
	at com.pega.platform.query.LogicalQueryExecutor$1.call(LogicalQueryExecutor.java:53) ~[logicaldatastore.jar:?]
	at com.pega.pegarules.data.internal.PRDataProviderImpl.executeReport(PRDataProviderImpl.java:1521) ~[prprivate.jar:?]
	at com.pega.pegarules.session.internal.mgmt.Executable.executeReport(Executable.java:11060) ~[prprivate.jar:?]
	at com.pegarules.generated.definition.ra_action_pxgetrulesbyrecordtypeandclassname_0342ae29de61501cabd6d3d0610572aa.perform(ra_action_pxgetrulesbyrecordtypeandclassname_0342ae29de61501cabd6d3d0610572aa.java:52) ~[?:?]
	at com.pega.pegarules.session.internal.mgmt.Executable.doAction(Executable.java:3226) ~[prprivate.jar:?]
	at com.pegarules.generated.activity.ra_action_pxretrievereportdata_40a1399d83bc4f5b98c382e1b9082d42.step33_circum0(ra_action_pxretrievereportdata_40a1399d83bc4f5b98c382e1b9082d42.java:3596) ~[?:?]
	at com.pegarules.generated.activity.ra_action_pxretrievereportdata_40a1399d83bc4f5b98c382e1b9082d42.perform(ra_action_pxretrievereportdata_40a1399d83bc4f5b98c382e1b9082d42.java:701) ~[?:?]
	at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3421) ~[prprivate.jar:?]
	at com.pega.pegarules.session.internal.mgmt.Executable.invokeActivity(Executable.java:10794) ~[prprivate.jar:?]

Show Less
To see attachments, please log in.
Posted: 6 years ago
Posted: 26 Jun 2018 2:55 EDT
Ujjwal Kumar Shukla (UjjwalShukla_GCS)
PEGA
Senior Manager, Technical Support
Pegasystems Inc.
IN

Hi There,

Could you please provide the activity name, if it is OOTB.
Can you also attach tracer results of the screen shot of that to understand more on this.

Regards,
Ujjwal

To see attachments, please log in.
Posted: 6 years ago
Posted: 26 Jun 2018 7:31 EDT
Ujjwal Kumar Shukla (UjjwalShukla_GCS)
PEGA
Senior Manager, Technical Support
Pegasystems Inc.
IN

ra_action_pxgetrulesbyrecordtypeandclassname can be seen in the logs
Can you run pxGetRulesByRecordTypeAndClassName and see how much time it takes. In data Access tab there is a limit of 30 Sec.

Regards,
Ujjwal

To see attachments, please log in.
Posted: 6 years ago
Posted: 28 Jun 2018 15:44 EDT
Arun Kumar Mahanty (Arun_Mahanty)
Pegasystems Inc.
Principal Technical Solutions Engineer
Pegasystems Inc.
IN

Which Pega version you are using  so we can tell you the supported Driver version .

Thanks,

Arun

To see attachments, please log in.
Posted: 6 years ago
Posted: 29 Jun 2018 15:26 EDT
Arun Kumar Mahanty (Arun_Mahanty)
Pegasystems Inc.
Principal Technical Solutions Engineer
Pegasystems Inc.
IN

As per platform support guide sqljdbc42.jar is supported for MSSQL with java8 . 


Thanks,

Arun

 

To see attachments, please log in.
Posted: 6 years ago
Posted: 29 Jun 2018 15:32 EDT
Arun Kumar Mahanty (Arun_Mahanty)
Pegasystems Inc.
Principal Technical Solutions Engineer
Pegasystems Inc.
IN

You can also create a SR as  existing hotfix  request and mention HFix-44396 , apply below DSS settings 

1. 
Purpose: reporting/useForceOrderHint 
Ruleset: Pega-RULES 
Value: true 
for ac 
2. 
reporting/useForceOrderHintForRRandAC 
ruleset: PEGA-RULES 
value: true 
3. 
Purpose: reporting/useMergeHintForRRquery 
Ruleset: Pega-Reporting 
Value: true

Thanks,

Arun

To see attachments, please log in.

Accepted Solution

Posted: 6 years ago
Posted: 29 Jun 2018 14:09 EDT
Anandh Palanisamy (Anandh Palanisamy)
Ford Motor Company

Ford Motor Company
US

Hi All,

This is SR-C41959 and HFix DL-88518 and DSS needs to be done.

PRPC restart is required and the following DSS must be created for the changes to take effect: 
1. 
Purpose: reporting/useForceOrderHint 
Ruleset: Pega-RULES 
Value: true 
for ac 
2. 
reporting/useForceOrderHintForRRandAC 
ruleset: PEGA-RULES 
value: true 
3. 
Purpose: reporting/useMergeHintForRRquery 
Ruleset: Pega-Reporting 
Value: true

 Regards,

Anandh

To see attachments, please log in.

Related content:

Question Solved

com.microsoft.sqlserver.jdbc.SQLServerException

Question

SQL Error code: 17009 SQLState: 99999 Closed Statement DatabaseException caused by prior exception: java.sql.SQLException: Closed

Question Solved

com.microsoft.sqlserver.jdbc.SQLServerException:Connection close (SA-1510)

Question Solved

Pega 8.7.1 - There was a problem getting a list: code: 0 SQLState: HY008

Question Solved

Error: An error occured on executing the query for the report definition - There was a problem getting a list: code: 0 SQLState: 08006 Message: An I/O error occurred while sending to the backend. DatabaseException caused by prior exception: org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend. | SQL Code: 0 | SQL State: 08006

Question

DatabaseException caused by prior exception: java.sql.SQLException: ORA-01000: maximum open cursors exceeded

Question

An I/O error occurred while sending to the backend.  | SQL Code: 0 | SQL State: 08006 

Question

SQL Code: 208 | SQL State: S0002 after 7.3 upgrade from 7.2.2

Question

AES 717 log shows com.microsoft.sqlserver.jdbc.SQLServerException: Operand type clash: datetime2 is incompatible with tinyint

Question

ORA-01460: unimplemented or unreasonable conversion requested&nbsp;&nbsp; | SQL Code: 1460 | SQL State: 72000 while sending data in mail

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