DB2 for i SQL Stored Procedures and User-Defined Functions in 2026: CREATE PROCEDURE, Cursors, UDFs, and Reusable Business Logic in DB2

Stored procedures and user-defined functions let you put business logic in the database layer of DB2 for i, where any language and any access method can call it. This guide covers CREATE PROCEDURE, parameters, variables and control flow, cursors, result sets, error handling with handlers, GET DIAGNOSTICS and SIGNAL, calling procedures from RPG, CL, and other languages, scalar and table UDFs, and external procedures backed by ILE programs.

Why SQL Stored Procedures on IBM i

A stored procedure on DB2 for i is a database object in a schema (library). Once created, it can be called from RPG, CL, Java, Node.js, Python, or any other interface that can issue an SQL CALL statement, so logic written once runs the same way regardless of which application tier calls it.

The practical advantages for IBM i shops are:

  • Fewer round trips. A procedure that processes 500 order records does so in a single CALL instead of 500 statements across a network connection, which matters for ODBC and JDBC callers.
  • Centralised business rules. Rules such as “an order cannot be approved if the customer’s credit limit is exceeded” live in one place instead of being duplicated across RPG, Java, and Node.js applications.
  • Easier testing. You can test a procedure directly from ACS Run SQL Scripts with a CALL statement, without deploying an application.
  • Incremental modernisation. Wrapping proven business logic in procedures lets new applications reuse it without re-implementing it.

There are two kinds of procedure:

  • SQL procedures have a body written in SQL with procedural extensions such as IF, LOOP, and cursors. They are the usual choice for new database logic.
  • External procedures register an existing ILE program (RPG, COBOL, C, or CL) so that it can be called with SQL CALL. They are covered at the end of this guide.

CREATE PROCEDURE Basics

CREATE OR REPLACE PROCEDURE creates the procedure, or replaces it if it already exists, so you can rerun the script as you iterate.

CREATE OR REPLACE PROCEDURE ORDLIB.LOG_SYSTEM_EVENT (
    IN P_EVTTYPE VARCHAR(20),
    IN P_EVTMSG  VARCHAR(200)
)
LANGUAGE SQL
SPECIFIC ORDLIB.LOG_SYSTEM_EVENT
MODIFIES SQL DATA
BEGIN
    INSERT INTO ORDLIB.SYSEVTLOG (EVTTYPE, EVTMSG, EVTTIME)
    VALUES (P_EVTTYPE, P_EVTMSG, CURRENT TIMESTAMP);
END;

CALL ORDLIB.LOG_SYSTEM_EVENT('STARTUP', 'Application initialised by batch job ORDBATCH');

Key clauses:

  • LANGUAGE SQL — the body is written in SQL. External procedures specify the language of the program instead, such as LANGUAGE RPGLE or LANGUAGE CL.
  • SPECIFIC — a unique name for this procedure within the schema. It distinguishes overloaded procedures that share a name but have different parameters, and it can be used in DROP SPECIFIC PROCEDURE and COMMENT ON SPECIFIC PROCEDURE.
  • SQL data access — MODIFIES SQL DATA allows the procedure to change data; the alternatives are READS SQL DATA, CONTAINS SQL, and NO SQL.

To document a procedure, use a separate statement after it has been created:

COMMENT ON SPECIFIC PROCEDURE ORDLIB.LOG_SYSTEM_EVENT
    IS 'Write one row to the system event log';

To remove a procedure:

DROP SPECIFIC PROCEDURE ORDLIB.LOG_SYSTEM_EVENT;

IN, OUT, and INOUT Parameters

  • IN parameters pass values into the procedure. The procedure can use them, but any change it makes is not returned to the caller.
  • OUT parameters return values to the caller.
  • INOUT parameters pass a value in and return the possibly changed value.

A procedure that returns a customer’s credit status in OUT parameters:

