We are a family owned USA based Corporate Training Company determined to help professionals, teams, and organizations improve.

Let us help you find the training program you are looking for.

If you can't find what you are looking for, contact us, we'll help you find it. We have over 800 training programs to choose from.

banner-img
Home » Database » Toad for Db2 - Advanced SQL Coding and Performance Tuning

Course Skill Level:

Advanced

Course Duration:

2 day/s

  • Course Delivery Format:

    Live, instructor-led.

  • Course Category:

    Database

  • Course Code:

    TDB2ASN22J02

Who should attend & recommended skills:

SQL coders with basic SQL experience

Who should attend & recommended skills

  • Anyone who will be coding SQL queries and is concerned with optimum performance
  • SQL: Basic (1-2 years’ experience)

About this course

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.

Skills acquired & topics covered

  • Tuning Complex Single Table SQL
  • Tuning and Working with Multiple Tables
  • Advanced SQL Processing Topics
  • EXPLAIN and Toad for DB2 Tuning

Course breakdown / modules

  • 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

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

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

  • 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

  • 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

  • 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

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

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

Browse our programs to take the next step toward advancing yourself, your team, and organization.

Browse Courses