Home About Courses Schedule Services Webinars Contact Search

Db2 for z/OS Database Performance Tuning

SEE SCHEDULE

Duration: 3.0 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

  1. Performance Overview
    • Performance Objectives
    • Service Level Agreement
    • Monitoring Performance
      • Continuous Monitoring
      • Periodic Monitoring
      • Detailed Monitoring
      • Exception Monitoring
    • Investigating Db2 Performance Problems
      • CPU regression
      • Application Response or elapsed time
      • Analyzing Concurrency
      • Investigation SQL performance using EXPLAIN
      • Maintaining data organization and statistics
    • Tuning Approach
      • Use the 80/20 Rule
      • SQL Tuning
      • Database Tuning
      • System Resource Tuning
  2. SQL Performance Tuning using EXPLAIN
    • Access Path Analysis
    • Predicate Analysis
    • Query Cost Analysis
    • Sorts
    • Parallelism
    • Join Methods
    • Materialism
  3. 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
  4. Maintaining Statistics
    • Catalog Statistics
    • Setting Default Statistics
    • Distribution Statistics
    • History Statistics
    • Real Time Statistics
    • Deciding whether to Rebind
    • Modeling Production Statistics in Test
  5. 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
  6. System Performance Tuning
    • Subsystem Parameters
    • Buffer Pools
    • Storage
    • Total Working Set