CV964G Db2 12 for z/OS SQL Performance and Tuning
Duration: 3 Days
Level: Intermediate
Audience: Systems Administrator
Next Sessions
| Start (YYYY-MM-DD) | End (YYYY-MM-DD) | Language | Amount | |
|---|---|---|---|---|
| 2024-05-06 | 2024-05-08 | English | 2175 EUR | |
| 2024-05-20 | 2024-05-22 | English | 2175 EUR | |
| 2024-06-03 | 2024-06-05 | English | 2175 EUR | |
| 2024-06-17 | 2024-06-19 | English | 2175 EUR | |
| 2024-07-01 | 2024-07-03 | English | 2175 EUR | |
| 2024-07-15 | 2024-07-17 | English | 2175 EUR | |
| 2024-07-29 | 2024-07-31 | English | 2175 EUR | |
| 2024-08-12 | 2024-08-14 | English | 2175 EUR | |
| 2024-08-26 | 2024-08-28 | English | 2175 EUR | |
| 2024-09-09 | 2024-09-11 | English | 2175 EUR | |
| 2024-09-23 | 2024-09-25 | English | 2175 EUR | |
| 2024-10-07 | 2024-10-09 | English | 2175 EUR | |
| 2024-10-21 | 2024-10-23 | English | 2175 EUR | |
| 2024-11-04 | 2024-11-06 | English | 2175 EUR | |
| 2024-11-18 | 2024-11-20 | English | 2175 EUR | |
| 2024-12-02 | 2024-12-04 | English | 2175 EUR | |
| 2024-12-16 | 2024-12-18 | English | 2175 EUR |
Overview
This course is designed to teach the students how to prevent SQL performance problems and how to improve the performance of existing SQL.
Prerequisites
- Familiarity with SQL
- Familiarity with Db2 12 for z/OS
- Familiarity with Db2 12 for z/OS application programming
After completing this course, students will be able to:
- Understand and design better indexes
- Determine how to work with the optimizer (avoid pitfalls, provide guidence)
- Optimize multi-table access
- Work with subqueries
- Avoid locking problems
- Use accounting traces and other tools to locate performance problems in existing SQL
- and more
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 (optional)
• UNION, EXCEPT, and INTERSECT
• Rules
• More about the set operators
• UNION ALL performance improvements
Table design (optional)
• 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 (optional)
• Skip locked data
• Currently committed data
• Optimistic locking
• Hot spots
• Application design
• Analyzing lock waits
Massive batch (optional)
• Batch performance issues
• Buffer pool operations
• Improving performance
• Benefit analysis
• Massive deletes