ibm.com/redbooks
Stored Procedures, Triggers, and
User-Defined Functions on DB2
Universal Database for iSeries
Hernando Bedoya
Fredy Cruz
Daniel Lema
Satid Singkorapoom
Develop robust DB2 Universal
Database for iSeries applications
Discover the details of SQL stored
procedures and SQL triggers
Learn the secrets of
user-defined functions
International Technical Support Organization
Stored Procedures, Triggers, and User-Defined
Functions on DB2 Universal Database for iSeries
October 2006
SG24-6503-02
© Copyright International Business Machines Corporation 2001, 2004, 2006. All rights reserved.
Note to U.S Government Users - Documentation related to restricted rights - Use, duplication or disclosure is subject to restrictions set
forth in GSA ADP Schedule Contract with IBM Corp.
Third Edition (October 2006)
This edition applies to V5R1, V5R2, and V5R3 of IBM OS/400 and V5R4 of IBM i5/OS, Program Number
5722-SS1.
Take Note! Before using this information and the product it supports, be sure to read the general
information in “Notices” on page xi.
iii
Contents
Notices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
Trademarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xii
Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
The team that wrote this redbook. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiv
Become a published author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvi
Comments welcome. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvi
Part 1. Background . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Chapter 1. Introducing DB2 Universal Database for iSeries . . . . . . . . . . . . . . . . . . . . . . 3
1.1 An integrated relational database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
1.2 DB2 Universal Database for iSeries: An overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
1.2.1 DB2 Universal Database for iSeries basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
1.2.2 Stored procedures, triggers, and user-defined functions . . . . . . . . . . . . . . . . . . . . 6
1.3 DB2 Universal Database for iSeries sample schema . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Chapter 2. Stored procedures, triggers, and user-defined functions: Order entry
application. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
2.1 Order Entry application overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
2.2 Order Entry database overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
2.3 Stored procedures and triggers in the Order Entry database . . . . . . . . . . . . . . . . . . . . 17
2.3.1 Stored procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
2.3.2 Triggers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
2.3.3 User-defined functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Chapter 3. Introduction to the SQL Persistent Stored Module in DB2 Universal Database
for iSeries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
3.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
3.2 System requirements and planning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
3.3 Structure of a SQL PSM program . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
3.4 SQL control statements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
3.4.1 Assignment statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
3.4.2 Conditional control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
3.4.3 Iterative control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
3.4.4 Calling procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
3.5 Compound SQL statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
3.5.1 Nested compound statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
3.5.2 Variable declaration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
3.5.3 Using cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
3.6 Using scrollable cursors in SQL PSM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
3.7 Dynamic SQL in SQL PSM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
3.7.1 PREPARE statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
3.7.2 EXECUTE statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
3.7.3 EXECUTE IMMEDIATE statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
3.7.4 Cursors based on dynamic SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
3.8 Moving into production (save and restore) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
3.8.1 Restore processing for V4R5 and prior releases . . . . . . . . . . . . . . . . . . . . . . . . . 35
3.8.2 Restore processing for V5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
