DB2 Training - Coding Stored Procedures and User Defined Functions

DB16 - Coding Stored Procedures and User Defined Functions
Duration	1 day (without SQL-PL) or 2 days (with SQL-PL)
Availability	Check course price and availability

Target Audience

This course provides the applications programmer with an in-depth knowledge of the construction and installation of DB2 Stored Procedures and User Defined Functions. During the course, Stored Procedures and UDFs will be written using a choice of programming language. If necessary the course can cover the automated generation of Stored Procedures either using the DB2 Development Center or RAD. The course can also optionally include tuition of the SQL Procedural Language (SQL-PL).

Pre-requisites

The delegate should be familiar with the host environment, together with a working knowledge of DB2 program development in COBOL, PL1 or Java.

Course Objectives

The aim of this course is to provide the programmer, already familiar with DB2, with the necessary skills required to generate (if required), code, install and test DB2 Stored Procedures, User Defined Functions and Triggers. An optional objective is to be able to write procedures in SQL-PL. On completion of this course the student will be able to:

understand Schemas and Schema Paths
write and implement Stored Procedures coded in COBOL, PL1 or Java
write and implement UDFs coded in COBOL, PL1 or Java
define and understand Triggers
use Query Result Sets within Stored Procedures
use Global Temporary tables within Stored Procedures
use Declared Temporary tables within Stored Procedures
use the Development Center to generate Stored Procedures (optional)
use the Rational Application Developer to generate Stored Procedures (optional)
code Stored Procedures written in SQL-PL (optional)

Course Environment

Development will be performed using DB2 running on:

LUW
z/OS

Stored Procedures can be written in a choice of the following languages:

COBOL
PL1
Java
SQL-PL

Stored Procedures can, if required, be generated using:

Development Center
Rational Application Developer

Course Details

