Invalid SQL Queries Cause Database or Query Timeout Errors in BI Publisher Enterprise
Invalid SQL queries can cause the following errors.
Example 1
The Data Access Driver shows the following error messages:
08 Dec 2009 09:34:56,531 [SEVERE] - [JDBJ] SQLSTATE = 42000 SQLMessage = SQLMessage = ORA-01795: maximum number of expressions in a list is 1000 08 Dec 2009 09:34:56,531 [SEVERE] - [JDBJ] Query failed to execute within the specified timeout of 10000 ms. Timeout the Query 08 Dec 2009 09:34:56,532 [SEVERE] - [BASE] com.jdedwards.database.base.JDBException: [QUERY_TIMED_OUT] Query timed out. Operation is cancelled. 08 Dec 2009 09:34:56,532 [SEVERE] - [BASE] com.jdedwards.database.base.JDBException: [QUERY_TIMED_OUT] Query timed out. Operation is cancelled. com.jdedwards.database.base.JDBException:
Solution: Rewrite the SQL query used in BI Publisher Enterprise to avoid using more than 1000 parameters in a WHERE clause expression.
Example 2
The Data Access Driver shows the following error messages:
com.jdedwards.database.base.JDBException: [QUERY_TIMED_OUT] Query timed out. Operation is cancelled.
Solution: Increase the connection timeout parameter in jdbj.ini.
[JDBj-CONNECTION POOL] connectionTimeout=1800000 cleanPoolInterval=1800000
Example 3
The Data Access Driver shows the following error messages:
[SQL_EXCEPTION_OCCURRED] An SQL exception occurred: Resultset timeout java.sql.SQLException
Solution: Increase the resultset timeout parameter in jdbj.ini.
[JDBj-RUNTIME PROPERTIES] msSQLQueryTimeout=1800000 resultSetTimeout=1800000 transactionTimeout=1800000