DB2 Stored Procedures Workshop

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