Know Db2

Advanced SQL Coding & Performance Tuning

Part 2 - DETAIL COURSE OUTLINE - 1/2 Day Classes

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

Topics Covered:

Duration: 4 - 1/2 day sessions.

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 Part 2 - Class Outline:

Day 5

SQL Performance

  • Matching Indexes to Predicates
  • Filter Factors and Analysis
  • Boolean and Non-Boolean Terms
  • Db2 Catalog and Distribution Statistics
  • RUNSTATS Utility
  • Comprehensive Statistics History
  • Histogram Statistics
  • PLAN _ TABLE management
  • DSN _ DETCOST _ TABLE
  • Advanced EXPLAIN Analysis
  • Tuning Workshop
  •  

Working with Multiple Tables

  • Joining Data from Multiple Tables
  • Inner / Full Outer Join
  • Left Outer Join/Right Outer Join
  • Tuning Workshop
  • Hands-on LAB
  • Exercises Statistics & SQL
  • Questions for Review
  • End of 1/2 day.
  •  

Day 6

Tuning Complex Queries

  • Using multiple join types in a Query
  • Nested Table Expressions
  • Using Global Temporary Tables
  • MERGE
  • Complex Access Paths
  • - Nested Loop Joins
  • - Merge Scan Joins
  • - Hybird Joins
  • Intermediate Tables - DSNDB07
  • EXPLAIN - Complex Queries
  •  

Summary Processing, Functions &
Advanced Coding

  • 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

Day 6 Cont...

  • Tuning Workshop
  • Hands-on LAB
  • Exercise Coding Complex SQL
  • Questions for Review
  • End of 1/2 day
  •  

Day 7

Advanced Tuning-Parallelism &
Influencing Access Paths

  • Partitioned Tablespaces
  • Recursive SQL
  • Static verses Dynamic SQL Processing
  • Parallel Operations
  • Enabling & Limiting Parallelism
  • Optimizer Hints - Access Paths
  • Filter Factors
  • Influencing the Access Path Optimizer
  • Dynamic SQL Governor
  • Insert within Select
  • Using Cursors: Definitions
  • Dynamic Scrollable Cursors
  • Cursor Comparisons
  • Multi-row Fetch with Scrollable Cursors
  • Comparison to Static Scrollable Cursors
  • GET DIAGNOSTICS
  • Tuning Workshop
  • Hands-on LAB
  • Exercise Cursor Uses
  • Questions for Review
  • End of 1/2 day
  •  

Day 8

Programming-Embedded SQL
Coding & Tuning

  • Update with Sub select
  • Using Save-points
  • Using Global Temporary Tables
  • Cursor Processing - Advanced Techniques
  • - Scrolling / OPTIMIZE FOR
  • - Existence Checking
  • - WITH HOLD
  • - COMMIT Implications
  • Stored Procedures Changes
  • External and Native Stored Procedures
  • Coding Techniques in Programming
  • Voltaile Tables and SQL
  • Transparent Rowid
  • Hands-on LAB
  • Exercises Cursors and Coding Techniques
  • Questions for Review
  • End of Part 2
  •  
  •  
    • ©2017 Computer Business International, Inc.

Would you like to schedule or customize this class?
Please call our toll free number (866-224-4968) or email us at info@cbi4you.com.