DB16 - DB2 Stored Procedures & Functions Workshop - 2 - 3 Days

Course Description

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

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).


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.


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


Environment

Development will be performed using DB2 running on LUW or z/OS. Stored Procedures can be written in a choice of the following languages:

  • COBOL
  • PL1
  • Java
  • SQL-PL


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

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


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.


Course Enquiry