Question

BIX Query timeout

Hi ,

We have a scenario where the BIX query is timing out . How and where is this timeout configured? Is this configured within Pega which is used for JDBC timeout setting?

Thanks in advance

Comments

Keep up to date on this post and subscribe to comments

July 10, 2019 - 2:42am

Hello,

Which timeout is it? Can we see it, do you have anything on the logs or on Fiddler?

Pega
July 11, 2019 - 9:28am

Has these recommended settings been applied?

<env name="/compatibility/BIXUseOptimizedClipboardXML" value="true" />

<env name="/compatibility/BIXdisableForwardChaining" value="true" />

<env name="/compatibility/BIXdisableBackwardChaining" value="true" />

July 11, 2019 - 11:37pm

These are not applied but I do not think these related to query timeout . 

Below is from the logs

 

    [java] 2019-07-08 18:10:50,519 [        XXXX] [  STANDARD] [                    ] [                    ] (exec.SqlQueryResultSetExecutor) 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.

     [java] DatabaseException caused by prior exception: com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.

     [java]  | SQL Code: 0 | SQL State: HY008

     [java] 

     [java] com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: 0 SQLState: HY008 Message: The query has timed out.

     [java] DatabaseException caused by prior exception: com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.

     [java]  | SQL Code: 0 | SQL State: HY008

     [java] 

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

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

     [java] at com.pega.pegarules.data.internal.access.Lister.convertSqlExceptionToDatabaseException(Lister.java:577) ~[prprivate.jar:?]

     [java] at com.pega.pegarules.data.internal.access.Lister.listWithResultPackager(Lister.java:421) ~[prprivate.jar:?]

     [java] at com.pega.pegarules.data.internal.access.Lister.list(Lister.java:185) ~[prprivate.jar:?]

     [java] at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:2906) ~[prprivate.jar:?]

     [java] at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:2898) ~[prprivate.jar:?]

     [java] at com.pega.pegarules.data.internal.rd.queryexec.SqlQueryResultSetExecutor.executeQuery(SqlQueryResultSetExecutor.java:89) ~[prprivate.jar:?]

     [java] at com.pega.pegarules.data.internal.rd.queryexec.SqlQueryResultSetExecutor.executeQuery(SqlQueryResultSetExecutor.java:72) ~[prprivate.jar:?]

     [java] at com.pega.pegarules.data.internal.rd.queryexec.SqlQueryResultSetExecutor.executeQuery(SqlQueryResultSetExecutor.java:48) ~[prprivate.jar:?]

     [java] at com.pega.pegarules.data.internal.rd.SqlReportResultSetExecutor.executeQuery(SqlReportResultSetExecutor.java:104) ~[prprivate.jar:?]

     [java] at com.pega.pegarules.data.internal.rd.SqlReportResultSetExecutor.executeSql(SqlReportResultSetExecutor.java:88) ~[prprivate.jar:?]

     [java] at com.pega.pegarules.data.internal.rd.SqlReportResultSetExecutor.execute(SqlReportResultSetExecutor.java:67) ~[prprivate.jar:?]

     [java] at com.pega.pegarules.data.internal.rd.SqlReportResultSetExecutor.execute(SqlReportResultSetExecutor.java:41) ~[prprivate.jar:?]

     [java] at com.pega.pegarules.data.internal.access.ExtractImpl.doExtract(ExtractImpl.java:1692) ~[prprivate.jar:?]

     [java] at com.pega.pegarules.data.internal.access.ExtractImpl.runExtract(ExtractImpl.java:1215) ~[prprivate.jar:?]

     [java] at com.pega.pegarules.data.internal.access.ExtractImpl.init(ExtractImpl.java:918) ~[prprivate.jar:?]

     [java] at com.pega.pegarules.data.internal.access.ExtractImpl.parseAndRunExtractCommand(ExtractImpl.java:867) ~[prprivate.jar:?]

     [java] at com.pega.pegarules.data.internal.access.ExtractImpl.parseAndRunExtractCommand(ExtractImpl.java:796) ~[prprivate.jar:?]

     [java] at com.pega.pegarules.data.internal.access.ExtractImpl.main(ExtractImpl.java:772) ~[prprivate.jar:?]

     [java] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_112]

     [java] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_112]

     [java] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_112]

     [java] at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_112]

     [java] at com.pega.pegarules.internal.bootstrap.PRBootstrap.invokeMethod(PRBootstrap.java:370) ~[prbootstrap.jar:7.4.0-185]

     [java] at com.pega.pegarules.internal.bootstrap.PRBootstrap.invokeMethodPropagatingThrowable(PRBootstrap.java:411) ~[prbootstrap.jar:7.4.0-185]

     [java] at com.pega.pegarules.boot.internal.extbridge.AppServerBridgeToPega.invokeMethodPropagatingThrowable(AppServerBridgeToPega.java:224) ~[prbootstrap-api.jar:7.4.0-185]

     [java] at com.pega.pegarules.boot.internal.extbridge.AppServerBridgeToPega.invokeMethod(AppServerBridgeToPega.java:273) ~[prbootstrap-api.jar:7.4.0-185]

     [java] at com.pega.pegarules.pub.PegaRULES.main(PegaRULES.java:85) ~[prbootstrap-api.jar:7.4.0-185]

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

     [java] at com.microsoft.sqlserver.jdbc.TDSCommand.checkForInterrupt(IOBuffer.java:4796) ~[sqljdbc4.jar:?]

     [java] at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:70) ~[sqljdbc4.jar:?]

     [java] at com.microsoft.sqlserver.jdbc.SQLServerResultSet.<init>(SQLServerResultSet.java:284) ~[sqljdbc4.jar:?]

     [java] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1504) ~[sqljdbc4.jar:?]

     [java] at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:390) ~[sqljdbc4.jar:?]

     [java] at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:340) ~[sqljdbc4.jar:?]

     [java] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575) ~[sqljdbc4.jar:?]

     [java] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400) ~[sqljdbc4.jar:?]

     [java] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179) ~[sqljdbc4.jar:?]

     [java] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154) ~[sqljdbc4.jar:?]

     [java] at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:322) ~[sqljdbc4.jar:?]

     [java] at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-java7-2.4.12.jar:?]

     [java] at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-java7-2.4.12.jar:?]

     [java] at com.pega.pegarules.data.internal.store.DatabasePreparedStatementImpl.execute(DatabasePreparedStatementImpl.java:511) ~[prprivate.jar:?]

     [java] at com.pega.pegarules.data.internal.access.Lister.listWithResultPackager(Lister.java:399) ~[prprivate.jar:?]

     [java] ... 25 more

     [java] 2019-07-08 18:10:50,520 [     XXXX   ] [  STANDARD] [                    ] [                    ] (exec.SqlQueryResultSetExecutor) ERROR   - Query: SELECT "PC0"."pzPVStream" AS "pzPVStream" , "PC0"."pxCommitDateTime" AS "pxCommitDateTime" , "PC0"."pzInsKey" AS "pzInsKey" FROM dbo.MEB_COBRA_WORK "PC0" WHERE  (  (  ( "PC0"."pxUpdateDateTime" >= ?  AND "PC0"."pxUpdateDateTime" < ?  )  )  AND  "PC0"."pzInsKey" LIKE ?   )  ORDER BY 3 ASC

     [java]      #Instances this thread updated: 0

     [java]      CPU Time (this thread): 0.0 seconds

July 15, 2019 - 5:40am
Response to BinodG12

Hello,

This is apparently a timeout from the database. Have a look into your PegaALERTS logs and get the query. Then run that same query outside Pega using a database tool and check the result with your DBA

July 16, 2019 - 9:43pm

If it was a timeout from database, it would have timedout from outside pega as well. However this does not timeout from outside Pega. I strongly believe this is JDBC timeout. However need the details on how to resolve this from Pega.

Pega
July 17, 2019 - 10:19am

Hi Binod,

Can you trace the sql query and run it directly from sql developer/pgadmin studio.Please verify how much time it takes to run execute the query.If it is taking more time then you can either optimize the query to improve its performance or increase timeout.

Thanks,
Abhinav

Pega
July 17, 2019 - 10:45am

Are you running BIX through the designer studio or via a script? How is the data being extracted? XML, CSV or target database?

Please confirm your version.

July 17, 2019 - 7:52pm

The BIX is run from a script. and the data is extracted to a target database. The Pega version being used is 7.4

Pega
July 17, 2019 - 11:36pm