DB2 for z/OS: Application Programming - Advanced Topics


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

This course is also available for one-company, on-site presentations and for live presentation over the Internet, via the Virtual Classroom Environment service.

Classroom dates (High Wycombe) - click to book!

13 November 2017 29 January 2018

Objectives

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)

£1495 (ex VAT)

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.


What the students say

Thanks for an excellent course, will continue to recommend RSM to my colleagues.

Senior Mainframe Developer

Royal Bank of Scotland Group

© RSM Technology 2017