CREATE OR REPLACE PROCEDURE APPLIB.GET_CUST_CREDIT_STATUS (
    IN  P_CUST_NO    CHAR(8),
    OUT P_CUST_NAME  VARCHAR(50),
    OUT P_CREDIT_LMT DECIMAL(11,2),
    OUT P_YTD_SPEND  DECIMAL(11,2),
    OUT P_STATUS     CHAR(1)        -- G good, W warning, X exceeded, N not found
)
LANGUAGE SQL
SPECIFIC APPLIB.GET_CUST_CREDIT_STATUS
READS SQL DATA
BEGIN
    DECLARE V_FOUND INT DEFAULT 1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_FOUND = 0;

    SELECT C.CUST_NAME, C.CREDIT_LIMIT, COALESCE(SUM(O.ORD_AMOUNT), 0)
      INTO P_CUST_NAME, P_CREDIT_LMT, P_YTD_SPEND
      FROM SALESLIB.CUSTMST C
      LEFT JOIN ORDLIB.ORDMST O
             ON O.CUST_NO = C.CUST_NO
            AND YEAR(O.ORD_DATE) = YEAR(CURRENT DATE)
     WHERE C.CUST_NO = P_CUST_NO
     GROUP BY C.CUST_NAME, C.CREDIT_LIMIT;

    IF V_FOUND = 0 THEN
        SET P_STATUS = 'N';
    ELSEIF P_YTD_SPEND >= P_CREDIT_LMT THEN
        SET P_STATUS = 'X';
    ELSEIF P_YTD_SPEND >= P_CREDIT_LMT * 0.80 THEN
        SET P_STATUS = 'W';
    ELSE
        SET P_STATUS = 'G';
    END IF;
END;

In ACS Run SQL Scripts, pass ? for each OUT parameter and the tool displays the returned values:

CALL APPLIB.GET_CUST_CREDIT_STATUS('C0001234', ?, ?, ?, ?);

An INOUT parameter is passed in, changed, and returned:

CREATE OR REPLACE PROCEDURE APPLIB.APPLY_PRICE_DISCOUNT (
    IN    P_CUST_CLASS CHAR(1),
    INOUT P_PRICE      DECIMAL(9,2)
)
LANGUAGE SQL
SPECIFIC APPLIB.APPLY_PRICE_DISCOUNT
CONTAINS SQL
BEGIN
    SET P_PRICE = P_PRICE * (1 - CASE P_CUST_CLASS
                                     WHEN 'A' THEN 0.15
                                     WHEN 'B' THEN 0.10
                                     ELSE 0.05
                                 END);
END;

Variables, Assignment, and Conditionals

Variables are declared with DECLARE and assigned with SET or SELECT ... INTO. IF, ELSEIF, and CASE provide branching.

CREATE OR REPLACE PROCEDURE ORDLIB.CALC_ORDER_DISCOUNT (
    IN  P_CUSTNO  CHAR(7),
    IN  P_ORDAMT  DECIMAL(11,2),
    OUT P_DISCAMT DECIMAL(11,2),
    OUT P_DISCPCT DECIMAL(5,2)
)
LANGUAGE SQL
SPECIFIC ORDLIB.CALC_ORDER_DISCOUNT
READS SQL DATA
BEGIN
    DECLARE V_CRTTIER CHAR(2) DEFAULT 'ST';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_CRTTIER = 'ST';

    SELECT CRTTIER INTO V_CRTTIER
      FROM CUSTLIB.CUSTMST
     WHERE CUSTNO = P_CUSTNO;

    IF V_CRTTIER = 'PL' THEN
        SET P_DISCPCT = CASE WHEN P_ORDAMT > 5000.00 THEN 15.00 ELSE 10.00 END;
    ELSEIF V_CRTTIER = 'GO' THEN
        SET P_DISCPCT = CASE WHEN P_ORDAMT > 2500.00 THEN 8.00 ELSE 5.00 END;
    ELSE
        SET P_DISCPCT = CASE WHEN P_ORDAMT > 1000.00 THEN 2.00 ELSE 0.00 END;
    END IF;

    SET P_DISCAMT = P_ORDAMT * (P_DISCPCT / 100);
