PM27180: ALLOW QUERY TRANSFORMATION PREDICATE PUSH DOWN (PUSHDOWN) INTO TABLE EXPRESSION WITH SCALAR FULL SELECT IN SELECT LIST

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Query performed poorly because DB2 could not pushdown a local
  predicate into a nested table expression that was defined with a
  scalar full select in its select list.
  
  For example
  
  select *
  from (select c1, (select c2 from t2)
  from t1) tx(c1, c2)
  where tx.c1 = 1
  
  The local predicate tx.c1 = 1 isn't pushed down into the table
  expression tx, because it is defined with a full select in it's
  subquery.
  

Local fix

• manual push down the predicate
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9 for z/OS and all DB2 10 for z/OS   *
  *                 users of queries that contain predicates     *
  *                 which can be pushdown to one of its'         *
  *                 materialised VIEW or Table expression.       *
  ****************************************************************
  * PROBLEM DESCRIPTION: Enable predicate pushdown to related    *
  *                      materialised view or table expression   *
  *                      which has a scalar fullselect in the    *
  *                      SELECT list of itself, when all of      *
  *                      the below conditions are satisfied:     *
  *                                                              *
  *                      1.There is no expression that contains  *
  *                        scalar fullselect which is in the     *
  *                        pushdown target view's SELECT list.   *
  *                                                              *
  *                      2.There is no non-deterministic         *
  *                        function or UDF with external action  *
  *                        in pushdown target view's SELECT      *
  *                        list or scalar fullselect's select    *
  *                        list.                                 *
  *                                                              *
  *                      3.Does not include nested scalar        *
  *                        fullselect in pushdown target         *
  *                        view or table expression's SELECT     *
  *                        list.                                 *
  *                                                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Enable predicate pushdown to related materialised view or table
  expression which has a scalar fullselect in the SELECT list of
  itself, when all of the below conditions are satisfied:
  
  1.There is no expression that contains scalar fullselect which
    occurs in the pushdown target view's SELECT list.
  
  2.There is no non-deterministic function or UDF with external
    action in the pushdown target view's SELECT list or scalar
    fullselect's select list.
  
  3.Does not include nested scalar fullselect in the pushdown
    target view or table expression's SELECT list.
  
  
  example:
  ================================================================
  1. Enable predicate pushdown.
  
     SELECT TX.C1, TX.C2
     FROM (SELECT TU1.CU11,
                  (SELECT TU2.CU22
                   FROM TU2
                   WHERE TU1.CU11 = TU2.CU21)
           FROM TU1) TX(C1, C2)
     WHERE TX.C1 IN (?);
  
     DB2 will pushdown "TX.C1 IN (?)" into TX(C1,C2) view.
  
  2. Disable predicate pushdown, when there is non-deterministic
     function referenced.
  
     SELECT TX.C1, TX.C2
     FROM (SELECT TU1.CU11,rand()
           FROM TU1) TX(C1, C2)
     WHERE TX.C1 IN (?);
  
     SELECT TX.C1, TX.C2
     FROM (SELECT TU1.CU11,
                  (SELECT RAND()
                   FROM TU2
                   WHERE TU1.CU11 = TU2.CU21)
           FROM TU1) TX(C1, C2)
     WHERE TX.C1 IN (?);
  
     DB2 will not pushdown "TX.C1 IN (?)",since non-deterministic
     function is referenced.
  
  3. Disable predicate pushdown, when there is an expression
     containing a scalar fullselect.
  
     SELECT TX.C1, TX.C2
     FROM (SELECT TU1.CU11,
                  (SELECT TU2.CU22
                   FROM TU2
                   WHERE TU1.CU11 = TU2.CU21)+1
           FROM TU1) TX(C1, C2)
     WHERE TX.C1 IN (?);
  
  4. Disable predicate pushdown, when there is nested Scalar
     fullselect is referenced.
  
     SELECT TX.C1, TX.C2
     FROM (SELECT TU1.CU11,
                  (SELECT (SELECT TU3.CU31
                           FROM TU3
                           WHERE TU1.CU11= TU2.CU21)
                   FROM TU2
                   WHERE TU1.CU11 = TU2.CU21)+1
           FROM TU1) TX(C1, C2)
     WHERE TX.C1 IN (?);
  
  5. Disable predicate pushdown, if UDF with external action is
  found in a location like example 2 or 3.
  
  DB2 disallow the preidcate pushdown, when there is a scalar
  fullselect in the target view or table expression's SELECT
  list.
  

Problem conclusion

• DB2 now can enable predicate pushdown even when there is a
  scalar fullselect in the target view or table expression
  SELECT list in some conditions.  The query's performance will
  be changed.
  
  Additional Keywords: SQLVIEW SQLTABLEEXPR SQLPREDPUSHDOWN
                       SQLSCALAR SQLFULLSELECT SQLSCALARFULSEL
                       SQLUDF
  

Temporary fix

Comments

• ž**** PE11/07/11 PTF IN ERROR. SEE APAR PM42647  FOR DESCRIPTION
  

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UK64508 UK64509

Modules/Macros

• DSNXOCT  DSNXODSO DSNXOVP
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK64508

     UP11/02/17 P F102

• R910 PSY UK64509

     UP11/02/17 P F102

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.