Db2 12 for z/OS SQL Performance and Tuning
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
- 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
*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