Collection of statistics on XML objects

You can use separate RUNSTATS control statements to collect statistics on XML table spaces, or on their associated base table spaces.

You can specify that RUNSTATS collects space statistics on an XML table space. You can use those statistics to determine when the XML table space must be reorganized. Statistics that are collected on the XML table space also affect access path selection.

Start of changeThe following tables shows statistics values that are collected for the implicitly created XML DOCID columns. The End of change

Start of change
Table 1. Statistics that are collected on the XML DOCID column.
Catalog Table Name Column Name Meaning
SYSCOLSTATS COLCARD The number of distinct values in the XML column. This value is set to -2 for other types of XML objects.
SYSCOLSTATS HIGHKEY The highest value of the column. This value is set to blanks for other types of XML objects.
SYSCOLSTATS HIGH2KEY The second highest value of the column. This value is set to blanks for other types of XML objects.
SYSCOLSTATS LOW2KEY The second lowest value of the column. This value is set to blanks for other types of XML objects.
SYSCOLSTATS LOWKEY The lowest value of the column. This value is set to blanks for other types of XML objects.
SYSCOLUMNS COLCARDF Estimated number of distinct values in the column. This value is set to -2 for other types of XML objects.
SYSCOLUMNS HIGH2KEY Second highest value in the column. This value is set to blanks for other types of XML objects.
SYSCOLUMNS LOW2KEY Second lowest value in the column. This value is set to blanks for other types of XML objects.
End of change

Start of changeThe following table shows that statistics values that are collected when RUNSTATS is run for XML indexes:End of change

Start of change
Table 2. Statistics that RUNSTATS collects for XML indexes
Catalog Table Name Column Name Meaning
SYSINDEXES FIRSTKEYCARDF The number of unique key values in the index.
SYSINDEXES FULLKEYCARDF The total number of key entries in the index.
SYSINDEXES FIRSTKEYCARDF The number of documents in the XML column. This value is collected only for implicitly created node ID indexes.
SYSINDEXES CLUSTERRATIOF This value is set to -2.
SYSKEYTARGETS HIGH2KEY For KEYSEQ=1, the second-highest key value.
SYSKEYTARGETS LOW2KEY For KEYSEQ=1, the second-lowest key value.
SYSKEYTARGETS CARDF The number of unique DOCID values in the index.
SYSKEYTARGETS KEYCARDF This value is set to -2.
End of change
RUNSTATS ignores the following keywords for certain types of objects in XML table spaces:
  • COLGROUP
  • KEYCARD (for indexes)
  • FREQVAL MOST|LEAST|BOTH
  • HISTOGRAM
DB2 issues message DSNU1345I when these keywords are ignored for an XML object.

XML indexes are related to XML tables, and not to the associated base tables. If you specify a base table space and an XML index in the same RUNSTATS control statement, Db2® generates an error. When you run RUNSTATS against a base table, RUNSTATS collects statistics only for indexes on the base table, including the document ID index.