OverviewThis course is designed for the experienced Db2 developer, focusing on advanced SQL statements and options. Additionally, the Db2 EXPLAIN facility is discussed as a tool to be used when choosing amongst competing SQL and design alternatives.<br><br>This course is available for one-company, on-site presentations and for live presentation over the Internet, via the Virtual Classroom Environment service.
PrerequisitesAttendance on the course Db2 for z/OS Application Programming Workshop, or equivalent experience.
Delegates will learn how 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.
OutlinePredicates, 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.