PM92272: BETWEEN PREDICATE ON A TIMESTAMP COLUMN UNDER AN OR NOT ELIGIBLE FOR MULTI-INDEX ACCESS

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 (or use
  multi-index access with fewer matchcols) when one of the
  predicates that should be matching in the multi-index plan
  is on a timestamp column.  DB2 incorrectly marks the predicate
  on the timestamp column as stage 2.
  
  APAR PM70345 solves this problem for equal and range predicates.
  This APAR solves the problem for between predicates only.
  
  SQLPERFORMANCE SQLACCESSPATH SQLTIMESTAMP SQLBETWEEN
  

Local fix

• Rewrite the between to multiple range predicates and apply
  PM70345.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 and 11 for z/OS users of a        *
  *                 static SQL statement that has a non-boolean  *
  *                 term BETWEEN predicate with a Timestamp      *
  *                 column and host variables.                   *
  ****************************************************************
  * PROBLEM DESCRIPTION: Performance regression could happen for *
  *                      a static SQL statement that has a       *
  *                      non-boolean term BETWEEN predicate with *
  *                      a Timestamp column and host variables.  *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Performance regression could happen for a static SQL statement
  that has a non-boolean term BETWEEN predicate with a Timestamp
  column and host variables.
  
  An examples is shown below:
  
  CREATE TABLE T1 (C1 CHAR(16), C2 TIMESTAMP);
  
  CREATE INDEX IX1 ON T1 (C1);
  CREATE INDEX IX2 ON T1 (C2);
  
  SELECT C1
  INTO :HV1
  FROM T1
  WHERE (:HV2 > 0 AND C1 = 'X')
        OR C2 BETWEEN :HV3 AND :HV4;
  
  Multiple-index access can not be well used for the above static
  SQL statement, while it could on DB2 9 for z/OS.
  
  DB2 did not correctly process the aforementioned SQL statements,
  which caused the performance regression.
  

Problem conclusion

• DB2 has been modified to correctly process the aforementioned
  SQL statement, so there will be no the performance regression as
  multiple-index access can be well used.
  
  Additional keywords: SQLTIMESTAMP SQLHOSTVAR SQLACCESSPATH,
                       SQLPERFORMANCE MIDX MULTIINDEX SQLBETWEEN
  

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:

  UK97360 UK97470

Modules/Macros

• DSNXOW2C DSNXOW2D
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK97360

     UP13/10/01 P F309

• RB10 PSY UK97470

     UP13/10/01 P F309

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.