Db2 Stored Procedure Guide for COBOL Programs



Db2 stored procedures group database work behind CALL.

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 EXECUTE privileges for the caller and underlying packages.
• Choose READS SQL DATA or MODIFIES SQL DATA to match the actual procedure behavior.
• Review COMMIT ON RETURN with 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.

Db2 User-Defined Functions: SQL and External UDF Guide



Db2 UDFs keep reusable logic close to SQL.

A payroll query that repeats the same allowance calculation in ten COBOL programs is hard to test and easy to change in one place only. A Db2 user-defined function can move that calculation behind one SQL routine, so the application calls PAYROLL.NET_PAY(...) instead of carrying copy-pasted expression logic through every cursor.

This refresh keeps the original topic, but narrows it to what a Db2 for z/OS developer needs: when a UDF belongs in SQL, how scalar and table functions differ, when an external COBOL or C routine is justified, and which options can affect performance or security.

What Is a Db2 User-Defined Function?

A user-defined function, or UDF, is a Db2 routine that you create with CREATE FUNCTION and then call from SQL. IBM's Db2 for z/OS documentation describes CREATE FUNCTION as the statement that registers a user-defined function with the database server. The function can return a single scalar value or a table, depending on how it is defined.

From an application point of view, the most common use is a scalar call in a SELECT, WHERE, or VALUES statement.

SELECT EMPNO,
       PAYROLL.NET_PAY(SALARY, BONUS, TAX_CODE) AS NET_AMOUNT
  FROM PAYROLL.EMP_PAY
 WHERE PAYROLL.ACTIVE_EMP(STATUS, TERM_DATE) = 'Y';

That example hides the rule behind a named function. It also creates a contract. If the rule changes, the DBA and development team can review the function definition instead of searching every batch program for a similar expression.

Use a UDF for Reusable SQL Logic

A UDF is a good fit when the result belongs inside a SQL expression. Date normalization, account-number formatting, small code translations, financial rounding rules, and common eligibility checks are typical examples.

Use case	Good UDF fit?	Reason
Return a normalized branch code for each account row.	Yes	The result is a scalar value used directly in SQL.
Calculate net pay from salary, allowance, and tax code.	Usually	The same expression is reused by reports and batch programs.
Post an accounting transaction across several tables.	No	That is transaction logic. Use a stored procedure or application service.
Return a filtered set of rows from a table-like routine.	Maybe	An SQL table function can work, but compare it with a view or direct query.

Scalar, Table, SQL, External, and Sourced Functions

The old post listed UDF categories but did not explain when each matters. Db2 supports several function forms, and the choice affects how the function is written, invoked, secured, and tuned.

Function type	What it returns	Typical use
SQL scalar function	One value	Reusable expression written in SQL PL or a single return expression.
SQL table function	A row set	Table-like result that can be referenced in a query.
External scalar function	One value	Logic implemented in COBOL, C, Java, PL/I, or Assembler.
External table function	A row set	External routine returns rows to the invoking SQL statement.
Sourced function	Depends on source function	Reuse an existing built-in or user-defined function with a distinct type or different signature.

For a COBOL application team, start with a SQL scalar function when the rule can be stated in SQL. Move to an external function only when the logic already exists in a tested language routine or needs facilities that SQL cannot express cleanly.

Create a Simple SQL Scalar UDF

The simplest UDF is a scalar SQL function. The function below standardizes a status value before the calling program compares it. A rule like this is small enough to keep in SQL and easy to test with VALUES.

CREATE FUNCTION APP.CLEAN_STATUS
       (P_STATUS CHAR(1))
  RETURNS CHAR(1)
  LANGUAGE SQL
  DETERMINISTIC
  NO EXTERNAL ACTION
  RETURN
    CASE
      WHEN P_STATUS IN ('A', 'I', 'S') THEN P_STATUS
      WHEN P_STATUS IS NULL            THEN 'U'
      ELSE 'X'
    END;

Test it before wiring it into a batch cursor.

VALUES APP.CLEAN_STATUS('A');
VALUES APP.CLEAN_STATUS(' ');
VALUES APP.CLEAN_STATUS(CAST(NULL AS CHAR(1)));

If the function is deterministic, tell Db2. If it reads tables, calls an external service, or depends on the current time, do not mark it deterministic just because that looks faster. The declaration should match the real behavior.

Call a UDF from COBOL SQL

