DB2 FOR LUW / DB2FORLUW / 970 - DB2

Queries involving a VALUES clause joined to a UNION or UNION ALL 
operation may experience poor performance. Affected queries may 
required fully reading the table referenced by the UNION or 
UNION ALL operator before applying the filtering offered by the 
VALUES clause. For example in the following query can exhibit 
this behaviour. 
 
select v.* 
  from ( 
    select c1, c2, c3 
      from ( 
        select * 
          from t1 
         union 
        select * 
          from t2 
           ) 
       ) v, table( 
    values (?, ?),(?, ?),(?, ?),(?, ?),(?, ?) 
       ) as c(c1, c2) 
 where v.c1 = c.c1 
   and v.c2 = c.c2; 
 
To determine if you are affected by this issue. Gather explain 
output for relevant queries and examine the db2exfmt output to 
see if the TFNUM_LINT table function is joined to the UNION 
result.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 LUW version 9.7 fixpak 10.                    * 
****************************************************************

Manually rewrite the query to push the values clause into each 
leg of the UNION operator.

First fixed in DB2 LUW version 9.7 Fixpak 10.

not known / see Local fix

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

02.09.2014
19.11.2014
19.11.2014

9.7.