DB2 Tuning and Performance

Course Number:  612-TF
Recommended Duration:  5 days

Intended Audience
Technical personnel who need to optimize the performance of a DB2 application system.

Course Overview
This course provides the programmer or data base administrator with the knowledge and skills to understand performance implications of SQL.  In addition, it shows how to adjust either an SQL statement, or the containing environment to improve performance.  Techniques for utility, security, and design performance improvement are also presented.

Objectives
The student will learn how to tune applications that use the DB2 environment, as well as design data bases to minimize the use of system resources.

Prerequisites
The student should have experience using DB2, either in the areas of applications development or data base administration.

Topics

• DB2 architecture

• Application program tuning

• Access Strategies

• EXPLAIN, PLAN_TABLEs, DSN_STATEMENT_TABLEs, and DSN_STATEMENT_CACHE_TABLEs

• Indexable, Stage 1, and Stage 2 predicates

• Catalog tables and filter factors

• Catalog table modification

• Join and subquery access strategies

• BIND parameter considerations

• Concurrency and locking

• Batch issues

• Security issues and strategies

• Sequential and list prefetch

• Visual Explain

• Attachment facility issues, RCT parameters, and thread reuse

• Data base design considerations

• Utility issues

• DB2 traces and Instrumentation Facility

• DB2 system parameters

• MVS issues