A COBOL program calls a Db2 UDF inside embedded SQL the same way it calls many built-in functions: qualify the function when needed, pass host variables, and fetch the result into a compatible host variable.

EXEC SQL
    SELECT APP.CLEAN_STATUS(STATUS)
      INTO :WS-CLEAN-STATUS
      FROM CUSTOMER_STATUS
     WHERE CUSTOMER_ID = :WS-CUSTOMER-ID
END-EXEC.

Keep the host variable data type close to the UDF return type. A CHAR(1) result belongs in a one-byte character field, not a loosely sized display field that later gets compared with padded values. The related DB2 Host Variables and Structures guide is the right companion when copybook definitions are the problem.

External UDFs Need More Operational Control

An external UDF registers code that lives outside the SQL function body. IBM's external scalar function reference notes that the statement registers an external scalar function and that a scalar function returns one value each time it is invoked. External definitions can specify options such as language, parameter style, WLM environment, security behavior, null handling, and SQL data access.

CREATE FUNCTION APP.RISK_SCORE
       (P_ACCT_NO CHAR(12), P_BALANCE DECIMAL(13,2))
  RETURNS INTEGER
  EXTERNAL NAME 'RSKSCORE'
  LANGUAGE COBOL
  PARAMETER STYLE SQL
  FENCED
  DETERMINISTIC
  NO SQL
  RETURNS NULL ON NULL INPUT
  NO EXTERNAL ACTION
  WLM ENVIRONMENT RISKUDF;

That definition is not just syntax. It tells the operations team where the routine runs, whether it can read SQL data, whether null input should call the routine, and whether the function has side effects outside Db2.

UDF or Stored Procedure?

Do not use a UDF as a hidden transaction processor. If the logic updates several tables, writes audit rows, sends messages, or owns commit boundaries, a stored procedure is usually the cleaner object. A UDF should behave like a function: input values in, result value or rows out.

Question	Choose UDF	Choose stored procedure
Can it be used inside a SELECT expression?	Yes	Usually no
Does it return one calculated value?	Yes	Not the main reason
Does it perform a business transaction?	No	Yes
Does COBOL call it with CALL?	No, it appears in SQL	Yes, SQL CALL is normal

For the next topic in this cleanup list, the DB2 Stored Procedure page should cover transaction-style routines, result sets, parameters, and deployment. This UDF page should stay focused on function behavior inside SQL.

Performance Checks Before You Add a UDF

A UDF can make SQL easier to read, but it can also hide work from the person reading the query. Before using a UDF in a high-volume cursor, check how often it runs and whether it blocks index-friendly predicates.

• Use EXPLAIN on the SQL that calls the function.
• Avoid wrapping indexed columns in a function inside a WHERE clause unless you have tested the access path.
• Mark DETERMINISTIC, NO SQL, and NO EXTERNAL ACTION only when they are true.
• For external UDFs, confirm WLM environment, RACF permissions, load module availability, and Language Environment setup.
• Test null handling with RETURNS NULL ON NULL INPUT or CALLED ON NULL INPUT.
• Do not give a UDF the same name as a built-in function unless the function signature and SQL path behavior are reviewed.

The companion Db2 SQL Optimization Tips for COBOL Programs article covers access-path checks in more detail.

Deployment Checklist

• Choose a schema that matches site naming rules and does not collide with system schemas.
• Use a specific name for functions that may be overloaded.
• Grant EXECUTE only to the packages, roles, or IDs that need the function.
• For static COBOL SQL, confirm bind or rebind steps after new SQL references are introduced.
• Keep source, DDL, grants, WLM definition, and load module promotion in the same change record.
• Prepare rollback DDL before changing a function used by nightly batch jobs.

FAQ

Can a Db2 UDF return more than one row?

Yes. A table function can return a set of rows. A scalar function returns one value each time it is invoked.

Can COBOL code be used in a Db2 UDF?

Yes, an external UDF can reference a routine written in COBOL when the site supports the required Language Environment, WLM, security, and deployment setup.

Should a UDF replace a stored procedure?

No. Use a UDF for reusable logic inside SQL expressions. Use a stored procedure for transaction-style work, multiple statements, result sets, or business operations called with SQL CALL.

A UDF is worth creating when it gives SQL a clear name for a repeated rule and behaves predictably under production volume. If it hides expensive work inside every fetched row, fix the design before it reaches the batch window.