About this Course

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 with no green screen. This course covers the mechanics of coding, testing and debugging and deploying these stored procedures.

Audience Profile

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

At Course Completion

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

• Understand the benefits of Stored Procedures
• Code DB2 Stored Procedures using SQL Procedure Language (including the new “native” stored procedures on DB2 for z/OS)
• Use the IBM Data Studio to develop SQL Procedures
• Use the Unified Debugger to test and troubleshoot stored procedures.

Prerequisites

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

Course Outline

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

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

3. IBM Data Studio

• Introduction to Data Studio
• Managing Database Connections
• Coding Stored Procedures
• Deploying Stored Procedures
• Importing Existing Stored Procedures

4. Error Handling in SQL PL

• Capturing SQLCODE and SQLSTATE
• Unhandled Exception Behavior
• Coding Exception Handlers
• GET DIAGNOSTICS
• SIGNAL & RESIGNAL Commands

5. Global Temporary Tables

• Created Global Temporary Tables
• Declared Global Temporary Tables
• Example of a Stored Procedure Using Declared Glorbal Temporary Table

6. Native SQL Logistical & Performance Considerations

• Naming Conventions
• Versioning Procedures
• Performance and Execution Considerations
• Using Explain
• Migrating External to Native

7. Unified Debugger in IBM Data Studio

• Invoking the Debugger
• The Debug Perspective
• Using Breakpoints
• Working with Variables & Values

8. User Defined Functions with SQL PL:

• External Used Defined Functions
• "Native" Scalar Functions
• Non-inline Scalar Functions
• "Native" Table Functions