Procedures, triggers, and user-defined functions (UDFs) are the key database software features for developing robust and distributed applications. IBM Universal Database™ for i (IBM DB2® for i) supported these features for many years, and they were enhanced in V5R1, V5R2, and V5R3 of IBM® OS/400® and V5R4 of IBM i5/OS™.

This IBM Redbooks® publication includes several of the announced features for procedures, triggers, and UDFs in V5R1, V5R2, V5R3, and V5R4. This book includes suggestions, guidelines, and practical examples to help you effectively develop IBM DB2 for i procedures, triggers, and UDFs. The following topics are covered in this book:

• External stored procedures and triggers
• Java procedures (both Java Database Connectivity (JDBC) and Structured Query Language for Java (SQLJ))
• External triggers
• External UDFs

This publication also offers examples that were developed in several programming languages, including RPG, COBOL, C, Java, and Visual Basic, by using native and SQL data access interfaces.

This book is part of the original IBM Redbooks publication, Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries, SG24-6503-02, that covered external procedures, triggers, and functions, and also SQL procedures, triggers, and functions. All of the information that relates to external routines was left in this publication. All of the information that relates to SQL routines was rewritten and updated. This information is in the new IBM Redbooks publication, SQL Procedures, Triggers, and Functions on IBM DB2 for i, SG24-8326.

This book is intended for anyone who wants to develop IBM DB2 for i procedures, triggers, and UDFs. Before you read this book, you need to know about relational database technology and the application development environment on the IBM i server.

Chapter 1. Introducing IBM DB2 for i

Chapter 2. Stored procedures, triggers, and user-defined functions for an Order Entry application

Chapter 3. Stored procedures

Chapter 4. External stored procedures

Chapter 5. Java stored procedures

Chapter 6. Stored procedure error handling

Chapter 7. Database triggers

Chapter 8. External triggers

Chapter 9. Triggers, referential integrity, and constraints

Chapter 10. User-defined functions

Chapter 11. External user-defined functions

Appendix A. Sample ILE C program that uses the QDBRTVFD API

Appendix B. Order Entry application: Detailed flow

Appendix C. Additional material