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