CV964G - Db2 12 for z/OS SQL Performance and Tuning

This course is designed to teach the students how to prevent SQL performance problems and how to improve the performance of existing SQL.

Duration: 3.0 days

Enquire Now

Start learning today!

Click Hereto customize your Training

Objectives

After completing this course, students 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
  • and more

Content

1. Introduction to SQL performance and tuning

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

2. Performance analysis tools

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

3. Index basics

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

4. Access paths

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

5. More on indexes

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

6. Tuning methodology and index cost

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

7. Index design

  • Approach
  • Designing indexes

8. Advanced access paths

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

9. Multiple table access

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

10. Subqueries

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

11. Set operations (optional)

  • UNION, EXCEPT, and INTERSECT
  • Rules
  • More about the set operators
  • UNION ALL performance improvements

12. Table design (optional)

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

13. 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

14. Locking issues

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

15. More locking issues (optional)

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

16. Massive batch (optional)

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

Audience

This course is for Db2 12 for z/OS application developers, Db2 12 for z/OS DBAs, and anyone else with a responsibility for SQL performance and tuning in a Db2 12 for z/OS environment.

Prerequisites

  • Familiarity with SQL
  • Familiarity with Db2 12 for z/OS
  • Familiarity with Db2 12 for z/OS application programming

Certification

product-certification

This course is not associated with any Certification.

Course Benefits

product-benefits
  • Career growth
  • Broad Career opportunities
  • Worldwide recognition from leaders
  • Up-to Date technical skills
  • Popular Certification Badges
Enquire Now
 
 
 
 
By clicking "Submit", I agree to the Terms Of Use and Privacy Policy