PM52237: SQL CODE -904 REASON 00D50001 OR EXCESSIVE STORAGE USAGE FOR QUERY WITH XMLTABLE CLAUSE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQL code -904 reason 00D70001 or excessive storage usage
  for query with xmltable clause.                        (d154060)
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9 and DB2 10 for z/OS XML users of   *
  *                 the XMLTABLE function.                       *
  ****************************************************************
  * PROBLEM DESCRIPTION: An SQLCODE904 (or -904) with reason     *
  *                      code rc00D50001 or rc00D50002 can be    *
  *                      issued from DSNXRXML when using an      *
  *                      XMLTABLE function, such as XMLPARSE, as *
  *                      the argument of the XMLTABLE.           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An SQLCODE904 (or -904) with reason code rc00D50001 or
  rc00D50002 can be issued from DSNXRXML when using the XMLTABLE
  function, such as XMLPARSE, as the argument of the XMLTABLE
  function.
  
  The following example can illustrate the problem.
  
    SELECT X.*
      FROM T1,
           XMLTABLE('/a/b'
                  PASSING XMLPARSE(C1)
                  COLUMNS C  INTEGER PATH 'c') AS X ;
  
  For each row returned from XMLTABLE, the XMLPARSE function
  will be called to parse the XML document (C1) which can
  utilize a lot of storage. However, for each XML document,
  we only need to parse once. The argument of the XMLTABLE
  function should be called only once for each row in T1.
  But currently it is called for each row returned by XMLTABLE.
  
  
  Please note.  The amount of storage used is dependent on the
  size of the XML document and how many rows are returned from the
  XMLTABLE function.
  

Problem conclusion

• The code in DB2 which processes the XMLTABLE function is
  modified to parse the XML document only once.  This can
  significantly lessen the amount of storage the XMLTABLE function
  utilizes.  This will reduce the chance of receiving a 'resource
  unavailable' condition, i.e. an SQLCODE -904, with reason code
  rc00D50001 or rc00D50002.
  
  Additional Keywords: SQLXML SQLXMLTABLE
  

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:

  UK77920 UK77921

Modules/Macros

• DSNXEMG1 DSNXGRTS DSNXGT2J DSNXREOJ DSNXREO9
  DSNXRFN  DSNXRFN9 DSNXROJ1 DSNXROJ9 DSNXRSFN DSNXRSGB DSNXRSG9
  DSNXRSN9 DSNXRT1J DSNXRT19 DSNXSBUC DSNXSIND DSNXSING
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK77920

     UP12/05/01 P F204

• R910 PSY UK77921

     UP12/05/01 P F204

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.