Db2 12 for z/OS SQL Performance and Tuning [CV964G]

24 Hrs

Corporate training

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.


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_x001a_ Performance issues_x001a_ Simple example_x001a_ Visualizing the problem_x001a_ SummaryPerformance analysis tools_x001a_ Components of response time_x001a_ Time estimates with VQUBE3_x001a_ SQL EXPLAIN_x001a_ The accounting trace_x001a_ The bubble chart_x001a_ Performance thresholdsIndex basics_x001a_ Indexes_x001a_ Index structure_x001a_ Estimating index I/Os_x001a_ Clustering index_x001a_ Index page splitsAccess paths_x001a_ Classification_x001a_ Matching versus Screening_x001a_ Variations_x001a_ Hash access_x001a_ Prefetch_x001a_ CaveatMore on indexes_x001a_ Include index_x001a_ Index on expression_x001a_ Random index_x001a_ Partitioned and partitioning, NPSI and DPSI_x001a_ Page range screening_x001a_ Features and limitationsTuning methodology and index cost_x001a_ Methodology_x001a_ Index cost: Disk space_x001a_ Index cost: Maintenance_x001a_ Utilities and indexes_x001a_ Modifying and creating indexes_x001a_ Avoiding sortsIndex design_x001a_ Approach_x001a_ Designing indexesAdvanced access paths_x001a_ Prefetch_x001a_ List prefetch_x001a_ Multiple index access_x001a_ Runtime adaptive indexMultiple table access_x001a_ Join methods_x001a_ Join types_x001a_ Designing indexes for joins_x001a_ Predicting table orderSubqueries_x001a_ Correlated subqueries_x001a_ Non-correlated subqueries_x001a_ ORDER BY and FETCH FIRST with subqueries_x001a_ Global query optimization_x001a_ Virtual tables_x001a_ Explain for subqueriesSet operations (optional)_x001a_ UNION, EXCEPT, and INTERSECT_x001a_ Rules_x001a_ More about the set operators_x001a_ UNION ALL performance improvementsTable design (optional)_x001a_ Number of tables_x001a_ Clustering sequence_x001a_ Denormalization_x001a_ Materialized query tables (MQTs)_x001a_ Temporal tables_x001a_ Archive enabled tablesWorking with the optimizer_x001a_ Indexable versus non-indexable predicates_x001a_ Boolean versus non-Boolean predicates_x001a_ Stage 1 versus stage 2_x001a_ Filter factors_x001a_ Helping the optimizer_x001a_ PaginationLocking issues_x001a_ The ACID test_x001a_ Reasons for serialization_x001a_ Serialization mechanisms_x001a_ Transaction locking_x001a_ Lock promotion, escalation, and avoidanceMore locking issues (optional)_x001a_ Skip locked data_x001a_ Currently committed data_x001a_ Optimistic locking_x001a_ Hot spots_x001a_ Application design_x001a_ Analyzing lock waitsMassive batch (optional)_x001a_ Batch performance issues_x001a_ Buffer pool operations_x001a_ Improving performance_x001a_ Benefit analysis_x001a_ Massive deletes


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.


No certification available.


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


Please contact us for upcoming schedules. Email Us