Know Db2

Advanced SQL Coding & Performance Tuning With Data Studio

Advanced SQL Performance and Tuning Workshop is designed to guide the students in writing effective SQL code and skills to modify complex SQL statements to achieve optimum performance. Our labs will include EXPLAIN and IBM Data Studio.

Topics Covered:

Duration: 2 Day Hands - on Labs.

Labs: DB2 EXPLAIN and IBM Data Studio

Audience: Anyone who will be coding SQL queries and is concerned with optimum performance.

Prerequisites: Working knowledge of SQL.

Advanced SQL Coding & Performance Tuning With Data Studio Class Outline:

Introduction to SQL Performance

  • Db2 Environment - How it works
  • What is the Optimizer?
  • Predicates and Index Use
  • Matching = Predicates
  • Screening = Stage 1 predicates
  • Stage 1 = Stage 1 predicates (sargable)
  • Stage 2 = Stage 2 predicates (non-sargable)
  • How Predicates are Applied
  • Access Paths and How they Work
  • Matching Indexes to Predicates
  • RUNSTATS Utility
  • Distribution Statistics
  • Real Time Statistics with Profiles
  • Comprehensive Statistics History
  • DB2 Catalog Tables to Know
  • Hands-On Tuning Workshops
  •  

SQL Performance Tuning Tools

  • EXPLAIN and Tables Used in Reporting
  • Looking at EXPLAIN Results
  • PLAN_TABLE Review
  • Data Studio Introduction
  • Using Data Studio as a Tuning Tool
  • Reporting from Data Studio
  • Data Studio Query Tuner Report
  • - Estimated PLAN Cost
  • - RUNSTATS recommendations
  • - Table / Column definitions
  • - Index Recommendations
  • - SQL Rewrite
  • Data Studio Visual EXPLAIN
  • Setting up Data Studio Client on Windows
  • Setting up Data Studio on DB2 z/OS
  • Hands-On Tuning Workshops
  •  

Tuning Complex Queries

  • Complex Access Paths
  • Nested Loop Joins
  • Merge Scan Joins
  • Hybrid Joins
  • DSNDB07 - Intermediate Tables
  • Sorting In Db2
  • Complex Queries in Data Studio
  • EXPLAIN – Complex Queries
  • Hands-On Tuning Workshops
  •  

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
  • Hands-On Tuning Workshops
  •  

Summary, Functions & Adv. Coding Tuning

  • Column Functions & Group By Expressions
  • Correlated and Non-Correlated Subqueries
  • Scalar Functions & Case Expressions
  • Statement Cost Comparisons
  • Query Performance Enhancements
  • Avoiding SORTS
  • Predicates for Performance
  • Mismatched String Types
  • Hands-On Tuning Workshops
  •  
  •  

Advanced Tuning

  • Static verses Dynamic SQL Processing
  • 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
  • Hands-On Tuning Workshops
  •  

Tuning Your Application for Optimum Concurrency

  • Coding Techniques Cheat Sheet
  • Recommended best practices
  • SQL Codes and Warnings
  • Hands-On Tuning Workshops
  •  

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.