DB2 FOR LUW / DB2FORLUW / A50 - 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 10.5 fixpack 7                        * 
****************************************************************

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 10.5 fixpack 7

not known / see Local fix

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

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

28.01.2014
20.01.2016
20.01.2016