Advanced SQL Coding & Performance Tuning
This hands-on workshop trains the Db2 Query User, Application Developer and DBA to code complex SQL statements to achieve optimum performance.
- Db2 Concepts and Facilities Review
- Tuning Complex Single Table SQL
- Tuning and Working with Multiple Tables
- Advanced SQL Processing Topics
- Advanced Performance Tuning & EXPLAIN
Duration: 4 Day Hands - on Labs.
Labs: Db2 EXPLAIN and/or Data Studio, in-house products.
Audience: Anyone who will be coding SQL queries and is concerned with optimum performance; including end-users, programmers, application designers and database administrators.
Prerequisites: Working knowledge of basic SQL, TSO, and SPUFI.
Advanced SQL Coding & Performance Tuning Class Outline:
- Db2 Environment-How it works
- Data Structures To Know About
- SQL Impact on the System
Tuning Complex Single Table SQL
- SELECT Topics to Know
- WHERE/ORDER BY/Result Sets
- Functions, Expressions, Special Registers
- Review and Workshop –EXPLAIN
Introduction to Performance
- 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 Workshop
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
- Using Global Temporary Tables
- Tuning Workshop - EXPLAIN
Tuning Complex Queries
- Complex Access Paths
- -Nested Loop Joins
- -Merge Scan Joins
- -Hybrid Joins
- Intermediate Tables - DSNDB07
- EXPLAIN - Complex
- Tuning Workshop
Summary, Functions & Advanced Coding Tuning
- Column Functions & Group By Expressions
- Correlated/Non-Correlated subqueries
- Scalar Functions & Case Expressions
- Statement Cost Comparisons
- Materialized Query Tables (MQT)
- How the Optimizer uses MQT’s
- Query Performance Enhancements
- Avoiding SORTS
- Predicates for Performance
- New Stage 1 & Indexable Predicates
- Mismatched String Types
- EXPLAIN - Tuning Workshop
Would you like to schedule or customize this class?
Please call our toll free number (866-224-4968) or email us at firstname.lastname@example.org.