UD13 - DB2 Coding Stored Procedures using SQL/PL - 2 days

View Dates and Locations

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.


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


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 IBM Data Studio to develop Stored Procedures
  • call Stored Procedures from Triggers


Development will be performed using DB2 running on:

  • LUW

Stored Procedures will be written using SQL/PL.

Stored Procedures can, if required, be written using:

  • Rational Application Developer
  • IBM Data Studio


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.

On-site course enquiry (from £165 pp per day)

Course Details

View Dates and Locations

Reduction in Network Traffic
Stored Procedure Advantages
Defining a Stored Procedure
Executing a Stored Procedure - the Call Statement
Error Handling within Stored Procedures
Execution Flow

The Create Procedure Statement
The Create Procedure - Catalog Information
Stored Procedure Definition Parameters - General
Further Definition Parameters for External Procedures
Allowable SQL Statements
The Alter Procedure Statement
Deleting a Stored Procedure Definition
Defining a Java Stored Procedure
Setting the SQL Terminator
Stored Procedure Authorisation
Customizing Precompile / Bind Options
Schemas and Paths
The Grant Schema Statement
Current Path - Special Register
Overriding the Search Path
Set Current Schema
Set Current Schema / Path Example

The SQL Procedures language
An SQL Procedure 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

Begin and End Statements (Compound Statements)
Declaring Host Variables
Host Variable Data Types
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
FOR 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

The Call Statement
Passing Parameters
Calling A Stored Procedure From C
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

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

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

Trigger Parts
Before and After Triggers
Trigger Examples
Allowable Combinations
Error Handling
Trigger Cascading
Instead Of Triggers
Instead Of Triggers Restrictions
Trigger Authorisation
Calling Stored Procedures from Triggers
Removing Triggers

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