Question & Answer
Question
Are there any basic tips for performance tuning IBM® DB2® databases for use with IBM Rational® ClearQuest®?
Cause
The causes of problems with DB2 Performance can result from many forms of change, including the following:
- Physical changes to the environment, such as a new CPU or different tape drives.
- Installing a new version or release of the operating system.
- Changes to system software, such as a new release of a product, the alteration of a product, or a new product. Also included is 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.
- Changes to the DB2 engine from maintenance releases, which can change the optimizer.
- Changes in system capacity. More or fewer jobs could be executing concurrently when the performance problem occurs.
- Environment changes, such as the implementation of client/server programs or the adoption of data sharing.
- Database changes. This involves changes to any DB2 object, ranging from adding a new column or an index 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.
Answer
The following tips are common tuning points for DB2 Administrators. For additional assistance with DB2, contact your DB2 Administrator or contact IBM DB2 support.
The challenge of tuning any of the above is the find the source of the change, gauge its impact and formulate a solution. Although a majority of the problems will be application-oriented, the tuning opportunities presented in the other environments when application tuning has little effect should be explored. Listed below are some tuning solutions associated with each environment that are supported by ClearQuest.
Operating System:
- Change the dispatching priority.
- Modify swappability.
- Add memory.
- Upgrade CPU.
- Implement data sharing.
- Use an active performance monitor (enables tuning on-the-fly).
Teleprocessing environments:
- Change the system generation parameters.
- Tune the program definition (PSB and PPT entries).
- Modify the Attachment Facility parameters.
- Use an active performance monitor (enables tuning on-the-fly).
DB2 subsystem:
- Modify DSNZPARMs to increase or decrease the number of concurrent users, change lock escalation, increase EDM pool storage, etc.
- Issue SET LOG commands to change log buffers.
- Issue ALTER BUFFERPOOL commands to change bufferpool sizes, increase or decrease buffferpool thresholds, and modify associated hiperpools.
- Tune the DB2 Catalog, including dropping and freeing objects, executing MODIFY, reorganizing DB2 Catalog tablespaces and indexes, rebuilding the DB2 Catalog indexes, adding indexes to the DB2 Catalog, changing data set placement, moving the DB2 Catalog to a faster DASD device, and implementing data set shadowing.
- Use a tool to change DSNZPARMs on-the-fly.
Tune Programs:
- Reduce network requests in client/server applications.
- Run RUNSTATS.
- Change locking strategies.
- Implement optimization hints.
- Use a testing tool to provide "what if" testing and tuning.
- Use a tool to sample the applications address space as it executes.
DB2 problems should be investigated in the following order:
- MVS Environment
- Teleprocessing Monitors
- DB2 Subsystem
- Physical Database Design
- Shared Data
- Common Routines
- Called Programs
- Application Programs
Note: It is important to note that any type of tuning involving database table normalization, clustering, and database partitioning across multiple drives is not supported by ClearQuest. Tuning strategies should be documented, including all the tools that are used. If possible, obtain managerial agreement from all areas involved to reduce the friction that can occur when diverse groups attempt to resolve a DB2 tuning problem.
[{"Product":{"code":"SSSH5A","label":"Rational ClearQuest"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Database Configuration\/Connectivity - DB2","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"2003.06.00;2003.06.12;2003.06.13;2003.06.14;2003.06.15;2003.06.16;7.0;7.0.0.1;7.0.0.2;7.0.0.3;7.0.0.4;7.0.0.5;7.0.1;7.0.1.1;7.0.1.2;7.0.1.3;7.0.1.4;7.1","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]
Historical Number
16464
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21123931