This month’s newsletter addresses a fantastic feature, which I bet you’ll – if you don’t already – exploit!
Just imagine you could give a new index a “try” before you build it, or you could even pre-check if your application’s performance will degrade if an index that you suspect isn’t used anymore (refer to the unused IX, but used stats dilemma!). This is exactly what a virtual index can do for you!
IBM brought in the DSN_VIRTUAL_INDEXES table in DB2 9 and fitted it into DB2 V8 CM, but with the shorter name, DSN_VIRTUAL_INDEX. Since then it has been studiously ignored by nearly everybody!
This table is actually incredibly useful in the DBAs job it is just that IBM made it very difficult to fill, change and test the effectiveness of what you can actually do with it…
So how do you get one?
Check out the DSN910.SDSNSAMP(DSNTESC) member or the DSNTIJOS
First check out the DSN910.SDSNSAMP(DSNTESC) member or the DSNTIJOS where IBM supply the sample DDL for all the OSC tables.
The DSN_VIRTUAL_INDEXES looks like.
"TBCREATOR" VARCHAR(128) NOT NULL
"TBNAME" VARCHAR(128) NOT NULL
"IXCREATOR" VARCHAR(128) NOT NULL
"IXNAME" VARCHAR(128) NOT NULL
"ENABLE" CHAR(1) NOT NULL
CHECK("ENABLE" IN('Y','N'))
"MODE" CHAR(1) NOT NULL
CHECK("MODE" IN('C','D'))
"UNIQUERULE" CHAR(1) NOT NULL
CHECK("UNIQUERULE" IN('D','U'))
"COLCOUNT" SMALLINT NOT NULL
CHECK("COLCOUNT" > 0)
"CLUSTERING" CHAR(1) NOT NULL
CHECK("CLUSTERING" IN('Y','N'))
"NLEAF" INTEGER NOT NULL
CHECK("NLEAF" >= -1)
"NLEVELS" SMALLINT NOT NULL
CHECK("NLEVELS" >= -1)
"INDEXTYPE" CHAR(1) NOT NULL WITH DEFAULT
CHECK("INDEXTYPE" IN('D','2'))
"PGSIZE" SMALLINT NOT NULL
CHECK("PGSIZE" IN(4, 8, 16, 32))
"FIRSTKEYCARDF" FLOAT NOT NULL WITH DEFAULT -1
CHECK("FIRSTKEYCARDF" = -1
OR "FIRSTKEYCARDF" >= 0)
"FULLKEYCARDF" FLOAT NOT NULL WITH DEFAULT -1
CHECK("FULLKEYCARDF" = -1
OR "FULLKEYCARDF" >= 0)
"CLUSTERRATIOF" FLOAT NOT NULL WITH DEFAULT -1
CHECK("CLUSTERRATIOF" = -1
OR "CLUSTERRATIOF" >= 0)
"PADDED" CHAR(1) NOT NULL WITH DEFAULT
CHECK("PADDED" IN(' ','Y','N'))
"COLNO1" SMALLINT
CHECK("COLNO1" IS NULL
OR "COLNO1" > 0)
"ORDERING1" CHAR(1)
CHECK("ORDERING1" IS NULL
OR "ORDERING1" IN('A','D'))
.
.
.
"COLNO64" SMALLINT
CHECK("COLNO64" IS NULL
OR "COLNO64" > 0)
"ORDERING64" CHAR(1)
CHECK("ORDERING64" IS NULL
OR "ORDERING64" IN('A','D'))
As can be seen there are an awful lot of columns that all have to be filled in correctly to get it to work, and lots of columns have got CHECK rules.
The contents of the columns is fully described in the “Performance Monitoring and Tuning Guide” however the INSERTs, SELECTs, DELETEs and UPDATEs must be coded by yourselves…(Hint: Use the DRAW REXX to generate these statements for you – If you do not have the DRAW REXX then contact us for a copy).
Here is an IBM example INSERT:
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, COLNO3, ORDERING3)
VALUES
('SAPR3', 'AUSP', 'SAPR3', 'AUSPTEST', 'Y', 'C', 'D', 7, 'N', 130000, 5, '2', 4, 1, 3693479, 0.95, 'N', 1, 'A', 6, 'A', 3, 'A');As the Index columns are NULLable, just ignoring them all in the INSERT is “OK”. Tthe trick is getting the NLEAF, NLEVELS, FIRSTKEYCARDF, FULLKEYCARDF and CLUSTERRATIOF “correct”, so that EXPLAIN can actually use it!
Of course, if the index is inserted as ENABLE = ‘Y’ and MODE = ‘D’ then you can simply do an INSERT from SELECT and simulate DROPped indexes – which is quite wonderful!
Here is another IBM example INSERT from SELECT:
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, '2', 4, FIRSTKEYCARDF, FULLKEYCARDF, CLUSTERRATIOF, PADDED
FROM SYSIBM.SYSINDEXES
WHERE CREATOR = 'SAPR3'
AND NAME = 'AUSP~Z1';There was an APAR (PM14223), opened last year for V8 UK60838 and V9 UK60839, which was closed on 2010-09-27, and corrected various problems with regard to empty tables and check constraints.
Set of EXPLAIN tables with the correct owner and the DSN_VIRTUAL_INDEXES
To actually get this feature to work, you will also need a set of EXPLAIN tables with the correct owner and the DSN_VIRTUAL_INDEXES with the *same* owner. So if you only create the SYSIBM or the OSC creator, then it is unlikely you will get the benefit of this feature. This is the reason I often hear , It didn’t work at our shop!”
Once everything is in place and you have enabled a virtual index, any BIND, REBIND or EXPLAIN will consider this index. This is good if it’s intentional, but it’s really bad if you just have a record sitting there!
Some golden rules of this table are:
- Only leave ENABLEd indexes for the duration of your tests – Never insert Enabled indexes and then go on vacation!
- Delete or set “disabled” as soon as you can! (Remember that EXPLAIN will use the virtual indexes, but the actual access paths for BIND/REBIND will not!)
- Take care to specify the “correct” values for space and clustering values.
The “correct” values are the hard part…
For Space you can use the space calculation in the DB2 Administration Guide.
For Cardinality you can use estimates or count(*) results.
For Clusterratiof you can use 0, 0.5 , 0.85 , 0.95 and 1.
If you manage all these pieces correctly, the capability is just great. However, it all reminds me a bit on the note Terry Purcell made during a presentation at IDUG NA the 1st week of May: We just make it possible, if you want to use it nicely you may want to talk to your tools vendor. If you have some DB2 experts in your shop, you may even figure it out yourself. – I fully agree. There are tools out there that act as a complete “front end” for this feature. They warn if ENABLEd indexes are found, they allow simple change, copy, insert, update and delete and also multiple explain runs to see if any of your queries will be affected by a created, changed or dropped index – subsystem wide. Of course I am referring to Bind ImpactExpert and SQL PerformanceExpert from us. (Well I have to mention our software somewhere in the newsletter!).
In a future newsletter I will describe a methodology for redundant index removal that will use this feature – so stay tuned!
I hope that this little bit of data was of interest and/or of use, and, as always, comments or questions are more than welcome!
TTFN,
Roy Boxwell
Senior Architect
