Db2 12 for z/OS SQL Performance and Tuning

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

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