Author: Jane Man

IBM Senior Software Engineer

janeman@us.ibm.com

What are XML related zParms?

I have been asked this question so many times that I decide to write a short article on this. Below are a list of some commonly used XML zParms.

XML_RANDOMIZE_DOCID

Acceptable values           YES, NO

Default                                 NO

As in the following figure, DB2 for z/OS stores the XML data in an auxiliary table in a separate table space (i.e. not inline in the base table). In the base table, there is a BIGINT column called DB2_GENERATED_DOCID_FOR_XML (in short, we call it DOCID in this article) which holds a unique identifier for XML columns in a row. A DOCID index is created in DOCID column and a NODEID index is created on the XML table in the auxiliary table space. Using the DOCID value with NodeID index, we can identify the XML data for a particular base row.

Figure 1. XML Storage Model in multi-versioning format

Initially, DOCID values are generated in sequential order via an implicitly created sequence object. Since XML DOCID and NODEID indexes are non-partitioned indexes (NPI), concurrent inserts create hotspots in these NPIs. As the number of threads increased, time spent waiting for page latch increased.

To solve this problem, DB2 for z/OS introduced a zParm called XML_RANDOMIZE_DOCID in PM31486 and PM31487 to allow randomization of the DOCIDs, which would eliminate the hotspots in both indexes. The default value is NO. The effect of setting XML_RANDOMIZE_DOCID to YES would be that any table that is created with an XML column or a table that is ALTERed to ADD its first XML column, the randomization behavior applies. Existing tables with XML columns are not affected.

Please note once a table starts to randomly generate DOCID values for XML columns, it cannot be changed to generate the value sequentially. Changing this parameter back to NO does not have any effect on tables that have had columns added with a randomized DOCID.

XMLVALA and XMLVALS

XMLVALA and XMLVALS are introduced in DB2 9 for z/OS to limit the storage DB2 used for XML processing. XMLVALA is storage limit per agent/user (suffix with ‘A’) while XMLVALS is used to limit the storage per whole subsystem (suffix with ‘S’).

XMLVALA

• Acceptable values 1 to 2,097,152 KB
• Default 204,800 KB
• Specifies in KB

XMLVALS

• Acceptable values 1 to 51200 MB
• Default 10240 MB
• Specifies in MB

In general, INSERT does not require too much XML storage. However, XML storage is required when we need to bring part (or whole) of the document from disk to memory for processing. Some functions, like XMLAGG function, require to keep the data of whole column, therefore, running this type of functions generally require a bigger value of XMLVALA and XMLVALS.

Please note these 2 zParms are deprecated in DB2 11 for z/OS. See following url for more details:

http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.inst/src/tpc/db2z_functionsdeprecatedfromv10.dita?lang=en

XML_RESTRICT_EMPTY_TAG

Acceptable value             YES, NO

Default                                 NO

According to W3C, both of the following are accepted empty XML element format.

The first one is an empty element tag.

The XML_RESTRICT_EMPTY_TAG zParm is used to tell DB2 how to serialize empty XML element.

The default value of XML_RESTRICT_EMPTY_TAG is NO, that is, DB2 serializes an empty XML element in either one of above format.

When XML_RESTRICT_EMPTY_TAG is set to YES, DB2 for z/OS serializes an empty XML element as a start-element tag followed by an end-element tag (i.e. the second format above).

TBSBPXML

Acceptable value             Any 16 KB buffer pool name

Default                                 BP16K0

By default, DB2 for z/OS will use BP16K0 for the 16 KB bufferpool for implicitly created XML table spaces. If you know BP16K0 is shared among other objects, you can use TBSBXML to specify the default buffer pool for XML table spaces.

Wrap up

Some customers mention DB2 for z/OS is too complicated and they don’t like zParms.  Fortunately, we don’t have a long list of XML zParms. Hopefully, by reading article, you learn about zParms you need to configure for your XML workloads.