IBM DB2: Application Programming (IBM, zOS, TSO ISPF, JCL, MVS)

Overview

Our IBM Mainframe Technologies Overview course provides an overview of IBM z/OS architecture, and related technologies.

This course aims to provide delegates with the skills necessary to design and develop commercial applications for the z/OS DB2 platform.

This training may be available onsite; please contact us if you are interested.

Audience

Systems Analysts and Application Programmers Systems & Database Administrators (as a first stage of training)

Skills Gained

• DB2 introduction
• Basic and Advanced SQL
• Creating DB2 Objects
• Application programming to read and update tables
• Description of Logical Relationships
• Introduction to other DB2 features

Prerequisites

COBOL or PL/1 programming experience, and program edit and submission knowledge.

Course Outline

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