DB2 Advanced Programming

Course Objectives
The aim of this Course is to provide the programmer, already familiar with DB2 applications program development using COBOL or PL1, 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 COBOL or PL1 program
• understand Schemas and Schema Paths
• understand and use Distinct Types (User Defined Types)
• write and implement User Defined Functions
• write and implement Stored Procedures
• define and test Triggers
• use Query Result Sets within Stored Procedures
• use Scrollable Cursors
• use Global Temporary tables
• use Declared Temporary tables
• use Savepoints within an application program
• define and use Identity Columns

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
SCHEMAS
The Grant Schema Statement
Schema Path - Bind Option
Current Path - Special Register
Overriding the Search Path
WHAT ARE STORED PROCEDURES?
Overview
Executing a Stored Procedure - the Call Statement
Execution Flow
STORED PROCEDURE SETUP
Defining the Stored Procedure
Changing a Stored Procedure Definition
Deleting a Stored Procedure Definition
STORED PROCEDURES - CLIENT SIDE
Basic Concept
Passing Nulls
Using Indicator Variables to Speed Processing
Receiving Nulls in a Stored Procedure
Common SQL Codes
STORED PROCEDURES - SERVER SIDE
Stored Procedure Language Requirements
Stored Procedure Restrictions
Preparing a Stored Procedure
Special Considerations for C and PL/I
Receiving parameters into a Stored Procedure
The Four Calling Conventions
The Four Receiving Conventions
Package Requirements
WORKLOAD MANAGER AND SPAS
Overview
DB2 SPAS
WLM Address Spaces
Summary of WLM Advantages
STORED PROCEDURES AUTHORITIES
Stored Procedure Authorisation
Authorisation Checking when calling a Procedure
QUERY RESULT SETS
Query Result Sets
Objects from which you can return Result Sets
Requirements for Query Result Sets
New Embedded SQL Statements
Query Results Sets Example
Declare Cursor with Return
Definition of Result-Set-Locator Variables
Associate Locators
The Allocate Cursor Statement
Describe Cursor
Describe Procedure
Using Global Temporary Tables
DISTINCT TYPES
Distinct Types (User-defined Data Types)
Create Distinct Type Statement
UDT Allowable Operations
Generated Cast Functions
Using Cast Functions
Defining a Sourced Function with Distinct Types
Defining a Sourced Function for an Operation
CAST Specifications
Cast Specifications in Application Programs
Distinct Type Privileges
Catalog Information
Dropping Distinct Types
USER DEFINED FUNCTIONS
User-Defined Functions
Built-in Functions
Creating External Functions
Creating Sourced Functions
Sourced Function Examples
Creating External Scalar Functions
External Scalar Function Examples
Creating External Table Functions
External Table Function Example
Parameter Options for External / Table Functions
User-Defined Function Parameters Summary
Implementing an External Function
Step 1 - Write the Function
The Half. C Function
The Half..PL/I Function
Translating DB2 Datatypes to C Program Variables
Step 2 - Preparing a User-Defined Function for Execution
Step 3 - Define the Function to DB2
Step 4 - Test the Function
Function Authorisation
Function Execution Environment
Dropping a Function
Using Explain for Function Resolution
The Stop Function Command
The Start Function Command
The Display Function Command
Changes to Catalog Tables
Supplied Functions
New Column Functions
New Scalar Functions
TRIGGERS
Trigger Parts
The Create Trigger Statement
Before and After Triggers
Invoking Stored Procedures and User-Defined Functions
Using Transition Tables
Allowable Combinations
Error Handling
Trigger Cascading
Ordering of Multiple Triggers
Triggers and Referential Integrity
Trigger Authorisation
Trigger Packages
Catalog Information for Triggers
Removing Triggers
Performance Considerations
SQL ENHANCEMENTS
Identity Columns
Identity_Val_Local Function
Savepoints
Global Temporary Tables
Declared Temporary Tables
Global Transactions
Greater use of Unions
Fetch First 'n' Rows Only Clause
Fetch First vs Optimize For
Using Fetch First for Singleton Selects
SCROLLABLE CURSORS
Declaring a Scrollable Cursor
Scrollable vs Non-scrollable Cursors
Updatable Cursors
Declaring a Scrollable Cursor
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
Scrollable Cursor Recommendations
Course Environment Development will be performed using:

• IBM Mainframe
• COBOL or PL1

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.