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

Course Skill Level:

Advanced

Course Duration:

2 day/s

  • Course Delivery Format:

    Live, instructor-led.

  • Course Category:

    Database

  • Course Code:

    DSASQLN22J02

Who should attend & recommended skills:

Those with basic SQL skills who will be coding SQL queries

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. Our labs will include IBM Data Studio for tuning SQL.

Skills acquired & topics covered

  • Tuning Complex Single Table SQL
  • Tuning and Working with Multiple Tables
  • Advanced SQL Processing Topics
  • IBM Data Studio 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
  • 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

  • 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

  • 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