WHAT ARE STORED PROCEDURES?: Overview; Reduction in Network Traffic; Stored Procedure Advantages; DB2 Address Spaces; Defining an External Stored Procedure; Executing a Stored Procedure - the Call Statement; Error Handling within Stored Procedures; Execution Flow
SCHEMAS AND PATHS: Schemas; The Grant Schema Statement; Schema Path - Bind Option; Current Path - Special Register; Overriding the Search Path; Set Current Schema; Set Current Package Path
STORED PROCEDURE DEFINITION: The Create Procedure Statement; Stored Procedure Parameters; Allowable SQL Statements; Create Procedure Example; The Alter Procedure Statement; Deleting a Stored Procedure Definition; Defining a Java Stored Procedure; Java Stored Procedures - Jar Installation
STORED PROCEDURES IN COBOL, PL1 or C: The Call Statement; Passing Parameters; Passing Nulls; Package Requirements; Calling A Stored Procedure From COBOL; Cobol Stored Procedure Language Requirements; Stored Procedure Restrictions; Using Commit and Rollback; Using Re-Entrant Code; Main Program or Sub-Program?; Preparing a Cobol, PL1 or C Stored Procedure; Package Requirements; Receiving Parameters into a Stored Procedure; Using Dbinfo with Parameter Style Db2sql; Coding a Stored Procedure in Cobol; Error Handling; Error Handling Using Mode DB2SQL - Setting Sqlstate; Error Handling Using Mode DB2SQL - Cobol Example
STORED PROCEDURES - REXX CODE: Setting the Environment; Connecting to a DB2 Subsystem; Sample Code; Executing SQL Statements - Execsql; Host Variables; The Prepare Statement; Cursor Processing; C1 to C100; C101 to C200; S1 to S100; Selecting Data - Cursor Processing; Null Indicators; SQLCA; Isolation Level; Calling A Stored Procedure From REXX
JAVA DATABASE ACCESS - JDBC: Database Environments; Dynamic SQL - What is JDBC?; What are JDBC Drivers?; JDBC Driver Types; The JDBC API; JDBC SQL Statements; Using the Statement Class to Select Rows; The DB2 Universal Driver; Running SQL Statements; Using the Statement Class to Update Rows; Processing Result Sets; Jdbc Cursor Operations; Cursor Scroll Types; Cursor Concurrency Types; Cursor Holdibility Types; Using the PreparedStatement Class; Handling NULL Values; Handling SQL Errors; Handling SQL Warnings; Transaction Control; Transaction Example; Isolation Levels; Calling A Stored Procedure From Java; Setting Stored Procedure Input / Inout Parameters; Registering Stored Procedure Output / Inout Parameters; Stored Procedures - Handling Nulls; Java - Handling Result Sets; Testing For Optional Result Sets; DataSources; Java Naming and Directory Interface - JNDI; Setting Up Connection Pooling using DataSources; Getting Database Connections via a DataSource
CODING PROCEDURES IN SQL: The SQL Procedures language; An SQL Procedure Example; Building SQL Procedures?; JCL Example; SQL Procedure Supported Statements; Terminating Statements in an SQL procedure; Simple DML Statements; Selecting Data - Singleton Selects; Selecting Data - Cursor Operations; The Update Statement; Update with Subselect; The Delete Statement; The Insert Statement; The Mass Insert Statement
SQL/PL REFERENCE: Begin and End Statements (Compound Statements); Declaring Host Variables; Assigning Values to Variables - The SET Statement; CASE Statement; Comments; FOR Statement; GET DIAGNOSTICS Statement; GOTO Statement; IF Statement; Comparison Operators; LEAVE and ITERATE Statements; LOOP statement; REPEAT Statement; RETURN Statement; WHILE statement; Handling Errors in an SQL Stored Procedure; Testing for Errors - SQLCode and SQLState; The Declare Handler Statement; Condition Handler Execution Path; Declaring Conditions for Handlers; Dynamic SQL statements; Returning Result Sets; Processing Result Sets From Other Stored Procedures; SIGNAL Statement and Message_Text Variable; RESIGNAL Statement; Coding Considerations
PROCEDURE EXECUTION: Overview; DB2 SPAS; WLM Address Spaces; Setting Up The WLM Environment; Comparison Of WLM vs SPAS Procedure Management; Summary of WLM Advantages; Access To Non-SQL Resources; Resource Recovery Services Attach Facility (RRSAF); The Display Procedure Command; Starting and Stopping Procedures; WLM Operational Commands; Using Explain for Function Resolution; z/OS Procedures - Dynamic Writing to Datasets
STORED PROCEDURE AUTHORITIES: Stored Procedure Authorities; Stored Procedure Authorisation; Authorisation Checking when Calling a Procedure
DYNAMIC RESULTS SETS: Dynamic Results Sets; Objects From Which You Can Return Result Sets; Requirements for Dynamic Result Sets; Cursor Processing Within the Stored Procedure; Dynamic Result Set Embedded SQL Statements; Query Results Sets Example; Declare Cursor With Return; Definition of Result-Set-Locator Variables; Associate Locators; The Allocate Cursor Statement; Using Global Temporary Tables; Declared Temporary Tables; Declared Temporary Table Enhancement; Declared Temporary Table Considerations; Declared Temporary Tables - Comparisons
USER DEFINED FUNCTIONS: User-Defined Functions; Built-in Functions; Creating External Functions; The Create Function Statement; Function Parameters; Function Parameters Summary; Sourced Function Examples; External Scalar Function Examples; Creating External Table Functions; External Table Function Example; Implementing an External Function; Step 1 - Write The Function; Function Example - Cobol; Function Example using Scratchpad - Cobol; Function Example - C; Function Example - PL/1; Step 2 - Preparing a User-Defined Function for Execution; Step 3 - Define The Function To DB2; Step 4 - Test the Function; Function Authorisation; Function Execution Environment; Dropping a Function; Using Explain for Function Resolution; The Stop Function Command; The Start Function Command; The Display Function Command
DEVELOPMENT CENTER: Overview; Building DB2 Stored Procedures and Functions; Creating a New Stored Procedure or Function; Development Center Wizard; Development Center Wizard - Procedure Name; Development Center Wizard - Pattern; Development Center Wizard - Parameters; Development Center Wizard - Options; The SQL Assistant; Building The Stored Procedure; Debugging Stored Procedures; Setting Breakpoints
RAD PROCEDURE BUILDER: Connecting To DB2 from RAD; Creating a Project; Importing a Database; Creating a Stored Procedure; Parameter Specification; Testing the Query; Amending the Procedure; Building the Stored Procedure; Specifying Run Settings; Running the Stored Procedure
TRIGGERS: Triggers; Trigger Parts; The Create Trigger Statement; Before and After Triggers; Trigger Examples; Invoking Stored Procedures and User-Defined Functions

Course Format

The course contains many practical exercises to ensure familiarity with the product. On completion of this course students will be able to develop Stored Procedures in either COBOL, PL1, Java or SQL and optionally be able to generate Stored Procedures using SPB or WSAD. The comprehensive Student Guide supplied is fully indexed serving as a useful reference tool long after the course has finished. Delegates will also be able to access a free help-line with technical questions relating to topics covered on the course.