DB2 FOR LUW / DB2FORLUW / A10 - DB2

In a partitioned database environment, the optimizer tends to 
sort at the sending end of a table queue, and merge the rows at 
the receiving end.  For a query with a stack of OLAP functions, 
the optimizer tends to stack multiple merging table queues and 
sorts, and if the the sorts are large enough to spill, the 
performance might degrade. 
 
For example, if a query contains multiple OLAP functions in the 
select list such as 
 
SELECT ... 
     MIN(A) OVER(PARTITION BY X ORDER BY Y,Z ROWS BETWEEN 20 
PRECEDING AND 20 FOLLOWING) as olap1, 
     MIN(B) OVER(PARTITION BY X ORDER BY Y,Z ROWS BETWEEN 20 
PRECEDING AND 20 FOLLOWING) as olap2, 
     MIN(C) OVER(PARTITION BY X ORDER BY Y,Z ROWS BETWEEN 20 
PRECEDING AND 20 FOLLOWING) as olap3, 
     MIN(D) OVER(PARTITION BY X ORDER BY Y,Z ROWS BETWEEN 20 
PRECEDING AND 20 FOLLOWING) as olap4 
 
then the access plan generated by the optimizer will show a 
stack of merging table queues and sorts as in the following 
formatted snippet using db2exfmt 
 
   ... 
     | 
 6.46086e+06 
   MDTQ 
   (  17) 
 4.50224e+06 
 1.22783e+06 
     | 
 6.46086e+06 
   TBSCAN 
   (  18) 
 4.45483e+06 
 1.22783e+06 
     | 
 6.46086e+06 
   SORT 
   (  19) 
 3.7682e+06 
 1.08216e+06 
     | 
 6.46086e+06 
   MDTQ 
   (  20) 
 2.51054e+06 
   936492 
     | 
   ...

**************************************************************** 
* USERS AFFECTED:                                              * 
* The problem only occurs in a database partitioned            * 
* environment.                                                 * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 10.1 Fix Pack 2, and set the registry * 
* variable DB2_SORT_AFTER_TQ=SCALAG.                           * 
****************************************************************

You can set DB2_SORT_AFTER_TQ=YES to help improve performance, 
but this will affect every sort decision made by the optimizer.

DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

The problem is first fixed in DB2 version 10.1 Fix Pack 2.

not known / see Local fix

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

23.10.2012
15.04.2013
15.04.2013