In one of my previous postings, I mentioned that there is no special setup needed to get started with XML in DB2 9 for z/OS. However, if you or your folks are getting more serious, some installation and serviceability parameters (zparms) related to XML may need to be adjusted. Here I will talk about four zparms related to the virtual storage limit: XMLVALA, XMLVALS, LOBVALA, and LOBVALS. I will also explain why you could insert and select XML data larger than the virtual storage limit.
The XMLVALA and XMLVALS are the virtual storage limit for XML processing for each thread and entire sub-system, respectively. The main goal is to prevent run-away processes from bringing down your DB2 system. The default for XMLVALA and XMLVALS is 200MB and 10GB, respectively. Here is the initial guideline for setting to different values.
For XMLVALA, use at least 2x the maximum XML document size if there is no XPath (such as in V8), and use at least 4x the maximum XML document size (either generated or stored) if there are any XPath queries, up to 2GB. The maximum limit is 2GB.
For XMLVALS, use the maximum possible that will not cause the DB2 system down when this amount is really used up by XML processing, or the maximum number of threads x 2GB (or XMLVALA), whichever is smaller, with the maximum limit 50GB.
LOBVALA and LOBVALS are relevant for bind-in and bind-out of XML data when XML data needs to be materialized as a Large Object (LOB) in memory. In general, set LOBVALA to the maximum XML document size inserted or selected, and set LOBVALS to the maximum number of threads x LOBVALA.
For optimization, DB2 uses streaming techniques whenever possible to reduce the storage consumption so that only a small amount of storage is used to handle very large documents. For background, the following table lists all the cases for materialization of XML data as in-memory LOBs for INSERT, UPDATE, and SELECT. The LOAD utility is the same as local INSERT, while UNLOAD is the same as local SELECT without FETCH CONTINUE, i.e. no materialization. Thank Li-mey Lee of our team for providing the details.
|Local, CLI |
and JDBC T2
|Always YES||Always YES|
From the above table, you can see that DB2 does not materialize XML data for a local application during simple INSERT or SELECT without using FETCH CONTINUE, so you can handle XML documents exceeding the zparm (XMLVALA or LOBVALA or both) limit. For a JDBC T4 application, you may be able to insert a large document exceeding the zparm limit, but you won't be able to select it back without increasing the limit.
Share with me your experience on setting the "correct" values for these zparms.