Mit den beiden APAR’s PM31486, PM31487 hat IBM eine Abhilfe für die Vermeidung von Hot Spots beim XML INSERT’s implementiert. Aber Achtung: Der ZPARM Default steht auf NO – auf die Performance-steigernde Optimierung wird also erstmal verzichtet!

• Problem: Since DB2 10 generates DOCID values in sequential order via an implicitly created sequence object and the XML DOCID and NODEID indexes are non-partitioned indexes, concurrent inserts create hotspots in these NPIs. As the number of threads increased, time spent waiting for page latch increased.
• Solution:
• allow randomization of the DOCIDs, which would eliminate the hotspots in both indexes.
• To enable it, set zparm XML_RANDOMIZE_DOCID to YES.
• Only applies to new XML columns.
• Field QWP1XRDI is added in IFCID 106 to trace the XML_RANDOMIZE_DOCID internal setting.
• Retrofit to DB2 10 via PM31486, PM31487

Quellen:

• DB2 10 for z/OS Knowledgebase
• DB2 11 for z/OS Knowledgebase
• IOD 2013 Session „Exciting pureXML Enhancements in IBM DB2 11 for z/OS“ (PDF)

RANDOMIZE XML DOCID field (XML_RANDOMIZE_DOCID subsystem parameter)

Specifies whether DB2® is to sequentially or randomly generate the DOCID values for XML columns.

This parameter does not affect existing tables that have XML columns. Those tables continue to generate DOCIDs in the order that was specified by XML_RANDOMIZE_DOCID when the table was created or in sequential order if the parameter was not specified.
Acceptable values: NO, YES
Default: NO
Update: option 24 on panel DSNTIPB
DSNZPxxx: DSN6SYSP XML_RANDOM
IZE_DOCID

NO DB2 generates the DOCID values for XML columns sequentially.
YES DB2 generates the DOCID values for XML columns randomly. Setting this parameter to YES can be beneficial for performance when concurrent inserts are expected.

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.
Parent topic: Performance and optimization panel: DSNTIP8
Previous topic: PACKAGE RELEASE COMMIT field (PKGREL_COMMIT subsystem parameter)
Next topic: DISABLE EDM RTS field (DISABLE_EDMRTS subsystem parameter)