DB2 Version 9 for z/OS has over 50 plus great performance features. Many of these really stand out and can provide a dramatic impact on your existing applications. Over the coming weeks I will write about these performance features.
After converting to Version 9, the first performance feature that should be evaluated for your environment and will probably have the greatest application performance impact is the indexes on expressions feature. The ability to put an index over a subset of a single column or derivatives of multiple columns, a formula, or a function used within your application can make a huge difference in application performance. By creating a customized index on an expression, you can change the access path of your problem applications from using a tablespace scan or a combination of multiple indexes to the use of a single customized index over the desired key. This key can be made from any SQL that is used for accessing your result set data. This can have a major impact on many applications and can be applied on any large or small table in your environment.
An index on an expression can be as simple as grabbing the date from the insert timestamp of the row, for example DATE(IN_TIMESTMP). An application that was previously doing a tablespace scan because it didn’t have the date portion of the timestamp can now use index access through this customized index expression. The index expression can also use multiple columns or expressions on multiple columns to create the index. In fact almost any expression that can be done in an SQL statement can be used to create an expression for an index.
Date and any other functions used in your application’s SQL are great candidates for index on expressions. So analyze your applications and understand the various columns functions or bad access that your application use and see if index over expressions might help your application performance.
_________________________________
Dave Beulke is an internationally recognized DB2 consultant, DB2 trainer and education instructor. Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments.
Leave a Reply