PM69176: SQLCODE -904 ON XMLTABLE / XMLQUERY WITH LARGE XML DATA

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• DB2 may issue SQLCODE -904 when using XMLTABLE or XMLQUERY with
  large XML data.
  This APAR will set the resettable token for XMLTABLE and
  XMLQUERY to reuse XML virtual storage.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 for z/OS users of XML.            *
  ****************************************************************
  * PROBLEM DESCRIPTION: When performing a SELECT or an          *
  *                      INSERT via SELECT on an XML document    *
  *                      from an XML column in the XMLTABLE      *
  *                      function, DB2 may issue SQLCODE -904    *
  *                      if the use of XML virtual storage       *
  *                      reaches the limit set by the            *
  *                      subsystem parameters XMLVALA and        *
  *                      XMLVALS.                                *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  DB2 may issue SQLCODE -904 (RC00D50001 or RC00D50002,
  TYPE OF RESOURCE 0000090A),
  if the use of XML virtual storage reaches the limit set by
  the subsystem parameters XMLVALA and XMLVALS.
  This APAR optimizes the reuse of XML virtual storage for each
  row of the XMLTABLE function with an output column of XML data
  type, and it alleviates the accumulated use of XML
  virtual storage. The following examples illustrate the
  reuse of XML virtual storage for the XMLTABLE function in
  SELECT and INSERT via SELECT statement.
  
  SELECT X.C1 FROM T1, XMLTABLE('/a/b' PASSING T1.DOC
                                COLUMNS
                                  C1 XML PATH 'c') X;
  
  INSERT INTO T2 (ID, DOC) SELECT T1.ID, XMLDOCUMENT(X.DOC)
                           FROM T1, XMLTABLE('/a/b' PASSING T1.DOC
                                    COLUMNS
                                      DOC XML PATH 'c') AS X
                           WHERE T1.ID > 0;
  

Problem conclusion

• DB2 code is modified to reuse the XML virtual storage for the
  XMLTABLE function with an output column of XML data type,
  so as to reduce the use of XML virtual storage.
  
  Additional keywords: SQLXML XMLINSERT SQLCODE904 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:

  UK81899

Modules/Macros

• DSNXEMG1 DSNXGRTM DSNXOGA1 DSNXOOS2 DSNXOPB
  DSNXRBND DSNXRIWS DSNXRXML
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK81899

     UP12/10/04 P F210

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.