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

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 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 number

    PM73542

  • 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-09-24

  • Closed date

    2013-12-23

  • Last modified date

    2014-02-05

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



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM73542

Modified date:

2014-02-05

Translate my page

Machine Translation

Content navigation