Topic
1 reply Latest Post - ‏2013-11-28T08:54:54Z by MatthiasNicola
AlanDovey
AlanDovey
4 Posts
ACCEPTED ANSWER

Pinned topic SYSXMLSTRINGS

‏2013-11-27T17:05:59Z |

What is the catalog table SYSXMLSTRINGS used for and is it possible that inserts to an XML column could lock this table so that other transactions are blocked?

  • MatthiasNicola
    MatthiasNicola
    309 Posts
    ACCEPTED ANSWER

    Re: SYSXMLSTRINGS

    ‏2013-11-28T08:54:54Z  in response to AlanDovey

    Hi Alan,

    this catalog table stores a mapping of XML tag names (i.e. element names, attribute names, namespace URIs) to integer numbers. When DB2 stores its internal tree representation of an XML document, each tag is stored as a number (StringID) instead of a string, not only to save space but also to make XML query evaluation more efficient.

    When an application retrieves XML from a table, DB2 uses the mapping information again to convert the StringIDs back to the original tag names. As a result, this mapping is completely transparent to applications.

    Each distinct tag name in the database occurs only once in this mapping table, even if this tag occurs in many documents and/or in many XML columns in the database. Hence, the size of this mapping is not proportional to the number of documents in the database, but to the number of unique element and attribute names in your XML documents, which is typically a small number (several hundred to several thousand).

    Inserts into this table happen only if a newly inserted XML document contains a tag name that the database has never seen before in any previous XML document. This tends to be very rare, unless your database is still empty.

    DB2 has a number of performance optimizations specifically for this mapping mechanism. We have never seen locking issues or other bottlenecks associated with this table, even when driving thousands of XML inserts per second with hundreds of concurrent connections:

    http://www.ibm.com/developerworks/data/library/dmmag/DBMag_2009_Issue1/DBMag_Issue109_TamingTerabyte/#N10185

    http://tpox.sourceforge.net/tpoxresults.htm

    Also see:

    http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0054590.html

     

    Thanks,

    Matthias

    Updated on 2013-11-28T08:55:45Z at 2013-11-28T08:55:45Z by MatthiasNicola