SQL0101N error message appears in DB2CLI log

Resolving The Problem

SQL0101N The statement is too long or too complex. SQLSTATE=
54001 - Increasing the DB2 Statement Heap Size

Description of Error :
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.messages.sql.doc/doc/msql00101n.html


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.
If the statement is one that creates or modifies a packed description, the new packed description may be too large for its corresponding column in the system catalogs.
Federated system users should also check to see if the statement:

• Exceeds either a federated server system limit or a data source system limit for length or complexity.
• Violates some other data source specific limit.

The statement cannot be processed.

Note:

Where character data conversions are performed for applications and databases running under different codepages, the result of the conversion is exceeding the length limit.
User Response:
Either:

• Break the statement up into shorter or less complex SQL statements.
• Increase the size of the statement heap (stmtheap) in the database configuration file.
• Reduce the number of check or referential constraints involved in the statement or reduce the number of indexes on foreign keys.
• Reduce the number of triggers involved in the statement.
• Federated system users: determine which data source is failing the statement (see the problem determination guide for procedures to follow to identify the failing data source) and determine the cause of the rejection. If the rejection is coming from the federated server, increase the size of the statement heap (stmtheap) in the database configuration file.

sqlcode: -101
sqlstate: 54001


To Check the current DB2 Statement Heap Size :

For unix systems:

1) su - <dbadminuser>
2) db2 connect to <dbname>
3) db2 get db config for <dbname>
4) check the SQL statement heap size (STMTHEAP) and update it to 5 times it's current setting using the procedure below.

For windows systems:

1) open a db2 command window
2) db2 connect to <dbname>
3) db2 get db config for <dbname>
4) check the SQL statement heap size (STMTHEAP) and update it to 5 times it's current setting using the procedure below.



To Increase DB2 Statement Heap:

For unix systems:

1. Kill the slapd process
2. su - <dbadminuser>
3. source the <dbname> profile: . sqllib/db2profile
4. Issue the "db2 update db cfg for <dbname> using STMTHEAP <value>" command where dbname is ldapdb2 or whatever db is used for IDS and value is the current statement heap size (STMTHEAP) multiplied by 5.
5. db2stop
6. exit back to root, and restart slapd (which will restart the <dbname> database)



For windows systems:
1. Kill the slapd process
2. open a db2 command window
3. db2 connect to dbname
4. Issue the "db2 update db cfg for <dbname> using STMTHEAP <value>" command where dbname is ldapdb2 or whatever db is used for IDS and value is the current statement heap size (STMTHEAP) multiplied by 5.
5. db2stop
6. exit back to root, and restart slapd (which will restart the <dbname> database)