Know Db2

Db2 12 Advanced SQL Coding & Performance Tuning

Using EXPLAIN and Data Studio

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

Topics Covered:

Duration: 3 Days Hands - on Labs with EXPLAIN and/or Data Studio Labs.

Audience: SQL coders who are concerned with optimum performance, including end-users, programmers, application designers and database administrators.

Prerequisites: SQL basics, TSO and SPUFI, or Data Studio.

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

Db2 Review

  • Db2 Environment
  • Data Structures to Know About
  •  

Complex Single Table SQL

  • SELECT Topics to Know
  • WHERE/ORDER BY
  • Functions, Expressions, Special Registers
  • Review and Workshops
  •  

Query Performance Overview

  • 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 Workshops
  •  

Working with 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
  •  

Tuning Complex Queries

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

Summary Processing, Functions & Advanced Coding Tuning

  • Column Functions & Group By Expressions
  • Grouping Sets: Rollup, Cube
  • Correlated and Non-Correlated Subqueries
  • Scalar Functions & Case Expressions
  • Statement Cost Comparisons
  • Materialized Query Tables (MQT)
  • How the Optimizer uses MQT's
  • Query Performance Enhancements
  •  
  •  

Summary Processing, Functions and Advanced Coding (cont.)

  • Avoiding SORTS
  • SQL UNION ALL performance
  • Predicates for Performance
  • MERGE Statement
  • Mismatched String Types
  • Tuning Workshop
  •  

Advanced Tuning - Influencing Access Paths

  • Universal Table Spaces
  • Recursive SQL
  • Static verses Dynamic SQL Processing
  • SQLLEVEL SQL Option
  • Aggregate Functions for Statistics
  • Optimizer Hints - Access Paths
  • Filter Factors
  • Influencing the Access Path Optimizer
  • Insert within Select
  • Dynamic Scrollable Cursors
  • Cursor Comparisons
  • Multi-row Fetch with Scrollable Cursors
  • Comparison to Static Scrollable Cursors
  • GET DIAGNOSTICS
  • Tuning Workshop
  •  

Enbedded SQL Coding and 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
  • Temporal Data
  • Big Data / Db2 Support
  • Transparent Rowid
  • Hands-On Tuning Workshops
  •  

Tuning Your Application for Optimum Concurrency

  • Aspects of Locking
  • BIND and REBIND Compatibility - Options
  • Coding Techniques Cheat Sheet
  • New SQL Codes and Warnings
  • New Reserved Words
  • 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.