A COBOL batch step that sends five separate SQL statements across a distributed connection can spend more time waiting than working. A Db2 stored procedure can put those statements behind one CALL, run the database logic near the data, and return output parameters or result sets to the caller.
This article refreshes the original stored procedure page for Db2 for z/OS developers. It covers native SQL procedures, external procedures, parameters, result sets, COBOL calls, package/security checks, and the line between a stored procedure and a user-defined function.
What Is a Db2 Stored Procedure?
A Db2 stored procedure is a routine that is created at the database server and invoked with the SQL CALL statement. IBM documents CREATE PROCEDURE as the statement that defines an SQL procedure, or a version of a procedure, at the current server and specifies the procedure source statements.
A stored procedure is a good fit when the logic performs a database operation rather than a simple expression. It can validate input, run several SQL statements, return status values, and expose a stable interface to COBOL, Java, CICS, batch, or distributed applications.
When a Stored Procedure Is the Right Object
Use a stored procedure when the work is a unit of database behavior. Do not create one just to hide a single simple SELECT unless the procedure gives a clear operational benefit.
| Requirement | Stored procedure fit? | Reason |
|---|---|---|
| Validate an account and insert an audit row. | Yes | Multiple SQL statements belong behind one controlled call. |
| Return customer detail rows to a service. | Yes | A procedure can return a dynamic result set when defined for it. |
| Normalize a one-column status code in a query. | No | A scalar UDF or direct expression is usually cleaner. |
| Run privileged database work from several applications. | Maybe | Security and package ownership can be controlled, but it needs careful grants. |
Native SQL Procedure Example
A native SQL procedure is written in SQL PL. The example below returns a formatted employee name and a simple status code. It is intentionally small; production procedures should use site naming, error handling, and deployment standards.
CREATE PROCEDURE HR.GET_EMPLOYEE_NAME
(IN P_EMPNO CHAR(6),
OUT P_EMP_NAME VARCHAR(40),
OUT P_STATUS CHAR(1))
LANGUAGE SQL
READS SQL DATA
DYNAMIC RESULT SETS 0
P1: BEGIN
DECLARE V_FIRST VARCHAR(12);
DECLARE V_LAST VARCHAR(15);
SET P_STATUS = 'N';
SELECT FIRSTNME, LASTNAME
INTO V_FIRST, V_LAST
FROM DSN8C10.EMP
WHERE EMPNO = P_EMPNO;
SET P_EMP_NAME = STRIP(V_LAST) || ', ' || STRIP(V_FIRST);
SET P_STATUS = 'Y';
END P1;The procedure has one input parameter and two output parameters. A caller should always check the output status and SQL return code. Do not assume a procedure completed business work just because the CALL returned to the program.
CALL from a COBOL Program
IBM's CALL statement reference describes how arguments map to stored procedure parameters. In COBOL, OUT and INOUT parameters must be host variables because Db2 needs somewhere to place returned values.
EXEC SQL
CALL HR.GET_EMPLOYEE_NAME
(:WS-EMPNO,
:WS-EMP-NAME,
:WS-STATUS)
END-EXEC.
IF SQLCODE = 0 AND WS-STATUS = 'Y'
PERFORM WRITE-EMPLOYEE-LINE
ELSE
PERFORM HANDLE-PROCEDURE-ERROR
END-IF.Match host variable types to the procedure definition. If the procedure defines VARCHAR(40), use the correct varying-length host variable pattern or the site-standard DCLGEN copybook. The DB2 Host Variables and Structures article is the companion check when returned values look truncated or padded.
IN, OUT, and INOUT Parameters
Parameter direction is part of the interface. Changing it is not a cosmetic edit; it can break callers and package behavior.
| Parameter type | Meaning | COBOL caller rule |
|---|---|---|
IN |
Caller passes a value into the procedure. | Can be a host variable or compatible expression. |
OUT |
Procedure returns a value to the caller. | Must be a host variable. |
INOUT |
Caller passes a value and receives a possibly changed value. | Must be a host variable and must be initialized before the call. |
When a procedure returns an error, do not trust OUT parameter contents unless the procedure contract says they are set before the failing point. In many designs, the safer pattern is a status parameter plus logged diagnostics.
Returning Result Sets
A stored procedure can return result sets when it is defined with DYNAMIC RESULT SETS. This is useful for distributed callers and service layers that need a list of rows rather than a single output value.
CREATE PROCEDURE HR.LIST_DEPT_EMP
(IN P_DEPTNO CHAR(3))
LANGUAGE SQL
READS SQL DATA
DYNAMIC RESULT SETS 1
P1: BEGIN
DECLARE C1 CURSOR WITH RETURN FOR
SELECT EMPNO, FIRSTNME, LASTNAME
FROM DSN8C10.EMP
WHERE WORKDEPT = P_DEPTNO
ORDER BY LASTNAME, FIRSTNME;
OPEN C1;
END P1;For COBOL callers, confirm how your site handles result-set locators or whether the procedure is mainly for DRDA/JDBC callers. A batch program that only needs one status value should not receive a cursor just because a result set looks flexible.
External Stored Procedures
An external stored procedure is implemented in a host language such as COBOL, C, Java, PL/I, or Assembler, then registered to Db2. Use this path when the procedure must reuse existing tested code or when the logic cannot be expressed cleanly in SQL PL.
CREATE PROCEDURE ACCT.POST_PAYMENT
(IN P_ACCOUNT_NO CHAR(12),
IN P_AMOUNT DECIMAL(13,2),
OUT P_STATUS CHAR(1))
LANGUAGE COBOL
EXTERNAL NAME 'PAYPOST'
PARAMETER STYLE SQL
MODIFIES SQL DATA
WLM ENVIRONMENT PAYWLM
COMMIT ON RETURN NO;External procedures need operational discipline: WLM environment, load library promotion, Language Environment options, RACF access, package grants, abend handling, and rollback behavior. A missing load module can turn a clean application deploy into a production call failure.
Stored Procedure or UDF?
The previous refreshed page covers Db2 User-Defined Functions. Keep the line clear: a UDF belongs inside an SQL expression; a stored procedure is called as a unit of work.
| Question | Choose stored procedure | Choose UDF |
|---|---|---|
| Does the routine run multiple SQL statements? | Usually yes | Usually no |
Is the routine called with SQL CALL? |
Yes | No |
Can it appear in a WHERE or select list? |
No | Yes |
| Does it return output parameters or result sets? | Yes | No, except table functions return rows inside SQL. |
Performance and Security Checks
A stored procedure can reduce network trips, but it can also hide expensive SQL behind a short call. Treat it like production code, not a shortcut.
- Explain the SQL statements inside the procedure, especially statements that join large tables.
- Confirm package ownership and
EXECUTEprivileges for the caller and underlying packages. - Choose
READS SQL DATAorMODIFIES SQL DATAto match the actual procedure behavior. - Review
COMMIT ON RETURNwith the application transaction design. - For external procedures, verify WLM environment, RACF access, and load module availability.
- Log enough status detail to diagnose failures without exposing sensitive data.
The Db2 SQL Optimization Tips for COBOL Programs post covers access-path work that should happen before a procedure is promoted.
Deployment Checklist
- Keep procedure DDL, grants, package bind steps, and rollback DDL in the same change record.
- Preserve parameter order and data types unless every caller is being changed at the same time.
- Test null input, not-found conditions, duplicate-row conditions, and SQL error paths.
- Check whether static callers need bind or rebind after adding the
CALL. - Document result-set expectations for COBOL, JDBC, and service callers separately.
- Run a production-volume test when the procedure replaces several remote SQL calls.
FAQ
How is a Db2 stored procedure called?
A Db2 stored procedure is called with the SQL CALL statement. COBOL programs use embedded SQL and pass host variables for parameters.
Can a stored procedure return rows?
Yes. A procedure can return dynamic result sets when it is defined for result sets and opens the relevant cursor before returning.
Should business logic be placed in a stored procedure?
Place database-centered business rules in a stored procedure when they need controlled SQL execution near the data. Keep screen flow, file formatting, and application orchestration outside the database routine.
A stored procedure should make database work easier to call and easier to control. If the procedure only hides unclear SQL, fix the SQL and interface before promoting it to production.
