On-site & Public, Instructor-led, Hands-on IT Training
800.756.3000
(International 01) 908.233.8900
  400+ Course Offerings:


Search for Course:

DB1032 DB2 for z/OS SQL Optimization, Performance & Tuning
 
 
Duration 5 Days
Delivery Method Instructor led, Hands-on workshops
Public Price $2750.00


DESCRIPTION (Return to Top)

This class is designed to break down SQL statements in detail so the participants understand in depth what the DB2 optimizer goes through in fulfilling an SQL request. Time will especially be spent breaking down SQL Joins and Subqueries in order to understand the fundamental issues associated with performance.

The class will address performance-related issues in relationship to database design, indexing, efficient and effective SQL queries, and the gathering of accurate statistical information for optimization. Participants will come out of this class having a much better understanding of why certain optimizations are chosen, and what can be done to get DB2 to choose differently.

Much of the class will be in workshops where the participants will review queries, execute DB2 Explains, and perform analysis on the information. Many of the workshops have the participants rewrite and/or apply tuning statements to existing queries in order to get them to perform more efficiently. Participants will know exactly what steps to take in getting poor performing SQL queries to execute more efficiently.



AUDIENCE (Return to Top)

All personnel involved in SQL Access path performance tuning that require an in-depth understanding of the full complement of Explain Tables provided by DB2.



OBJECTIVES (Return to Top)

A working knowledge of DB2 for z/OS environment and the SQL language

  • Display a working knowledge of the DB2 Optimizer and Access Path Selection
  • Use the EXPLAIN feature of DB2 to:
    • Analyze SQL access paths
    • Identify and tune poorly coded SQL Predicates
    • Identify and tune poor performing Joins
    • Identify and tune poor performing Subqueries
    • Make appropriate SQL and access path tuning recommendations
  • Make appropriate SQL and access path tuning recommendations
  • Identify critical catalog statistics used in access path selection
  • Select appropriate statistics to gather using the RUNSTATS utility


TOPICS (Return to Top)
  • Introduction
  • Predicate Types and Performance
  • Basic Access Paths and Performance
  • Tuning Joins
  • Aggregate Functions, SQL Subqueries & Performance
  • Tables expressions and Performance
  • DB2 Catalog Statistics and Access Path Selection
  • Advanced Query Tuning


PREREQUISITES (Return to Top)

A working knowledge of DB2 for z/OS environment and the SQL language.