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

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

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.

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.

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

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

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 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 data; ROW_NUMBER; RANK; DENSE_RANK; Moving sums; Moving averages.

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

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; Changing attributes; Using identity columns & sequences in SQL statements.