Objectives

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

• understand optimal table design
• choose optimal index design
• reduce query elapsed times via efficient SQL design
• choose optimal SQL code
• select optimal locking strategy
• understand package and plan implications
• understand the implementation and use of DB2 object-oriented facilities.

Who Should Attend

Experienced DB2 applications developers working with DB2 for z/OS up to Release 9.

Prerequisites

Attendance on the RSM course 'DB2 for Applications Programmers', or equivalent experience.

Duration

4 days

Fee

£1495 (ex. VAT)

Course Code

DASE

Contents

DB2 Objects & Constraints

DB2 Objects overview; Storage group; Database; Tablespace; Table; Indexspace/Index; Views; Synonym; Alias; Referential Integrity concepts; Referential Integrity constraints; Table check constraints; Unique constraints; Constraint syntax enhancements; Constraint management enhancements.

Index Design

Index structure; When to index; Clustering index; When not to index; Composite keys; Reorganising indexes; Creating indexes.

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.

The DB2 Optimizer

Input to the Optimiser; Catalog statistics; Filter factors; Filter factor and clustering; Filter factor examples; Influencing the Optimiser; Influencing the Optimiser - manually adjusting statistics; Influencing the Optimiser - using optimisation hints; Influencing the Optimiser - modelling production values; Catalog statistics; RUNSTATS options; Statistics columns; RUNSTATS - examples.

EXPLAIN (central to most practicals)

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

Joins

Inner Join; Left Outer Join; Right Outer Join; Full Outer Join; Using COALESCE; Join predicates evaluation; Join transformation; Nested Loop Join; Merge Scan Join; Hybrid Join; Join performance.

Subqueries

Definitions; Subquery with IN list; Existence testing; Performance considerations; Self-referencing subselects with UPDATE; Self-referencing Subselects with DELETE; Self-referencing subselects - positioned UPDATE and DELETE.

Views & Temporary Tables

Views; Nested table expressions; Nested table expressions with joins; Nested table expressions - performance; Common table expressions; Recursive SQL; View options; Temporary tables; Created temporary tables;Declared temporary tables; Table comparisons; Temporary tables examples.

Unions, Intersections, Exceptions & Case Expressions

UNION; UNION with views; UNION with nested table expression; UNION with Subqueries; UNION with data manipulation; INTERSECT; EXCEPT; Case expressions; Case expression with WHERE clause; Case expression with function; Case expression with UPDATE.

Distinct Data Types and User-defined Functions

Distinct data types; User-defined functions; Identifying functions; Sourced user defined function; External user defined function; External user defined table functions; User defined SQL functions; Invoking functions; Function resolution.

Triggers

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

Other SQL & Programming Enhancements

Longer names; Longer statements; New data types; Scalor fullselect; Multiple DISTINCT; Expressions in GROUP BY; TRUNCATE; Update detection; Scrollable cursors; Multi-row processing; MERGE statement; Multi-row condition handling; GET DIAGNOSTICS; Generated data; Identity columns; Sequences; SELECT FROM INSERT/UPDATE/DELETE; INCLUDE with INSERT/UPDATE; Ranking data; ROW-NUMBER; RANK; DENSE_RANK; SQL procedures; Row level security.

Locking & Concurrency

Locking overview; Reasons for locking: lost updates, reading uncommitted data, repeatable read; Lock compatibility: row and page locks, table and tablespace locks; Bind - ACQUIRE and RELEASE parameters; Bind - ISOLATION parameter; Bind - CURRENTDATA parameter; Lock avoidance; Lock avoidance example; Locking and concurrency recommendations.

Packages & Plans

Program preparation; Bind alternatives - plan only; Bind alternatives - packages; Packages and collections; Package options - varying parameters; Package options - mirrored tables; Package options - versions; Considerations.