Profience

Performance for DB2 Database Applications

Why take this course?

After completing this course, you will be able to
  • understand the major factors contributing to database application performance.
  • learn how tablespace, table, and index design effect performance.
  • have a comprehensive understanding of how application design affects performance.
  • learn about the impact of concurrency on performance.
  • understand how database optimizers works.
  • gain an in-depth knowledge of database access Paths.
  • become fully conversant in the use of tools such Explain
    including the use of Optimizer hints.
  • be able to judge the efficiency of SQL statements and tune ineffective queries.
  • learn to use utilities as part of the performance tuning process.
This hands-on course examines database performance issues from the application programmer's perspective. The discussion of database performance factors is accompanied by practical tips and techniques for designing and implementing efficient database applications. The SQL language is revisited to study efficient coding techniques. Hands-on exercises ensure an effective learning experience.
Who should attend?
The course is primarily intended for designers, programmer analysts, programmers and administrators who design, code or support database applications. Primary emphasis is DB2 11 for z/OS however most content is applicable to any database application environment.
Any prerequisites?
You should be familiar with the SQL language and application programming.

Dates and Fees

3 Days   ? - $ 1,047 (CAD) ? Up to 3 - $ 2,097 Up to 6 - $ 3,144   ? Plus - $ 524
 
Sorry, no classes are currently scheduled, please contact us to request a class
Contact us to arrange your preferred date and/or in-class training

What Topics are covered?

Application Tuning
  • Performance Factors
  • Design vs Implementation vs Execution
  • Performance Tuning and Baselines
  • Monitoring Tools and Techniques
  • The EXPLAIN Facility
Database Design for Performance
  • Tablespace & Table Design
  • Datatypes and Performance
  • Denormalization Options
  • Leveraging Constraints, Triggers and Procedures
  • Index Design and Structure
  • Clustering, Composite and Multiple Index Options
  • Best Practices for Indexes
Application Design for Performance
  • General Tips and Techniques
    • Reduce DB2 Interactions
    • Nested Loop Pullback
    • Local Reference Tables
    • Use the Index Order
    • Defer Work Till Needed
    • Fail Fast Ordering
  • Database Interaction Design Review
    • Transaction Analysis
    • Data Usage Maps
    • Transaction Load Matrix
    • Complex Query Analysis
  • Online Design
    • Design Objectives
    • Dialog Design
    • Prompts
    • Retrieval Options
    • Update Options
  • Batch Design
    • Design Objectives
    • Promoting Prefetch
    • Recoverability and Restart
    • Use of Utilities
Concurrency Control and Performance
  • Concurrency Control
  • Locking Strategy
  • Lock Size
  • Lock Mode
  • Lock Duration
  • Reducing Contention
Database Access Paths
  • What is an access path?
  • Single Index Access
  • Multiple Index Access
  • Join Strategies
  • Nested Loop Join
  • Merge Scan Join
  • Hybrid Join
  • Using Multiple Indexes in Joins
  • Combining Join Methods
The EXPLAIN Facility
  • The Optimizer
  • EXPLAIN Facility
  • Plan Tables
SQL Processing
  • The Optimizer
  • Processing SQL
  • SQL Cost
  • Filter Factor
  • The Catalog
Coding for Performance Part I
  • General SQL Considerations
  • SQL Predicates
Coding for Performance Part II
  • Multi Table Access
  • Avoiding Sorts
  • Performance Tips