Db2 12 for z/OS SQL Performance and Tuning


Start End Duration Location Details

Course Details

Db2 12 for z/OS SQL Performance and Tuning

Course code: CV964G

Duration: 3 Days


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

Course Description:

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

Course Objectives:

After taking this course, you should 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

Intended Audience:

•    This course is for Db2 12 for z/OS application developers
•    Db2 12 for z/OS DBAs
•    Responsibility for SQL performance and tuning in a Db2 12 for z/OS environment.

Course Outlines:

•    Introduction to SQL performance and tuning 
•    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) 
•    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