When you get the error "SQL0101N The statement is too long or too complex" you can decrease the query optimization class.

Query optimization class

A query optimization class is a set of query rewrite rules and optimization techniques for compiling queries.

The primary query optimization classes are:

1

Restricted optimization. Useful when memory and processing resources are severely restrained. Roughly equivalent to the optimization provided by Version 1.

2

Slight optimization. Specifies a level of optimization higher than that of Version 1, but at significantly less optimization cost than levels 3 and above, especially for very complex queries.

3

Moderate optimization. Comes closest to matching the query optimization characteristics of DB2^(R) for MVS^(TM)/ESA.

5

Normal optimization. Recommended for a mixed environment using both simple transactions and complex queries.

7

Normal optimization. The same as query optimization 5 except that it does not reduce the amount of query optimization for complex dynamic queries.

Other query optimization classes, to be used only under special circumstances, are:

0

Minimal optimization. Use only when little or no optimization is required (that is, for very simple queries on well-indexed tables).

9

Maximum optimization. Uses substantial memory and processing resources. Use only if class 5 is insufficient (that is, for very complex and long-running queries that do not perform well at class 5).

In general, use a higher optimization class for static queries and for queries that you anticipate will take a long time to execute, and a lower optimization class for simple queries that are submitted dynamically or that are run only a few times.

To set the query optimization for dynamic SQL or XQuery statements, enter the following command in the command line processor:

SET CURRENT QUERY OPTIMIZATION = n;

where 'n' is the desired query optimization class.