IBM Support

PM70345: MULTI-INDEX ACCESS NOT USED WITH A TIMESTAMP COLUMN

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 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 which means the desired
    multi-index access path is not a candidate access path.
    
    SQLPERFORMANCE SQLACCESSPATH SQLTIMESTAMP
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 10 for z/OS users of a static SQL    *
    *                 statement that has a non-boolean term        *
    *                 predicate with Timestamp column.             *
    ****************************************************************
    * PROBLEM DESCRIPTION: Performance regression could happen for *
    *                      a static SQL statement that has a       *
    *                      non-boolean term predicate with         *
    *                      Timestamp column.                       *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    Performance regression could happen for a static SQL statement
    that has a non-boolean term predicate with Timestamp column.
    
    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 = :HV3;
    
    Multiple-index access can not be 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 used.
    
    Additional keywords: SQLTIMESTAMP SQLHOSTVAR SQLACCESSPATH
                         SQLPERFORMANCE MIDX MULTIINDEX
    

Temporary fix

Comments

APAR Information

  • APAR number

    PM70345

  • 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

    2012-08-06

  • Closed date

    2012-09-28

  • Last modified date

    2012-11-01

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

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

    UK82227

Modules/Macros

  • DSNXOW2D
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK82227

       UP12/10/16 P F210

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.

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
01 November 2012