Can we create virtual index on expression in db2 12 for zos. I want to create virtual index on substr(c1,1,2).
Venkata rama rajesh
Sorry if name is not right.
So DSN_VIRTUAL_KEYTARGETS is available for use as Roy points out. Populating it correctly is just a bit tricky. You might not get it right the first time, but it is doable with perseverance. You have to know what the Optimizer is looking for in the columns and the manual documentation for doing this maybe not be as detailed as you would like. You still have to populate DSN_VIRTUAL_INDEXES as well. I failed on my very first attempts, but eventually worked it out, and created some doco for myself.
Data Studio should be able to help with virtual index on expression, but I have not yet tried that approach myself.
Now you want to index SUBSTR(C1, 1, 2). Hopefully your purpose is to match on a predicate like:
AND SUBSTR(C1, 1, 2) = :hostvar
or similar. Db2 12 z/OS is smart enough to match on column C1 in an index even without using the expression, by internal rewrite of the predicate, however it would be seen as a range type predicate match, and would have to be the last matched column.
Index on Expression will allow it to be not the last matched index column, or could be much more efficient if C1 was a long CHAR column. I don't like to see long CHAR columns. VARCHAR is better unless fixed length CHAR has no trailing blanks.
If you are finding it difficult to create the Virtual Index on Expression and have no software that assists, suggest you just try out the proposed index in a development/test environment. Can be easier.
Building non unique indexes SHRLEVEL CHANGE can be so quick and easy these days, if tables are not too large, and too busy. Choose a quiet time, and collect the inline Stats.
Then do a dynamic EXPLAIN of the query you want to use your new index.
Testing using Virtual Indexes is good if you are well setup to do it, or have 3rd party software, but otherwise "try it and see" can help using a real CREATE INDEX NON UNIQUE DEFER YES. You can import a full set of Production Catalog Stats into 1 or more test environments too, so that what you try in test is a reliable indicator of what the Optimizer will do in Production too. That can take a effort also unless you already have software to do it. A site can even have an access path test environment with Prod Stats but no data populated in the tables. All DEFINE NO. Ha ha.
Lots of good options.
DB2 Application Performance Specialist