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

 Jay Amaranayake (JayForWeb)
MeBank

MeBank
AU
JayForWeb Member since 2013 1 post
MeBank
Posted: May 4, 2017
Last activity: May 12, 2017
Posted: 4 May 2017 8:27 EDT
Last activity: 12 May 2017 0:56 EDT
Closed
Solved

[PEGA 7.1.9] Noticed random occurrences of the same SQLState HY008 error on the logs

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?view=sql-server-ver15&viewFallbackFrom=sql-server-ver15%3Fview%3Dsql-server-ver15
https://docs.oracle.com/cd/E13157_01/wlevs/docs30/jdbc_drivers/mssqlserver.html

Should we raise a separate SR or is it advisable to go ahead with the accepted correct answer?

Thanks,

Jay

***Updated by moderator: Lochan to create new post from this reply***

Original Post: Getting DatabaseException : the Query has timed out, when trying to open any item from Data Explorer

To see attachments, please log in.
Data Integration
System Administration

Accepted Solution

Posted: 7 years ago
Posted: 10 May 2017 21:49 EDT
Kevin Zheng (KevinZheng_GCS)
PEGA
Director, Technical Support
Pegasystems Inc.
US

If the query timeout are due to various queries, you may have to analyze them case by case. The one listed here is using UDF from a report, which is known to have performance issue (definitely never use UDF in production). You can avoid using UDF by exposing all the relevant columns in the report definition. Here are some guidelines from our internal performance experts:

The UDF’s primary value is for design time and unit testing. UDF usage is really discouraged in production.

 

Show More

If the query timeout are due to various queries, you may have to analyze them case by case. The one listed here is using UDF from a report, which is known to have performance issue (definitely never use UDF in production). You can avoid using UDF by exposing all the relevant columns in the report definition. Here are some guidelines from our internal performance experts:

The UDF’s primary value is for design time and unit testing. UDF usage is really discouraged in production.

 

We are not asking the customer to drop UDF’s per se but

 

  1. a-      Do not use UDF’s in queries that run frequently
  2. b-      Do not use UDF’s in queries that return many rows
  3. c-       Never use UDF’s in a where clause.

 

In a development environment, at design Pega7 will not allow use of UDF in a where clause. You will get an error if you try to save a report definition that selects using a non-exposed property.

 

If you see a UDF in a where clause, that typically means that someone has moved a report definition into production without dependent schema changes and schemas are out of sync from dev to prod. We do not validate rules during migration.

 


Show Less
To see attachments, please log in.
Posted: 7 years ago
Posted: 4 May 2017 16:27 EDT
Kevin Zheng (KevinZheng_GCS)
PEGA
Director, Technical Support
Pegasystems Inc.
US

All the property names should be case insensitive. The pdn article should be corrected. In your case, what app server are you using? Can you show your datasource definition and how the parameters are defined?

To see attachments, please log in.
Posted: 7 years ago
Posted: 4 May 2017 23:32 EDT
Jay Amaranayake (JayForWeb)
MeBank

MeBank
AU

Thanks Kevin.

We are running on JBOSS EAP 6.4 with Java 1.8

Below is our datasource definition.

Show More

Thanks Kevin.

We are running on JBOSS EAP 6.4 with Java 1.8

Below is our datasource definition.

<datasources>
	<datasource jta="true" jndi-name="java:/jdbc/PegaRULES" pool-name="PegaRULES" enabled="true" use-ccm="true" statistics-enabled="false">
		<connection-url>jdbc:sqlserver://peg_prd_agl01:1433;DatabaseName=PEGA_ProcessCommander;integratedSecurity=true;selectMethod=cursor;sendStringParametersAsUnicode=false</connection-url>
		<driver>mssql</driver>
		<pool>
			<min-pool-size>30</min-pool-size>
			<max-pool-size>100</max-pool-size>
		</pool>
		<validation>
			<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>
			<validate-on-match>true</validate-on-match>
		</validation>
		<timeout>
			<set-tx-query-timeout>false</set-tx-query-timeout>
			<blocking-timeout-millis>0</blocking-timeout-millis>
			<idle-timeout-minutes>0</idle-timeout-minutes>
			<query-timeout>0</query-timeout>
			<use-try-lock>0</use-try-lock>
			<allocation-retry>0</allocation-retry>
			<allocation-retry-wait-millis>0</allocation-retry-wait-millis>
		</timeout>
		<statement>
			<prepared-statement-cache-size>100</prepared-statement-cache-size>
			<share-prepared-statements>true</share-prepared-statements>
		</statement>
	</datasource>
	<drivers>
		<driver name="h2" module="com.h2database.h2">
			<xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
		</driver>
		<driver name="mssql" module="com.microsoft.sqlserver.jdbc">
			<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
		</driver>
	</drivers>
