DB2 for z/OS Stored Procedures

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

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

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

Learn how to develop, write and invoke DB2 stored procedures from an application.

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.