Virtual index for expression based index

mvr rajesh

Virtual index for expression based index

Hi Team

Can we create virtual index on expression in db2 12 for zos. I want to create virtual index on substr(c1,1,2). 

Regards 

Venkata rama rajesh 

Roy Boxwell

Virtual index for expression based index
(in response to mvr rajesh)
Just use DSN_VIRTUAL_KEYTARGETS like you would do in Db2 11 they were new in Db2 11 not in 12.



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: mvr rajesh <[login to unmask email]>
Sent: Wednesday, July 1, 2020 6:23 AM
To: [login to unmask email]
Subject: [DB2-L] - Virtual index for expression based index



Hi Team

Can we create virtual index on expression in db2 12 for zos. I want to create virtual index on substr(c1,1,2).

Regards

Venkata rama rajesh



-----End Original Message-----

Attachments

  • smime.p7s (5.1k)

Michael Hannan

RE: Virtual index for expression based index
(in response to mvr rajesh)

Rahesh,

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.

See   https://davebeulke.com/another-5-db2-sql-performance-tips/

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.

Michael Hannan,
DB2 Application Performance Specialist

Edited By:
Michael Hannan[Organization Members] @ Jul 05, 2020 - 06:38 PM (Australia/Melbourne)