Wouldn't it be great if you were able to see the change in access path for a SQL statement when adding a new index. This is of course very easy to acheive just by creating the index. Maybe you do not have sufficient authorisation to create the index and another problem is that a new index immediately may impact the access path for existing SQL statements. In DB2 version 9 IBM invented virtual indexes and in this tip I will tell you about how you can invent new indexes, destroy existing ones and monitor how it impacts the access path for your SQL statement without affecting anyone but you.

First of all you need a table called DSN_VIRTUAL_INDEXES. This table must be created using the same creator as the PLAN_TABLE where the access path is stored. And please note that virtual indexes only has effect for statements explained using EXPLAIN PLAN. In other words you cannot see any impact of a virtual index for a BIND PACKAGE with EXPLAIN(YES). And of course virtual indexes are not used when executing your SQL statement.

Most likely there is already created a table called DSN_VIRTUAL_INDEXES on your DB2 subsystem with a creator of DB2OSC. In this case you can execute a CREATE DSN_VIRTUAL_INDEXES LIKE DB2OSC.DSN_VIRTUAL_INDEXES to create the table. If this does not work you should take a look in the SDSNSAMP dataset in member DSNTIJOS. If you do not know how to access the SDSNSAMP dataset then contact your DBA or DB2 systems programmer.

When the table is created with the same creator as your PLAN_TABLE you are ready! Run an EXPLAIN of the following SQL statement using your favourite tool for this purpose (I hope you have SELECT authorisation on SYSIBM.SYSTABLES):

SELECT *
FROM SYSIBM.SYSTABLES
WHERE OWNER = 'SYSIBM'

Then execute this insert (I hope you have SELECT authorisation on SYSIBM.SYSINDEXES):

INSERT INTO DSN_VIRTUAL_INDEXES
( TBCREATOR, TBNAME, IXCREATOR, IXNAME, ENABLE, MODE
, UNIQUERULE, COLCOUNT, CLUSTERING, NLEAF, NLEVELS, INDEXTYPE
, PGSIZE , FIRSTKEYCARDF , FULLKEYCARDF , CLUSTERRATIOF, PADDED
, COLNO1, ORDERING1, COLNO2, ORDERING2
)
SELECT TBCREATOR, TBNAME, CREATOR, 'MYVIRTUALINDEX', 'Y', 'C'
     , UNIQUERULE, COLCOUNT, 'Y', NLEAF, NLEVELS, INDEXTYPE
     , PGSIZE , FIRSTKEYCARDF , FULLKEYCARDF , CLUSTERRATIOF, PADDED
     , 53, 'A', 28, 'A'
FROM SYSIBM.SYSINDEXES
WHERE CREATOR = 'SYSIBM'
  AND NAME = 'DSNDTX03'

And rerun your EXPLAIN to see if DB2 will use the virtual index you just created. Hopefully this should be the case. Please notice that the index is built using column numbers (53 and 28) instead of column names. The index I am copying has two columns, so I did not need to hardcode the value of COLCOUNT which must match the number of columns in the index you specify. I have set CLUSTERING to Y in this example to ensure that the optimizer selects the virtual index.

And now let us have some fun and try an EXPLAIN of this SQL statement:

SELECT NAME
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'SYSIBM'
  AND NAME LIKE 'SYS%'

Then try this insert:

INSERT INTO DSN_VIRTUAL_INDEXES
( TBCREATOR, TBNAME, IXCREATOR, IXNAME, ENABLE, MODE
, UNIQUERULE, COLCOUNT, CLUSTERING, NLEAF, NLEVELS, INDEXTYPE
, PGSIZE , FIRSTKEYCARDF , FULLKEYCARDF , CLUSTERRATIOF, PADDED
)
SELECT TBCREATOR, TBNAME, CREATOR, NAME, 'Y', 'D'
     , UNIQUERULE, COLCOUNT, CLUSTERING, NLEAF, NLEVELS, INDEXTYPE
     , PGSIZE , FIRSTKEYCARDF , FULLKEYCARDF , CLUSTERRATIOF, PADDED
FROM SYSIBM.SYSINDEXES
WHERE CREATOR = 'SYSIBM'
  AND NAME = 'DSNDTX01'

And rerun an explain of the SELECT statement. The optimizer should either have selected another index or a tablespace scan, because the last insert simulated a drop of an index.

It is a real good idea to copy index data for an existing index on the table you want to change the access path to. If no index exist on the table then copy one alike from another table. I use INSERT ... SELECT ... FROM ... to do the copy, primarily to get some reasonable useful values in the columns containing RUNSTATS information.

The columns in DSN_VIRTUAL_INDEXES not found in SYSIBM.SYSINDEXES controls how EXPLAIN PLAN should use the virtual index and which columns the index consists of (not needed if you simulate a drop of an index). Set column ENABLE to Y when EXPLAIN should consider using the virtual index and N when it should not. With an UPDATE DSN_VIRTUAL_INDEXES SET ENABLE = 'N' you disable all virtual indexes and EXPLAIN PLAN will work just like if DSN_VIRTUAL_INDEXES did not exist. The column MODE is set to C for simulation of a new index and to D to simulate that the index does not exist any more.

The columns COLNO1 to COLNOn are filled with the column number of the column(s) from the table which the index should consist of. Highest possible value for n is 64. ORDERING1 to ORDERINGn is set to A for ascending or D for descending. If your index should consist of column 7 and 11 from the table you set COLNO1 to 7 and COLNO2 to 11 and leave the remaining COLNO columns as is. You do the same for the ORDERING columns. And most important you set the column COLCOUNT to 2, because there are two columns in your index.

And here at the end of the tip you should know that the table DSN_VIRTUAL_INDEXES are part of an IBM product, so handle with care. You can read more about virtual indexes on this blog written by one of the brains behind DB2.