BIX Query TimedOut

General
, , , ,
1

We are having an SQL query timeout issue with BIX extract when it’s being executed from in background processing through an agent .

We are working with DB team to look at the indexes of this table but meanwhile we would like to how to increase the default timeout (30 sec) of BIX query execution.

Is the below hotfix still relevant for Pega 8.8.1 ?

HFIX-50605 and Add below DSS.

  • Setting purpose :-“BIX/selectQueryTimeout”
  • Rule set :- “Pega-Rules”.
  • Value :- << Highest value which required for the query to run in seconds >>

java.sql.SQLTimeoutException: The query has timed out

SQL: SELECT COUNT(“PC0”.“pzInsKey”) AS “pySummaryCount(1)” FROM pegaDATA.pc_Version_Work “PC0” WHERE ( ( ( “PC0”.“pxSaveDateTime” >= ? AND “PC0”.“pxSaveDateTime” < ? ) ) AND “PC0”.“pxObjClass” = ? )<Caused by SQL Problems.Problem 1, SQLState HY008, Error code 0: java.sql.SQLTimeoutException: The query has timed out.

java.sql.SQLTimeoutException: The query has timed out. | SQL Code: 0 | SQL State: HY008][STACK][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: java.sql.SQLTimeoutException: The query has timed out. | SQL Code: 0 | SQL State: HY008From: (BF88E0SQTRKB29SOKD64GU8M21KMZZSNOA) SQL: SELECT COUNT(“PC0”.“pzInsKey”) AS “pySummaryCount(1)” FROM pegaDATA.pc_Version_Work “PC0” WHERE ( ( ( “PC0”.“pxSaveDateTime” >= ? AND “PC0”.“pxSaveDateTime” < ? ) ) AND “PC0”.“pxObjClass” = ? )Caused by SQL Problems.Problem 1, SQLState HY008, Error code 0: java.sql.SQLTimeoutException: The query has timed out. at com.pega.pegarules.data.internal.access.ExceptionInformation.createAppropriateExceptionDueToDBFailure(ExceptionInformation.java:385) at com.pega.pegarules.data.internal.access.ExceptionInformation.createExceptionDueToDBFailure(ExceptionInformation.java:364) at com.pega.pegarules.data.internal.access.Lister.convertSqlExceptionToDatabaseException(Lister.java:638) at com.pega.pegarules.data.internal.access.Lister.listWithResultPresenter(Lister.java:271) at com.pega.pegarules.data.internal.rd.queryexec.SqlQueryExecutor.executeQuery(SqlQueryExecutor.java:100) at com.pega.pegarules.data.internal.rd.queryexec.SqlQueryExecutor.executeQuery(SqlQueryExecutor.java:50) at com.pega.pegarules.data.internal.rd.SQLExecutorResultsCP.executeQuery(SQLExecutorResultsCP.java:111) at com.pega.pegarules.data.internal.rd.SqlReportExecutor.executeResolvedReport(SqlReportExecutor.java:202) at com.pega.pegarules.data.internal.rd.SqlReportExecutor.executeMainReport(SqlReportExecutor.java:236) at com.pega.pegarules.data.internal.rd.SqlReportExecutor.execute(SqlReportExecutor.java:91) at com.pega.pegarules.data.internal.rd.SqlReportExecutor.execute(SqlReportExecutor.java:60) at com.pega.pegarules.data.internal.access.ExtractImpl.executeSQLReport(ExtractImpl.java:3242) at com.pega.pegarules.data.internal.access.ExtractImpl.getInstanceCount(ExtractImpl.java:3208) at com.pega.pegarules.data.internal.access.ExtractImpl.doExtract(ExtractImpl.java:1731) at com.pega.pegarules.data.internal.access.ExtractImpl.runExtract(ExtractImpl.java:1297) at com.pega.pegarules.data.internal.access.ExtractImpl.init(ExtractImpl.java:1003) at com.pega.pegarules.data.internal.access.ExtractImpl.parseAndRunExtractCommand(ExtractImpl.java:929) at com.pega.pegarules.data.internal.access.ExtractImpl.executeCommandLineExtract(ExtractImpl.java:858) at com.pega.pegarules.data.internal.PRDataProviderImpl.executeCommandLineExtract(PRDataProviderImpl.java:1737) at com.pega.pegarules.session.internal.mgmt.Executable.executeCommandLineExtract(Executable.java:8916) at com.pegarules.generated.activity.ra_action_pxextractdatawithargs_b390b959167377f95f63ce79c39386b3.step2_circum0(ra_action_pxextractdatawithargs_b390b959167377f95f63ce79c39386b3.java:318)

2

@Brahmesh@ these types of questions are best logged via the MSP so that our GCS team can help investigate your performance issues.

I can tell you that HFIX-50605 (BIX extraction timing out after 30 seconds in 7.4) was built for Pega 7.4 and fixed from 8.1.4 onwards.

The DSS allows to specify the query timeout value in second.if value is empty then it will take default query timeout to 30 second other wise user specified value will be taken.

In order to avoid timeouts, the new DSS “BIX/selectQueryTimeout” has been added to allow setting a custom value. Further refinements will be made for multi-tenant environments in a future release.

The underlying defect was therefore already fixed in your current release but you must set the DSS first. Also carry out troubleshooting first:

Troubleshooting BIX for on-premises environments > Query timeout exception

If setting the DSS is not helping, please log a support ticket via the MSP and provide the INC id here so we can track the investigation.

3

@MarijeSchillern I don’t see these settings in our environment either in prconfig file or in DSS ,so Does BIX node consider all these settings by default ? (or) Do we need to configure them explicitly prconfig/DSS ?

Location Setting name Description Default value
prconfig Compatibility/BIXUseOptimizedClipboardXML Optimizes the Clipboard to facilitate extracting a large number of Class instances to XML.

For more information, see Configuring optional prconfig.xml settings in on-premises systems.

 true
prconfig Compatibility/BIXdisableForwardChaining Disables forward-chaining calculations of properties when loading the Clipboard for the extraction process.

For more information, see Configuring optional prconfig.xml settings in on-premises systems.

 true
prconfig Compatibility/BIXDisableBackwardChaining Disables backward-chaining calculations of properties when loading the Clipboard for BIX extract.

For more information, see Configuring optional prconfig.xml settings in on-premises systems.

 true
4

@Brahmesh@ it would be best if you check with our GCS team by logging a ticket.

5

@MarijeSchillern Sure ..INC-B5473

6

@Brahmesh@ The support ticket was closed February 13th.

Below the text from the final analysis:

We checked the mentioned hotfix and for the Pega 8.8.1 version you don’t need to install that hotfix as the DSS is available inside of the Pega 8.8.1 version already. Maybe, you just have to add it in Dev Studio, under Records > SysAdmin > Dynamic System Settings: Create (on the right upper side of the window).

There is however one note from our side as well on that case. Another path to resolve it might be to think on splitting that BIX extract to smaller parts, or to run it more often if that could help avoiding the timeout.

To your question:

“Does BIX node consider all these settings by default ? (or) Do we need to configure them explicitly prconfig/DSS ?”

https://docs.pega.com/bundle/platform/page/platform/reporting/troubleshooting-bix-on-premises-environments.html#untitled1__Specify

The answer is yes. Checking on the BIX/SelectQueryTimeout, it takes the default QuerryTimeout value from the global QuerryTimeout setting which is usually 30s.

So, unless you specify directly in DSS or in prconfig file different value for BIX/selectQueryTimeout, it will take that global default QuerryTimeout value.

That means, that you can specify that value in your DSS setting or in prconfig file and then try running that query with longer BIX/selectQueryTimeout.

The system is checking on the setting, if it exists inside of DSS or prconfig.
And when the setting cannot be found, like in your case, it means Pega is taking the default value mentioned in the last column of that table.

utdi.png
utdi.png1109×852 84.8 KB