DB2 for z/OS Stored Procedures

Audience

The Course is designed to instruct those delegates, who are fairly confident in using SQL, as well as developing applications, in all the facilities at their disposal to develop, write and test DB2 SQL procedures. The course contains practical exercises to consolidate the course content.

Prerequisites

Those attending the course should be confident in writing DB2 applications.

Duration

2 days. Hands on.

Course Objectives

The delegates on the course will learn how to develop, write and invoke DB2 stored procedures from an application.

Course Content

Introduction
Basics
Without Stored Procedures
Benefits of Stored Procedures
Types of Stored Procedures
Stored Procedure Execution
System directed access
Connect

Development
Languages
Stored Procedure address space
Rules
Create Procedure
Load Module
The Catalog

Writing External Stored Procedures
Introduction
Writing the Stored Procedure
Receiving Parameters in the Stored Procedure
Returning Parameters from the Stored Procedure
Parameters possible errors
Using COMMIT/ROLLBACK
Using Temporary Tables
MRSP
WLM Environment

An External Stored Procedure
The SQL CALL Statement
Permissions
Calling the Stored Procedure
MRSP
Example
COMMIT and ROLLBACK
SQLCODE
Parameters to Stored Procedures

Development
Stored Procedure preparations
SPL SQL Procedure Language
Introduction
Example
Declaring variables
Error handling
Commands
Assignment statement
CALL statement
CASE statement
GET DIAGNOSTICS
GOTO
IF statement
LEAVE
LOOP statement
REPEAT statement
WHILE statement

Stored procedure builder
SPL development