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

Environment

Development will be performed using DB2 running on LUW. Stored Procedures will be written using 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

WHAT ARE STORED PROCEDURES?
Overview
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

STORED PROCEDURE DEFINITION
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

CODING PROCEDURES IN SQL
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

SQL/PL REFERENCE
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
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

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

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