Explain Tables - DSN_VIRTUAL_INDEXES DB2 11 & 12

Horacio Villa

Explain Tables - DSN_VIRTUAL_INDEXES DB2 11 & 12
Hi,

this table is in DSNTESC as one of the Explain Tables.
But is not here:
https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/usrtab/src/tpc/db2z_explaintables.html
or here:
https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/usrtab/src/tpc/db2z_explaintables.html

Why?

Horacio Villa











Michael Hannan

RE: Explain Tables - DSN_VIRTUAL_INDEXES DB2 11 & 12
(in response to Horacio Villa)

In Reply to Horacio Villa:

this table is in DSNTESC as one of the Explain Tables.
But is not here:
https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/usrtab/src/tpc/db2z_explaintables.html
or here:
https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/usrtab/src/tpc/db2z_explaintables.html

Why?

Simple answer, these tables are not populated by the Explain function, therefore they do have to be grouped with Explain Tables. Why IBM does something with their documentation is not the most important thing. We don't need to know that. The place Explain tables are documented has changed at least once in the past I think. These Virtual tables are not mandatory to run a dynamic explain. Can be personal tables or group shared tables given right auth setup.

DSN_VIRTUAL_INDEXES, DSN_VIRTUAL_KEYTARGETS

The manual "Managing Performance" for Db2 zOS  is an apt place to document them and in fact they do appear there along with the Explain tables.

Populating the two tables when wanting to test an Index On Expression proposed, is quite tricky, but doable if very careful, and on understands the doco. IBM intended us to use a product to populate them, e.g. Data Studio (I read that but yet too try it).  I have code to assist me to populate them. Doing it manually is tough and time consuming. Something to automate and some other several vendors have done that.

Unfortunately there is no Virtual table for the SYSCOLDIST Stats that might apply to your Virtual Indexes. You have to populate the normal Catalog SYSCOLDIST table, temporarily or permanently, if you have the authority to do so, if any Keycards or Freq Value Stats are needed.

I have not tested Virtual Indexes for XML as yet.

An alternative to Virtual Indexes, if want to test something beyond Virtual capabilities like a new partitioning scheme, is to have a dummy database and clone the original object definitions into there, with whatever changes you want to test, and populate suitable stats (if have authority). BMC Apptune is a product with a "What If" Clone  function but also with some limits (I don't work for BMC and don't know the limitations fully).

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Jun 08, 2019 - 08:40 AM (Europe/Berlin)