Troubleshooting
Problem
IBM DB2 Error occurs when attempting to execute a Report, update Audit Database or Content Store database. Error message appears in the cogserver.log: [IBM][CLI Driver][DB2/AIX64] SQL0101N The statement is too long or too complex.
Symptom
IBM Cognos Business Intelligence:
QE-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'.
UDA-SQL-0107 A general exception has occurred during the operation "prepare".
[IBM][CLI Driver][DB2] SQL0101N The statement is too long or too complex. SQLSTATE=54001
or
CM-SYS-5092 Content Manager failed while returning the query results.
[IBM][CLI Driver][DB2/6000] SQL0101N The statement is too long or too complex. SQLSTATE=54001
IBM Cognos Series 7 Impromptu:
SQL0101N The statement is too long or too complex.
Cause
IBM DB2 statement heap size is too small.
Environment
Applies to all products using a IBM DB2 database.
Resolving The Problem
This is an issue with the heap size for IBM DB2. To update the heap size.
Steps:
The following is an extract from IBM DB2 documentation:
SQL0101N The statement is too long or too complex.
Explanation: The statement could not be processed because it exceeds a system limit for either length or complexity, or because too many constraints or triggers are involved.
The default statement heap size for DB2 is too small to support the complex SQL statements.
Try updating the statement heap size to be 8000 or more.
To update the heap size, open a DB2 command prompt and type the following:
> db2 update db cfg for <database name> using stmtheap 8000
Please consult your DBA and/or consult DB2 customer support for further assistance and information.
Related Information
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21349200