PM94911: PERFORMANCE REGRESSION ON PARTITION TABLE WITH EQUAL PREDICATE ON TIMESTAMP COLUMN (PAGE_RANGE = Y) AFTER MIGRATION TO DB2 10

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Partition pruning is disabled as DB2 cannot handle page range
  pruning with CAST DOWN to precision 6 for equal predicate, if
  the column is timestamp (6).
  

Local fix

• Rewrite the "TS = :H" predicate into "TS BETWEEN :H AND :H"
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 for z/OS and DB2 11 for z/OS      *
  *                 users of queries with equal predicates that  *
  *                 include a timestamp column and a string host *
  *                 variable.                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION: Apar PM94911 is a pre-conditioning apar *
  *                      for PM73542, the enabling apar.         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  This APAR provides the necessary pre-conditioning code for
  a later apar, PM73542.
  
  Apar PM73542 will provide a solution for the following problem.
  
  A performance regression can occur for a static SQL statement
  that contains an equal predicate with a TIMESTAMP column and a
  string host variable.
  
  
  The following example provides such a case.
  
  1. Create a Table T1 that uses a range partitioning scheme
      using a timestamp column.
  
     CREATE TABLE T1(INTCOL INT,
                     TSCOL  TIMESTAMP)
         PARTITION BY (TSCOL)
         (PARTITION 1 ENDING AT ('2020-01-01-00.00.00.000000'),
          PARTITION 2 ENDING AT ('2030-01-01-00.00.00.000000'),
          PARTITION 3 ENDING AT (MAXVALUE));
  
  
  2. The static query below contains an equal predicate on column
     TSCOL and a string host variable. DB2 may not choose page
     range screening (as shown by PAGE_RANGE='N' in the
     PLAN_TABLE) when an EXPLAIN is performed for the following
     SQL statement.
  
     EXEC SQL
     SELECT INTCOL INTO :HVINT1:IND1
     FROM  T1
     WHERE TSCOL  = :HVVCHAR2:IND2;
  
  Page range screening (PAGE_RANGE='Y') may not be chosen by DB2
  for the above static query.  However, for DB2 v9, the choice is
  more likely.
  
  DB2 did not correctly process the aforementioned SQL statement.
  This can cause a performance regression.
  

Problem conclusion

• This apar adds the pre-conditioning code needed for a later
  enabling apar PM73542.
  
  Additional Keywords: SQLTIMESTAMP SQLHOSTVAR SQLACCESSPATH
                       SQLPERFORMANCE PAGERANGE
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

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

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

  UI11780 UI11781

Modules/Macros

• DSNXEBR  DSNXEMG1 DSNXGOSQ DSNXGRM1 DSNXGRSR DSNXGRTM DSNXGRTS
  DSNXGSFN DSNXRBND
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI11780

     UP13/11/13 P F311 ½

• RB10 PSY UI11781

     UP13/11/13 P F311 ½

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.