Db2 12 for z/OS SQL Performance and Tuning

COURSE OUTLINE:

Description

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

Prerequisites

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

Learning Objectives

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

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
  • SQL EXPLAIN
  • 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

�

Subqueries

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

�

Set operations

  • UNION, EXCEPT, and INTERSECT
  • 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