DB2 Stored Procedures Programming Workshop (SPVC)

DB2 Stored Procedure Introduction

• Describe a stored procedure
• Determine when a stored procedure should be used
• Describe execution flow using stored procedures
• List DB2 stored procedure design and general tips

IBM Optim Development Studio or Command Line Tools

• Describe the IBM Data Studio Developer
• Identify the capabilities of the IBM Data Studio Developer
• Create and execute simple stored procedures using IBM Data Studio Developer, Command Editor, and Command Window

Basic SQL Procedure Structure

• Describe the structure of an SQL procedure
• Explain various clauses of the CREATE PROCEDURE statement
• Describe the structure of the procedure body
• List the statements that can be coded in the procedure body
• Alter Procedure
• Drop Procedure
• Create Module
• Replace Module
• Alter Module
• Drop Module

Overview of SQL PL Language Elements

• Discuss DB2 data types
• Select the proper data types
• Work with user-defined data types
• Work with Row data type
• Work with Associative arrays
• Implement data type anchoring

Using Flow of Control Statements

• Use Compound statements (ATOMIC and NOT ATOMIC)
• Use labels in both compound statements and loops
• Work with Conditional Statements (IF and CASE)
• Implement Looping (FOR, WHILE,REPEAT, and LOOP)
• Transfer Control using (GOTO, LEAVE, ITERATE, RETURN, COMMIT, and ROLLBACK)

Cursors and Result Sets

• Explain cursor processing
• Use cursors inside SQL procedures
• Perform positioned deletes and positioned updates
• Describe cursor behavior on commit and rollback
• Use cursor processing to return result sets to an application
• Explain how cursors affect locking

Condition Handling

• Explain the difference in SQLSTATE and SQLCODE
• Describe a condition handler
• Explain when to use a condition handler
• Use condition handlers
• Force an application exception and set the appropriate message
• Check the success or failure of an SQL statement
• Raise an SQL error

Nested SQL Procedures

• Call a stored procedure
• Describe a nested stored procedure
• Pass parameters between nested SQL procedures
• Return and receive result sets from within nested SQL procedures
• Write recursive SQL procedures

IBM Optim Development Studio SQL Procedure Debug

• Describe the IBM Optim Development Studio Routine Debugger capabilities
• Describe platforms supported for debugging
• Deploy a stored procedure for debugging
• Debug a stored procedure with the assistance of the Routine Debugger

SQL Procedure Tracing using sqlpltrc

• Describe the purpose of sqlpltrc
• Define the process for installing sqlpltrc
• Define the procedures for trace management
• Define the procedures for trace reporting
• Define the procedures for trace entries
• Show the steps to create a sample trace
• Discuss steps used to identify and resolve problems

Security Considerations in SQL Procedures

• Describe the relationship between a DB2 SQL procedure and a package
• Rebind a package associated with an SQL PL procedure
• Define who a package owner is and describe the package owner's responsibilities
• Define who a routine definer is and describe the routine definer's responsibilities
• Describe the routine invoker's behavior and authorization requirements

SQL Stored Procedures Performance

• Analyze and improve SQL access plans
• Avoid or simplify multiple statements
• Exploit set-at-a-time semantics of SQL
• Improve cursor performance

Agenda

• Unit 1: DB2 Stored Procedure Introduction
• Exercise 1: The exercise environment
• Unit 2: IBM Optim Development Studio or Command Editor and Command Window
• Exercise 2: IBM Optim Development Studio or Command Editor and Command Window
• Unit 3: Basic SQL Procedure Structure
• Exercise 3: Basic SQL Procedure Structure
• Unit 4: Overview of SQL PL Language Elements
• Exercise 4: Overview of SQL PL Language Elements
• Unit 5: Using Flow of Control Statements
• Exercise 5: Using Flow of Control Statements
• Unit 6: Understanding and Using Cursors and Result Sets
• Exercise 6: Understanding and Using Cursors and Result Sets
• Unit 7: Condition Handling
• Exercise 7: Condition Handling
• Unit 8: Nested SQL Procedures
• Exercise 8: Nested SQL Procedures
• Unit 9: IBM Optim Development Studio SQL Procedure Debug
• Exercise 9: IBM Optim Development Studio SQL Procedure Debug
• Unit 10: SQL Procedure Tracing using sqlpltrc
• Exercise 10: SQL Procedure Tracing using sqlpltrc
• Unit 11: Security Considerations in SQL Procedures
• Exercise 11: Security Considerations in SQL Procedures
• Unit 12: SQL Procedure Performance
• Exercise 12: SQL Procedure Performance