Db2 for z/OS: Application Programming Workshop

Virtual Classroom Environment dates - click to book!

UK Start Times

What do I need?

• webcam
• headphones with microphone
• sufficient bandwidth, at least 1.5 Mb/s in each direction.

What you will learn

On successful completion of this course you will be able to:

• use SPUFI to code SQL statements
• code SQL SELECT statements including those using techniques such as joins, nested table expressions, subqueries, unions and case expressions
• code SQL INSERT, UPDATE, DELETE and MERGE statements to modify data
• identify access paths chosen by Db2 by using the EXPLAIN feature
• embed SQL statements into an application programming language such as COBOL, PL/I or Assembler
• understand and control the locking strategy used by Db2 to maintain data integrity
• prepare and execute Db2 applications and understand the use of precompile and bind steps in the preparation process.

Who Should Attend

Those responsible for writing and/or maintaining Db2 application programs.

Prerequisites

Attendees must be familiar with a major application programming language, e.g. COBOL, PL/I or Assembler. Previous exposure to the z/OS environment and the ability to use TSO/ISPF and JCL would also be a big advantage.

Duration

5 days

Fee (per attendee)

£2205 (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

DAPW

Contents

DBMS Overview

Database Management Systems; Hierarchical database structure; Network database structure; Relational database structure; Database comparison; Table structure; A brief history of Db2.

Relational Theory & Concepts

Dr. E F Codd's 12 rules; The relational model; Structure - tables; Structure - rules for columns and rows; Structure: candidate keys, primary and alternate key, foreign keys; Manipulation: relational algebra, projection, restriction, join, union, intersection, difference, product; Integrity - domain and user-defined integrity; Integrity - entity and referential integrity; Referential integrity - terminology; Tablespace set; Referential integrity - defining constraint; Constraint definition restrictions; Data manipulation restrictions; Catalog entries; Referential Integrity and INSERT; Referential Integrity and UPDATE; Referential Integrity and DELETE; Referential Integrity review.

Db2 System Architecture

Architecture overview; Working Storage Areas; Buffer, Sort, RID & EDM pools; Buffer Pools - 64 bit addressing; EDM pool; Sort pool; RID pool; Db2 Attachments; System datasets & databases; DB2 objects; Object characteristics; SQL overview; Db2 Interactive (Db2I); Basic Db2 operations; Db2 commands; Db2I commands panel.

Introduction to Structured Query Language

Structured Query Language; Data Definition Language; Data Control Language; Data Manipulation Language: SELECT, INSERT, UPDATE, DELETE, MERGE; COMMIT / ROLLBACK; DB2 optimizer..

Introduction to Db2 Interactive (Db2i)

Db2I primary option menu; Db2i option D - DB2i defaults; Db2i option 1 - SPUFI; Current SPUFI defaults; SPUFI SQL statement creation; Browsing SPUFI output; SPUFI commit or rollback panel; SPUFI AUTOCOMMIT options; Db2i option 2 - DCLGEN (Declarations Generator); DCLGEN output; Db2i option 3 - Program Preparation; Db2i option 4 - Precompile; Db2i option 5 - Bind / Rebind / Free; Db2i option 6 - Run; Db2i option 7 - DB2 commands; Db2i option 8 - Db2 utilities; Query Management Facility; Reporting options - SPUFI versus QMF 154; SPUFI report; QMF report.

Basic SELECT Statements

Supplied database structure; Supplied table formats; Specifying table names; Using view, alias or synonym; Specifying column names; Selecting all columns; |Column sequence; Naming columns; Eliminating duplicate rows; Row sequence; Row restriction; Comparing columns from the same row; Multiple conditions; The BETWEEN operator; The IN operator; The LIKE operator; The SQL ESCAPE character; Searching for apostrophes; Arithmetic in SELECT statements; Arithmetic in the WHERE clause; Naming derived columns; Using literals in the SELECT list; Special registers; The concatenation operator; Date and time columns.

SQL Built-in Functions

Column functions; GROUP BY; HAVING; Rules for GROUP BY / HAVING; Scalar functions; Data conversion functions; Date manipulation functions; Numeric manipulation functions; String manipulation functions; Handling null values; User defined functions.

Joins & Nested Table Expressions

Inner JOIN; Correlation names; A JOIN of three tables; Joining a table to itself; A three-way join of two tables; Cartesian product; Outer join; Full outer join; Full outer join using COALESCE; Left / right outer joins; Nested table expressions - example.

Unions, Exceptions, Intersections & CASE Expressions

UNION, INTERSECT and EXCEPT; UNION and UNION ALL; UNION with named derived result columns; INTERSECT and EXCEPT; CASE expressions; CASE expression formats; CASE expressions in functions; CASE expressions in the WHERE clause.

Subqueries

Simple (non-correlated) subquery; Simple subquery with multiple rows; Subquery with multiple columns; Quantified predicates; Correlated subquery; Tests for existence with correlated subqueries; Performance considerations; Null values and SQL.

Table Modification Statements

.The INSERT statement; Inserting all columns, single row; Inserting a subset of columns, single row; Inserting multiple rows using SELECT; Inserting multiple rows using row set; Inserting using expressions; The UPDATE statement; Updating rows; Updating & case expressions; Updating and subselects; The DELETE statement; Deleting rows; The MERGE statement; Merging rows; Referential Integrity & INSERT; Referential Integrity & UPDATE; Referential Integrity & DELETE.

The Db2 Optimiser and Access Path Selection

Db2 Optimiser; Access path selection; Access paths - index usage; Access paths - direct row access; Access paths - matching index access; Access paths - non-matching index access; Access paths - table(space) scan; Input to the optimiser; Filter factors; Filter factor and clustering; Filter factor and column cardinality; Filter factor and column correlation; Filter factor and column distribution; Influencing the optimiser; Influencing the optimiser - manually adjusting statistics; Influencing the optimiser - using optimisation hints; Influencing the optimiser - modelling production values; Catalog statistics; Updating catalog statistics using RUNSTATS; Statistics columns; RUNSTATS examples.

Db2 EXPLAIN

XPLAIN; EXPLAIN table enhancements; EXPLAIN syntax enhancements; PLAN_TABLE (DB2 version 1); PLAN_TABLE (Versions 2 to 6); PLAN_TABLE (Db2 versions 7 to 11); DSN_STATEMNT_TABLE; DSN_FUNCTION_TABLE; EXPLAIN example 1 (basic access paths); EXPLAIN example 2 (multi-index access); EXPLAIN example 3 (nested queries).

Basic Db2 Application Programming

Embedded SQL; The DECLARE TABLE statement; Host language variables; Host language variable data types; Variable length columns; Variable length columns and LIKE; Handling null values; Setting null values; Extended indicator variables; Using host language structures; Nulls and host language structures; Indicator variables and insertions; COMMIT and ROLLBACK; Explicit COMMIT and ROLLBACK; SAVEPOINTS; Error handling; SQLCODE & SQLWARNn flags; Other SQLCA fields; The Declarations Generator (DCLGEN); DCLGEN output - DECLARE TABLE; DCLGEN output - Host language structure; Including DCLGEN output.

Cursor Processing

Multi-row processing; Cursor processing; Using a cursor to update data; Cursor WITH HOLD; Ambiguous cursors; Read-only cursors; Scrollable cursors; Insensitive scrollable cursors; Sensitive static scrollable cursors; Sensitive dynamic scrollable cursors; Controlling fetch sensitivity; Controlling cursor scrolling; Scrollable cursor review.

Program Preparation & Execution

Processing SQL statements; Program preparation overview; Db2 precompile; Db2 BIND; BIND alternatives - plan only; BIND alternatives - packages; Packages & collections; BIND parameters; Plan management & access path stability; Plan management (RE)BIND parameters; Packages - varying BIND options; Packages - using mirrored tables; Packages - using versions; Program execution - TSO; Program execution - IMS; Program execution - CICS.

Db2 Locking & Concurrency

Controlling concurrent access; Claims and drains; Claim classes; Drains; Utility restrictive states; Transaction locking; Reasons for locking - preventing lost updates; Reasons for locking - preventing reads of uncommitted data; Reasons for locking - allowing repeatable reads of data; Lock control; Lock options - installation parameters; Lock options - tablespace creation; Lock options - SQL statements; Locking hierarchy; Lock compatibility - row and page locks; Lock compatibility - table and tablespace locks; Lock options - BIND parameters; BIND - ACQUIRE and RELEASE parameters; BIND - ISOLATION; BIND - CURRENTDATA; Lock avoidance; Lock avoidance example; Locking and concurrency recommendations; Monitoring locking - DB2 commands; Monitoring locking - LOCKINFO; Monitoring locking - DB2PM / DB2PE reports.