Target Audience

This course provides the applications programmer with an in-depth knowledge of the construction and installation of DB2 Stored Procedures, User Defined Functions and Triggers. During the course, Stored Procedures and UDFs may be written in a choice of languages. If necessary the course can cover the automated generation of Stored Procedures either using the Stored Procedure Builder, WSAD or RAD. The course can also optionally include tuition of the SQL Procedural Language (SQL-PL).

Pre-requisites

The delegate should be familiar with the host environment, together with a working knowledge of DB2 program development in COBOL, PL1 or Java.

Course Objectives

The aim of this course is to provide the programmer, already familiar with DB2, with the necessary skills required to generate (if required), code, install and test DB2 Stored Procedures, User Defined Functions and Triggers. An optional objective is to be able to write procedures in SQL-PL. On completion of this course the student will be able to:

•     understand Schemas and Schema Paths
•     write and implement Stored Procedures coded in COBOL, PL1 or Java
•     write and implement UDFs coded in COBOL, PL1 or Java
•     define and understand Triggers
•     use Query Result Sets within Stored Procedures
•     use Global Temporary tables within Stored Procedures
•     use Declared Temporary tables within Stored Procedures
•     use the Stored Procedure Builder to generate Stored Procedures (optional)
•     use the Websphere Studio Application Developer to generate Stored Procedures (optional)
•     code Stored Procedures written in SQL-PL (optional)

Course Environment

Development will be performed using DB2 running on:

•     LUW
•     OS/390
•     z/OS

Stored Procedures can be written in a choice of the following languages:

•     COBOL
•     PL1
•     Java
•     SQL-PL

Stored Procedures can, if required, be generated using:

•     Stored Procedure Builder
•     Websphere Studio Application Developer

Course Details

WHAT ARE STORED PROCEDURES?

Overview

Reduction in Network Traffic

Stored Procedure Advantages

DB2 Address Spaces

Defining an External Stored Procedure

Executing a Stored Procedure - the Call Statement

Error Handling within Stored Procedures

Execution Flow

SCHEMAS

Schemas

The Grant Schema Statement

Schema Path - Bind Option

Current Path - Special Register

Overriding the Search Path

STORED PROCEDURE DEFINITION

The Create Procedure Statement

Stored Procedure Parameters

Allowable SQL Statements

Create Procedure Example

The Alter Procedure Statement

Deleting a Stored Procedure Definition

Defining a Java Stored Procedure

Java Stored Procedures - Jar Installation

CALLING STORED PROCEDURES

The Call Statement

Passing Parameters

Passing Nulls

Common SQL Codes Returned from the Call

Package Requirements

Calling A Stored Procedure From COBOL

Calling A Stored Procedure From REXX

Calling A Stored Procedure From Java

Java - Getting a Connection using DataSources

Java Naming and Directory Interface - JNDI

Java - Setting Up Connection Pooling using DataSources

Java - Getting Database Connections via a DataSource

Java - Handling Result Sets

CODING A STORED PROCEDURE

Stored Procedure Language Requirements

Stored Procedure Restrictions

Using Commit and Rollback

Using Re-Entrant Code

Main Program or Sub-Program?

Preparing a Cobol, PL1 or C Stored Procedure

Preparing a Java Stored Procedure

Package Requirements

Receiving Parameters into a Stored Procedure

Using Dbinfo with Parameter Style Db2sql

Coding a Stored Procedure in Cobol

Coding a Stored Procedure in PL/1

Coding a Stored Procedure in Java

Coding a Stored Procedure in Java - Example

Java - Handling Nulls

Java - Parameter / Data Type Mappings

Error Handling

Error Handling Using Mode DB2SQL - Setting Sqlstate

Error Handling Using Mode DB2SQL - Cobol Example

Java Error Handling - Technique 1

Java Error Handling - Technique 2

PROCEDURE EXECUTION

Overview

DB2 SPAS

WLM Address Spaces

Setting Up The WLM Environment

Comparison Of WLM vs SPAS Procedure Management

Summary of WLM Advantages

Access To Non-SQL Resources

Resource Recovery Services Attach Facility (RRSAF)

The Display Procedure Command

Starting and Stopping Procedures

WLM Operational Commands

Using Explain for Function Resolution

z/OS Procedures - Dynamic Writing to Datasets

STORED PROCEDURE AUTHORITIES

Stored Procedure Authorities

Stored Procedure Authorisation

Authorisation Checking when Calling a Procedure

DYNAMIC RESULTS SETS

Dynamic Results Sets

Objects From Which You Can Return Result Sets

Requirements for Dynamic Result Sets

Cursor Processing Within the Stored Procedure

Dynamic Result Set Embedded SQL Statements

Query Results Sets Example

Declare Cursor With Return

Definition of Result-Set-Locator Variables

Associate Locators

The Allocate Cursor Statement

Returning Result Sets in Java

Java - Processing Result Sets

Testing For Optional Result Sets

Using Global Temporary Tables

Declared Temporary Tables

Declared Temporary Table Considerations

Declared Temporary Tables - Comparisons

SQL PROCEDURES LANGUAGE

The SQL Procedures language

An SQL Procedure Example

Building SQL Procedures?

JCL Example

SQL Procedure Supported Statements

Terminating Statements in an SQL procedure

Begin and End Statements (Compound Statements)

Declaring Host Variables

Assigning Values to Variables - The SET Statement

CASE Statement

Comments

Cursor Operations

FOR Statement

GET DIAGNOSTICS Statement

GOTO Statement

IF Statement

Comparison Operators

LEAVE and ITERATE Statements

LOOP statement

REPEAT Statement

RETURN Statement

WHILE statement

Handling Errors in an SQL Stored Procedure

Testing for Errors - SQLCode and SQLState

The Declare Handler Statement

Condition Handler Execution Path

Declaring Conditions for Handlers

Dynamic SQL statements

Returning Result Sets

Processing Result Sets From Other Stored Procedures

SIGNAL Statement and Message_Text Variable

RESIGNAL Statement

Coding Considerations

STORED PROCEDURE BUILDER

Overview

Building DB2 Stored Procedures

Creating a New Stored Procedure

SPB SmartGuide

SPB SmartGuide - Procedure Name

SPB SmartGuide - Pattern

SPB SmartGuide - SQL Statement

SPB SmartGuide - Parameters

SPB SmartGuide - Options

The SQL Assistant

Building The Stored Procedure

Debugging Stored Procedures

Setting Breakpoints

WSAD PROCEDURE BUILDER

Connecting To DB2 from WSAD

Creating a Project

Importing a Database

Creating a Stored Procedure

Generate an SQL Statement

Parameter Specification

Testing the Query

Amending the Procedure

Building the Stored Procedure

Specifying Run Settings

Running the Stored Procedure

USER DEFINED FUNCTIONS

User-Defined Functions

Built-in Functions

Creating External Functions

The Create Function Statement

Function Parameters

Function Parameters Summary

Sourced Function Examples

External Scalar Function Examples

Creating External Table Functions

External Table Function Example

Implementing an External Function

Step 1 - Write The Function

Function Example - Cobol

Function Example using Scratchpad - Cobol

Function Example - C

Function Example - PL/1

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

TRIGGERS

Triggers

Trigger Parts

The Create Trigger Statement

Before and After Triggers

Trigger Examples

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

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 Stored Procedures in either COBOL, PL1, Java or SQL and optionally be able to generate Stored Procedures using SPB or WSAD. 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.