Know Db2

Advanced SQL Coding & Performance Tuning

This hands-on workshop trains the Db2 Query User, Application Developer and DBA to code complex SQL statements to achieve optimum performance.

Topics Covered:

Duration: 4 Day Hands - on Labs.

Labs: Db2 EXPLAIN and/or Data Studio, in-house products.

Audience: Anyone who will be coding SQL queries and is concerned with optimum performance; including end-users, programmers, application designers and database administrators.

Prerequisites: Working knowledge of basic SQL, TSO, and SPUFI.

Advanced SQL Coding & Performance Tuning Class Outline:

Db2 Review:

  • Db2 Environment-How it works
  • Data Structures To Know About
  • SQL Impact on the System
  •  

    • Tuning Complex Single Table SQL

  • SELECT Topics to Know
  • WHERE/ORDER BY/Result Sets
  • Functions, Expressions, Special Registers
  • Review and Workshop –EXPLAIN
  •  

Introduction to Performance

  • What is the Optimizer?
  • Predicates-Indexable, Stage 1 and Stage 2
  • Access Paths and How they work
  • Matching Indexes to Predicates
  • Comprehensive Statistics History
  • Db2 Catalog and Distribution Statistics
  • PLAN_TABLE Changes
  • EXPLAIN - Advanced
  • Tuning Workshop
  •  

Tuning Multiple Tables

  • Joining Data from multiple Tables
  • Inner / Full Outer Join
  • Left Outer Join / Right Outer Join
  • Using multiple join types in a Query
  • Nested Table Expressions
  • Using Global Temporary Tables
  • Tuning Workshop - EXPLAIN
  •  

Tuning Complex Queries

  • Complex Access Paths
  • -Nested Loop Joins
  • -Merge Scan Joins
  • -Hybrid Joins
  • Intermediate Tables - DSNDB07
  • EXPLAIN - Complex
  • Tuning Workshop
  •  

Summary, Functions & Advanced Coding Tuning

  • Column Functions & Group By Expressions
  • Correlated/Non-Correlated subqueries
  • Scalar Functions & Case Expressions
  • Statement Cost Comparisons
  • Materialized Query Tables (MQT)
  • How the Optimizer uses MQT’s
  • Query Performance Enhancements
  • Avoiding SORTS
  • Predicates for Performance
  • New Stage 1 & Indexable Predicates
  • Mismatched String Types
  • EXPLAIN - Tuning Workshop
  •  

Advanced Tuning – Parallelism & Influencing Access Paths

  • Partitioned Table spaces
  • Recursive SQL
  • Static verses Dynamic SQL Processing
  • Parallel Operations
  • Enabling & Limiting Parallelism
  • Optimizer Hints - Access Paths
  • Filter Factors
  • Influencing the Access Path Optimizer
  • Dynamic SQL Governor
  • Insert within Select
  • Dynamic Scrollable Cursors
  • Cursor Comparisons
  • Multi - row Fetch with Scrollable Cursors
  • Comparison to Static Scrollable Cursors
  • GET DIAGNOSTICS
  • Tuning Workshop - EXPLAIN
  •  
  •  

Embedded SQL Coding & Tuning

  • Update with Subselect
  • Using Savepoints
  • Using Global Temporary Tables
  • Cursor Processing – Advanced Techniques
  • -Scrolling / OPTIMIZE FOR
  • -Existence Checking
  • Stored Procedures Changes
  • Coding Techniques
  • Volatile Tables and SQL
  • Transparent Rowid
  • EXPLAIN - Tuning Workshop
  •  
  •  

Tuning Your Application for Optimum Concurrency

  • Aspects of Locking
  • BIND and REBIND Options and Impact
  • Coding Techniques Cheat Sheet
  • New SQL Codes and Warnings
  • New Reserved Words
  • Tuning Workshop - EXPLAIN
  •  
  •  

Applying techniques learned to your data

  • NOTE: Each attendee should bring one or more
    complex queries to class for a case study.
  •  
  •  
  •  
  •  
  •  

    •  

 

 

  • © Computer Business International, Inc.

 

Would you like to schedule or customize this class?
Please call our toll free number 1.866.224.4968 or email us at info@cbi4you.com.