</datasources>

Thanks,

Jay


Show Less
To see attachments, please log in.
Posted: 7 years ago
Posted: 5 May 2017 0:13 EDT
Kevin Zheng (KevinZheng_GCS)
PEGA
Director, Technical Support
Pegasystems Inc.
US

what jdbc driver jar are you using? You should use sqljdbc42.jar if not already for java 1.8. Also can u post the complete exception stack in your case?

To see attachments, please log in.
Posted: 7 years ago
Posted: 10 May 2017 20:28 EDT
Jay Amaranayake (JayForWeb)
MeBank

MeBank
AU

Sorry for the late reply. We are using sqljdbc42.jar with Java 8. 
Below is a sample stack trace [attached as a txt file as well] that I could spot in the logs. It happens randomly for various queries.

Show More

Sorry for the late reply. We are using sqljdbc42.jar with Java 8. 
Below is a sample stack trace [attached as a txt file as well] that I could spot in the logs. It happens randomly for various queries.

2017-05-03 09:05:36,624 [] [TABTHREAD0] [ ] [ ] ( rd.queryexec.SqlQueryExecutor) 
ERROR - 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: (H6D2BE52FA1D0188B280C38C123E8E75A:) 
	SQL: SELECT dbo.pr_read_from_stream('.pxCurrentStage', "PC0".pzInsKey, "PC0".pzPVStream) AS "pxCurrentStage" , "PC0".pxObjClass AS "pxObjClass", COUNT(dbo.pr_read_from_stream('.pxCurrentStage', "PC0".pzInsKey, "PC0".pzPVStream)) AS "pySummaryCount(1)" FROM dbo.MEB_COBRA_WORK "PC0" WHERE  ( dbo.pr_read_from_stream('.pxCurrentStage', "PC0".pzInsKey, "PC0".pzPVStream) IS NOT NULL AND "PC0".pxObjClass = ?  AND  "PC0".pyStatusWork NOT LIKE ?   )  GROUP BY dbo.pr_read_from_stream('.pxCurrentStage', "PC0".pzInsKey, "PC0".pzPVStream) , "PC0".pxObjClass ORDER BY 1 ASC, 2 ASC
	SQL Inserts: <MEB-OPS-COBRA-Work>	<Resolved%>	

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:262)
	at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:6283)
	at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:5979)
	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:1229)
	at com.pega.pegarules.session.internal.mgmt.Executable.executeReport(Executable.java:10829)
	at com.pegarules.generated.definition.ra_action_pycountbystage_f73ad5082bf6aeb943ac3d7d9ac1b3be.perform(ra_action_pycountbystage_f73ad5082bf6aeb943ac3d7d9ac1b3be.java:50)
	at com.pega.pegarules.session.internal.mgmt.Executable.doAction(Executable.java:3310)
	at com.pegarules.generated.activity.ra_action_pxretrievereportdata_b344d5bdd6cbecf59bb8240bcd2b34c3.step30_circum0(ra_action_pxretrievereportdata_b344d5bdd6cbecf59bb8240bcd2b34c3.java:3418)
	at com.pegarules.generated.activity.ra_action_pxretrievereportdata_b344d5bdd6cbecf59bb8240bcd2b34c3.perform(ra_action_pxretrievereportdata_b344d5bdd6cbecf59bb8240bcd2b34c3.java:637)
	at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3505)
	at com.pega.pegarules.session.internal.mgmt.Executable.invokeActivity(Executable.java:10563)
	at com.pegarules.generated.activity.ra_action_pxcallretrievereportdata_ac7c5191bd54e9e32ee884ac93fa0a8e.step4_circum0(ra_action_pxcallretrievereportdata_ac7c5191bd54e9e32ee884ac93fa0a8e.java:373)
	at com.pegarules.generated.activity.ra_action_pxcallretrievereportdata_ac7c5191bd54e9e32ee884ac93fa0a8e.perform(ra_action_pxcallretrievereportdata_ac7c5191bd54e9e32ee884ac93fa0a8e.java:123)
	at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3505)
	at com.pega.pegarules.session.internal.mgmt.Executable.invokeActivity(Executable.java:10563)
	at com.pegarules.generated.activity.ra_action_pygetstagecountforcase_ef8296b9ae1ad0dddf0f888be11fcd66.step3_circum0(ra_action_pygetstagecountforcase_ef8296b9ae1ad0dddf0f888be11fcd66.java:387)
	at com.pegarules.generated.activity.ra_action_pygetstagecountforcase_ef8296b9ae1ad0dddf0f888be11fcd66.perform(ra_action_pygetstagecountforcase_ef8296b9ae1ad0dddf0f888be11fcd66.java:103)
	at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3505)
	at com.pega.pegarules.exec.internal.declare.infengine.pages.DeclarativePageDirectoryImpl.runRule(DeclarativePageDirectoryImpl.java:994)
	at com.pega.pegarules.exec.internal.declare.infengine.pages.DeclarativePageDirectoryImpl.runLoadActivity(DeclarativePageDirectoryImpl.java:452)
	at com.pega.pegarules.session.internal.mgmt.base.handler.ReadOnlyDataPageHandler.loadDataPage(ReadOnlyDataPageHandler.java:239)
	at com.pega.pegarules.session.internal.mgmt.base.handler.ReadOnlyDataPageHandler.findDataPage(ReadOnlyDataPageHandler.java:395)
	at com.pega.pegarules.session.internal.mgmt.base.AbstractPageDirectory.findDataPageInCurrentDir(AbstractPageDirectory.java:920)
	at com.pega.pegarules.session.internal.mgmt.base.AbstractPageDirectory.getDirectPage(AbstractPageDirectory.java:713)
	at com.pega.pegarules.session.internal.mgmt.base.ThreadPageDir.getDirectPage(ThreadPageDir.java:469)
	at com.pega.pegarules.session.internal.mgmt.base.ThreadPageDir.getPage(ThreadPageDir.java:427)
	at com.pega.pegarules.session.internal.mgmt.base.ThreadPageDir.getPage(ThreadPageDir.java:419)
	at com.pega.pegarules.session.internal.mgmt.PRThreadImpl.getPage(PRThreadImpl.java:534)
	at com.pega.pegarules.data.internal.clipboard.PropertyReferenceImpl.getTopPage(PropertyReferenceImpl.java:2169)
	at com.pega.pegarules.data.internal.clipboard.PropertyReferenceImpl.getTopPage(PropertyReferenceImpl.java:2113)
	at com.pega.pegarules.data.internal.clipboard.PropertyReferenceImpl.resolveEmbeddedPages(PropertyReferenceImpl.java:1794)
	at com.pega.pegarules.data.internal.clipboard.PropertyReferenceImpl.doGet(PropertyReferenceImpl.java:1369)
	at com.pega.pegarules.data.internal.clipboard.PropertyReferenceImpl.doGet(PropertyReferenceImpl.java:1349)
	at com.pega.pegarules.data.internal.clipboard.PropertyReferenceImpl.doGet(PropertyReferenceImpl.java:1333)
	at com.pega.pegarules.data.internal.clipboard.PropertyReferenceImpl.get(PropertyReferenceImpl.java:1169)
	at com.pega.pegarules.session.internal.mgmt.Executable.getProperty(Executable.java:2299)
	at com.pegarules.generated.html_section.ra_stream_pxdisplaystagesreport_e651746cdb0d436ca3a7f43382a63789.simpleLayout_2(ra_stream_pxdisplaystagesreport_e651746cdb0d436ca3a7f43382a63789.java:423)
	at com.pegarules.generated.html_section.ra_stream_pxdisplaystagesreport_e651746cdb0d436ca3a7f43382a63789.pzLayoutBodyWrapper_2(ra_stream_pxdisplaystagesreport_e651746cdb0d436ca3a7f43382a63789.java:319)
	at com.pegarules.generated.html_section.ra_stream_pxdisplaystagesreport_e651746cdb0d436ca3a7f43382a63789.pzLayout_2(ra_stream_pxdisplaystagesreport_e651746cdb0d436ca3a7f43382a63789.java:513)
	at com.pegarules.generated.html_section.ra_stream_pxdisplaystagesreport_e651746cdb0d436ca3a7f43382a63789.pzSectionBody_1(ra_stream_pxdisplaystagesreport_e651746cdb0d436ca3a7f43382a63789.java:253)
	at com.pegarules.generated.html_section.ra_stream_pxdisplaystagesreport_e651746cdb0d436ca3a7f43382a63789.execute(ra_stream_pxdisplaystagesreport_e651746cdb0d436ca3a7f43382a63789.java:100)
	at com.pega.pegarules.session.internal.mgmt.StreamBuilderTools.appendStreamKeepProperties(StreamBuilderTools.java:717)
	at com.pega.pegarules.session.internal.mgmt.autostreams.IncludeStreamRuntime.getStream(IncludeStreamRuntime.java:332)
	at com.pega.pegarules.session.internal.mgmt.autostreams.IncludeStreamRuntime.emitIncludeStreamReference(IncludeStreamRuntime.java:252)
	at com.pega.pegarules.session.internal.mgmt.autostreams.AutoStreamRuntimeImpl.emitIncludeStreamReference(AutoStreamRuntimeImpl.java:358)
	at com.pegarules.generated.html_section.ra_stream_pydashboardmiddle_695c4510cbc3100585b0f26a563bcc09.sectionBodyIncludeInCell_1(ra_stream_pydashboardmiddle_695c4510cbc3100585b0f26a563bcc09.java:545)
	at com.pegarules.generated.html_section.ra_stream_pydashboardmiddle_695c4510cbc3100585b0f26a563bcc09.simpleLayoutCell_1(ra_stream_pydashboardmiddle_695c4510cbc3100585b0f26a563bcc09.java:566)
	at com.pegarules.generated.html_section.ra_stream_pydashboardmiddle_695c4510cbc3100585b0f26a563bcc09.simpleLayout_1(ra_stream_pydashboardmiddle_695c4510cbc3100585b0f26a563bcc09.java:590)
	at com.pegarules.generated.html_section.ra_stream_pydashboardmiddle_695c4510cbc3100585b0f26a563bcc09.pzLayoutBodyWrapper_1(ra_stream_pydashboardmiddle_695c4510cbc3100585b0f26a563bcc09.java:507)
	at com.pegarules.generated.html_section.ra_stream_pydashboardmiddle_695c4510cbc3100585b0f26a563bcc09.pzLayout_1(ra_stream_pydashboardmiddle_695c4510cbc3100585b0f26a563bcc09.java:603)
	at com.pegarules.generated.html_section.ra_stream_pydashboardmiddle_695c4510cbc3100585b0f26a563bcc09.simpleLayoutCell_2(ra_stream_pydashboardmiddle_695c4510cbc3100585b0f26a563bcc09.java:625)
	at com.pegarules.generated.html_section.ra_stream_pydashboardmiddle_695c4510cbc3100585b0f26a563bcc09.simpleLayout_3(ra_stream_pydashboardmiddle_695c4510cbc3100585b0f26a563bcc09.java:673)
	at com.pegarules.generated.html_section.ra_stream_pydashboardmiddle_695c4510cbc3100585b0f26a563bcc09.pzLayoutBodyWrapper_3(ra_stream_pydashboardmiddle_695c4510cbc3100585b0f26a563bcc09.java:373)
	at com.pegarules.generated.html_section.ra_stream_pydashboardmiddle_695c4510cbc3100585b0f26a563bcc09.pzLayout_3(ra_stream_pydashboardmiddle_695c4510cbc3100585b0f26a563bcc09.java:687)
	at com.pegarules.generated.html_section.ra_stream_pydashboardmiddle_695c4510cbc3100585b0f26a563bcc09.pzSectionBody_1(ra_stream_pydashboardmiddle_695c4510cbc3100585b0f26a563bcc09.java:225)
	at com.pegarules.generated.html_section.ra_stream_pydashboardmiddle_695c4510cbc3100585b0f26a563bcc09.execute(ra_stream_pydashboardmiddle_695c4510cbc3100585b0f26a563bcc09.java:100)
	at com.pega.pegarules.session.internal.mgmt.StreamBuilderTools.appendStreamKeepProperties(StreamBuilderTools.java:717)
	at com.pega.pegarules.session.internal.mgmt.autostreams.IncludeStreamRuntime.getStream(IncludeStreamRuntime.java:332)
	at com.pega.pegarules.session.internal.mgmt.autostreams.IncludeStreamRuntime.emitIncludeStreamReference(IncludeStreamRuntime.java:252)
	at com.pega.pegarules.session.internal.mgmt.autostreams.AutoStreamRuntimeImpl.emitIncludeStreamReference(AutoStreamRuntimeImpl.java:358)
	at com.pegarules.generated.html_section.ra_stream_pyportalpageheader_3715d9f8fe75a748de77b7e3ff0640cf.sectionBodyIncludeInCell_1(ra_stream_pyportalpageheader_3715d9f8fe75a748de77b7e3ff0640cf.java:378)
	at com.pegarules.generated.html_section.ra_stream_pyportalpageheader_3715d9f8fe75a748de77b7e3ff0640cf.simpleLayoutCell_1(ra_stream_pyportalpageheader_3715d9f8fe75a748de77b7e3ff0640cf.java:399)
	at com.pegarules.generated.html_section.ra_stream_pyportalpageheader_3715d9f8fe75a748de77b7e3ff0640cf.simpleLayout_1(ra_stream_pyportalpageheader_3715d9f8fe75a748de77b7e3ff0640cf.java:423)
	at com.pegarules.generated.html_section.ra_stream_pyportalpageheader_3715d9f8fe75a748de77b7e3ff0640cf.pzLayoutBodyWrapper_3(ra_stream_pyportalpageheader_3715d9f8fe75a748de77b7e3ff0640cf.java:340)
	at com.pegarules.generated.html_section.ra_stream_pyportalpageheader_3715d9f8fe75a748de77b7e3ff0640cf.pzLayout_3(ra_stream_pyportalpageheader_3715d9f8fe75a748de77b7e3ff0640cf.java:436)
	at com.pegarules.generated.html_section.ra_stream_pyportalpageheader_3715d9f8fe75a748de77b7e3ff0640cf.simpleLayoutCell_2(ra_stream_pyportalpageheader_3715d9f8fe75a748de77b7e3ff0640cf.java:458)
	at com.pegarules.generated.html_section.ra_stream_pyportalpageheader_3715d9f8fe75a748de77b7e3ff0640cf.simpleLayout_3(ra_stream_pyportalpageheader_3715d9f8fe75a748de77b7e3ff0640cf.java:506)
	at com.pegarules.generated.html_section.ra_stream_pyportalpageheader_3715d9f8fe75a748de77b7e3ff0640cf.pzLayoutBodyWrapper_5(ra_stream_pyportalpageheader_3715d9f8fe75a748de77b7e3ff0640cf.java:234)
	at com.pegarules.generated.html_section.ra_stream_pyportalpageheader_3715d9f8fe75a748de77b7e3ff0640cf.pzLayout_5(ra_stream_pyportalpageheader_3715d9f8fe75a748de77b7e3ff0640cf.java:520)
	at com.pegarules.generated.html_section.ra_stream_pyportalpageheader_3715d9f8fe75a748de77b7e3ff0640cf.pzSectionBody_1(ra_stream_pyportalpageheader_3715d9f8fe75a748de77b7e3ff0640cf.java:223)
	at com.pegarules.generated.html_section.ra_stream_pyportalpageheader_3715d9f8fe75a748de77b7e3ff0640cf.execute(ra_stream_pyportalpageheader_3715d9f8fe75a748de77b7e3ff0640cf.java:100)
	at com.pega.pegarules.session.internal.mgmt.Executable.getStream(Executable.java:4038)
	at com.pega.pegarules.session.internal.mgmt.autostreams.AutoStreamRuleRuntime.getSectionStream(AutoStreamRuleRuntime.java:149)
	at com.pegarules.generated.html_section.ra_stream_pydashboard7_f9bce75f003f7e54682b798f94090fc8.includeSectionBody_1(ra_stream_pydashboard7_f9bce75f003f7e54682b798f94090fc8.java:234)
	at com.pegarules.generated.html_section.ra_stream_pydashboard7_f9bce75f003f7e54682b798f94090fc8.pzLayoutBodyWrapper_1(ra_stream_pydashboard7_f9bce75f003f7e54682b798f94090fc8.java:223)
	at com.pegarules.generated.html_section.ra_stream_pydashboard7_f9bce75f003f7e54682b798f94090fc8.pzLayout_1(ra_stream_pydashboard7_f9bce75f003f7e54682b798f94090fc8.java:239)
	at com.pegarules.generated.html_section.ra_stream_pydashboard7_f9bce75f003f7e54682b798f94090fc8.pzSectionBody_1(ra_stream_pydashboard7_f9bce75f003f7e54682b798f94090fc8.java:212)
	at com.pegarules.generated.html_section.ra_stream_pydashboard7_f9bce75f003f7e54682b798f94090fc8.execute(ra_stream_pydashboard7_f9bce75f003f7e54682b798f94090fc8.java:100)
	at com.pega.pegarules.session.internal.mgmt.Executable.getStream(Executable.java:4038)
	at com.pega.pegarules.session.internal.mgmt.autostreams.AutoStreamRuleRuntime.getSectionStream(AutoStreamRuleRuntime.java:149)
	at com.pegarules.generated.html_harness.ra_stream_pydashboard7_562b53626bb8a6eff448fcca3fa430a5.includeSectionBody_1(ra_stream_pydashboard7_562b53626bb8a6eff448fcca3fa430a5.java:3038)
	at com.pegarules.generated.html_harness.ra_stream_pydashboard7_562b53626bb8a6eff448fcca3fa430a5.generatePegaHarnessDiv_6(ra_stream_pydashboard7_562b53626bb8a6eff448fcca3fa430a5.java:1852)
	at com.pegarules.generated.html_harness.ra_stream_pydashboard7_562b53626bb8a6eff448fcca3fa430a5.execute(ra_stream_pydashboard7_562b53626bb8a6eff448fcca3fa430a5.java:699)
	at com.pega.pegarules.session.internal.mgmt.Executable.getStream(Executable.java:4038)
	at com.pega.pegarules.session.internal.mgmt.Executable.getStream(Executable.java:3866)
	at com.pegarules.generated.activity.ra_action_displayharnesswithclass_4cc9f9e9cb9b892dd9c83273e3c08a6c.step4_circum0(ra_action_displayharnesswithclass_4cc9f9e9cb9b892dd9c83273e3c08a6c.java:495)
	at com.pegarules.generated.activity.ra_action_displayharnesswithclass_4cc9f9e9cb9b892dd9c83273e3c08a6c.perform(ra_action_displayharnesswithclass_4cc9f9e9cb9b892dd9c83273e3c08a6c.java:120)
	at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3505)
	at com.pega.pegarules.session.internal.mgmt.Executable.invokeActivity(Executable.java:10563)
	at com.pegarules.generated.activity.ra_action_uiactiondisplayharness_e25153649f09cc31b4c450da2335ba4c.step8_circum0(ra_action_uiactiondisplayharness_e25153649f09cc31b4c450da2335ba4c.java:765)
	at com.pegarules.generated.activity.ra_action_uiactiondisplayharness_e25153649f09cc31b4c450da2335ba4c.perform(ra_action_uiactiondisplayharness_e25153649f09cc31b4c450da2335ba4c.java:198)
	at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3505)
	at com.pega.pegarules.session.internal.mgmt.Executable.invokeActivity(Executable.java:10563)
	at com.pegarules.generated.activity.ra_action_douiaction_b1b5bac99d191ebf335be422b0d3ab71.step18_circum0(ra_action_douiaction_b1b5bac99d191ebf335be422b0d3ab71.java:1868)
	at com.pegarules.generated.activity.ra_action_douiaction_b1b5bac99d191ebf335be422b0d3ab71.perform(ra_action_douiaction_b1b5bac99d191ebf335be422b0d3ab71.java:355)
	at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3505)
	at com.pega.pegarules.session.internal.mgmt.base.ThreadRunner.runActivitiesAlt(ThreadRunner.java:646)
	at com.pega.pegarules.session.internal.mgmt.PRThreadImpl.runActivitiesAlt(PRThreadImpl.java:461)
	at com.pega.pegarules.session.internal.engineinterface.service.HttpAPI.runActivities(HttpAPI.java:3358)
	at com.pega.pegarules.session.external.engineinterface.service.EngineAPI.processRequestInner(EngineAPI.java:385)
	at sun.reflect.GeneratedMethodAccessor59.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.pega.pegarules.session.internal.PRSessionProviderImpl.performTargetActionWithLock(PRSessionProviderImpl.java:1270)
	at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:1008)
	at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:841)
	at com.pega.pegarules.session.external.engineinterface.service.EngineAPI.processRequest(EngineAPI.java:331)
	at com.pega.pegarules.session.internal.engineinterface.service.HttpAPI.invoke(HttpAPI.java:852)
	at com.pega.pegarules.session.internal.engineinterface.etier.impl.EngineImpl._invokeEngine_privact(EngineImpl.java:315)
	at com.pega.pegarules.session.internal.engineinterface.etier.impl.EngineImpl.invokeEngine(EngineImpl.java:263)
	at com.pega.pegarules.session.internal.engineinterface.etier.impl.EngineImpl.invokeEngine(EngineImpl.java:240)
	at com.pega.pegarules.priv.context.JNDIEnvironment.invokeEngineInner(JNDIEnvironment.java:278)
	at com.pega.pegarules.priv.context.JNDIEnvironment.invokeEngine(JNDIEnvironment.java:223)
	at com.pega.pegarules.web.impl.WebStandardImpl.makeEtierRequest(WebStandardImpl.java:574)
	at com.pega.pegarules.web.impl.WebStandardImpl.doPost(WebStandardImpl.java:374)
	at sun.reflect.GeneratedMethodAccessor55.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.pega.pegarules.internal.bootstrap.PRBootstrap.invokeMethod(PRBootstrap.java:370)
	at com.pega.pegarules.internal.bootstrap.PRBootstrap.invokeMethodPropagatingThrowable(PRBootstrap.java:411)
	at com.pega.pegarules.boot.internal.extbridge.AppServerBridgeToPega.invokeMethodPropagatingThrowable(AppServerBridgeToPega.java:223)
	at com.pega.pegarules.boot.internal.extbridge.AppServerBridgeToPega.invokeMethod(AppServerBridgeToPega.java:272)
	at com.pega.pegarules.internal.web.servlet.WebStandardBoot.doPost(WebStandardBoot.java:121)
	at com.pega.pegarules.internal.web.servlet.WebStandardBoot.doGet(WebStandardBoot.java:92)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:734)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:295)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:231)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:149)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:420)
	at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:169)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:150)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:97)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:102)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:854)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:653)
	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:926)
	at java.lang.Thread.run(Thread.java:745)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.
	at com.microsoft.sqlserver.jdbc.TDSCommand.checkForInterrupt(IOBuffer.java:7727)
	at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:78)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1650)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:440)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:385)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2444)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:367)
	at org.jboss.jca.adapters.jdbc.CachedPreparedStatement.execute(CachedPreparedStatement.java:296)
	at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:404)
	at com.pega.pegarules.data.internal.access.DatabasePreparedStatementImpl.execute(DatabasePreparedStatementImpl.java:581)
	at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:6202)
	... 144 more

 


