IntroductionCourse Outline

Objectives

On successful completion of this course attendees will be able to:

  • choose optimal SQL code
  • understand the DB2 optimizer and the use of EXPLAIN in determining access path and statement efficiency
  • understand the differences between views, nested table expressions, common table expressions, and temporary tables and select the best option for a specific task
  • understand the use of materialized query tables, clone tables and temporal tables and the SQL statements associated with them
  • describe the use of distinct data types, user-defined functions, and OLAP functions
  • understand how and when triggers may be used
  • explain the advanced programming possibilities when using the INSERT, UPDATE, DELETE and MERGE statements
  • explain the advanced programming possibilities when using the INSERT, UPDATE, DELETE and MERGE statements
  • understand the difference between, and use of, identity columns and sequences.

Audience

Experienced DB2 applications developers working with DB2 for z/OS.

Prerequisites

Attendance on the RSM course DB2 for z/OS Application Programming, or equivalent experience.

Duration

3 days

Outline for DB2 for z/OS: Application Programming - Advanced Topics

1. Predicates, Access Paths, & I/O Types

  • Predicates
  • Access paths - matching index scan
  • Access paths - non-matching index scan
  • Access paths - table or tablespace scan
  • Access paths - direct row access
  • Indexable and non-indexable predicates
  • Predicate processing
  • Stage 1 and Stage 2 predicates
  • Summary of predicate processing
  • Predicate evaluation sequence
  • Sequential prefetch
  • List prefetch
  • Index lookaside.

2. The DB2 Optimizer

  • Input to the Optimizer
  • Catalog statistics
  • Filter factors
  • Filter factor and clustering
  • Filter factor examples
  • Influencing the Optimizer
  • Influencing the Optimizer by manually adjusting statistics, modelling production values & using optimization hints'
  • Catalog statistics
  • RUNSTATS options
  • Statistics columns
  • RUNSTATS - examples.

3. DB2 EXPLAIN

  • EXPLAIN
  • PLAN_TABLE
  • PLAN_TABLE additions
  • DSN_STATEMNT_TABLE
  • DSN_FUNCTION_TABLE
  • EXPLAIN: basic access paths, multi-index access, nested queries examples.

4. Views & Temporary Tables

  • Views
  • Nested table expressions
  • Common table expressions
  • Recursive SQL
  • View options
  • Created temporary tables
  • Declared temporary tables
  • Table comparisons.

5. Materialized Query, Clone & Temporal Tables

  • Materialized query tables
  • maintaining data in MQTs
  • Using MQTs
  • Automatic Query Rewrite (AQR)
  • Clone tables
  • Using clone tables
  • Exchanging data
  • System period temporal tables
  • Application period temporal tables
  • Using temporal tables
  • FROM period specification.

6. Distinct Data Types & User-defined Functions

  • Distinct data types
  • Casting data
  • Sourced user defined function
  • External user defined function
  • User defined SQL functions
  • Table functions
  • Inline and non-inline scalar functions
  • Invoking functions
  • Identifying functions
  • Function resolution.

7. OLAP Processing

  • Ranking data
  • ROW_NUMBER
  • RANK
  • DENSE_RANK
  • Moving sums
  • Moving averages.

8. Triggers

  • Triggers
  • Trigger components
  • Trigger options
  • Trigger body statements
  • BEFORE, AFTER & IINSTEAD OF triggers
  • Trigger examples
  • Trigger performance.

9. Advanced INSERT, UPDATE, DELETE & MERGE Options

  • SELECT FROM INSERT/UPDATE/DELETE
  • INCLUDE with INSERT & UPDATE
  • Multi-row processing with INSERT & MERGE
  • Multi-row condition handling
  • GET DIAGNOSTICS
  • Statement information
  • Condition information.

10. Identity Columns & Sequences

  • Identity columns
  • Sequences
  • Changing attributes
  • Using identity columns & sequences in SQL statements.