END;

Cursor Loops in SQL Procedures

Cursor loops are the SQL procedure equivalent of an RPG read loop. Inside a compound statement, declarations must appear in this order: variables and conditions, then cursors, then handlers, then the executable statements.

CREATE OR REPLACE PROCEDURE ORDLIB.APPLY_LATE_FEE (
    IN P_CUTOFFDT DATE,
    IN P_FEERATE  DECIMAL(5,4)
)
LANGUAGE SQL
SPECIFIC ORDLIB.APPLY_LATE_FEE
MODIFIES SQL DATA
BEGIN
    DECLARE V_ORDNO  CHAR(8);
    DECLARE V_CUSTNO CHAR(7);
    DECLARE V_ORDAMT DECIMAL(11,2);
    DECLARE V_DONE   INT DEFAULT 0;

    DECLARE C_OVERDUE CURSOR FOR
        SELECT ORDNO, CUSTNO, ORDAMT
          FROM ORDLIB.ORDMST
         WHERE ORDSTS  = 'OP'          -- open order
           AND ORDDUE  < P_CUTOFFDT    -- past due
           AND FEECHGD = 'N'           -- fee not yet charged
        FOR UPDATE OF ORDSTS, FEECHGD, FEEAMT;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_DONE = 1;

    OPEN C_OVERDUE;

    FETCH_LOOP: LOOP
        FETCH C_OVERDUE INTO V_ORDNO, V_CUSTNO, V_ORDAMT;
        IF V_DONE = 1 THEN
            LEAVE FETCH_LOOP;
        END IF;

        UPDATE ORDLIB.ORDMST
           SET ORDSTS  = 'OD',
               FEECHGD = 'Y',
               FEEAMT  = V_ORDAMT * P_FEERATE
         WHERE CURRENT OF C_OVERDUE;

        INSERT INTO ORDLIB.ORDAUDIT
               (ORDNO, CUSTNO, AUDTYPE, AUDAMT, AUDTIME, AUDUSER)
        VALUES (V_ORDNO, V_CUSTNO, 'LATEFEE',
                V_ORDAMT * P_FEERATE, CURRENT TIMESTAMP, SESSION_USER);
    END LOOP FETCH_LOOP;

    CLOSE C_OVERDUE;
END;

WHERE CURRENT OF updates exactly the row that was just fetched. The NOT FOUND handler sets a flag when FETCH reaches the end of the result set (SQLSTATE 02000), and the loop leaves on that flag.

For simple row-by-row processing, a FOR loop is shorter because it opens, fetches, and closes the cursor for you:

FOR V AS SELECT ORDNO AS V_ORDNO, ORDAMT AS V_ORDAMT
            FROM ORDLIB.ORDMST
           WHERE ORDSTS = 'OP' DO
    INSERT INTO ORDLIB.ORDAUDIT (ORDNO, AUDTYPE, AUDAMT, AUDTIME)
    VALUES (V_ORDNO, 'REVIEW', V_ORDAMT, CURRENT TIMESTAMP);
END FOR;

Returning Result Sets

A procedure can return rows to its caller as a result set. Declare DYNAMIC RESULT SETS, declare the cursor WITH RETURN, open it, and leave it open:

CREATE OR REPLACE PROCEDURE ORDLIB.LIST_OPEN_ORDERS (
    IN P_CUSTNO CHAR(7)
)
LANGUAGE SQL
SPECIFIC ORDLIB.LIST_OPEN_ORDERS
READS SQL DATA
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE C_ORDERS CURSOR WITH RETURN TO CALLER FOR
        SELECT ORDNO, ORDDAT, ORDDUE, ORDAMT
          FROM ORDLIB.ORDMST
         WHERE CUSTNO = P_CUSTNO
           AND ORDSTS = 'OP'
         ORDER BY ORDDUE;

    OPEN C_ORDERS;
