Advanced SQL Query Building

Course Number:  605-TF
Recommended Duration:  2 days

Intended Audience
Experienced DB2 programmers who need to construct complex, efficient queries using SQL, or who need to tune existing complex queries for performance.

Course Overview
Course consists of the more sophisticated SQL techniques contained in the Advanced DB2 Application Programming course, and includes the SQL tuning discussion from the DB2 Tuning and Performance class.

Objectives
The student will learn to use the EXPLAIN facility to understand physical access paths in DB2, to practice developing complex queries, and to assess the performance implications of various SQL coding techniques.

Prerequisites:  Experience coding SQL queries, using either SPUFI or QMF.

Topics

• Relational access concepts

• EXPLAIN, PLAN_TABLE, and DSN_STATEMNT_Table

• Access strategies

• Use of indexes

• Filter factors

• Indexable, Stage 1, and Stage 2 predicates

• Nested Table Expressions

• Joins: Inner, Outer, and Full

• Subqueries

• Unions

• Joined access strategies - nested loop, merge scan, hybrid

• Subquery access strategies

• Cursor performance