Exploiting Native SQL Stored Procedures on Db2 for z/OS

SEE SCHEDULE

Duration: 3 Days

Method: Instructor led, Hands-on workshops

Price: $1920.00

Course Code: DB1029

View Related Video

Audience

Analysts and programmers involved in the development of Db2 for z/OS SQL Stored Procedures.

Description

One of the most anticipated new features of Db2 for z/OS was the Native SQL PL stored procedure. A key advantage is the ability to work entirely within the IBM Data Studio environment and accomplish Db2 development and deployment with no green screen. Students will leave this course with the ability to code new SQL PL stored procedures and maintain existing. This is a very hands on course where students code, test, and deploy 7 stored procedure programs. First introduced in V9, there has been a number of enhancements to the SQL PL language that also get covered in this course. Topics like XML parameters, array parameters, autonomous processing, piece-wise delete, and UDF code enhancements are covered. Everyone leaves with a set of ‘Best Practices’.

Objectives

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

Understand the benefits of Stored Procedures
Code Db2 SQL PL Stored Procedures
Use the IBM Data Studio to code, test, deploy, and debug SQL PL Stored Procedures
Understand the new XML and Array parameters
Understand the many ways of error handling
Use the Unified Debugger to test and troubleshoot stored procedures
Understand how to use SQL PL enhancements within User Defined Functions

Prerequisites

Understanding of relational database concepts and SQL syntax (Db2 SQL For Everyone or equivalent).

Topics

I. Introduction to Stored Procedures
- What are Stored Procedures?
- Problems Solved Using Stored Procedures
- Advantages of using Stored Procedures
- Calling a Stored Procedure
- Native and External SQL Procedures
- Stored Procedure Run-time Environments
II. The SQL Procedure Language
- CREATE PROCEDURE statement
- Db2 for z/OS BIND options
- Declaring SQL Variables
- Procedural Statements in SQ
- Creating & Retrieving Result Sets
- Calling another Stored Procedure
III. IBM Data Studio
- Introduction to Data Studio
- Managing Database Connections
- Coding Stored Procedures
- Deploying Stored Procedures
- Importing Existing Stored Procedures
IV. Error Handling in SQL PL
- Capturing SQLCODE and SQLSTATE
- Unhandled Exception Behavior
- Coding Exception Handlers
- GET DIAGNOSTICS
- SIGNAL & RESIGNAL Commands
V. Global Temporary Tables
- Created Global Temporary Tables
- Declared Global Temporary Tables
- Example of a Stored Procedure Using Declared Global Temporary Table
VI. Native SQL Logistical & Performance Considerations
- Naming Conventions
- Versioning Procedures
- Performance and Execution Considerations
- Using Explain
- Migrating External to Native
VII. Unified Debugger in IBM Data Studio
- Invoking the Debugger
- The Debug Perspective
- Using Breakpoints
- Working with Variables & Values
VIII. User Defined Functions with SQL PL:
- External User Defined Functions
- “Native” Scalar Functions
- Non-inline Scalar Functions
- “Native” Table Functions