DB2 for z/OS Application Programming

Objectives
On successful completion of this course, attendees 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 and DELETE 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.

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 would also be a big advantage.
Duration
5 days

Course Code
DAPW
Contents
Relational concepts overview
The relational theory and relational model; an overview of RDBMS theory.


DB2 data & system overview
Data storage components; the DB2 catalog, directory, address spaces and buffer pools.
Describes the major components of the DB2 system and databases and clarifies terminology.

Basic DML
Selecting all columns; row & column control; multiple conditions; special operators; the escape character; arithmetic in DML; using constants; special registers; concatenation; date and time columns.
Provides sufficient knowledge to code basic SELECT statements.

Built-in functions
Column functions; scalar functions; 'group by' and 'having' clauses.
Introduces built-in functions.

Joins and nested table expressions
Joins; correlation names; Cartesian product; inner and outer joins; nested table expressions.
Some of the more complex SQL code.

Union and case expressions
Union, union all, and case expressions.
Further expansion of the SELECT statement.

Subqueries
Simple subqueries; correlated subqueries; 'IN', 'NOT IN' 'NOT EXISTS'; quantified predicates, effect of nulls on SQL statements.
Introduces the nesting of statements.

Table modification commands
Insert; inserting multiple rows; update and delete statements; the impact of referential integrity constraints
Completes the DML syntax covering how to make changes to data.

Access path evaluation and EXPLAIN
DB2 optimizer, EXPLAIN facility, available access paths, catalog columns affecting access path, EXPLAIN output, PLAN_TABLE, DSN_STATEMNT_TABLE and DSN_FUNCTION_TABLE contents.
This segment describes how the EXPLAIN feature may be used to ensure that SQL is performing as efficiently as possible

Basic application programming
Language support; embedded SQL; the declare table statement; the SQLCA; SQLCODE; host language variables; handling variable length columns & nulls; using host structures; commit and rollback.
Introduces the concept of embedding SQL code in an application programming language.

Cursor processing
Cursor processing, cursors for update, cursor with hold, scrollable cursors.
Describes the method of processing multi-row sets in a record at a time environment.

Program preparation
Precompile, bind, program preparation and execution; plans and packages.
Explains the additional considerations when preparing and executing DB2 application programs.

Locking and concurrency
Reasons for locking, lock types and compatibility, lock options; lock avoidance
Describes the DB2 locking method and.the factors and options that control it.