Tuning application performance is concerned with those performance topics associated with your applications and their interaction with the database. There are topics specific to applications themselves: Concurrency, Locking, Optimization Classes, control of results sets on queries, row blocking, use of compound SQL. In addition, there are brief discussions of: Character conversion as it relates to application performance; stored procedures; activation of databases; and the advantages of parallel processing. See Chapter 18, Application Considerations for more information.
There are topics specific to optimization of queries: Configuration parameters affecting query optimization, the impact of node groups and table spaces on query optimization, and the large impact that indexes can have on query optimization. See Chapter 19, Environmental Considerations for more information.
System catalog statistics have a significant influence on how well data is accessed by applications. The following topics are associated with statistics: The RUNSTATS utility, distribution statistics, index statistics, and those statistics that can be updated by users. See Chapter 20, System Catalog Statistics for more information.
The SQL compiler takes each application and determines the best access plan for that application. Each query within the application is evaluated and may undergo several different operations designed to most clearly define the goal of the query. Then different methods of access (scans and joins) are reviewed for each query to determine the quickest way to retrieve the data requested by the query. The affects of parallelism are also considered. See Chapter 21, Understanding the SQL Compiler for more information.
There are different tools available within the DB2 product to assist in the understanding of what is happening with the queries of an application. These tools are concerned with explaining what is affecting application performance. See Chapter 22, SQL Explain Facility for more information.
In addition to tuning individual applications, you should also consider the performance of the database where those applications are running. Performance of your database is determined in large part by how well memory is used. There are many topics surrounding memory that are concerned with performance: buffer pools, prefetching of data, parallel I/O, sorting capabilities, the need to reorganize the data in tables, and the concept of database agents. See Chapter 23, Operational Performance for more information.
There is a Governor that can be set up to manage how applications are using the database. See Chapter 24, Using the Governor for more information.
The number of processors and the number of database partitions can be increased to improve the performance of the database. See Chapter 25, Scaling Your Configuration for more information.
Once you have increased the number of database partitions, you will want to ensure the data in the database is spread or redistributed correctly among the database partitions. See Chapter 26, Redistributing Data Across Database Partitions for more information.
To determine how well your database is performing, you can conduct benchmark testing. The methodology for benchmark testing, how to prepare for a benchmark test, the creation of a benchmark program, and the running of benchmark tests are all topics of importance. See Chapter 27, Benchmark Testing for more information.
The very extensive set of database manager and database configuration parameters are presented individually within Chapter 28, Configuring DB2.
There is additional information that is related to these performance topics. The appendices include the following: