Objectives
On successful completion of this course attendees 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
- 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.
Audience
Experienced DB2 applications developers working with DB2 for z/OS.
Prerequisites
Attendance on the RSM course DB2 for z/OS Application Programming, or equivalent experience.
Duration
3 days
Outline for DB2 for z/OS: Application Programming - Advanced Topics Training
1. 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.
2. 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.
3. DB2 EXPLAIN
- EXPLAIN
- PLAN_TABLE
- PLAN_TABLE additions
- DSN_STATEMNT_TABLE
- DSN_FUNCTION_TABLE
- EXPLAIN: basic access paths, multi-index access, nested queries examples.
4. Views & Temporary Tables
- Views
- Nested table expressions
- Common table expressions
- Recursive SQL
- View options
- Created temporary tables
- Declared temporary tables
- Table comparisons.
5. Materialized Query, Clone & Temporal Tables
- 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.
6. Distinct Data Types & User-defined Functions
- Distinct data types
- Casting data
- Sourced user defined function
- External user defined function
- User defined SQL functions
- Table functions
- Inline and non-inline scalar functions
- Invoking functions
- Identifying functions
- Function resolution.
7. OLAP Processing
- Ranking data
- ROW_NUMBER
- RANK
- DENSE_RANK
- Moving sums
- Moving averages.
8. Triggers
- Triggers
- Trigger components
- Trigger options
- Trigger body statements
- BEFORE, AFTER & IINSTEAD OF triggers
- Trigger examples
- Trigger performance.
9. Advanced INSERT, UPDATE, DELETE & MERGE Options
- 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.
10. Identity Columns & Sequences
- Identity columns
- Sequences
- Changing attributes
- Using identity columns & sequences in SQL statements.