Db2 Tools for zOS

 View Only

 SQL Performance Analyzer - How can I create a virtual Expression-based index?

Soledad Martinez's profile image
Soledad Martinez IBM Champions posted 10/13/26 03:04 AM

Good Day,

I cannot find the way to do it or any reference at the manual, but would like to confirm it.

  • Is there any way to create a virtual Expression-based index/Index on expression with the SQL PA?

Thanks!

Michael Hannan's profile image
Michael Hannan

Although SQL PA supports Virtual Indexes for a "what if",  standard Virtual Indexes implementation is a bit crude (at least in DB2 for z/OS), and not covering "Indexes on Expression".  So if you can't find any doco that explicitly says SQL PA supports Index on Expression Creation, then likely it has nothing special for it.  I create Indexes on Expression all the time, just using normal DDL and my own analysis.  As a virtual index does not work you could create it on a test copy of the table with no data but all the Access Path stats info in the catalog copied across. In fact I think I recall some 3rd party product does that in its Clone What if function.  Its not that hard to have stats migrate automated by your self, and generate test clone DDLs.

So I do manage to create Index on Expression in test env, populate some stats and check that target SQLs explained can use the new index successfully. All this without using SQL PA.

Generally we want Index on expression column to show up in explain as either a matching column or at least doing strong index screening.  Otherwise it might be little value. I don't recall if I ever managed to use it purely to get index only or to be usable for GROUP BY and sort avoidance.

Hope I am not too far on a tangent to what you really look for.