Part V: DB2 Performance Tuning

Now that you understand how to monitor the DB2 environment, you must develop a plan to analyze the performance data you have accumulated and tune DB2 to boost performance. As you will see in this section, diverse tuning strategies are involved in making DB2 perform optimally.

It is not sufficient to merely monitor and tune DB2 alone. A comprehensive DB2 tuning program involves monitoring and tuning the following five areas:

• The OS/390 (MVS) system

• The DB2 subsystem

• The teleprocessing and networking environments (including Internet connectivity for Web applications)

• DB2 database design

• DB2 application program design

Some areas require more DB2 tuning attention than others. The DB2 performance tuning pie, although split into five pieces, is not split into five equal pieces. Figure V.1 shows the percentage of tuning usually spent in each area. Each percentage represents a comparative number encompassing the estimated number of incidences in the environment requiring tuning.

Figure V.1. The DB2 performancetuning pie.

For example, the OS/390 system constitutes a small portion of the tuning pie. This does not mean that there are few tuning options for MVS. Instead, it means that the number of times a DB2 performance problem is due to an OS/390 factor is minimal.

As the size of the piece of pie increases, the opportunities for DB2 performance tuning generally increase. But note that these numbers are estimates. Your tuning experiences might vary, but if they vary significantly, be sure that you are concentrating your tuning efforts wisely. The 80-20 rule applies here: 80% of performance gains accrue from 20% of your tuning efforts, as shown in Figure V.2. In other words, do not expend undue energy "tuning the life" out of an area if you expect only small gains. Instead, distribute your tuning efforts across each area. Concentrate on problem areas or areas in which you expect large performance gains.

Figure V.2. The 80-20rule.

Return your attention to Figure V.1, and you can see that the majority of DB2 performance problems result from improper application design, such as inefficient SQL, redundant SQL, or poor BIND options. The second most prominent area for tuning is in the application's relational database design. Was it based on relational techniques or converted from a nonrelational platform? Is it normalized, overnormalized, or undernormalized? Can it support the application requirements? The final three areas—the MVS, teleprocessing, and DB2 subsystems—should make up a small portion of your tuning efforts.

Note

For DB2 client/server applications, network tuning usually is a much larger component of the tuning effort. This is true because the client/server architecture relies heavily on networking to connect the clients to the server. So, plan for a heavier network tuning load when running DB2 client/server applications.

Remember, though, that you must monitor and tune each area that affects DB2 performance. Simply because there are fewer MVS tuning opportunities, for example, does not mean that the impact of a poorly tuned MVS subsystem is less substantial than a poorly tuned DB2 application program. Quite to the contrary! If MVS is not tuned to enable optimal DB2 performance, no amount of application tuning will ever result in proper performance. Implement a tuning strategy that encompasses all aspects of DB2 performance.