The Sample Data Base
Review of Relational Concepts: Select, Project, Join
Access Strategies in DB2
EXPLAIN, and the Use of Indexes

• Computer Exercise: EXPLAIN and PLAN_TABLEs

Performance Considerations

• DB2 Processing Sequence
• Predicate Characteristics
• Indexable, Stage 1, and Stage 2 Predicates
• Filter Factors

Aggregated Queries

• Built-in Column Functions
• GROUP BY
• HAVING
• ORDER BY
• Computer Exercise: Data Aggregates

Special Registers
Dates, Times, Timestamps, and Durations
Built-in Scalar Functions
Nested Table Expressions

• Computer Exercise: Special Registers and Scalar Functions

Joins

• Name Specification
• Primary Keys and Foreign Keys
• Joins on non-key columns
• Joins and aggregates
• Join Access Strategies
• Computer Exercise: Joins

Joins, Part 2

• Outer Joins
• Outer Joins and Nested Table Expressions
• The “Classic” Join Syntax
• Influencing the Join Access Path
• Computer Exercise: Outer Joins

Subqueries

• IN and NOT IN
• Arithmetic Comparison
• ANY and ALL
• EXISTS and NOT EXISTS

Correlated subqueries

• Finding mismatches between tables

Subquery Access Strategies

• Computer Exercise: Subqueries

UNION and UNION ALL

• Query parallelism

EXPLAINing a Package or Application Plan
Programming Considerations

• Static and Dynamic SQL
• Cursor issues
• Optional Computer Exercise: UNION and Plan Explains.