DB2 Stored Procedures & Functions Workshop

Audience

This course provides the applications programmer with an in-depth knowledge of the construction and installation of DB2 Stored Procedures and User Defined Functions. During the course, Stored Procedures and UDFs will be written using a choice of programming language. If necessary the course can cover the automated generation of Stored Procedures using IBM Data Studio. The course can also optionally include tuition of the SQL Procedural Language (SQL-PL).

Prerequisites

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

Duration

2 days (using 1 external programming language) or 3 days (using 1 programming language plus SQL-PL).  Hands on.

This course is available on site only. Please call for details.

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:

• define and code Stored Procedures using COBOL, PL1 or Java
• execute Stored Procedures
• code Stored Procedures using SQL-PL (optional)
• return Dynamic Result Sets from Stored Procedures
• understand Schemas and Schema Paths
• use the ARRAY datatype within procedures
• use Global Temporary tables within Stored Procedures
• use Declared Temporary tables within Stored Procedures
• use SPUFI / IBM Data Studio to develop Stored Procedures
• call Stored Procedures from Triggers

Course Content

INTRODUCTION TO STORED PROCEDURES

Overview

Reduction in Network Traffic

Stored Procedure Advantages

DB2 Address Spaces

Defining a Stored Procedure

Error Handling within Stored Procedures

DB2 for z/OS Stored Procedure Enhancements

External vs Native SQL Procedures

STORED PROCEDURE DEFINITION

The Create Procedure Statement

The Create Procedure - Catalog Information

Stored Procedure Definition Parameters - General

Further Definition Parameters for External Procedures

Further Definition Parameters for Native Procedures

Allowable SQL Statements

The Alter Procedure Statement

Deleting a Stored Procedure Definition

Defining a Java Stored Procedure

Native (Internal) SQL Procedures

How Native SQL Procedures Work

Setting the SQL Terminator

Creating Native and External SQL Procedures

Altering Native SQL Procedures - Versioning

Creating the Original Version

Adding a New Version

Version Manipulation

Stored Procedure Security

The Stored Procedure Owner

Stored Procedure Authorisation

Authorisation for Dynamic Statements

CALLING STORED PROCEDURES

The Call Statement

Passing Parameters

Calling A Stored Procedure From COBOL

Calling a Stored Procedure from REXX

Calling A Stored Procedure From Java

Java - Setting Input / Inout Parameters

Java - Registering Output / Inout Parameters

Java - Getting Output / Inout Parameters

Java - Parameter / Data Type Mappings

Java - Handling Nulls

Java - Handling SQL Errors

Java - Handling SQL Warnings

Calling Program - Package Requirements

Common SQL Codes Returned from the Call

SCHEMAS AND PATHS

Schemas

The Grant Schema Statement

Schema Path – Bind Option

Current Path - Special Register

Overriding the Search Path

Set Current Schema

Set Current Schema Behaviour

Set Current Package Path

Set Current Package Path Behaviour

WRITING STORED PROCEDURES IN COBOL, PL1, C OR JAVA

Cobol 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

Package Requirements

Receiving Parameters into a Stored Procedure

Using Dbinfo with Parameter Style Db2sql

Coding a Stored Procedure in Cobol

Error Handling

Error Handling Using Mode DB2SQL - Setting Sqlstate

Error Handling Using Mode DB2SQL - Cobol Example

PROCEDURE EXECUTION

WLM Address Spaces

Setting Up The WLM Environment

WLM Features

The Display Procedure Command

Starting and Stopping Procedures

WLM Operational Commands

Using Explain for Function Resolution

z/OS Procedures - Dynamic Writing to Datasets

PASSING DYNAMIC RESULTS SETS, TEMPORARY TABLES AND ARRAYS

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

Return to Caller or Client?

Definition of Result-Set-Locator Variables

Associate Locators

The Allocate Cursor Statement

Java - Handling Dynamic Result Sets

Java - Testing For Optional Result Sets

Rexx - Handling Dynamic Result Sets

Using Created Global Temporary Tables

Using Declared Temporary Tables

Declared Temporary Table - Commit Behaviour

Declared Temporary Table Considerations

Declared Temporary Tables – Comparisons

Example - Using Declared Tables in a Stored Procedure

Example - Passing Temporary Data to a Stored Procedure

Passing Arrays

Ordinary Arrays

Associative Arrays

The Array_Agg Function

Other Array Functions

Passing Arrays - Examples

IBM DATA STUDIO

Overview

Building DB2 Stored Procedures and Functions

Creating a New Workspace, Connection and Project

Creating a Stored Procedure

Deploying and Executing the Stored Procedures

Debugging Stored Procedures

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

The Create Trigger Statement

Before and After Triggers

Trigger Examples

Invoking Stored Procedures and User-Defined Functions

CODING PROCEDURES IN SQL (OPTIONAL)

The SQL Procedures language

An SQL Procedure Example

Building External SQL Procedures?

Building External SQL/PL Procedures - JCL Example

SQL Procedure Supported Statements

DML Statement Review

Selecting Data

Selecting Data - Singleton Selects

Implicit Casting

Selecting Data - Cursor Operations

The Update Statement

The Delete Statement

The Insert Statement

The Mass Insert Statement

The Merge Statement

Special Operators

Scalar Functions

SQL/PL REFERENCE (OPTIONAL)

Begin and End Statements (Compound Statements)

Declaring Host Variables

Host Variable Data Types

Native Stored Procedures - Nested Compound Statements

Using Labels

SQL Procedure Example – Nested Compound Statement

Scoping Rules

SQL-PL Statements

Assigning Values to Variables - The SET Statement

CASE Statement

Comments

FOR Statement

GET DIAGNOSTICS Statement

GOTO Statement

IF Statement

Comparison Operators

LEAVE and ITERATE Statements

LOOP statement

REPEAT Statement

RETURN Statement

WHILE statement

Dynamic SQL Statements

Protection from SQL Injection

Returning Result Sets

Processing Result Sets From Other Stored Procedures

Handling Errors in an SQL Stored Procedure

Common SQLCODE and SQLSTATE Values

Testing for Errors - SQLCode and SQLState

The Declare Handler Statement

Declaring Conditions for Handlers

Condition Handler Execution Path

Error Handling - SIGNAL Statement

RESIGNAL Statement

Sql Pl - Special Registers

Debugging Native Stored Procedures