Course Description

Advanced SQL Query Building

This course: course objectives; course outline;
DB2 curriculum; Topic list; Course list; Home page

Benefits

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.

Audience

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

Prerequisites

Experience coding SQL queries, using either SPUFI or QMF.

IBM Materials Required

DB2 SQL Reference (current  version)

• 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

Exercises

There are seven machine exercises.