In Reply to Horacio Villa:
this table is in DSNTESC as one of the Explain
But is not here:
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
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
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).
DB2 Application Performance Specialist
CPT Global Ltd