External Procedures, Triggers, and User-Defined Functions on IBM DB2 for i

External Procedures, Triggers, and User-Defined Functions on IBM DB2 for i

by Hernando Bedoya, Fredy Cruz, Daniel Lema, Satid Singkorapoom
Released April 2016
Publisher(s): IBM Redbooks
ISBN: 9780738441597

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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.

    Table of contents

    1. Front cover
    2. Notices
      1. Trademarks
    3. Preface
      1. Authors
      2. Now you can become a published author, too!
      3. Comments welcome
      4. Stay connected to IBM Redbooks
    4. Summary of changes
      1. April 2016, Fourth Edition
    5. IBM Redbooks promotions
    6. Chapter 1. Introducing IBM DB2 for i
      1. 1.1 An integrated relational database
      2. 1.2 DB2 for i overview
        1. 1.2.1 DB2 for i basics
        2. 1.2.2 Stored procedures, triggers, and user-defined functions
      3. 1.3 DB2 for i sample schema
    7. Chapter 2. Stored procedures, triggers, and user-defined functions for an Order Entry application
      1. 2.1 Order Entry application overview
      2. 2.2 Order Entry database overview
      3. 2.3 Stored procedures and triggers in the Order Entry database
        1. 2.3.1 Stored procedures
        2. 2.3.2 Triggers
        3. 2.3.3 User-defined functions
    8. Chapter 3. Stored procedures
      1. 3.1 Introduction
      2. 3.2 Stored procedure types
        1. 3.2.1 SQL stored procedures
        2. 3.2.2 External stored procedure
      3. 3.3 Registering stored procedures
        1. 3.3.1 CREATE PROCEDURE
        2. 3.3.2 DECLARE PROCEDURE
      4. 3.4 System catalog tables
        1. 3.4.1 SYSROUTINES catalog
        2. 3.4.2 SYSPARMS catalog
      5. 3.5 Procedure signature and procedure overloading
      6. 3.6 Deleting or replacing stored procedures
        1. 3.6.1 Using a command line to drop a procedure
        2. 3.6.2 Dropping overloaded procedures
      7. 3.7 Authorization and adopted authority
      8. 3.8 Returning result sets from stored procedures
    9. Chapter 4. External stored procedures
      1. 4.1 Registering external stored procedures
        1. 4.1.1 Registering an external procedure with System i Navigator
      2. 4.2 Parameter styles in external stored procedures
        1. 4.2.1 SQL parameter style
        2. 4.2.2 DB2SQL parameter style
        3. 4.2.3 GENERAL WITH NULLS parameter style
        4. 4.2.4 GENERAL parameter style
      3. 4.3 Coding external stored procedures
        1. 4.3.1 Coding for SQL parameter style
        2. 4.3.2 Coding the DB2SQL parameter style
        3. 4.3.3 Coding the GENERAL WITH NULLS parameter style
      4. 4.4 Returning result sets from external procedures
        1. 4.4.1 Coding external stored procedures that return cursor result sets
        2. 4.4.2 Coding external stored procedures that return array result sets
      5. 4.5 CLI client program that calls a procedure that returns multiple result sets
      6. 4.6 Moving into production (save and restore)
      7. 4.7 The Order Entry application: Stored procedure examples
        1. 4.7.1 Calling a stored procedure
        2. 4.7.2 Sample stored procedure: SQL RPG version
      8. 4.8 External stored procedure that uses a service program
      9. 4.9 RPG IV example for an external stored procedure
        1. 4.9.1 External stored procedure that writes to a data queue
        2. 4.9.2 External stored procedure that reads from a data queue
        3. 4.9.3 Calling external stored procedures from the Run SQL Scripts utility
    10. Chapter 5. Java stored procedures
      1. 5.1 Prerequisites
      2. 5.2 Coding DB2 for i Java stored procedures
        1. 5.2.1 Parameter styles
        2. 5.2.2 Data type compatibility
        3. 5.2.3 Database connection in a Java stored procedure
        4. 5.2.4 Returning result sets in Java stored procedures
      3. 5.3 Coding examples
        1. 5.3.1 Compilation of Java code
        2. 5.3.2 Where to place Java classes
        3. 5.3.3 Creating Java programs
      4. 5.4 Registering Java stored procedures
        1. 5.4.1 Registering Java stored procedures with System i Navigator
        2. 5.4.2 Using the Run SQL Scripts utility
        3. 5.4.3 Using the native interface
      5. 5.5 Calling Java stored procedures
      6. 5.6 Using SQL NULL
      7. 5.7 SQLJ procedures to manipulate JAR files
        1. 5.7.1 SQLJ.INSTALL_JAR
        2. 5.7.2 SQLJ.REMOVE_JAR
        3. 5.7.3 SQLJ.REPLACE_JAR
        4. 5.7.4 SQLJ.UPDATEJARINFO
        5. 5.7.5 SQLJ.RECOVERJAR
      8. 5.8 Additional considerations
        1. 5.8.1 Moving into production (save and restore)
      9. 5.9 GetSuppliers example: Implementation with no result sets
        1. 5.9.1 Stored procedure: GetSupplier
        2. 5.9.2 Java client: ClientGetSupplier
        3. 5.9.3 Java GUI client: ClientGetSupplierGUI
      10. 5.10 GetSupplierRS example: Implementation with result sets
        1. 5.10.1 GetSupplierRS stored procedure with the JAVA parameter style
        2. 5.10.2 GetSupplierRS stored procedure with the DB2GENERAL parameter style
        3. 5.10.3 Java clients: ClientGetSupplier and ClientGetSupplierGUI
      11. 5.11 Problem determination
        1. 5.11.1 Debugging
        2. 5.11.2 Tracing
    11. Chapter 6. Stored procedure error handling
      1. 6.1 Database error reporting strategy
        1. 6.1.1 User-defined errors and warnings
        2. 6.1.2 Consistent error handling
      2. 6.2 Error handling in SQL stored procedures
        1. 6.2.1 Condition and handler declaration
        2. 6.2.2 SIGNAL and RESIGNAL
        3. 6.2.3 SQLCODE and SQLSTATE variables in the SQL procedure
        4. 6.2.4 Returning values by using the RETURN statement
        5. 6.2.5 GET DIAGNOSTICS
        6. 6.2.6 Error handling in nested compound statements
        7. 6.2.7 Use nested compound statements for better performance
      3. 6.3 Error handling in external stored procedures
        1. 6.3.1 Checking the stored procedure completion status
        2. 6.3.2 GENERAL and GENERAL WITH NULLS parameter styles
      4. 6.4 Error handling in Java stored procedures
      5. 6.5 Retrieving user-defined errors in a client application
        1. 6.5.1 Retrieving error conditions in a JDBC client
        2. 6.5.2 Retrieving error conditions from an ODBC or CLI client
      6. 6.6 Transaction management in stored procedures
        1. 6.6.1 Transaction management terminology
        2. 6.6.2 Transactional behavior
        3. 6.6.3 SQL statements for controlling transactions
        4. 6.6.4 Transaction management in compound statements
      7. 6.7 External stored procedures and commitment control
        1. 6.7.1 Activation group
        2. 6.7.2 Savepoints
      8. 6.8 Several practical examples
        1. 6.8.1 SQL stored procedure example
        2. 6.8.2 External stored procedure example
        3. 6.8.3 Java stored procedure example
        4. 6.8.4 C++ client code that uses ODBC
        5. 6.8.5 Java example client code
        6. 6.8.6 Results for the example programs
    12. Chapter 7. Database triggers
      1. 7.1 Trigger concepts
      2. 7.2 Types of triggers in DB2 for i
        1. 7.2.1 SQL triggers
        2. 7.2.2 External triggers
      3. 7.3 Enabling and disabling a trigger
      4. 7.4 Displaying and reviewing trigger information
        1. 7.4.1 Using System i Navigator to view the properties of a trigger
        2. 7.4.2 Displaying trigger information
        3. 7.4.3 Printing trigger information
      5. 7.5 System catalog tables
      6. 7.6 Authorization and adopted authorities on triggers
      7. 7.7 Renaming and copying
    13. Chapter 8. External triggers
      1. 8.1 Defining a trigger
        1. 8.1.1 ADDPFTRG
        2. 8.1.2 Using System i Navigator to add an external trigger
      2. 8.2 Trigger program structure
        1. 8.2.1 Trigger buffer for RPG
        2. 8.2.2 Trigger buffer for COBOL
        3. 8.2.3 Trigger buffer for C
        4. 8.2.4 Using the trigger buffer
      3. 8.3 Trigger feedback to application programs
        1. 8.3.1 Commitment control and triggers
      4. 8.4 Designing trigger programs
        1. 8.4.1 Order Entry application scenario
        2. 8.4.2 Audit trail trigger example programs
        3. 8.4.3 Updating a trigger on the Order Header file program examples
        4. 8.4.4 Softcoding the trigger buffer example
        5. 8.4.5 Changing the record that fired a trigger
      5. 8.5 Applications and triggers: Design considerations
      6. 8.6 Recommendations
    14. Chapter 9. Triggers, referential integrity, and constraints
      1. 9.1 Transaction isolation and recovery
      2. 9.2 Trigger journal entries
      3. 9.3 Triggers and referential integrity
      4. 9.4 Comparing referential integrity and triggers
        1. 9.4.1 Using triggers to implement referential integrity rules
      5. 9.5 Constraints and triggers: Ordering the actions
        1. 9.5.1 Insert operations
        2. 9.5.2 Update operations
        3. 9.5.3 Delete operations
      6. 9.6 Triggers, referential integrity, and commitment control
        1. 9.6.1 When the application is not running commitment control
        2. 9.6.2 When the application runs under commitment control
      7. 9.7 Referential integrity, triggers, and journal entries
    15. Chapter 10. User-defined functions
      1. 10.1 Introduction
      2. 10.2 Nature of user-defined functions
        1. 10.2.1 User-defined scalar functions
        2. 10.2.2 User-defined table functions
      3. 10.3 Type of user-defined functions
        1. 10.3.1 Sourced UDFs
        2. 10.3.2 SQL UDFs
        3. 10.3.3 External UDFs
      4. 10.4 Creating user-defined functions
        1. 10.4.1 CREATE FUNCTION
        2. 10.4.2 Modifying a UDF
        3. 10.4.3 Dropping a UDF
      5. 10.5 Resolving a UDF
        1. 10.5.1 UDF overloading and function signature
        2. 10.5.2 Parameter matching and promotion
        3. 10.5.3 Function path and the function selection algorithm
      6. 10.6 System catalog tables
        1. 10.6.1 SYSROUTINES catalog
        2. 10.6.2 SYSPARMS catalog
      7. 10.7 Authorization and adopted authority
      8. 10.8 Transaction management considerations
      9. 10.9 Coding considerations
    16. Chapter 11. External user-defined functions
      1. 11.1 User-defined function considerations
      2. 11.2 Registering an external UDF
        1. 11.2.1 Registering an external UDF with System i Navigator
        2. 11.2.2 Registering a Java UDF with System i Navigator
      3. 11.3 Parameter styles in external UDFs
        1. 11.3.1 SQL parameter style
        2. 11.3.2 DB2SQL parameter style
        3. 11.3.3 GENERAL parameter style
        4. 11.3.4 GENERAL WITH NULLS parameter style
        5. 11.3.5 DB2GENERAL parameter style
        6. 11.3.6 JAVA parameter style
      4. 11.4 Scratchpad in UDFs and UDTFs
      5. 11.5 UDF and UDTF calling sequence
      6. 11.6 Coding an external UDF
        1. 11.6.1 Coding the SQL parameter style
        2. 11.6.2 Coding the DB2SQL parameter style
        3. 11.6.3 Coding the GENERAL parameter style
        4. 11.6.4 Coding the GENERAL WITH NULLS parameter style
        5. 11.6.5 Coding the DB2GENERAL parameter style
        6. 11.6.6 Coding the JAVA parameter style
      7. 11.7 Error handling in external UDFs
        1. 11.7.1 Error handling with the DB2SQL parameter style
        2. 11.7.2 Error handling with the DB2GENERAL parameter style
      8. 11.8 Pointer arithmetic and the scratchpad
        1. 11.8.1 Debugging external UDFs
      9. 11.9 Coding example for an external user-defined table function
    17. Appendix A. Sample ILE C program that uses the QDBRTVFD API
      1. Sample ILE C program that uses the QDBRTVFD API
    18. Appendix B. Order Entry application: Detailed flow
      1. Program flow for the Insert Order Header program
      2. Program description for the Insert Order Header program
      3. Program flow for the Insert Order Detail program
      4. Program description for the Insert Order Detail program
      5. Program flow for the Finalize Order program
      6. Program description for the Finalize Order program
    19. Appendix C. Additional material
      1. Locating the web material
      2. Using the web material
    20. Related publications
      1. IBM Redbooks publications
      2. Referenced websites
      3. Help from IBM
    21. Back cover

    Product information

    • Title: External Procedures, Triggers, and User-Defined Functions on IBM DB2 for i
    • Author(s): Hernando Bedoya, Fredy Cruz, Daniel Lema, Satid Singkorapoom
    • Release date: April 2016
    • Publisher(s): IBM Redbooks
    • ISBN: 9780738441597