Home About Courses Schedule Services Webinars Contact Search

Db2 for LUW Advanced Query Tuning with IBM Data Studio


Duration: 3.0 days

Method: Instructor led, Hands-on workshops

Price: $1800.00

Course Code: DB1006


Application Architects, analysts, developers and DBAs involved in the development and support of Db2 LUW/SQL applications


This course is designed for the Db2 LUW development professional who desires to understand the fundamental issues associated with performance for Db2 SQL applications. Emphasis is placed on understanding optimization and the many access paths chosen by the optimizer. Performance related issues in the areas of database design, indexing, application SQL coding, and data statistics will be addressed. Much of the class will be in workshops where the students write queries, execute Db2 Explains, and perform analysis on the information.


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

  • Why the optimizer chooses certain access paths
  • That the way they structure their SQL queries has a huge impact on optimization
  • How data statistics play a part in optimization
  • How to use and analyze the Db2 Explain tool
  • What to do with both Table space scans and Index scans
  • How both the logical and physical design plays a part in optimization


A basic understanding of SQL development and optimization is helpful.


  • I. Optimization Goals & Tools
    • Goals of Optimization
      • Reduce CPU
      • Reduce I/O
      • Reduce Contention
    • Deciding what to tune
      • Trace / Snapshot Data
      • Critical path queries
      • Ad hoc query optimization
    • What causes CPU and I/O
    • Optimizer overview
    • Explain output examples
  • II. Access Paths with Visual Explain
    • Single Table Access
      • Tablespace Scan
      • Index Scans
      • Matching
      • Non-matching
      • Index Screening
    • Multiple Table Access
      • Join types
      • Inner
      • Left/Right Outer
      • Full Outer
      • Join methods
    • Sorting
    • Reasons for sorts
    • Avoiding sorts
    • Join order
  • III. Filter Factors & Performance
    • Catalog Statistics
    • What is a filter factor?
    • Use in index selection
    • Use in join order selection
    • Use in join method selection
  • IV. Predicate Types
    • Software levels
    • Predicate processing order
    • Viewing predicate details in Visual Explain
    • Stage 1 vs Stage 2 processing
  • V. Design for Performance
    • Index Only Access for screening and data retrieval
    • Clustering decisions
    • Multiple Index Access
    • Sort Avoidance
  • VI. Top 25+ Tuning and coding Tips for Db2 SQL Developers
    • SQL Standards and Guidelines
    • Programming guidelines
    • Db2 SQL Tuning Tips
    • Application Design tips
    • Predicate rewrite examples
    • Developer ‘Best Practices’