PM27153: HUGE STORAGE USED WHEN XMLSERIALIZE FUNCTION IS INPUT ARGUMENT OF LENGTH FUNCTION, MAY ISSUE SQLCODE904 RESOURCE 0000090A TOO

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• DBM1 may use excessive amount of Variable storage above the
  BAR with XMLSERIALIZE as input argument of LENGTH function.
  
  ex.
  SELECT
  MAX(LENGTH(XMLSERIALIZE( XMLELEMENT ( NAME "BLOBLEN",
                TB.XMLDATA) AS BLOB(10M))))
  FROM TB
  
  If zparm use small XMLVALA & XMLVALS, the SQL may get
  SQLCODE904, RESOURCE 0000090A
  
  If we enlarge XMLVALA & XMLVALS to big value, the SQL
  may get result, but consumed excessive storage.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9 for z/OS and DB2 10 for z/OS       *
  *                 users of LENGTH and XMLSERIALIZE functions.  *
  ****************************************************************
  * PROBLEM DESCRIPTION: SQLCODE904 with RESOURCE 0000090A might *
  *                      occur when a large amount of XML        *
  *                      documents is serialized to calculate    *
  *                      the length.                             *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  SQLCODE904 with RESOURCE 0000090A might occur when a large
  amount of XML documents is serialized to calculate the length.
  
  For example:
  The following query just needs to get the MAX of the length of
  the serialized XML documents.
  
  SELECT MAX(LENGTH(XMLSERIALIZE(
                      XMLELEMENT(NAME "BLOBLEN", TB.XMLDATA)
                      AS BLOB(10M))))
  FROM TB;
  
  With the current implementation, DB2 will keep the serialized
  XML documents in XML storage for further processing. After
  accumulating a large amount of serialized XML documents, the
  XML storage usage might reach the XML user/system limit set by
  XMLVALA/XMLVALS and get SQLCODE904.
  

Problem conclusion

• DB2 is modified to not keep the serialized XML documents when
  the XMLSERIALIZE function is the input argument to the LENGTH
  function. This prevents the accumulation of XML storage usage
  and prevents the SQLCODE904.
  
  Additional keywords: SQLXML
  

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:

  UK64740 UK64741

Modules/Macros

• DSNDSCFD DSNXGSFN DSNXOSCF DSNXRLOB DSNXRLO9
  DSNXRP   DSNXRT
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK64740

     UP11/02/26 P F102

• R910 PSY UK64741

     UP11/02/26 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.