DB2 FOR LUW / DB2FORLUW / 970 - DB2

When the following conditions are true, scalar subqueries will 
not be pushed down through UNION ALL. 
1. UNION ALL contains one or more correlated subqueries 
2. UNION All view has a predicate referencing a SQL scalar UDF 
3. The UDF has a function body that is a simple RETURN 
fullselect 
 
 
As an example please find following statement: 
 
CREATE VIEW VIEW1 (EMPNO, DEPTNO, ISRESP) as ( 
 SELECT A.EMPNO, A.WORKDEPT, 
  CASE WHEN NOT EXISTS ( SELECT 1 FROM project B WHERE A.EMPNO = 
B.RESPEMP ) 
   THEN 0 
   ELSE 1 
   END 
  AS ISRESP FROM employee A ) 
 UNION ALL 
 SELECT MGRNO, ADMRDEPT, 0 FROM DEPARTMENT 
 
 
and function used as predicate for EMPNO: 
 
CREATE OR REPLACE FUNCTION SCALAR_FUNCTION ( EMPNO int ) 
  RETURNS INTEGER 
  DETERMINISTIC CONTAINS SQL NO EXTERNAL ACTION 
  RETURN VALUES ( 
   CASE WHEN EMPNO < 100 
    THEN EMPNO 
    ELSE 9999 
   END 
  ) 
 
 
Once you generate explain plan for the execution, you will 
notice: 
 
 
Access Plan: 
----------- 
    Total Cost:        27.3384 
    Query Degree:        1 
 
             Rows 
            RETURN 
            (   1) 
             Cost 
              I/O 
               | 
             2.24 
            NLJOIN 
             (   2) 
            27.3384 
               4 
          /---+-------\ 
         1                2.24 
     TBSCAN         FILTER 
       (   3)            (   4) 
  1.6532e-005     13.7274 
        0                   2 
        |                   | 
        1                  56 
TABFNC: SYSIBM   UNION 
     GENROW          (   5) 
                         13.7139 
                             2 
                  /-------+--------\ 
                 14                     42 
         TBSCAN                   NLJOIN 
           (   6)                      (   7) 
         6.81553                   6.89572 
              1                           1 
             |                      /----+-----\ 
           14                   42                 1 
     TABLE: MBASTER   TBSCAN        TBSCAN 
       DEPARTMENT       (   8)             (   9) 
           Q1               6.82217         0.00690429 
                                   1                  0 
                                   |                  | 
                                  42             0.47619 
                        TABLE: MBASTER     SORT 
                             EMPLOYEE         (  10) 
                                 Q6           0.00667615 
                                                      0 
                                                      | 
                                                 0.47619 
                                                 IXSCAN 
                                                  (  11) 
                                               0.00619229 
                                                      0 
                                                      | 
                                                     20 
                                           INDEX: MBASTER 
                                                  XPROJ2 
                                                     Q3 
 
 
Predicate on EMPNO is applied on FILTER 4: 
 
        Predicates: 
        ---------- 
        2) Residual Predicate, 
            Comparison Operator:        Equal (=) 
            Subquery Input Required:    No 
            Filter Factor:            0.04 
 
            Predicate Text: 
            -------------- 
            (DECFLOAT(Q8.$C0, 34, '.') = Q10.$C0) 
 
 
 
Presence of correlation: 
 
CASE WHEN NOT EXISTS ( 
 SELECT 1 FROM project B WHERE A.EMPNO = B.RESPEMP ) 
 THEN 0 
 ELSE 1 
 END AS ISRESP FROM employee A ) 
 
prevents the scalar predicate push down through the UNION ALL.

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

In order to workaround the problem, you need to remove VALUE 
modifier from the function used as a predicate: 
 
 
CREATE OR REPLACE FUNCTION NONSCALAR_FUNCTION (EMPNO int) 
  RETURNS INTEGER 
  DETERMINISTIC CONTAINS SQL NO EXTERNAL ACTION 
  RETURN ( CASE WHEN EMPNO < 100 THEN EMPNO ELSE 9999 
    END 
  ); 
 
Once you generate explain plan for the execution, you will 
notice that predicate on EMPNO is applied on TBSCAN: 
 
 
        Predicates: 
        ---------- 
        3) Sargable Predicate, 
            Comparison Operator:        Equal (=) 
            Subquery Input Required:    No 
            Filter Factor:            0.04 
 
            Predicate Text: 
            -------------- 
            (DECFLOAT(Q6.EMPNO, 34, '.') = 20) 
 
 
Further, we can also apply additional predicate on IXSCAN due 
to correlation A.EMPNO = B.RESPEMP : 
 
        Predicates: 
        ---------- 
        2) Sargable Predicate, 
                Comparison Operator:            Equal (=) 
                Subquery Input Required:        No 
                Filter Factor:                  0.0228571 
 
                Predicate Text: 
                -------------- 
                (DECFLOAT(Q1.MGRNO, 34, '.') = 20)

Problem was first fixed in DB2 version 9.7 fixpack 10

not known / see Local fix

The suggested alternate behavior has been entered into IBM 
internal record wsdbu01235788. If you need further information, 
please contact IBM Support.

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

28.01.2014
13.11.2014
13.11.2014

9.7.FP10