END;

ACS Run SQL Scripts, JDBC, ODBC, and Node.js callers receive the rows directly. This is often the cleanest way to give non-IBM i applications a controlled, parameterised query.

Error Handling: Handlers, SQLSTATE, GET DIAGNOSTICS, and SIGNAL

Every SQL statement sets an SQLSTATE. Class 00 is success, 01 is a warning, 02 is no data, and every other class is an error (SQLEXCEPTION). Handlers decide what happens when a condition occurs:

  • A CONTINUE handler runs its action and then continues with the statement after the one that raised the condition. It suits NOT FOUND flags and recoverable conditions.
  • An EXIT handler runs its action and then leaves the compound statement. It suits unexpected errors, where continuing would be unsafe.

GET DIAGNOSTICS retrieves the SQLSTATE and message text inside a handler, and SIGNAL raises your own error with a custom SQLSTATE and message:

CREATE OR REPLACE PROCEDURE APPLIB.SAFE_ORDER_INSERT (
    IN  P_ORD_NO  CHAR(10),
    IN  P_CUST_NO CHAR(8),
    IN  P_AMOUNT  DECIMAL(11,2),
    OUT P_RESULT  CHAR(1),          -- S success, E error
    OUT P_MESSAGE VARCHAR(200)
)
LANGUAGE SQL
SPECIFIC APPLIB.SAFE_ORDER_INSERT
MODIFIES SQL DATA
BEGIN
    DECLARE V_STATE CHAR(5);
    DECLARE V_TEXT  VARCHAR(200);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS EXCEPTION 1
            V_STATE = RETURNED_SQLSTATE,
            V_TEXT  = MESSAGE_TEXT;
        SET P_RESULT  = 'E';
        SET P_MESSAGE = 'SQLSTATE ' || V_STATE || ': ' || V_TEXT;
    END;

    IF NOT EXISTS (SELECT 1 FROM SALESLIB.CUSTMST WHERE CUST_NO = P_CUST_NO) THEN
        SIGNAL SQLSTATE '75010'
            SET MESSAGE_TEXT = 'Customer not found: ' || TRIM(P_CUST_NO);
    END IF;

    INSERT INTO ORDLIB.ORDMST (ORD_NO, CUST_NO, ORD_AMOUNT, ORD_DATE, STATUS)
    VALUES (P_ORD_NO, P_CUST_NO, P_AMOUNT, CURRENT DATE, 'O');

    SET P_RESULT  = 'S';
    SET P_MESSAGE = 'Order inserted';
END;

The custom SQLSTATE 75010 starts with 7, one of the classes DB2 leaves free for application-defined conditions, so it will not collide with a DB2-supplied SQLSTATE. Because the EXIT handler catches it, the caller receives P_RESULT = 'E' and the message instead of an exception. If the caller should receive the error itself, use RESIGNAL in the handler after logging.

Leave transaction control to the caller where you can: the calling program knows whether the procedure is one step in a larger unit of work, and committing inside the procedure would also commit the caller’s earlier changes.

Calling Stored Procedures from RPG

From ILE RPG, use embedded SQL in an SQLRPGLE source member compiled with CRTSQLRPGI. OUT and INOUT parameters are returned into host variables:

**FREE
Ctl-Opt DftActGrp(*No) ActGrp('PROCGRP');

Dcl-S CustNo    Char(8)      Inz('C0001234');
Dcl-S CustName  Varchar(50);
Dcl-S CreditLmt Packed(11:2);
Dcl-S YtdSpend  Packed(11:2);
Dcl-S CreditSts Char(1);

Exec SQL
    CALL APPLIB.GET_CUST_CREDIT_STATUS(:CustNo, :CustName, :CreditLmt,
                                       :YtdSpend, :CreditSts);

