DB2 for z/OS Advanced Application Programming

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.
Prerequisites
Attendance on the RSM course 'DB2 for Applications Programmers', or equivalent experience.
Duration
4 days

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 Expressio
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 peocedures; 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.