PI69414: POOR SQL PERFORMANCE FOR MERGE STATEMENT

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Poor SQL performance for MERGE statement
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All DB2 12 for z/OS users of the MERGE statement using data  *
  * from the result of a table reference                         *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * An index on expression may not be used for a MERGE statement *
  * that uses data from the result of a table reference.         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An index on expression may not be used for a MERGE statement
  that uses data from the result of a table reference.
  
  An example is shown below to illustrate the problem:
  
  CREATE TABLE MYTABLE (A TIMESTAMP);
  
  CREATE INDEX MYINDEX ON MYTABLE (DATE(A));
  
  SELECT * FROM FINAL TABLE
    (MERGE INTO MYTABLE T1
     USING (SELECT * FROM MYTABLE
             WHERE DATE(A) > '2016-01-01') AS T2
        ON DATE(T1.A) = DATE(T2.A)
      WHEN MATCHED
      THEN UPDATE SET A = CURRENT TIMESTAMP
      ELSE IGNORE
     );
  
  DB2 may not choose the index on expression (MYINDEX) to access
  table MYTABLE.
  

Problem conclusion

• DB2 has been modified to correctly process the aforementioned
  MERGE statement so that index on expression may be used to
  render better performance.
  
  Additional Keywords: SQLPERFORMANCE SQLACCESSPATH SQLINDEXONEXP
  SQLMERGE
  

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:

  UI42698

Modules/Macros

• DSNXOWPT
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI42698

     UP16/12/03 P F612

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.