A Quick and Dirty Guide to DB2 Performance

All performance problems are caused by change. Now as regular readers know, normally I avoid all-encompassing terms like all, always, none and never, but in this case, all is the appropriate term. If your DB2 environment is humming along and things are performing well, then the only thing that will disrupt that is a change of some form.

Change can take many forms, not just a program change. Consider the following ways in which change can be introduced to your DB2 world:

• Physical changes to the environment, such as a new CPU, additional memory, new disk, or different storage technology (e.g. SSD).
• Changes to system software, such as a new release of a product (for example, QMF, CICS, or GDDM), the alteration of a product (for example, the addition of more or fewer CICS regions or an IMS SYSGEN), or a new product (for example, implementation of DFHSM). 
• And let's not forget the installation of a new release or version of DB2, which can result in changes in access paths and the utilization of features new to DB2. 
• An updated or  new version of the operating system can also cause DB2 performance problems.
• Changes to the DB2 engine from maintenance releases, which can change the DB2 optimizer and other facets of DB2's operation.
• Changes in system capacity. More or fewer jobs could be executing concurrently when the performance problem occurs.
• Environment changes, such as the implementation of dynamic SQL, web-based programs or the adoption of Data Sharing.
• Database changes. This involves changes to any DB2 object, and ranges from adding a new column or an index, to changing table space types, to dropping and re-creating an object.
• Changes to the application development methodology, such as usage of check constraints instead of application logic or the use of stored procedures.
• Changes to application code.

So regardless of how it may seem at times, performance problems are not caused by magic. Something tangible changes, creating a performance problem in the application, database, or subsystem. The challenge of tuning is to find the source of the change -- the root cause if you will, gauge its impact, and formulate a solution.

Although the majority of your problems will be application-oriented, you will frequently need to explore the tuning opportunities outside of SQL and programming changes when application tuning has little effect.

The following is a quick reference of the possible tuning options for each environment:

To tune z/OS

• Change WLM parameters to enhance throughput for DB2, its allied agent, and/or programs.
• Modify swappability.
• Add memory.
• Upgrade CPU.
• Add zIIPs and tune SQL/program to take advantage of zIIP processors.
• Implement data sharing.
• Use an active performance monitor (enables tuning on the fly).

To tune the teleprocessing environments

• Change the system generation parameters.
• Tune the program definition (PSBs and PPT entries).
• Modify the Attachment Facility parameters.
• Add or modify resource parameters (e.g. RDO)
• Use an active performance monitor (enables tuning on the fly).

To tune the DB2 subsystem

• Modify DSNZPARMs, for example to increase log buffers, increase or decrease the number of concurrent users, change lock escalation, and so on.
• Issue DISPLAY and ALTER BUFFERPOOL commands to review buffer pool usage characteristics, change buffer pool sizes, and increase or decrease buffer pool thresholds.
• Tune the DB2 Catalog, including dropping and freeing objects, executing MODIFY, reorganizing DB2 catalog table spaces and indexes, recovering the DB2 catalog indexes, building additional indexes on the DB2 catalog, tuning disk usage, and implementing data set shadowing.
• Perform DSNDB07 tuning.

To tune the DB2 database design

• Modify the logical and physical model.
• Modify and issue DDL.
• Execute ALTER statements.
• Ensure that proper parameters are specified.
• Implement table changes.
• Partition data into universal PBG table spaces.
• Spread non-partitioned objects over multiple devices using PIECESIZE.
• Add indexes.
• REORG tablespaces.
• REORG or RECOVER indexes.
• Consider or reconsider data compression.
• Denormalize the database design.
• Consider adding redundant tables.

To tune programs

• Perform SQL tuning.
• Tune the high-level language (such as COBOL or 4GL).
• Use a program restructuring tool.
• Run RUNSTATS.
• Execute EXPLAIN, modify your code, and REBIND.
• Examine the REOPT clause and whether it can help your specific code.
• Consider using SQL tweaks and/or hints.
• Use the OPTIMIZE FOR n ROWS clause.
• Consider activating query parallelism.
• Change locking strategies.
• Change the DB2 catalog statistics and REBIND.
• Use a testing tool to provide what if testing and tuning.
• Use a tool to sample the application's address space as it executes.

Of course, this is not an exhaustive list and should only be used as a guideline for the

types of tuning options available to you.

Hopefully this high-level overview of DB2 performance and various tuning options at your disposal has given you some food for thought as you tackle the task of keeping your DB2 environment operating smoothly and efficiently...