PH52030: ADD NEW FUNCTION RELATED TO CORRELATED SUBQUERY OFFLOADING TO IBM DB2 ANALYTICS ACCELERATOR V7

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• Add new function related to correlated subquery offloading to
  IBM Db2 Analytics Accelerator V7.
  IDAAV7R1/K IDAAV7R5/K
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 and Db2 13 for z/OS users who                     *
  * use IBM Db2 Analytics Accelerator V7.                        *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Performance of query with certain                            *
  * correlated subquery sometimes is not                         *
  * as expected when offloaded to IBM Db2                        *
  * Analytics Accelerator V7.                                    *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When certain query with correlated subquery is offloaded to IBM
  Db2 Analytics Accelerator V7 and the performance is not as
  expected, the offloading of this kind of query to IBM Db2
  Analytics Accelerator V7 could be conditionally blocked.
  Examples of such query with correlated subquery includes:
  create table T1(c1 integer,c2 integer, c3 integer, c4 integer);
  create table T2(c1 integer,c2 integer, c3 integer, c4 integer);
  create table T3(c1 integer,c2 integer, c3 integer, c4 integer);
  (1) query with correlated scalar fullselect.
      SELECT C1, C2 FROM T1 A
      WHERE
        C1 BETWEEN 2 * (SELECT MIN(C1) FROM T2 where c1=A.c2)
              AND .5 * (SELECT MAX(C1) FROM T2 where c1=A.c2);
  (2) query with correlated subquery with SET operator(UNION/
      UNION ALL/INTERSECT/INTERSECT ALL/EXCEPT/EXCEPT ALL).
      SELECT * FROM  T1 WHERE EXISTS
         (SELECT C2 FROM  T2
          WHERE T1.C2=T2.C2
         UNION ALL
          SELECT C1 FROM  T3);
  (3) query with correlated subquery with GROUPBY/HAVING clause.
      SELECT * FROM T1 WHERE C1=
       (SELECT C1 FROM T2 WHERE T1.C2=T2.C2 GROUP BY C1);
  (4) query with more than one level of nested correlated
      subquery.
      SELECT T1.C1 FROM  T1
      WHERE EXISTS (SELECT T2.C1 FROM T2
              WHERE 1=1 AND
              EXISTS (SELECT T3.C1 FROM T3
                     WHERE T1.C1=T3.C1));
  (5) query with correlated subquery under an OR predicate.
      SELECT * FROM T1 WHERE EXISTS
        (SELECT C1 FROM T2
         WHERE T1.C2=T2.C2)
               OR EXISTS (SELECT C1 FROM T3 WHERE 1=1);
  (6) query with correlated subquery in CASE statement.
      SELECT CASE
               WHEN EXISTS
                (SELECT * FROM T2 WHERE T2.C1=T1.C1)
               THEN T1.C1 ELSE T1.C1+1
             END
      FROM T1;
  (7) query with sideway reference.
      SELECT T1.C1, T1.C2
      FROM T1, Table(
                 select C1,C2 from T2
                 where T1.C3  = T2.C3 )
               AS TX1(C1,C2)
      where    T1.C1=TX1.C1
  Please Contact IBM Customer Service if you want to disable
  query offloading for these types of queries.
  Additional Keywords:
      IDAAV7R1/K IDAAV7R5/K SQLPERFORMANCE
      SQLCODE4742 SQLCORRSUBQ SQLSUBQUERY SQLCASE
      SQLSCALAR SQLFULLSELECT SQLSCALARFULSEL SQLOR
      SQLUNION SQLUNIONALL SQLINTERSECT SQLINTERSECTALL
      SQLEXCEPT SQLEXCEPTALL SQLGROUPBY SQLHAVING
      SQLSIDEWAY SQLSIDEWAYREF
  

Problem conclusion

Temporary fix

Comments

APAR Information

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

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

  UI90824 UI90825

Modules/Macros

• DSN6SPRM DSNXONZO DSNDQWPZ DSNWZIFD DSNWZIFC DSNDSPRM DSNXOBZO
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI90824

     UP23/03/11 P F303

• RD10 PSY UI90825

     UP23/03/11 P F303

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.