Home About Courses Schedule Services Webinars Contact Search

DB2 for z/OS SQL Optimization, Performance & Tuning

SEE SCHEDULE

Duration: 4 Days

Method: Instructor led, Hands-on workshops

Price: $2450.00

Course Code: DB1032



View Related Video

Audience

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.

Description

Want to elevate yourself to a higher level of SQL programming? Want to differentiate yourself from most SQL developers in the world today? Want to understand how to execute and understand a DB2 Explain? Want to know what it takes to get optimal performance out of queries, programs, and applications? Then this is your class! SQL statements will be broken down in detail so the participants understand in depth what the DB2 optimizer goes through in fulfilling an SQL request, and what can be done in order to get it to run faster. Time will especially be spent breaking down SQL Joins, Subqueries, and Table Expressions in order to understand the fundamental issues associated with performance. Performance-related issues in relationship to database design, indexing, efficient and effective SQL queries, and the gathering of accurate statistical information for optimization are addressed. Participants will come out of this class being ‘Empowered’ and having a much better understanding of why certain optimizations are chosen, and what can be done to change them for the better. Much of the class will be in workshops where the participants will code and 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.

Objectives

Upon successful completion of this course, the student will be able to:

  • Display a working knowledge of the DB2 Optimizer and Access Path Selection
  • Use the EXPLAIN feature of DB2
  • 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
  • Identify critical catalog statistics used in access path selection
  • Select appropriate statistics to gather using the RUNSTATS utility

Prerequisites

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

Topics

  1. Introduction
    • Tuning Approaches
    • The DB2 Optimizer and Access Path Decisions
    • SQL Tuning Strategy
    • Using Explain to Determine the Optimizers Access Path Choice
    • Estimated SQL Statement Cost
  2. Predicate Types and Performance
    • Predicate Processing
    • Predicate Types
    • Predicate Evaluation Sequence
    • Predicate Types and Processing
      • Stage 1
      • Indexable
      • Stage 2
    • Identify and tune poorly coded Predicates
      • Explain Predicate Table
      • Explain Filter Table
      • Coding tradeoffs
  3. Basic Access Paths and Performance
    • Overview
    • Tablespace Scans
    • Index Structures and access paths
      • Matching Index access
      • Non-matching Index access
      • IN-list Index scans
      • List Prefetch
      • Index Screening
      • Multi-Index Access
      • Index only Access
      • Direct Row Access
      • Hash Access
    • Sort Activity
    • Parallel Operations
    • Access Path Analysis using Explain
    • What Explain Does Not Tell You
  4. Tuning Joins
    • Join Types
      • Inner Joins
      • Outer Joins
      • Star Joins and Other types of Joins
    • Join Methods
      • Nested Loop Join
      • Merge Scan Join
      • Hybrid Join
      • Using Explain to validate Join methods
    • Sort Activity
      • Sorts via the Explain Tables
      • Tuning Sorts
  5. Aggregate Functions, SQL Subqueries & Performance
    • Aggregate Functions
    • Column Function Evaluations
    • Subqueries
      • Scalar Fullselects
      • Non Correlated Subqueries
      • Correlated Subqueries
      • Tuning Subqueries using Explain
    • Existence Checking
      • Methods of Existence Checking
      • Choosing the best method
    • Row Value Expression
      • Usage
    • Analyze and tune Subqueries
  6. Tables expressions and Performance
    • Nested Table Expressions
    • Common Table Expressions
    • Views and Nested Table Expression Processing
    • Materialization
    • Using Table Expressions as a tuning approach
    • Using Explain to analyze Table Expression Performance
  7. DB2 Catalog Statistics and Access Path Selection
    • The DB2 Catalog and Access Paths
    • Catalog Statistics and Filter Factors
    • Maintaining Statistics in the Catalog
    • Using RUNSTATS to Maintain Catalog Statistics
      • Basic Tablespace Statistics
      • Index Statistics
      • Table Statistics
      • Column Statistics
      • Distribution Statistics
      • Correlation Statistics
      • History Statistics
      • Histogram Statistics Use
      • Is Rebind Necessary?
    • Real Time Statistics
    • When to REORG
    • Best Practices
  8. Advanced Query Tuning
    • DB2 Predicate Manipulation
    • Predicate Generation through Transitive Closure
    • Join Simplification
    • Subquery Transformation
    • Adding Extra Local Predicates
    • Using OPTIMIZE FOR n ROWS
    • Plan Management and Binds
    • REOPT(VARS)
    • Using Host Variables Efficiently
    • Using Optimization Hints
    • SQL Optimization Coding Standards and Guidelines
    • Top 10+ Steps to Tuning a Query