DB13 - DB2 Coding Stored Procedures using SQL/PL - 2 Days

Course Description

This course provides the applications programmer with an in-depth knowledge of the construction and installation of DB2 Stored Procedures written in SQL-PL.


Pre-requisites

The delegate should be familiar with the host environment, together with a working knowledge of SQL.


Objectives

The aim of this course is to provide the programmer, already familiar with DB2, with the necessary skills required code, install and test DB2 Stored Procedures written in SQL-PL.

On completion of this course the student will be able to:

  • define and code Stored Procedures using SQL-PL
  • execute Stored Procedures
  • 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

Stored Procedures will be written using SQL/PL. Development will be performed using DB2 running on:

  • z/OS
  • LUW


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

CODING PROCEDURES IN SQL
The SQL Procedures language
An SQL Procedure Example
Building External SQL Procedures?
Building External SQL/PL Procedures - JCL Example
Summary of SQL PL 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
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

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 DB2 Command Line
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

COBOL STORED PROCEDURES
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

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
Instead Of Triggers
Instead Of Triggers – Restrictions
Instead Of Triggers – Authorisation
Instead Of Triggers – Catalog Changes
Calling Stored Procedures from 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.


Course Enquiry