DB2 FOR LUW / DB2FORLUW / A10 - DB2

With optimizer level 5 a query that has many outer joins and 
a group by clause that includes all columns in the select, can 
have slow performance due to a long compilation time or its 
compilation might fail with 
SQL0101N  The statement was not processed because a limit such 
as a memory limit, an SQL limit, or a database limit was 
reached. 
SQLSTATE=54001 
 
This is due to DB2 spending a long time calculating all the 
possible access plans. 
 
Increasing the statement heap only delays the error SQL0101N 
after the value is exhausted.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 10.1 and Fix Pack 5                   * 
****************************************************************

The query might compile with optimization level 0 or 
optimization level 1, and adjusting the Statement heap to 
automatic. 
 
The solution that was developed for APAR IY71442 might help. 
Enabling it requires setting DB2_REDUCED_OPTIMIZATION registry 
variable to a special value.  To acquire further instruction, 
contact DB2 support.

Problem was first fixed in DB2 Version 10.1 and Fix Pack 5

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

19.02.2015
13.07.2015
13.07.2015