Show Less
To see attachments, please log in.

Accepted Solution

Posted: 7 years ago
Posted: 10 May 2017 21:49 EDT
Kevin Zheng (KevinZheng_GCS)
PEGA
Director, Technical Support
Pegasystems Inc.
US

If the query timeout are due to various queries, you may have to analyze them case by case. The one listed here is using UDF from a report, which is known to have performance issue (definitely never use UDF in production). You can avoid using UDF by exposing all the relevant columns in the report definition. Here are some guidelines from our internal performance experts:

The UDF’s primary value is for design time and unit testing. UDF usage is really discouraged in production.

 

Show More

If the query timeout are due to various queries, you may have to analyze them case by case. The one listed here is using UDF from a report, which is known to have performance issue (definitely never use UDF in production). You can avoid using UDF by exposing all the relevant columns in the report definition. Here are some guidelines from our internal performance experts:

The UDF’s primary value is for design time and unit testing. UDF usage is really discouraged in production.

 

We are not asking the customer to drop UDF’s per se but

 

  1. a-      Do not use UDF’s in queries that run frequently
  2. b-      Do not use UDF’s in queries that return many rows
  3. c-       Never use UDF’s in a where clause.

 

In a development environment, at design Pega7 will not allow use of UDF in a where clause. You will get an error if you try to save a report definition that selects using a non-exposed property.

 

If you see a UDF in a where clause, that typically means that someone has moved a report definition into production without dependent schema changes and schemas are out of sync from dev to prod. We do not validate rules during migration.

 


Show Less
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