CA Performance Handbook Supplement for DB2 for z/OS

CA Performance
Handbook Supplement
for DB2 for z/OSTable of Contents
SECTION 1 1About This Supplement
SECTION 2 3Ensure Efficient Application SQL for DB2 for z/OSwith CA Database Management CA SQL-Ease® for DB2 for z/OS CA Plan Analyzer® for DB2 for z/OS
SECTION 3 7Ensure Efficient Object Access for DB2 for z/OSwith CA Database Management CA Database AnalyzerT for DB2 for z/OSCA Rapid Reorg® for DB2 for z/OS
SECTION 4 11Monitor Applications for DB2 for z/OS withCA Database ManagementCA InsightT Database Performance Monitorfor DB2 for z/OSCA Detector® for DB2 for z/OSCA Subsystem Analyzer for DB2 for z/OSCA Thread Terminator Tool
SECTION 5 17Further Improve Application Performance forDB2 for z/OS with CA Database ManagementCA Index ExpertT for DB2 for z/OS
Copyright © 2007 CA. All Rights Reserved. One CA Plaza, Islandia, N.Y. 11749. All trademarks, trade names, service marks, and logos referenced herein belong to their respective companies.SECTION 1
About This Supplement
This supplement to the CA Performance Management Handbook for DB2 for z/OS providesspecific information on how CA Database Management for DB2 for z/OS addresses theperformance management challenges outlined in the handbook with an approach that reducesCPU and DB2 resource overhead and streamlines labor intensive tasks with automatedprocesses. The supplement provides information to ensure efficient application SQL, ensureefficient object access, monitor applications, and improve application performance.
About The CA Performance Management Handbookfor DB2 for z/OSThe handbook is a technical resource from CA that provides information to consider as youapproach database performance management, descriptions of common performance andFor more tuning issues during design, development, and implementation of a DB2 for z/OS applicationinformation, and specific techniques for performance tuning and monitoring.
visit . Chapter 1: Provides a overview information on database performance tuning ca.com/db. Chapter 2: Provides descriptions of SQL access paths . Chapter 3: Describes SQL tuning techniques. Chapter 4: Explains how to design and tune tables and indexes for performance . Chapter 5: Describes data access methods. Chapter 6: Describes how to properly monitor and isolate performance problem . Chapter 7: Describes DB2 application performance features . Chapter 8: Explains how to adjust subsystem parameters, and configure subsystemresources for performance. Chapter 9: Describes tuning approaches when working with packaged applications. Chapter 10: Offers tuning tips drawn from real world experience
The primary audiences for the handbook are physical and logical database administrators.The handbook assumes a good working knowledge of DB2 and SQL, and is designed to helpyou build good performance into the application, database, and the DB2 subsystem. It providestechniques to help you monitor DB2 for performance, and to identify and tune productionperformance problems.
Individuals can register to receive the handbook at ca.com/db.
CA PERFORMANCE HANDBOOK SUPPLEMENT FOR DB2 FOR z/OS 1Page intentionally left blankSECTION 2
Ensure Efficient Application SQL for DB2
for z/OS with CA Database Management
When developing DB2 applications it is critical that SQL statements within the application areas efficient as possible to ensure good database and application performance. Inefficient SQLcan lead to poor application response times and high resource use and CPU costs within theDB2 system in which it runs.
It is much more cost effective to develop efficient SQL from the outset -during development,rather than to work to identify and fix inefficient SQL running in a live application.
Sometimes, the original design of the SQL is not entirely under your control. You may beimplementing Enterprise Resource Planning (ERP) applications using dynamic SQL andpackaged solutions on your systems. It is still important to ensure the SQL within theseapplications is efficient. Tuning SQL in ERP applications can yield substantial performancegains and cost savings.
For more Coding efficient SQL is not always easy since there are often a number of ways to obtain theinformation, same result set. SQL should be written to filter data effectively while returning the minimumnumber of rows and columns to the application. Data filtering should b... [download for more]