In late 2010 the major theme of customer conversations was that every company was analyzing ways to save money and trim their IT budget. Those cost savings efforts slashed budgets by about 8.1% according to a 2010 CIO poll. The main method used was virtualization, combining all those UNIX and Windows environments that have sprouted up over the years. The new DB2 release comes along at just at the right time because Version 10 DB2 performance tuning solutions are potentially huge for many of the common designs, practices and DB2 performance tuning problems that are being used today.
Even if your design cannot handle removing all the indexes, DB2 10 also improves INSERT performance by using more parallelism. When INSERT SQL modifies a table with multiple indexes, DB2 10 does the pre-fetch of multiple DB2 indexes in parallel. By initiating parallel I/Os for the multiple indexes, the process is not waiting for synchronous I/Os, reducing the overall INSERT processing time. This cuts down the timeframe of possible contention within your system and improves DB2 performance tuning opportunities for all your applications.
The DB2 10 SQL optimizer now evaluates scalar functions and non-matching index predicates during the Stage 1 evaluation of the SQL access path. Data entries that previously waited until the Stage 2 process are done early in the optimization process and dramatically limit the number of qualifying data pages and rows that have to be evaluated. By eliminating these rows early in Stage 1, I/Os and CPU of these additional data entries are not passed into Stage 2, significantly reducing and dramatically improving query elapsed time and overall query performance. This DB2 10 Optimizer SQL performance tuning solution alone will make a huge difference in all of your application’s performance.