If SQLCODE < 0;
    Dsply ('Procedure call failed, SQLSTATE ' + SQLSTATE);
Else;
    Select;
    When CreditSts = 'X';
        Dsply ('Credit exceeded: ' + %TrimR(CustName));
    When CreditSts = 'W';
        Dsply ('Credit warning: ' + %TrimR(CustName));
    When CreditSts = 'N';
        Dsply ('Customer not found');
    Other;
        Dsply ('Credit OK: ' + %TrimR(CustName));
    EndSl;
EndIf;

*InLR = *On;
CRTSQLRPGI OBJ(APPLIB/CREDITCHK) SRCFILE(APPLIB/QRPGLESRC) +
           SRCMBR(CREDITCHK) COMMIT(*NONE) OBJTYPE(*PGM)

Check SQLCODE or SQLSTATE after every CALL. A negative SQLCODE is an error; a positive value is a warning, for example +466 when the procedure returned result sets.

Calling Stored Procedures from CL

RUNSQL runs a single SQL statement from CL, which is convenient for procedures that have only IN parameters:

RUNSQL SQL('CALL APPLIB.PURGE_OLD_AUDIT_LOG()') COMMIT(*NONE)

RUNSQL SQL('CALL ORDLIB.APPLY_LATE_FEE(CURRENT DATE - 30 DAYS, 0.0150)') +
       COMMIT(*NONE)

RUNSQL cannot receive OUT or INOUT parameter values and does not accept parameter markers. When CL needs a value back, call the procedure from a small SQLRPGLE program and return the value as a program parameter, or have the procedure write its result somewhere the CL program can read.

Calling Stored Procedures from Other Languages

Java (JDBC), Python and Node.js (ODBC), and .NET clients call procedures with CALL and parameter markers, registering OUT parameters through their driver. On IBM i itself, Node.js applications can use IBM’s idb-connector or idb-pconnector packages, which run only in IBM i PASE and connect to the local database with *LOCAL. Applications on other servers use the IBM i Access ODBC driver or the JDBC driver from JTOpen instead.

Scalar User-Defined Functions

A scalar UDF returns one value and can be used wherever an expression is allowed: in a SELECT list, a WHERE clause, or a HAVING clause.

-- Format a 10-digit phone number as (NNN) NNN-NNNN
CREATE OR REPLACE FUNCTION CUSTLIB.FORMATTED_PHONE (
    P_RAWPHONE CHAR(10)
)
RETURNS VARCHAR(14)
LANGUAGE SQL
SPECIFIC CUSTLIB.FORMATTED_PHONE
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
    CASE
        WHEN P_RAWPHONE IS NULL OR LENGTH(TRIM(P_RAWPHONE)) <> 10
            THEN COALESCE(TRIM(P_RAWPHONE), 'UNKNOWN')
        ELSE '(' || SUBSTR(P_RAWPHONE, 1, 3) || ') ' ||
             SUBSTR(P_RAWPHONE, 4, 3) || '-' ||
             SUBSTR(P_RAWPHONE, 7, 4)
    END;

SELECT CUSTNO, CUSTNAM,
       CUSTLIB.FORMATTED_PHONE(CUSTPH1) AS PHONE
  FROM CUSTLIB.CUSTMST
 WHERE CUSTAT = 'AC'
 ORDER BY CUSTNAM;

DETERMINISTIC tells DB2 that the same input always produces the same output, which gives the optimiser more freedom in how often it evaluates the function. CONTAINS SQL means the function uses SQL expressions but does not read or change table data.

Table User-Defined Functions

A table UDF returns rows and is referenced with TABLE() in a FROM clause, so it behaves like a parameterised view:

