Target Audience

This course provides the applications programmer with an in-depth knowledge of the DB2 development process together with an understanding on the DB2 design process. It is aimed at programmers who need to design, code or support DB2 application programs written in COBOL, PL1 or C.

Pre-requisites

The delegate should be familiar with the z/OS host environment, together with a working knowledge of either COBOL, PL/I or C program development using TSO / ISPF.

Course Objectives

The aim of this course is to provide the programmer, unfamiliar with DB2, with the necessary skills required to define, access and manipulate DB2 data, either via an application program or using SPUFI. On completion of this course the student will be able to:

•     use design techniques such as Normalisation
•     understand the performance issues involved in constructing a system
•     implement an efficient tablespace, table and index design
•     set up a DB2 test environment, using correctly defined tables, views, indexes, synonyms and aliases
•     use both permanent and temporary tables
•     use the DB2I toolset, including SPUFI
•     code SQL statements to read and manipulate DB2 data
•     develop, prepare and execute DB2 programs
•     bind packages and plans
•     use non-scrollable and scrollable cursors
•     describe the locking process used by DB2
•     create Triggers
•     define and use Referential Integrity
•     define and use Table Check Constraints
•     describe the security features of DB2
•     produce EXPLAIN output using a PLAN_TABLE
•     use the LOAD utility to populate tables

Course Environment

Development will be performed using:

•     IBM Mainframe

Course Details

INTRODUCTION TO DB2

The Relational Model

Data Representation

The DB2 Environment

DB2 Table Structure

DB2 Data Types

DB2 Catalog

DB2 Interactive

SPUFI

Accessing The Data

Interfaces to DB2

SQL Structure

Embedded SQL

DB2 Data Relationships

Access Path Selection

Database Design / Data Analysis

Overview of Normalisation

First Normal Form

Order Form Example

Second Normal Form

Third Normal Form

Check 3NF

THE DESIGN PROCESS

Introduction - The Basic Elements of a Database

The Flat File Database

The Hierarchical Database

The Relational Database

Relational Database Advantages

Database Design Phases

Key Roles in the Design Process

CONCEPTUAL DESIGN

Why Analyse?

Who Will be Involved in the Analysis?

Entities

Entity Relationships - A Worked Example

Entity Relationship Diagrams

Relationship Types

Many to Many Relationships - Association Entities

Common Parentage

Attributes

LOGICAL DATABASE DESIGN

Overview of Normalisation

Benefits of Normalisation

Primary and Foreign Keys

Primary Key Considerations

The Normalisation Process

First Normal Form

Second Normal Form

Third Normal Form

Fourth Normal Form

Fifth Normal Form

Normalisation Check - Homonyms and Synonyms

Conversion of Entities to Base Tables

Attributes

Identification of Keys

PHYSICAL DATABASE DESIGN

Introduction

Gathering of Required Information

Data Flow Diagrams

Access Path Requirements

Entity Life Histories

Data And Relationship Volumes

Denormalisation Considerations

Denormalisation Techniques

RI Requirements

Index Requirements

Locking Requirements

Locking Considerations

View Requirements

DATA DEFINITION LANGUAGE

The Structure of DB2 Objects

Definition of DB2 Objects - DDL

Database Definition

Tablespace / Page Organisation

Page Sizes

Tablespaces

Tablespace Layout

Types of Tablespace

Tablespace Definition

Tables

Table Definition

Table Names

Copying Table Definitions

Rename Table

Column Types

Null Values

User Defined Default Values

Identity Columns

Identity Columns - Examples

Identity Columns - Considerations

Identity Columns - Restrictions

Identity_Val_Local Function

Temporary Tables

Global Temporary Tables

Declared Temporary Tables

Declared Temporary Table Considerations

Declared Temporary Tables Comparisons

Views

Read Only Views

Views - With Check Option

Views Based Upon Other Views

Creating a View of Two Tables

Synonyms and Aliases

Indexes

Index Organisation - The B Tree Index

Index Clustering

Non-Unique Indexes

Index Definition

Partitioning Indexes

Index Design Considerations

The Alter Statements

The Drop Statements

DB2 INTERACTIVE (DB2I)

DB2I Options

SPUFI - SQL Processor Using File Input

Running Queries

SPUFI Defaults

SPUFI - Setting AUTOCOMMIT to NO

DB2 Commands

DB2 Utilities

The DB2I Defaults Panel

DATA MANIPULATION LANGUAGE

SQL - Structured Query Language

DB2 Environments

SQL Features

SQL Query Results

The SELECT Statement

The 'As' Clause

Column concatenation

Expressions

Functions

Special Registers

The WHERE Clause

Special Operators

NOT Operand

IN Operand

LIKE Operand

BETWEEN Operand

Statements Using Nulls

Column Functions

Using 'Distinct'

The GROUP BY Clause

The HAVING Clause

The ORDER BY Clause

Fetch First 'n' Rows Only Clause

