DB19 - DB2 Advanced Programming - 2 Days

Course Description

This course is aimed at Programmers who need to understand the major new programming features introduced in recent releases of DB2 for z/OS up to and including Version 12.

Pre-requisites

A strong knowledge and experience in DB2 Applications Development is required to attend this course.

Objectives

The aim of this course is to provide the programmer, already familiar with DB2 applications program development, with the necessary skills required to use some of the more recent development related features of DB2.

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

  • define large objects
  • access and manipulate large objects from a program
  • code Common Table Expressions
  • code Recursive SQL statements
  • use scrollable cursors
  • use rowset processing
  • understand and use the GET DIAGNOSTICS statement
  • use MERGE statements
  • use SELECT FROM INSERT / UPDATE / DELETE / MERGE statements
  • code INSERSECT and EXCEPT statements
  • use Identity Columns and Sequences
  • understand how and when to use Materialized Query Tables

Environment

Development will be performed using:

  • IBM Mainframe
  • DB2 for z/OS or DB2 LUW - Versions 9, 10 or 11
  • COBOL, PL1 or Java

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

LARGE OBJECTS
Large Objects (LOBs)
LOB Definition and Manipulation
Base Table Definition
LOB Tablespace and Auxiliary Table Requirements
LOB Tablespace Definition
Auxiliary Table Definition
Auxiliary Table - Index Definition
Loading the LOB Data
LOB Data Options
LOB Data Manipulation
LOB Programming Issues
Declaring LOB Variables
Example using LOB Variables
LOB Manipulation
Data Spaces for LOB manipulation
Using LOB Locators
LOB Locator Considerations
Example using LOB Locators
LOB Limitations
File Reference Variables
Defining File Reference Variables
File Reference Variables - Example
Loading Data using File Reference Variables
File Reference Variables - New DSNZPARM
Fetch Continue
Fetch Continue Example
Fetch Continue Considerations

RECENT SQL ENHANCEMENTS
Common Table Expressions
Writing a Common Table Expression
Common Table Expression Example
Common Table Expression Considerations
Recursive SQL
Recursive SQL Example
Recursive SQL - Controlling Depth of Recursion
The Merge Statement
Merge Statement Restrictions
Explaining Merge Statements
Merge Statement Authorisation
Select From Merge / Update / Delete Statements
Select from Insert
Select from Merge
Select from Update
Select from Delete
Order By and Fetch First in Subselect
The Order By Order Of Clause
The Truncate Statement
Using Truncate With Multi-Level Security
Union, Intersect and Except
Bigint - Data Type
Binary / Varbinary - Data Types
Decfloat - Data Type

PROGRAMMING ENHANCEMENTS
Scrollable Cursors
Insensitive Cursor
Sensitive Static Cursor with Insensitive Fetch
Sensitive Static Cursor with Sensitive Fetch
Fetching from a Scrollable Cursor
Sensitive Fetches - Update and Delete Holes
Sensitive Fetches - Updated Data
Sensitive Static Cursor with Sensitive Fetch
Sensitive Dynamic and Asensitive Cursors
Sensitive Cursors - Fetching Rows
Scrollable Cursor Considerations
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
Fetch First 'n' Rows
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

APPLICATION PERFORMANCE ENHANCEMENTS
Optimistic Locking
Optimistic Locking Example
Row Change Timestamp Selection
Row Change Timestamp Insertion
Skip Locked Data
Index On Expression
Index On Expression Considerations
Relief for Sequential Key Insert
Randomized Index Ordering
Reordered Row Format

IDENTITY COLUMNS AND SEQUENCES
Identity Column Review
Identity Column Enhancements
Altering Identity Columns
Identity Columns - New Parameters
Identity Columns - Data Sharing Implications
Using Identity Columns with the Load Utility
Catalog Table Changes
Sequences
Create Sequence Syntax
Sequence Ordering
Altering Sequences
Dropping Sequences
Sequence Authorities
Using Sequences in Applications
Sequences - Considerations and Restrictions
Sequence Application Examples
Consumed Values / Gaps in a Sequence
Duplicate Sequence Values
Sequence Cycle Considerations
Defining a Constant Sequence
Cache Considerations
Sequences and Identity Columns Comparison

MATERIALIZED QUERY TABLES
What Are Materialized Query Tables?
MQT Features
Creating an MQT
Create MQT Example
Altering an MQT
Alter MQT Example
MQT Fullselect Features / Restrictions
Refresh Table
Populating User Maintained MQTs
Automatic Query Rewrite using MQT
Enabling Automatic Query Rewrite
Enabling Automatic Query Rewrite - DDL Options
Enabling Automatic Query Rewrite - Special Registers
AQR - Using Both Registers
Enabling Automatic Query Rewrite - System Properties
Enabling Automatic Query Rewrite - Query Properties
AQR Examples
Determining if Query Rewrite Occurred
MQTs and Referential Integrity
MQTs and RI - Informational Constraints

TEMPORAL TABLES
Temporal Tables
Temporal Tables and Versioning
Temporal Tables – Versioning Example
System Temporal Tables – Data Access
Temporal Tables – Considerations
Temporal Tables – Application Controlled
Business Temporal Tables – Data Selection
Business Temporal Tables – Update and Delete
Business Temporal Tables – Update Example
Business Temporal Tables – Delete Example

Course Format

The course contains many practical exercises to ensure familiarity with the product. On completion of this course students will be able to develop application programs which use advanced programming techniques.

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