Up to Blog Posts in Mainframe 2.0 Community

Mainframe 2.0 Community

2 Posts tagged with the index tag
Dave Beulke

Index design is one of the most important items or maybe the most important item in database design. Sometimes it is better to enhance the database with more indexes instead of trying to fix the application SQL. Tuning SQL is always good but tuning many SQL statements takes too much time. When time is tight most of the problem application SQLs can be instantaneously fixed through better indexes.


DB2 Version 9 indexes have several new options that help performance. First is the enhancement that I have written about before in the blog (Expression Performance) is Index on Expression. The Index on Expression enhancement alone can make a huge performance impact by tailoring the index to the application WHERE clause predicates. This customization of an index design can be a huge performance improvement for many applications, especially applications using DATE, TIME and other SQL functions within the SQL.


Next, is Index Compression which cuts disk requirements for the index structure dramatically. Most of the time compression can yield over a 50% savings and sometimes over 80%. This is especially important for large BI/DW databases indexes that can sometimes be terabytes before compression.


Next, index page sizes can be set to a larger page size that helps reduce the overall number of pages and the number of page splits within the index structure. Minimizing page splits helps avoid contention, helps cache all the index entries and keeps index value entries near each other on the same index page.


Another improvement for indexes that have ascending/descending keys is the new key randomization feature. Using the RANDOM option during the CREATE or ALTER of the index causes the index entries to be stored at random places in the index tree. This random inserting cuts down on index contention and is especially important for robust data sharing systems that are inserting many ascending/descending index keys.


So next time an application has SQL problems take a look at improving or defining a new index on the database. You might improve a huge number of applications instead of only a single application program and have time left to focus on other issues.


117 Views 0 Comments 0 References Permalink Tags: dave_beulke, db2_9, performance, index, index_design, database_design
Dave Beulke

While talking with clients and friends at the recent conferences, I noticed that everyone seemed to like the blog entries I’ve done about the performance features of DB2 v9 for z/OS. Some have even implemented some of the features previously mentioned and have already reduced their costs and overall processing elapsed times. So, with DB2 Version 9 for z/OS having over 50+ great new performance features, we will continue highlighting these many great new features in the coming weeks.


One of the biggest improvements has come for application and SQL processing in the improvements in index lookaside. This application and SQL index lookaside improvement is built into DB2 Version 9 and leverages the access of the previous work within the current SQL process or other SQL processes.


Index lookaside is the improved process where DB2 keeps track of the index and data pages referenced and their associated index value ranges using system cache. This provides DB2 with the ability to bypass or ignore any subsequent duplicate requests for I/Os against these desired pages. Since these duplicate page requests happen quite frequently during insert activities, index lookaside provides tremendous I/O relief for insert activities. Index lookaside is a great index I/O performance improvement feature that came with DB2 Version 8 for insert activities only.


In DB2 Version 9, the index lookaside process has been leveraged for delete processing also. Since most insert and delete activities process via some order, the index key values and their associated index and data pages could be requesting duplicate I/Os. According to IBM, analysis has found that the new index lookaside feature can save tremendous amounts of I/O — sometime up to a 100 times reduction in I/O requests.


Given these index lookaside features that are built into DB2 Version 9 for z/OS it should be immediately available to all your SQL and applications. This should help improve the performance of existing applications that do a large portion of insert and delete processing with or index lookaside reducing overall elapsed time for your processing.


Leave a comment below. If you don't see the Add a Comment button, please log in or register.
49 Views 0 Comments 0 References Permalink Tags: db2_9, z/os, sql, sql_processing, index, lookaside, i/o, performance


Popular Blog Posts