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

 Binod Garg (BinodG12)
Members Equity Bank Pty Ltd

Members Equity Bank Pty Ltd
AU
BinodG12 Member since 2017 8 posts
Members Equity Bank Pty Ltd
Posted: Jul 9, 2019
Last activity: Feb 3, 2020
Posted: 9 Jul 2019 21:11 EDT
Last activity: 3 Feb 2020 11:26 EST
Closed

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

To see attachments, please log in.
Java and Activities
Data Integration

Posted: 5 years ago
Posted: 11 Jul 2019 9:28 EDT
Simon Chan (chans3)
PEGA
Senior Principal Engineer, Technical Support - 1:1 Customer Engagement
Pegasystems Inc.
GB

Has these recommended settings been applied?

<env name="/compatibility/BIXUseOptimizedClipboardXML" value="true" />
<env name="/compatibility/BIXdisableForwardChaining" value="true" />
<env name="/compatibility/BIXdisableBackwardChaining" value="true" />
To see attachments, please log in.
Posted: 5 years ago
Posted: 11 Jul 2019 23:37 EDT
Binod Garg (BinodG12)
Members Equity Bank Pty Ltd

Members Equity Bank Pty Ltd
AU

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

Below is from the logs

 

Show More

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

Show Less
To see attachments, please log in.
Posted: 5 years ago
Posted: 15 Jul 2019 5:40 EDT
Marc Lasserre (MarcLasserre_GCS)
PEGA
Technical Account Manager
Pegasystems Inc.
FR

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

To see attachments, please log in.
Posted: 5 years ago
Posted: 16 Jul 2019 21:43 EDT
Binod Garg (BinodG12)
Members Equity Bank Pty Ltd

Members Equity Bank Pty Ltd
AU

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.

To see attachments, please log in.
Posted: 5 years ago
Posted: 17 Jul 2019 10:19 EDT
Abhinav Gupta (Abhinav7)
PEGA
Manager, Technical Support - Platform Service Engineering
Pegasystems Inc.
IN

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

To see attachments, please log in.
Posted: 5 years ago
Posted: 17 Jul 2019 10:45 EDT
Simon Chan (chans3)
PEGA
Senior Principal Engineer, Technical Support - 1:1 Customer Engagement
Pegasystems Inc.
GB

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.

To see attachments, please log in.
Posted: 5 years ago
Posted: 17 Jul 2019 19:52 EDT
Binod Garg (BinodG12)
Members Equity Bank Pty Ltd

Members Equity Bank Pty Ltd
AU

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

To see attachments, please log in.
Posted: 4 years ago
Posted: 3 Feb 2020 11:26 EST
Ravi Kumar B (br001)
Pegasystems Inc.
Principal Technical Solutions Engineer
Pegasystems Inc.
IN

Hi BinodG12,

 

 After collecting the query from PRPC and running the query from DB ,If the query gets timed out  from DB ,we need to increase from DB query time out value .

If the query timeout is set will necessary value ,kindly raise SR by hotline asking for the 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 >>

 

As we have 30 sec minimal for BIX query till 7.4 version of the PRPC.

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