Course Description
Create database level applications. Participants will code procedural constructs including control statements, loops and cursors and create SQL-PL stored database procedures. Participants will learn to create stored procedures using SQL PL, Java, and other programming languages Exercises and lab sessions reinforce the learning objectives and provide participants the opportunity to gain practical hands-on experience. The ideal candidate should have a basic understanding of Relational Database Management Systems, and Structured Query Language (SQL). Also, a working knowledge of a programming language, such as ‘C’ language or Java is recommended, but not mandatory.

You Learn to…
·Develop Procedural Applications
·Describe a DB2 stored procedure
·Identify the client application tasks and stored procedure tasks
·Determine correct CALL statement syntax for invoking a DB2 stored procedure
·Identify the capabilities of the DB2 Stored Procedure Builder (SPB) GUI
·Use the SPB to create and execute Java and SQL PL stored procedures
·Pass parameters to/from a DB2 stored procedure
·Create result sets in a DB2 stored procedure
·Describe the structure of the DB2 SQL PL procedural language
·Perform exception handling in an SQL PL procedure
·Implement nested SQL PL stored procedures
·Implement external stored procedures (i.e. Java stored procedures)
·Describe the requirements for debugging using the Stored Procedure Builder
·Code a Visual Basic client application to access a DB2 stored procedure
·Code a ‘C’ language DB2 stored procedure
·Define differences between DB2 SQL PL and Oracle’s PL/SQL procedural language
·Define differences between DB2 SQL PL and Transact SQL procedural language
·Define requirements specific to developing DB2 stored procedures that will run on DB2 UDB for OS/390

Who Needs to Attend
·Data Analysts
·Data Administrators
·System Administrators
·Developers
·Support Staff


Prerequisites:
You must have knowledge of SQL

Follow-On Courses
SQL Tuning Techniques


Course Content:
1. Overview of DB2 stored procedures
·What is a DB2 stored procedure?
·When to use DB2 stored procedures
·Client/Stored procedure application flow
·Client tasks
·Stored procedure tasks
·DB2 stored procedure restrictions

2. The DB2 Stored Procedure Builder GUI
·Overview of the SPB components
·Projects, GUI structure, menu options including environment properties
·Options for accessing the SPB GUI
·Building an SQL procedure using SPB
·Running an SQL procedure through SPB
·SPB Demo

3. SQL-PL language
·SQL PL language structure
·Use of compound statements
·Compound statement restrictions
·SQL PL variable and condition declarations
·SQL PL cursor declarations and cursor processing
·SQL PL handler declarations
·SQL PL procedural statements
·Assignment statement
·Control flow statements (CASE, IF, LOOP, REPEAT, WHILE, FOR, ITERATE, LEAVE)
·SQL PL Exception handling

4. Using the SPB to create Java Stored Procedures
·Overview of Java stored procedure
·Creating a JDBC stored procedure
·Creating an SQLJ stored procedure
·Create a Java stored procedure

5. Calling DB2 stored procedures
·How are DB2 stored procedures invoked?
·Calling client applications
·Calling SQL PL stored procedures (nested SQL PL procedures)
·CALL statement syntax in calling client applications (Java, C, Cobol)
·Nested SQL PL procedure syntax elements
·Declare RESULT_SET_LOCATOR
·Associate
·Allocate

6. Debugging DB2 stored procedures
·Debugging requirements
·The ROUTINE_DEBUG table
·Debugging environment setup
·Debugging using the SPB

7. VB Client calling SQL PL procedure
·Requirements in a VB client to call an SQL PL procedure
·VB client code example
·Demo – Creating a VB client

8. Stored Procedures in other host languages
·Creating ‘C’ language stored procedures
·‘C’ language calling stored procedure related syntax elements
·Create procedure statement for a ‘C’ language stored procedure
·Preparing the ‘C’ language stored procedure for execution

9. Comparing DB2 SQL PL with other procedural languages
·Comparing SQL PL with Oracle’s PL SQL
·Comparing SQL PL with Transact SQL