PM72026: ENABLE MULTIPLE INDEX ACCESS FOR SOME PREDICATES THAT HAVE A NON CORRELATED SUBQUERY UNDER AN OR

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• An sql may get a bad performing access path if it needs multiple
  index access.  This apar adds additional opportunities to get
  multiple index access specific to predicates with a
  non-correlated subquery under an OR.
  

Local fix

• Query Rewrite
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9 and DB2 10 for z/OS users of       *
  *                 queries containing a non-Boolean term        *
  *                 uncorrelated subquery predicate.  The left   *
  *                 side of the subquery predicate is a not null *
  *                 column, and the subquery select column is    *
  *                 also a not null column.  The subquery        *
  *                 predicate is ANDing with a stage 2           *
  *                 predicate.                                   *
  ****************************************************************
  * PROBLEM DESCRIPTION: APAR PM72026 provides support for       *
  *                      multiple index access on some non-      *
  *                      Boolean term uncorrelated subquery      *
  *                      predicates.  The left side of the       *
  *                      subquery predicate is a not null        *
  *                      column, and the subquery select column  *
  *                      is also a not null column.  The         *
  *                      comparison operator of the subquery     *
  *                      predicate can be >, >=, =, <, <=.  The  *
  *                      subquery predicate is ANDing with a     *
  *                      stage 2 predicate.                      *
  *                                                              *
  *                      APAR PM72026 adds online-changeable     *
  *                      subsystem parameter in DSN6SPRM called  *
  *                      SUBQ_MIDX that can be used to enable    *
  *                      this performance improvement.  The      *
  *                      default value is DISABLE.               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  APAR PM72026 provides support for multiple index
  access on some non-Boolean uncorrelated subquery
  predicates.The left side of the subquery predicate is
  a not null column, and the subquery select column is
  also a not null column.  The comparison operator of
  the subquery predicate can be >, >=, =, <, <=.  The
  subquery predicate is ANDing with a stage 2 predicate.
  
  
  The following example illustrates such a case.
  
  
      CREATE TABLE T1 (T1C1 INT,
                       T1C2 VARCHAR(5) NOT NULL,
                       T1C3 CHAR(5) NOT NULL);
  
      CREATE TABLE T2 (T2C1 INT,
                       T2C2 VARCHAR(5) NOT NULL,
                       T2C3 CHAR(5) NOT NULL);
  
  
  
      CREATE INDEX I1 ON T1 (T1C2);
      CREATE INDEX I2 ON T1 (T1C3);
  
      SELECT T1C2, T1C3
      FROM T1 WHERE T1C2='AAAAA'
                    OR
                    (T1C3 = (SELECT T2C3
                            FROM T2
                            WHERE T2C1=3)
                     AND T1C1=T1C1);
  
  
      The subquery predicate is non-Boolean term, and it is stage
      2 because it ANDs with a residual predicate T1C1=T1C1.
      With this PTF, multiple index access could be chosen for
      this subquery predicate on indexes I1 and I2:
  
            T1C2='AAAAA'
            OR
            T1C3 = (SELECT T2C3
                    FROM T2
                    WHERE T2C1=3)
  
      The whole predicate will be re-applied again afterwards
      as residual predicate.
  
           WHERE T1C2='AAAAA'
                 OR
                 (T1C3 = (SELECT T2C3
                         FROM T2
                         WHERE T2C1=3)
                  AND 1=1);
  
  APAR PM72026 adds online-changeable subsystem parameter in
  DSN6SPRM called SUBQ_MIDX that can be used to enable this
  performance improvement. The default value is DISABLE.
  

Problem conclusion

• The DB2 code is modified to support this performance
  enhancement.
  
  Additional Keywords: SQLSUBQUERY SQLINDEX MIDX SQLLT SQLGT
                       SQLEQ SQLGE SQLLE SQLNOTNULL
                       SQLNONCORRSUBQ SQLACCESSPATH
                       SQLPERFORMANCE
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

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

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

  UK83312 UK83313

Modules/Macros

• DSN@XAZP DSNDQWPZ DSNDSPRM DSNTIDXA DSNTIDXB
  DSNTIJUZ DSNTINST DSNTXAZP DSNWZIFA DSNWZIF9 DSNXEMG1 DSNXGINB
  DSNXGOSQ DSNXGTBC DSNXOBM  DSNXOGA1 DSNXOMIS DSNXOMPS DSNXOQ2
  DSNXOW2A DSNXRBND DSNXRBN9 DSN6SPRM
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK83312

     UP12/11/22 P F211

• R910 PSY UK83313

     UP12/11/22 P F211

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.