Db2 for z/OS Database Performance Tuning

SEE SCHEDULE

Duration: 3 Days

Method: Instructor led, Hands-on workshops

Price: $1920.00

Course Code: DB3052

Audience

Db2 Database and System Administrators who will be monitoring and tuning Db2 for z/OS Databases.

Description

Meeting performance objectives is a key priority for the Db2 database and systems administrators. Db2 for z/OS database performance is best achieved by first establishing performance requirements and planning for performance monitoring. Our workshops assist in identifying critical performance bottlenecks, implementing performance tuning techniques and reviewing tuning results. We provide detailed guidance information for all personnel who will be participating in database performance in both a data sharing and non-data sharing environment.

Objectives

Upon successful completion of this course, the student will be able to:

Develop a Performance Monitoring Strategy
Reduce overall CPU consumption
Reduce overall lock contention problems
Improve overall application response times
Identify and tune I/O overhead

Prerequisites

DB3010 Db2 for z/OS Database Administration or equivalent experience and a working knowledge of SQL.

Topics

I. Performance Overview
- Managing Performance
- Monitoring Performance
- Trace Types
- Tuning Approach
- DSNTIPN Installation Panel
- Performance Monitor Overview
- Batch Reporting
II. Performance Analysis
- Drill Down Performance Analysis
- Statistics Report
- Accounting Reports
- Exception Reporting
- Online Monitoring
III. SQL Performance Tuning using EXPLAIN
- Access Path Analysis
- Predicate Analysis
- Query Cost Analysis
- Sorts
- Parallelism
- Join Methods
- Materialism
IV. Application Performance Tuning
- Identify Workload type
  - OLTP Performance
  - OLAP Performance
  - Batch Performance
- Code Review
- Tuning for Concurrency
- Minimizing trips to Db2
- Insert Processing
- Delete Processing
- Stored Procedures
- Identifying Long Running Units of Recovery
- Identifying Bottlenecks
V. Maintaining Statistics
- Catalog Statistics
- Setting Default Statistics
- Distribution Statistics
- History Statistics
- Real Time Statistics
- Deciding whether to Rebind
- Modeling Production Statistics in Test
VI. Database Performance Tuning
- Indexes for Performance
  - Index Type
  - Composite Indexes
  - Index Compression
- Tablespace Performance Tuning
  - Page Size
  - Free Space
  - Locking & Concurrency
  - Partitioning for Parallelism
  - Resource Consumption
  - Compression
  - Health Check
VII. Extended Features Performance and Tuning
- Extended Table Types
- Triggers
- Temporal Data
VIII. System Performance Tuning
- Subsystem Parameters
- Buffer Pools
- Storage
- Total Working Set