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

A fix is available

Subscribe

You can track all active APARs for this component.

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 number

    PM92272

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-07-02

  • Closed date

    2013-09-13

  • Last modified date

    2013-10-04

  • 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.



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM92272

Modified date:

2013-10-04

Translate my page

Machine Translation

Content navigation