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
CALLinstead 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
CALLstatement, 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 RPGLEorLANGUAGE 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 PROCEDUREandCOMMENT ON SPECIFIC PROCEDURE. - SQL data access —
MODIFIES SQL DATAallows the procedure to change data; the alternatives areREADS SQL DATA,CONTAINS SQL, andNO 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 FOUNDflags 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.