SQLCODE -101: Statement too long or too complex

What it means

Db2 has a maximum length for the text of a statement, and it has internal limits on how much work it will do to parse and optimize one. Those limits depend on things like the number of tables, predicates, nested expressions, subqueries and UNION branches, and on the storage Db2 needs to hold the statement's internal structure. When a statement goes past any of them, Db2 stops and returns -101. No exact figure is given in the message, and the limits vary between releases.

Hand-written SQL rarely gets this far. The usual source is SQL built by a program, a report tool or a framework, which can produce thousands of IN list values or hundreds of OR or UNION branches without anyone noticing.

IBM notes that query parallelism makes a statement more complex to process. A query that is close to the limit can fail with parallelism on and work with it off.

Common causes

• A very long IN list generated from data, for example one value per selected account, with no upper limit in the program.
• A query built from many UNION ALL branches or OR predicates, one per input item.
• Views defined on views, so a short query expands into a very large one once Db2 merges the view definitions.
• Deeply nested CASE expressions or subqueries produced by a reporting tool.
• Parallelism (DEGREE(ANY) for static SQL, or CURRENT DEGREE = 'ANY' for dynamic SQL) on a query that is already large.

How to find the problem

1. Get the full statement text. For dynamic SQL, log it in the program before the PREPARE, or capture it with your monitor. Check its length and count the IN list values, UNION branches and tables.
2. Check whether the statement reads views, and look up their definitions in SYSIBM.SYSVIEWS to see how large the merged query becomes.
3. For static SQL, check the DEGREE bind option of the package in SYSIBM.SYSPACKAGE. For dynamic SQL, check what CURRENT DEGREE is set to.
4. Ask whether the same statement worked with fewer input values. If it fails only above a certain number, the generated part is the cause.

How to fix it

• Put long lists of values in a declared global temporary table or a work table and join to it, instead of building a giant IN list.
• Split the work into several smaller statements, for example by processing values in groups of a few hundred.
• Simplify the views, or query the base tables directly for this case.
• If parallelism is on, try the statement with DEGREE(1) for static SQL, or with SET CURRENT DEGREE = '1' for dynamic SQL.
• Change the report tool or framework settings that generate the SQL, so they don't expand lists without limit.

What it looks like

DSNT408I SQLCODE = -101, ERROR:  THE STATEMENT IS TOO LONG OR TOO COMPLEX
DSNT418I SQLSTATE   = 54001 SQLSTATE RETURN CODE

Technical reviewer: Albert Joseph, WND Software.

Last reviewed 2026-10-18 by the WND Software team. Written in our own words. For the full reference, check IBM’s Db2 for z/OS documentation for your release.