Db2 12 for z/OS SQL Performance and Tuning Course Details:

Learn how to prevent SQL performance problems and how to improve the performance of existing SQL.

    No classes are currenty scheduled for this course.

    Call (919) 283-1653 to get a class scheduled online or in your area!

Introduction to SQL performance and tuning

  • Performance issues
  • Simple example
  • Visualizing the problem
  • Summary

Performance analysis tools

  • Components of response time
  • Time estimates with VQUBE3
  • The accounting trace
  • The bubble chart
  • Performance thresholds

Index basics

  • Indexes
  • Index structure
  • Estimating index I/Os
  • Clustering index
  • Index page splits

Access paths

  • Classification
  • Matching versus Screening
  • Variations
  • Hash access
  • Prefetch
  • Caveat

More on indexes

  • Include index
  • Index on expression
  • Random index
  • Partitioned and partitioning, NPSI and DPSI
  • Page range screening
  • Features and limitations

Tuning methodology and index cost

  • Methodology
  • Index cost: Disk space
  • Index cost: Maintenance
  • Utilities and indexes
  • Modifying and creating indexes
  • Avoiding sorts

Index design

  • Approach
  • Designing indexes

Advanced access paths

  • Prefetch
  • List prefetch
  • Multiple index access
  • Runtime adaptive index

Multiple table access

  • Join methods
  • Join types
  • Designing indexes for joins
  • Predicting table order


  • Correlated subqueries
  • Non-correlated subqueries
  • ORDER BY and FETCH FIRST with subqueries
  • Global query optimization
  • Virtual tables
  • Explain for subqueries

Set operations

  • Rules
  • More about the set operators
  • UNION ALL performance improvements

Table design

  • Number of tables
  • Clustering sequence
  • Denormalization
  • Materialized query tables (MQTs)
  • Temporal tables
  • Archive enabled tables

Working with the optimizer

  • Indexable versus non-indexable predicates
  • Boolean versus non-Boolean predicates
  • Stage 1 versus stage 2
  • Filter factors
  • Helping the optimizer
  • Pagination

Locking issues

  • The ACID test
  • Reasons for serialization
  • Serialization mechanisms
  • Transaction locking
  • Lock promotion, escalation, and avoidance

More locking issues

  • Skip locked data
  • Currently committed data
  • Optimistic locking
  • Hot spots
  • Application design
  • Analyzing lock waits

Massive batch

  • Batch performance issues
  • Buffer pool operations
  • Improving performance
  • Benefit analysis
  • Massive deletes

*Please Note: Course Outline is subject to change without notice. Exact course outline will be provided at time of registration.

After completing this course, you will be able to:

  • Understand and design better indexes
  • Determine how to work with the optimizer (avoid pitfalls, provide guidance)
  • Optimize multi-table access
  • Work with subqueries
  • Avoid locking problems
  • Use accounting traces and other tools to locate performance problems in existing SQL

Familiarity with SQL, Db2 12 for z/OS, and application programming

Ready to Jumpstart Your IT Career?