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.
- Db2 Environment Review
- SQL Tuning Complex Single Table SQL
- Multiple Tables Coding and Tuning
- Advanced SQL Processing Topics
- Advanced Performance Tuning and EXPLAIN
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 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
- 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
Would you like to schedule or customize this class?
Please call our toll free number (866-224-4968) or email us at email@example.com.