DB03 - DB2 z/OS for COBOL / PL1 Developers - 3 Days

Course Description

This course provides the applications programmer with an in-depth knowledge of the DB2 development process. It is aimed at programmers who are already familiar with SQL and need to code or support DB2 application programs written in either COBOL or PL1.

Pre-requisites

The delegate should be familiar with the z/OS host environment, together with a working knowledge of either COBOL or PL/I program development using TSO / ISPF. In addition, a working knowledge of DB2 SQL is required. This can be acquired by attending our SQL Workshop (DB08).

Objectives

The aim of this course is to provide the programmer, already familiar with DB2 SQL, with the necessary skills required to access and manipulate DB2 data via a COBOL or PL1 application program.

On completion of this course the student will be able to:

  • use both permanent and temporary tables
  • develop, prepare and execute DB2 programs
  • bind packages and plans
  • use non-scrollable and scrollable cursors
  • use multi-row INSERT and FETCH processing
  • describe the locking process used by DB2
  • define and use Referential Integrity
  • use Identity Columns and Sequences
  • use the LOAD utility to populate tables

Environment

Development will be performed using:

  • DB2 for z/OS Versions 10, 11 or 12

Customisation

For on-site courses (i.e. at your premises), we are more than happy to tailor the course agenda to suit your exact requirements. In many cases, we are able to build your in-house standards and naming conventions into the delivered course.

Course Details

APPLICATION PROGRAMMING
DB2 Environments
Important Note
Development Cycle With DB2
SQL Statement Format - Cobol
SQL Statement Format - Pl/I
Table Declaration
SQL Statements Used In Application Programs
SQL Include
SQL Communication Area
Retrieving Data Into Host Variables
Ambiguous Host Variables
SQL Error Codes
The SQLca - SQL Communications Area
Decoding The SQLca In A Program
Singleton Selects
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
Insensitive Cursor
Sensitive Static Cursor with Insensitive Fetch
Sensitive Static Cursor with Sensitive Fetch
Sensitive Dynamic Cursor
Asensitive Cursor
Scrollable vs Non-scrollable Cursors
Updatable Cursors
Declaring a Scrollable Cursor
Fetching from a Scrollable Cursor
Absolute Fetching Examples
Relative Fetching Examples
Sensitive Dynamic Cursors - Fetching Rows
Sensitive Fetches - Update and Delete Holes
Positioned Updates Using A Sensitive Cursor
Scrollable Cursor Considerations

MULTI-ROW PROCESSING
Multi-row Fetch and Insert
New Syntax for Declare Cursor
Fetching Rowsets
Fetch Examples
Host Variable Arrays
Catering for Update and Delete Holes
Partial Rowsets
Rowsets - SQLCA
Locking Rowsets
Positioned Update
Positioned Delete
Multi-row Insert
Multi-row Insert Syntax
Static & Dynamic Insert
Get Diagnostics
Get Diagnostics - Statement Information
Get Diagnostics - Condition Information
Get Diagnostics - Connection Information
Get Diagnostics - Examples
Diagnostic Information for Multi-Row Fetch
Get Diagnostics Fetch Example
Diagnostic Information for Multi-Row Insert
Get Diagnostics Insert Example
The Merge Statement
Merge Statement Restrictions
Merge Statement Example
Get Diagnostics Recap
Get Diagnostics - Statement Information
Get Diagnostics - Condition Information
Get Diagnostics - Merge Example

PLANS AND PACKAGES
DB2 Bind
Rebind
Packages Overview
Explanation Of 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

IDENTITY COLUMNS AND SEQUENCES
Identity Columns
Identity Columns - Examples
Identity Column Enhancements
Altering Identity Columns
Identity Columns - New Version 8 Parameters
Identity Columns - Retrieving the Generated Number
Identity Columns - Data Sharing Implications
Using Identity Columns with the Load Utility
Sequences
Create Sequence Syntax
Sequence Ordering
Altering Sequences
Dropping Sequences
Using Sequences in Applications
Sequences - Considerations and Restrictions
Sequence Application Examples
Sequences and Identity Columns Comparison

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
Skip Locked Data
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

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

UTILITIES
Utility Overview
Running Utilities
The LOAD / UNLOAD Utilities
Online Load Resume
Online Load Considerations

Course Format

The course contains many practical exercises to ensure familiarity with the product. Delegates develop COBOL or PL/I programs accessing and manipulating data held on 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.

Course Enquiry