PM73542: MULTI-INDEX ACCESS NOT USED WITH A PREDICATE COMPARING A TIMESTAMP COLUMN TO A LITERAL VALUE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• In DB2 10, DB2 may neglect to use multi-index access when one of
  the predicates that should be matching in the multi-index plan
  is comparing a timestamp column to a literal value.  DB2
  incorrectly marks the predicate on the timestamp column as
  stage 2 which means the desired multi-index access path is not
  a candidate access path.
  
  APAR PM70345 resolves a similar problem but only addresses the
  case where the timestamp column is compared to a host variable
  value.  This APAR completes the fix by also addressing
  comparison with literal values.
  
  SQLPERFORMANCE SQLACCESSPATH SQLTIMESTAMP
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 and DB2 11 for z/OS users of an   *
  *                 SQL statement that contains a predicate      *
  *                 where a timestamp column is compared to a    *
  *                 string constant or host variable.            *
  ****************************************************************
  * PROBLEM DESCRIPTION: (1) Performance regression could happen *
  *                      for an SQL statement that contains a    *
  *                      predicate where a timestamp column is   *
  *                      compared to a timestamp string          *
  *                      constant;                               *
  *                      (2) A predicate where a timestamp       *
  *                      column is compared to a host variable   *
  *                      may not be pushed down into a view or   *
  *                      table expression whose materialization  *
  *                      is deferred;                            *
  *                      (3) Page range screening may not be     *
  *                      applied for an equal predicate where a  *
  *                      timestamp column is compared to a host  *
  *                      variable.                               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An example is shown below to illustrate problem (1):
  
  CREATE TABLE T1(C1 TIMESTAMP, C2 CHAR(2));
  CREATE TABLE T2(C1 TIMESTAMP, C2 VARCHAR(4));
  CREATE TABLE T3(C1 TIMESTAMP, C2 VARCHAR(4));
  
  SELECT DISTINCT T1.C1, TY.C1
  FROM T1 INNER JOIN (SELECT DISTINCT TX.C1, TX.C2
                      FROM (SELECT C1, C2 FROM T2
                            UNION ALL
                            SELECT C1, C2 FROM T3
                            ) TX(C1, C2)
                     ) TY(C1, C2)
       ON T1.C1 = TY.C1
  WHERE TY.C1 = '1997-01-03-18.25.10.005000';
  
  Predicate TY.C1 = '1997-01-03-18.25.10.005000' may not be
  pushed down into the table expression for the above query.
  However, for DB2 9, the choice is more likely.
  
  Another example is shown below to illustrate problem (1):
  
  CREATE TABLE T1 (C1 CHAR(16), C2 TIMESTAMP);
  
  CREATE INDEX IX1 ON T1 (C1);
  CREATE INDEX IX2 ON T1 (C2);
  
  SELECT C1
  FROM T1
  WHERE (? > 0 AND C1 = 'X')
        OR C2 = '2012-09-06-00.00.00.000000';
  
  Multiple-index access may not be used for the above SQL
  statement. However, for DB2 9, the choice is more likely.
  
  An example is shown below to illustrate problem (2):
  
  CREATE TABLE T1(C1 TIMESTAMP, C2 CHAR(2));
  CREATE TABLE T2(C1 TIMESTAMP, C2 VARCHAR(4));
  CREATE TABLE T3(C1 TIMESTAMP, C2 VARCHAR(4));
  
  SELECT DISTINCT T1.C1, TY.C1
  FROM T1 INNER JOIN (SELECT DISTINCT TX.C1, TX.C2
                      FROM (SELECT C1, C2 FROM T2
                            UNION ALL
                            SELECT C1, C2 FROM T3
                            ) TX(C1, C2)
                     ) TY(C1, C2)
       ON T1.C1 = TY.C1
  WHERE TY.C1 = :HV;
  
  Predicate pushdown may not happen for predicate TY.C1 = :HV for
  the above query. However, for DB2 9, the choice is more likely.
  
  An example is shown below to illustrate problem (3):
  
  CREATE TABLE T1 (C1 INT NOT NULL,
                   C2 TIMESTAMP)
         PARTITION BY (C2)
         (PARTITION 1 ENDING AT ('2000-01-01-00.00.00.000000'),
          PARTITION 2 ENDING AT ('2010-01-01-00.00.00.000000'),
          PARTITION 3 ENDING AT (MAXVALUE));
  
  SELECT C1 INTO :HV1 FROM T1
   WHERE C2 = :HV2;
  
  Page range screening (PAGE_RANGE='Y') may not be chosen by DB2
  for the above static query.  However, for DB2 9, the choice is
  more likely.
  

Problem conclusion

• DB2 has been modified to correctly process the aforementioned
  SQL statements.
  
  This APAR is the enabling APAR for APAR PM94911, the pre-
  conditioning APAR.  Together, these two APARs are fixing the
  problem of performance regression for static SQL queries on
  tables that use a range partitioning scheme and have an equal
  predicate on a timestamp column and a string host variable.
  
  In DB2 10, the change can be enabled by setting an online-
  changeable subsystem parameter in DSN6SPRM called PGRNGSCR to
  YES. The default setting of PGRNGSCR is NO.
  
  In DB2 11, the change is enabled by default.
  
  Additional keywords: SQLTIMESTAMP SQLACCESSPATH SQLPERFORMANCE
                       MIDX MULTIINDEX SQLWHERE SQLBETWEEN
                       SQLEQUAL SQLVIEW SQLTABLEEXPR SQLHOSTVAR
                       SQLPREDPUSHDOWN PAGERANGE
  

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:

  UI13818 UI13819

Modules/Macros

• DSNXOB2  DSNXOEXF DSNXOEXJ DSNXOSCF DSNXOVP  DSNXOW2C DSNXOW2D
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI13818

     UP14/01/08 P F401

• RB10 PSY UI13819

     UP14/01/08 P F401

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.