CREATE OR REPLACE FUNCTION ORDLIB.GET_CUST_ORDERS (
    P_CUSTNO CHAR(7)
)
RETURNS TABLE (
    ORDNO  CHAR(8),
    ORDDAT DATE,
    ORDDUE DATE,
    ORDAMT DECIMAL(11,2),
    ORDSTS CHAR(2)
)
LANGUAGE SQL
SPECIFIC ORDLIB.GET_CUST_ORDERS
READS SQL DATA
NO EXTERNAL ACTION
RETURN
    SELECT ORDNO, ORDDAT, ORDDUE, ORDAMT, ORDSTS
      FROM ORDLIB.ORDMST
     WHERE CUSTNO = P_CUSTNO
       AND ORDSTS IN ('OP', 'PD', 'OD');
-- Open orders for one customer
SELECT *
  FROM TABLE(ORDLIB.GET_CUST_ORDERS('CUST001')) AS T
 ORDER BY T.ORDDUE;

-- Apply the function to every active customer
SELECT C.CUSTNAM, T.ORDNO, T.ORDDUE, T.ORDAMT
  FROM CUSTLIB.CUSTMST C,
       TABLE(ORDLIB.GET_CUST_ORDERS(C.CUSTNO)) AS T
 WHERE C.CUSTAT = 'AC'
   AND T.ORDDUE < CURRENT DATE
 ORDER BY C.CUSTNAM, T.ORDDUE;

The second query passes a column from the outer table into the function, so the function runs once for each customer row, much like a lateral join. Put ORDER BY in the query that uses the function; the order of rows returned by a function itself is not guaranteed.

External Stored Procedures

When a procedure needs to run CL commands, work with the IFS, call service programs, or reuse an existing ILE program, register that program as an external procedure:

CREATE OR REPLACE PROCEDURE ORDLIB.SEND_ORDER_CONFIRM (
    IN P_ORDNO CHAR(8),
    IN P_EMAIL VARCHAR(100)
)
LANGUAGE RPGLE
SPECIFIC ORDLIB.SEND_ORDER_CONFIRM
EXTERNAL NAME 'ORDLIB/SNDORDCFM'
PARAMETER STYLE GENERAL
MODIFIES SQL DATA;

The program ORDLIB/SNDORDCFM receives the parameters in the order they are declared. With PARAMETER STYLE GENERAL, no null indicators are passed. OUT parameters work the same way: the program sets the corresponding parameter before it returns. To the SQL caller, an external procedure looks exactly like an SQL procedure.

Choosing between them:

  • Use SQL procedures when the logic is relational: reading and changing tables and applying business rules to query results. They are managed entirely through SQL.
  • Use external procedures when you need CL commands, IFS operations, data queues, existing service programs, or other ILE capabilities.

Finding Procedures and Functions in the Catalog

QSYS2.SYSROUTINES lists every SQL routine:

SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_BODY, EXTERNAL_NAME,
       EXTERNAL_LANGUAGE, SQL_DATA_ACCESS, MAX_DYNAMIC_RESULT_SETS,
       SPECIFIC_NAME
  FROM QSYS2.SYSROUTINES
 WHERE ROUTINE_SCHEMA = 'ORDLIB'
 ORDER BY ROUTINE_TYPE, ROUTINE_NAME;

ROUTINE_TYPE distinguishes PROCEDURE from FUNCTION, and ROUTINE_BODY distinguishes SQL routines from external ones. For SQL routines, ROUTINE_DEFINITION contains the routine body. Use this view to document and audit the logic that lives in your database.

Summary

DB2 for i stored procedures put reusable business logic in the database: CREATE PROCEDURE with IN, OUT, and INOUT parameters, cursors and result sets for row processing, and handlers with GET DIAGNOSTICS and SIGNAL for predictable error handling. Scalar and table UDFs bring the same reuse into ordinary queries, and external procedures expose existing ILE programs through SQL. Call them from RPG with embedded SQL, from CL with RUNSQL when only IN parameters are needed, and from Java, Python, and Node.js through their database drivers.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top