Troubleshooting
Problem
The following message shows up in your db2cli log file : native retcode = -101; state = "54001"; message = "[IBM][CLI D river][DB2/AIX64] SQL0101N The statement is too long or too complex. SQLSTATE= 54001
Cause
DB2 statement heap size is too small.
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.
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.
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)
Product Synonym
IDS;ITDS;Directory Server;IBM Tivoli Directory Server
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21230096