Stored Procedure Options on Db2 for z/OS

Duration: 4.0 days

Method: Instructor led, Hands-on workshops

Price: $2600.00

Course Code: DB1020

Audience

Database Administrators and Application Developers who will be setting up the Stored Procedures environment on the Db2 for z/OS platform as well as calling and/or coding stored procedures in the Db2 z/OS environment.

Description

This course presents coding and administration requirements for implementing Db2 Stored Procedures on Db2 for z/OS. Both external (COBOL) and native SQL Procedure Language Stored Procedures are covered.

Objectives

Upon successful completion of this course, the student will be able to:

Understand the Stored Procedures Environment and setup requirements to implement stored procedures
Code and test Db2 for z/OS Stored Procedures using COBOL
Code and test a Stored Procedure Calling Program
Use IBM Data Studio to code an SQL PL Stored Procedure
Understand the two types of Global Temporary Tables and their use with Stored Procedures
Understand the major Performance Considerations for Db2 z/OS Stored Procedures

Prerequisites

DB1010 Db2 for z/OS SQL & Application Programming. Working knowledge of COBOL and SQL.

Topics

I. Introduction to Stored Procedures
- Distributed Processing
- Stored Procedures Solutions
- Architecture
- Installation Considerations
- WLM-Established Stored Procedures Address Spaces
- Enabling Stored Procedures After Installation
- Stored Procedures Run-time Environment
- Administrator Tasks
- zIIP Elligibility
- Using Commands
II. External COBOL Stored Procedures
- Parameter Passing Stored Procedures
  - Writing and Preparing COBOL Stored Procedures
  - Invoking Stored Procedures
  - Defining Your Procedure to Db2
  - Calling Simple COBOL Stored Procedures
- Result Set Passing Stored Procedures
  - Improved Functionality with Result Sets
  - Requirements for Returning Result Sets
  - How to Write Applications That Receive Result Sets
- Coding calling programs in COBOL
III. Native SQL Procedure Language (SQL PL) Stored Procedures
- Introduction to Data Studio
  - Managing Database Connections
  - Coding Stored Procedures
  - Deploying Stored Procedures
- SQL PL
  - CREATE PROCEDURE Statement
  - Bind Options
  - Declaring SQL Variables
  - Procedural Statements in SQL
  - Creating Result Sets
  - Calling Another Stored Procedure
  - Retrieving a Result Set
- Debugging with the Unified Debugger
IV. Global Temporary Tables
- Created Temporary Tables
- Declared Temporary Tables
- Using Temporary Tables to Hold Result Sets
V. Stored Procedure Performance
- Workload Manager Address Space Considerations
- Block / Multi Row Fetch with Stored Procedures
- Distributed Data Facility & Network Issues
- Efficient SQL