Db2 for z/OS: Application Programming - Advanced Topics

What you will learn

On successful completion of this course you 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
• understand the difference between, and use of, identity columns and sequences.

Who Should Attend

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

Prerequisites

Attendance on the course Db2 for z/OS Application Programming Workshop, or equivalent experience.

Duration

3 days

Fee (per attendee)

£1995 (ex VAT)

This includes free online 24/7 access to course notes.

Hard copy course notes are available on request from rsmshop@rsm.co.uk

at £50.00 plus carriage per set.

Course Code

DASE

Contents

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 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.

Db2 EXPLAIN

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

Views & Temporary Tables

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

Specialised Table Types

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.

User-defined Functions

User-defined functions; User-defined function types; Sourced user-defined function; External user-defined scalar function; External user-defined table functions; User-defined SQL scalar functions; User-defined SQL scalar functions; User-defined SQL table functions; Identifying functions; Invoking functions; Function resolution; Function Security; Controlling User Defined Functions.

Ranking & Grouping Data

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

Triggers

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

Advanced Data Manipulation Language

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.

Identity Columns & Sequences

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