IBM Support

SQL0101N The statement is too long or too complex.

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.

[{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"--","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"10.2.2;10.2.1;10.2","Edition":"All Editions","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 June 2018

UID

swg21349200