Special Registers

Current Date

Current Time

Current Timestamp

User Keyword

The UPDATE Statement

Update with Subselect

The DELETE Statement

The INSERT Statement

The Mass Insert Statement

Functions

Scalar Functions

Function Examples

Date, Time and Timestamp Functions

The Case Statement

JOINS

Cartesian Joins

Inner Joins

Outer Joins

Nested Table Expression

The UNION Statement

Subqueries

Subqueries Using in

Exists

The 'All' Subquery

The 'Any' Or 'Some' Subquery

APPLICATION PROGRAMMING

DB2 Environments

Development Cycle With DB2

Precompilation

DBRM's

DCLGENs

Plans

Packages

SQL Statement Format - COBOL

SQL Statement Format - PL/1

SQL Statement Format - C

Table Declaration

SQL Statements Used in Application Programs

SQL INCLUDE

SQL Communication Area

Decoding The Sqlca in a Program

Retrieving Data Into Host Variables

Ambiguous Host Variables

SQL Error Codes

Singleton Selects

Cursors

Using a Cursor To Retrieve a Result Set

Declare Cursor

Open Cursor

Fetch a Row

Row Update

Row Deletion

Close Cursor

With Hold Option

The Optimize Statement

Fetch First 'n' Rows Only Clause

Fetch First vs Optimize for

Handling Nulls

Retrieving System Registers

ROWID - Direct Row Access

Direct Row Access - Example

Table Names in Application Programs

Batch Execution

SCROLLABLE CURSORS

Scrollable Cursors

Declaring a Scrollable Cursor

Scrollable vs. Non-scrollable Cursors

Updatable Cursors

Fetching from a Scrollable Cursor

Absolute Fetching Examples

Relative Fetching Examples

Insensitive and Sensitive Cursors

Fetch Sensitivity for Sensitive Cursors

Sensitive Fetches - Update and Delete Holes

Sensitive Fetches - Updated Data

Scrollable Cursor - Locks on the Base Table

PLANS AND PACKAGES

Bind

Rebind

Plans and Packages

Advantages of Using Packages

Binding Packages

Binding Plans

Defaults for Binding

Binding in Batch

Identifying a Collection Within a Program

Program Execution in Batch

Plan Names in Application Programs

LOCKING

Implications of Concurrent Processing

IMS Resource Lock Manager (IRLM)

DB2 Locking Methods

The Lock Table Statement

Lock Modes

When Locks are Acquired

When Locks are Released

Isolation Levels

Controlling The Isolation Level At SQL Level

Keep Update Locks

Unit of Work in TSO

Commit and Rollback

Declaring Cursors With Hold

CICS Issues

IMS Issues

Savepoints

Savepoint Definition

Savepoints – Considerations and Restrictions

Transaction Deadlocks

Design Considerations

SECURITY

Using DB2 Views

Controlling Resource Access - Data Control Language

DB2 Special Users

Database Privileges

Table Privileges

Authorisation of Plans

Plan / Package Privileges

Retain Execution Authority

Primary, Secondary and Current Authids

Grant Examples

Revoke Examples

Grant and Revoke Notes

SYSTEM CATALOG

Catalog Contents

Catalog Tables

Catalog Access

TRIGGERS

Triggers

Trigger Parts

Before and After Triggers

Trigger Examples

Error Handling

Trigger Cascading

Ordering of Multiple Triggers

Trigger Authorisation

Catalog Information for Triggers

Removing Triggers

REFERENTIAL INTEGRITY

What Is Referential Integrity?

Parent and Dependent Tables

The Primary Key

The Foreign Key

Referential Constraint Rules

Constraint Names

More Complex Referential Structures

Check Pending Status

Resetting Check Pending Status

Referential Integrity Access

Design Considerations

TABLE CHECK CONSTRAINTS

Overview of Check Constraints

Constraint Syntax

Allowable Constraints

When are Constraints Enforced?

Current Rules

When Is Check Pending Set

PERFORMANCE AND TUNING

The DB2 EXPLAIN Function

SQL Explain Statement

Plan Table Layout

Plan Table Column Definitions

Predicate Evaluation

Indexable Predicates

Stage 1 and Stage 2 Predicates

Predicate Evaluation Sequence

UTILITIES

Introduction

Utility Overview

Running Utilities

The LOAD Utility

Online Load Resume

Online Load Considerations

Course Format

The course contains many practical exercises to ensure familiarity with the product. Initially students create Tables and Indexes, and then proceed to develop COBOL, PL/I or C programs accessing the data held on the DB2 Tables. On completion of this course students will be ready to start the development of application programs accessing DB2 data. The comprehensive Student Guide supplied is fully indexed serving as a useful reference tool long after the course has finished. Delegates will also be able to access a free help-line with technical questions relating to topics covered on the course. An IBM SQL REFERENCE SUMMARY HANDBOOK is provided free to all attendees as part of the training course.