PM25984: VARIOUS EXPLAIN FIXES FOR DSN_QUERY_TABLE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Various Explain fixes for DSN_QUERY_TABLE.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 for z/OS users of EXPLAIN table   *
  *                 DSN_QUERY_TABLE.                             *
  ****************************************************************
  * PROBLEM DESCRIPTION: This APAR fixes problems where EXPLAIN  *
  *                      output may not be displayed correctly.  *
  *                      The specific situations are:            *
  *                                                              *
  *                        1. In the NODE_DATA column of         *
  *                           DSN_QUERY_TABLE, the output may    *
  *                           have columns missing in a          *
  *                           PARTITION BY clause.               *
  *                                                              *
  *                        2. In the NODE_DATA column of         *
  *                           DSN_QUERY_TABLE, the output may    *
  *                           be incorrectly displayed when      *
  *                           the statement contains the         *
  *                           XMLMODIFY function.                *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  This APAR fixes problems where EXPLAIN output may not be
  displayed correctly. The specific situations are:
  
    1. In the NODE_DATA column of DSN_QUERY_TABLE, the output may
       have columns missing in a PARTITION BY clause.
  
    2. In the NODE_DATA column of DSN_QUERY_TABLE, the output may
       be incorrectly displayed when the statement contains the
       XMLMODIFY function.
  
  Example statements of the specific situations are shown below:
  
    1. SELECT WORKDEPT, FIRSTNME, BONUS,
              AVG(BONUS) OVER (PARTITION BY WORKDEPT, JOB)
       FROM EMP
  
    2. UPDATE T1
       SET XMLCOL = XMLMODIFY(
         'declare default element namespace
         "http://www.test.com/item";
         replace value of node /Item with "test"')
       WHERE ID = 1;
  
  The output from the above queries before applying the fix
  is shown below:
  
    1. ...
       <PARTITION-BY>
        PARTITION BY
        <COLUMN TNO="1">DSN8A10.EMP.WORKDEPT</COLUMN>
       </PARTITION-BY>
       ...
  
    2. ...
       <FUNCTION>
        INTERNAL SCLF (
         <LIT VALUE="">
          'declare default element namespace
          "http://www.test.com/item";
          replace value of node /Item with "test"'
         </LIT>
         ,
         <COLUMN TNO="1">SYSADM.T1.XMLCOL</COLUMN>
        )
       </FUNCTION>
       ...
  
  The output from the above queries after applying the fix
  is shown below:
  
    1. ...
       <PARTITION-BY>
        PARTITION BY
       <COLUMN TNO="1">DSN8A10.EMP.WORKDEPT</COLUMN>
       ,
       <COLUMN TNO="1">DSN8A10.EMP.JOB</COLUMN>
       </PARTITION-BY>
       ...
  
    2. ...
       <FUNCTION>
        XMLMODIFY (
         <LIT VALUE="">
          'declare default element namespace
          "http://www.test.com/item";
          replace value of node /Item with "test"'
         </LIT>
        )
       </FUNCTION>
       ...
  
  Additional keywords:
  SQLEXPLAIN
  

Problem conclusion

• Code has been updated to:
  
    1. Correctly display all the columns in the PARTITION BY
       clause.
  
    2. Correctly display the XMLMODIFY function.
  

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:

  UK64162

Modules/Macros

• DSNXODTX
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK64162

     UP11/02/08 P F102

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.