> "Every time you ship a business rule to the client and the batch job and the CICS transaction, you've created three places where that rule will eventually diverge." — Kwame Asante, CNB Architecture Review, 2019
Learning Objectives
- Design COBOL stored procedures that encapsulate business logic at the database layer
- Implement external COBOL stored procedures with proper parameter passing, result sets, and error handling
- Create user-defined functions (scalar and table) in COBOL for reusable DB2 logic
- Evaluate when to use stored procedures vs application-level COBOL for performance and maintainability
- Design the stored procedure layer for the HA banking system
In This Chapter
- When and How to Move Logic to the Database
- Opening Vignette
- 10.1 When Logic Belongs in the Database
- 10.2 COBOL External Stored Procedures
- 10.3 Parameter Passing and Result Sets
- 10.4 User-Defined Functions in COBOL
- 10.5 Performance Implications
- 10.6 Stored Procedure Governance
- 10.7 Decision Framework: Application vs. Database Logic
- 10.8 Progressive Project: HA Banking System Stored Procedure Layer
- Chapter Summary
- Key Terms Glossary
Chapter 10: Stored Procedures and User-Defined Functions in COBOL
When and How to Move Logic to the Database
"Every time you ship a business rule to the client and the batch job and the CICS transaction, you've created three places where that rule will eventually diverge." — Kwame Asante, CNB Architecture Review, 2019
Opening Vignette
It was 2:47 AM on a Tuesday when Sandra Kowalski's phone buzzed. The Federal Benefits Administration's nightly eligibility batch had been running for nine hours — three hours past its window — and the operations team was threatening to cancel it. Sandra pulled up the job log from her kitchen table and saw the problem immediately: the batch was making 14 million individual SQL calls, one per beneficiary, each executing the same eligibility calculation that involved reading from seven tables, applying twelve business rules, and writing results back. The logic lived in the COBOL batch program. The same logic also lived, slightly differently, in the CICS online transaction that case workers used. And a third copy existed in the IMS BMP that processed real-time enrollment changes.
"We've been maintaining three copies of this calculation for eight years," Sandra told Marcus Chen the next morning. "Each one has drifted. The batch version still uses the 2021 income thresholds for dental. The online version got the 2024 update but missed the spousal override. The IMS version is actually correct, but nobody knows that because nobody trusts it."
Marcus had been pushing for stored procedures for two years. Sandra had resisted — she'd seen poorly implemented stored procedures at her previous agency bring a DB2 subsystem to its knees. But 14 million network round-trips in a batch window was its own kind of problem.
"Show me how to do it right," she said. "One copy of the truth, living where the data lives."
This chapter is about that decision — when to move logic into the database, how to build COBOL stored procedures and user-defined functions that are production-grade, and how to avoid the performance and governance pitfalls that make DBAs reach for the kill switch.
10.1 When Logic Belongs in the Database
The Core Problem: Logic Duplication
Every mainframe shop older than ten years has the same disease. Business rules live in multiple programs. Those programs were written by different people in different decades. The rules drift. Nobody has a single source of truth.
Stored procedures and user-defined functions address this by placing logic at the data layer. Instead of every calling program implementing the calculation, they call a single procedure that lives in DB2. Change the procedure, and every caller gets the new behavior.
But this is not a free lunch. Moving logic to the database introduces its own complexity: WLM address space management, different debugging tools, separate deployment pipelines, CPU accounting concerns, and the risk of turning your database server into an application server.
The decision framework is not "should we use stored procedures?" It's "for this specific piece of logic, does the benefit of centralization outweigh the cost of database-layer complexity?"
The Centralization Argument
Logic belongs in the database when:
Multiple access paths execute the same business rule. If a calculation runs in batch COBOL, online CICS, and a web service, maintaining three implementations guarantees divergence. Sandra's eligibility calculation is the textbook case.
The logic is data-intensive. If a calculation reads from five tables to produce one result, executing that logic on the database server avoids five network round-trips per invocation. For batch programs processing millions of rows, this is the difference between a four-hour job and a forty-minute job.
Referential integrity requires server-side enforcement. When a business rule says "you cannot close an account that has pending transactions," enforcing this in every calling program is fragile. A stored procedure that checks and acts atomically is safer.
You need result set encapsulation. When a complex query involves temporary tables, intermediate calculations, and conditional logic, packaging it as a stored procedure hides the complexity from callers.
The Counter-Argument
Logic does not belong in the database when:
The logic is compute-intensive but data-light. If your calculation reads one row and then does heavy mathematical processing, you're consuming DB2 CPU that could be serving other SQL. COBOL application programs running in their own address space are cheaper to scale.
The logic changes frequently. Stored procedure deployment requires a BIND, possibly a WLM refresh, and coordination with the DBA team. If the business rule changes weekly, the deployment overhead may not be worth it.
Debugging requires application context. Stored procedures run in a WLM-managed address space. Your CEDF, your XPEDITER session, your abend-AID — they don't work the same way. If the logic requires frequent debugging in the context of the calling transaction, keeping it in the application may be more pragmatic.
You're already in a single access path. If only one program ever executes this logic and there's no realistic prospect of that changing, centralization solves a problem you don't have.
💡 Spaced Review: Chapter 5 — WLM Address Spaces
Recall from Chapter 5 that WLM (Workload Manager) manages stored procedure address spaces as service classes. Each stored procedure runs in a WLM-managed address space, not in the DB2 DBM1 address space. This isolation is deliberate — a runaway stored procedure won't crash the database engine. But it means stored procedure performance is governed by WLM service class definitions, goal modes, and the number of TCBs allocated. If you haven't configured WLM for stored procedures, go back to Chapter 5 before deploying anything from this chapter into production.
10.2 COBOL External Stored Procedures
What "External" Means
DB2 supports two types of stored procedures:
- Native SQL procedures — written entirely in SQL PL, compiled by DB2 into a package. No COBOL involved.
- External stored procedures — written in a host language (COBOL, PL/I, C, Java), compiled by the language compiler, and called by DB2 at runtime.
This chapter focuses on external COBOL stored procedures. Native SQL procedures have their place — they're easier to deploy and manage — but when you need the full power of COBOL's data manipulation, file I/O, or existing copybook structures, external procedures are the tool.
Anatomy of a COBOL External Stored Procedure
A COBOL external stored procedure is, at its core, a COBOL program. It has an IDENTIFICATION DIVISION, an ENVIRONMENT DIVISION (usually minimal), a DATA DIVISION, and a PROCEDURE DIVISION. The critical differences from a regular COBOL program:
- The PROCEDURE DIVISION USING clause defines the parameters that DB2 passes in.
- The program runs in a WLM-managed stored procedure address space, not in a CICS region or batch initiator.
- The program is registered in the DB2 catalog via a CREATE PROCEDURE DDL statement.
- SQLCA is available — the procedure can execute SQL statements.
- The program should not perform STOP RUN — it returns control to DB2 by falling through the end of the PROCEDURE DIVISION or executing GOBACK.
Here is the structural template:
IDENTIFICATION DIVISION.
PROGRAM-ID. ACTVLDSP.
*================================================================*
* STORED PROCEDURE: ACCOUNT VALIDATION *
* VALIDATES AN ACCOUNT NUMBER AND RETURNS STATUS, BALANCE, *
* AND LAST ACTIVITY DATE *
*================================================================*
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 WS-SQLCODE PIC S9(9) COMP.
01 WS-ERROR-MSG PIC X(200).
01 WS-CURRENT-DATE PIC X(10).
01 WS-DAYS-INACTIVE PIC S9(5) COMP.
01 WS-DORMANCY-LIMIT PIC S9(5) COMP VALUE 365.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL INCLUDE ACCTROW END-EXEC.
LINKAGE SECTION.
*----------------------------------------------------------------*
* PARAMETERS MATCHING THE CREATE PROCEDURE DEFINITION *
*----------------------------------------------------------------*
01 LS-ACCOUNT-NUM PIC X(12).
01 LS-ACCOUNT-STATUS PIC X(1).
01 LS-CURRENT-BALANCE PIC S9(13)V99 COMP-3.
01 LS-LAST-ACTIVITY PIC X(10).
01 LS-STATUS-CODE PIC S9(9) COMP.
01 LS-STATUS-MSG PIC X(200).
PROCEDURE DIVISION USING LS-ACCOUNT-NUM
LS-ACCOUNT-STATUS
LS-CURRENT-BALANCE
LS-LAST-ACTIVITY
LS-STATUS-CODE
LS-STATUS-MSG.
0000-MAIN.
PERFORM 1000-VALIDATE-ACCOUNT
GOBACK
.
1000-VALIDATE-ACCOUNT.
MOVE 0 TO LS-STATUS-CODE
MOVE SPACES TO LS-STATUS-MSG
EXEC SQL
SELECT ACCT_STATUS,
CURRENT_BALANCE,
CHAR(LAST_ACTIVITY_DATE, ISO)
INTO :LS-ACCOUNT-STATUS,
:LS-CURRENT-BALANCE,
:LS-LAST-ACTIVITY
FROM ACCOUNT_MASTER
WHERE ACCOUNT_NUMBER = :LS-ACCOUNT-NUM
END-EXEC
EVALUATE SQLCODE
WHEN 0
CONTINUE
WHEN +100
MOVE 4 TO LS-STATUS-CODE
STRING 'ACCOUNT NOT FOUND: '
LS-ACCOUNT-NUM
DELIMITED BY SIZE
INTO LS-STATUS-MSG
GOBACK
WHEN OTHER
MOVE 8 TO LS-STATUS-CODE
MOVE SQLCODE TO WS-SQLCODE
STRING 'DB2 ERROR SQLCODE='
WS-SQLCODE
DELIMITED BY SIZE
INTO LS-STATUS-MSG
GOBACK
END-EVALUATE
PERFORM 2000-CHECK-DORMANCY
.
2000-CHECK-DORMANCY.
EXEC SQL
SET :WS-DAYS-INACTIVE =
DAYS(CURRENT DATE) -
DAYS(LAST_ACTIVITY_DATE)
FROM ACCOUNT_MASTER
WHERE ACCOUNT_NUMBER = :LS-ACCOUNT-NUM
END-EXEC
IF WS-DAYS-INACTIVE > WS-DORMANCY-LIMIT
MOVE 'D' TO LS-ACCOUNT-STATUS
MOVE 2 TO LS-STATUS-CODE
STRING 'ACCOUNT DORMANT - '
WS-DAYS-INACTIVE
' DAYS INACTIVE'
DELIMITED BY SIZE
INTO LS-STATUS-MSG
END-IF
.
The CREATE PROCEDURE DDL
The COBOL program alone isn't enough. DB2 needs to know about it. The CREATE PROCEDURE statement registers the procedure in the DB2 catalog:
CREATE PROCEDURE CNB.VALIDATE_ACCOUNT
(IN P_ACCOUNT_NUM CHAR(12),
OUT P_ACCOUNT_STATUS CHAR(1),
OUT P_CURRENT_BALANCE DECIMAL(15,2),
OUT P_LAST_ACTIVITY CHAR(10),
OUT P_STATUS_CODE INTEGER,
OUT P_STATUS_MSG VARCHAR(200))
LANGUAGE COBOL
EXTERNAL NAME ACTVLDSP
PARAMETER STYLE GENERAL
NOT DETERMINISTIC
MODIFIES SQL DATA
WLM ENVIRONMENT WLMENV1
PROGRAM TYPE MAIN
COLLID CNBCOLL
COMMIT ON RETURN NO
ASUTIME LIMIT 5000;
Every clause matters. Let's walk through the ones that trip people up:
LANGUAGE COBOL — Tells DB2 the load module is COBOL. DB2 uses this to set up the Language Environment runtime correctly.
EXTERNAL NAME ACTVLDSP — The load module name in the STEPLIB of the WLM-managed address space. This must match the PROGRAM-ID, and the load module must be in a library accessible to the WLM address space.
PARAMETER STYLE GENERAL — DB2 passes parameters directly as host variables. This is the standard for COBOL. The alternative, PARAMETER STYLE GENERAL WITH NULLS, adds indicator variable arrays for nullable parameters.
NOT DETERMINISTIC — This procedure may return different results for the same input (because account data changes). If a procedure always returns the same result for the same input, mark it DETERMINISTIC so the optimizer can cache results.
MODIFIES SQL DATA — The procedure can execute INSERT, UPDATE, DELETE. Options are NO SQL, CONTAINS SQL, READS SQL DATA, and MODIFIES SQL DATA. Be honest — if you say READS SQL DATA and then execute an UPDATE, you'll get SQLCODE -577.
WLM ENVIRONMENT WLMENV1 — The WLM application environment where this procedure runs. The DBA and sysprog must create this environment before the procedure can execute.
COMMIT ON RETURN NO — The procedure does not automatically commit when it returns. The calling program controls commit scope. For batch callers that commit every N rows, this is essential. Set this to YES only when the procedure must be an autonomous transaction.
ASUTIME LIMIT 5000 — CPU time limit in service units. This is your safety net against runaway procedures. Set it tight for production; loosen it for development.
The WLM Managed Address Space
When a caller executes CALL CNB.VALIDATE_ACCOUNT(...), DB2 doesn't run the COBOL program in the DB2 address space. It routes the call to a WLM-managed stored procedure address space. This is a separate address space, started and managed by WLM, that loads and executes your COBOL program.
The configuration involves:
- WLM Application Environment — Defined in the WLM ISPF panels or policy. Specifies the JCL procedure to start the address space.
- JCL Procedure (typically in PROCLIB) — Defines STEPLIB (where your COBOL load module lives), DBRMLIB, and other DD statements.
- Service Class — WLM assigns the address space to a service class that governs CPU priority, storage, and goals.
- Number of TCBs — Controls how many stored procedure calls can execute concurrently. Too few and callers queue. Too many and you thrash.
//WLMENV1 PROC
//STEP1 EXEC PGM=DSNX9WLM,
// PARM='SSID=DB2P,APPLENV=WLMENV1,NUMTCB=20'
//STEPLIB DD DSN=CNB.PROD.LOADLIB,DISP=SHR
// DD DSN=DB2.SDSNLOAD,DISP=SHR
// DD DSN=CEE.SCEERUN,DISP=SHR
//DBRMLIB DD DSN=CNB.PROD.DBRMLIB,DISP=SHR
//SYSPRINT DD SYSOUT=*
//CEEDUMP DD SYSOUT=*
⚠️ Critical: If your COBOL stored procedure loads copybooks, DCLGEN members, or calls subroutines, every required load module must be in the STEPLIB of the WLM address space. This is the number-one deployment failure. The procedure works perfectly in test because the test STEPLIB has everything. It fails in production because someone forgot to add the subroutine library.
Compilation and Binding
The compile-and-bind process for an external COBOL stored procedure is identical to any DB2 COBOL program:
- Precompile — DB2 precompiler processes EXEC SQL statements, generates a DBRM and modified COBOL source.
- Compile — Enterprise COBOL compiler produces an object module.
- Link-edit — Linkage editor produces a load module. Place it in the STEPLIB of the WLM address space.
- BIND PACKAGE — Bind the DBRM into a DB2 package. The COLLID in the CREATE PROCEDURE must match the collection ID used in BIND.
Kwame Asante at CNB automated this into a single Jenkins pipeline stage:
DB2_PRECOMPILE -> COBOL_COMPILE -> LINK_EDIT -> BIND_PACKAGE ->
DEPLOY_LOADLIB -> CREATE_OR_ALTER_PROCEDURE -> REFRESH_WLM
The final step — REFRESH WLM — is often forgotten. After deploying a new version of the load module, you need to either:
- Issue -STOP PROCEDURE(CNB.VALIDATE_ACCOUNT) followed by -START PROCEDURE(CNB.VALIDATE_ACCOUNT) to force DB2 to pick up the new module.
- Or recycle the WLM address space with VARY WLM,APPLENV=WLMENV1,REFRESH.
Without this step, the old version of your program may continue running from the previously loaded copy in memory.
10.3 Parameter Passing and Result Sets
Parameter Modes: IN, OUT, INOUT
DB2 stored procedures support three parameter modes:
IN — Passed from caller to procedure. The procedure can read but should not modify (DB2 won't send modifications back). Use for search criteria, keys, and control flags.
OUT — Passed from procedure to caller. The initial value is undefined. Use for return values, status codes, and error messages.
INOUT — Bidirectional. The caller passes a value; the procedure can modify it and the caller sees the modification. Use when the caller provides an initial value that the procedure enriches or transforms.
In the COBOL program, all parameters appear in the LINKAGE SECTION and PROCEDURE DIVISION USING clause. There is no syntactic distinction between IN, OUT, and INOUT at the COBOL level — that distinction exists only in the CREATE PROCEDURE DDL. The COBOL program can read and write any parameter. DB2 enforces the direction when marshaling values between caller and procedure.
PARAMETER STYLE GENERAL WITH NULLS
When parameters can be NULL, you need indicator variables. PARAMETER STYLE GENERAL WITH NULLS adds two extra parameters to the COBOL program's PROCEDURE DIVISION USING clause:
- An indicator array — one SMALLINT per parameter.
- The SQLSTATE output — a CHAR(5) that the procedure can set to signal conditions.
LINKAGE SECTION.
01 LS-ACCOUNT-NUM PIC X(12).
01 LS-ACCOUNT-STATUS PIC X(1).
01 LS-CURRENT-BALANCE PIC S9(13)V99 COMP-3.
01 LS-LAST-ACTIVITY PIC X(10).
01 LS-STATUS-CODE PIC S9(9) COMP.
01 LS-STATUS-MSG PIC X(200).
01 LS-INDICATORS.
05 LS-IND PIC S9(4) COMP
OCCURS 6 TIMES.
01 LS-SQLSTATE PIC X(5).
PROCEDURE DIVISION USING LS-ACCOUNT-NUM
LS-ACCOUNT-STATUS
LS-CURRENT-BALANCE
LS-LAST-ACTIVITY
LS-STATUS-CODE
LS-STATUS-MSG
LS-INDICATORS
LS-SQLSTATE.
If LS-IND(1) is negative, the first parameter (account number) was passed as NULL. Always check indicators before using parameter values when NULLS are possible.
Returning Result Sets
A stored procedure can return one or more result sets to the caller. This is the mechanism that makes stored procedures powerful for reporting and complex queries — you can open a cursor, do intermediate processing, and hand the final result set back.
In COBOL, returning a result set requires:
- Declare a cursor WITH RETURN in the stored procedure.
- Open the cursor.
- Do not close the cursor. Leave it open when the procedure returns.
- Declare the result set count in CREATE PROCEDURE using DYNAMIC RESULT SETS.
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL
DECLARE TRANS_CURSOR CURSOR WITH RETURN
FOR
SELECT TRANSACTION_ID,
TRANSACTION_DATE,
AMOUNT,
DESCRIPTION
FROM TRANSACTION_HISTORY
WHERE ACCOUNT_NUMBER = :LS-ACCOUNT-NUM
AND TRANSACTION_DATE >= :LS-START-DATE
ORDER BY TRANSACTION_DATE DESC
END-EXEC
PROCEDURE DIVISION USING LS-ACCOUNT-NUM
LS-START-DATE
LS-STATUS-CODE.
0000-MAIN.
MOVE 0 TO LS-STATUS-CODE
EXEC SQL
OPEN TRANS_CURSOR
END-EXEC
IF SQLCODE NOT = 0
MOVE SQLCODE TO LS-STATUS-CODE
END-IF
GOBACK
.
The corresponding DDL:
CREATE PROCEDURE CNB.GET_ACCOUNT_TRANSACTIONS
(IN P_ACCOUNT_NUM CHAR(12),
IN P_START_DATE DATE,
OUT P_STATUS_CODE INTEGER)
LANGUAGE COBOL
EXTERNAL NAME ACTTRHSP
PARAMETER STYLE GENERAL
NOT DETERMINISTIC
READS SQL DATA
WLM ENVIRONMENT WLMENV1
DYNAMIC RESULT SETS 1
COLLID CNBCOLL;
The caller receives the result set using ASSOCIATE LOCATORS and ALLOCATE CURSOR:
* CALLING PROGRAM
EXEC SQL
CALL CNB.GET_ACCOUNT_TRANSACTIONS
(:WS-ACCOUNT-NUM,
:WS-START-DATE,
:WS-STATUS-CODE)
END-EXEC
IF WS-STATUS-CODE = 0
EXEC SQL
ASSOCIATE LOCATORS (:WS-LOC1)
WITH PROCEDURE CNB.GET_ACCOUNT_TRANSACTIONS
END-EXEC
EXEC SQL
ALLOCATE TRANS_CSR CURSOR FOR
RESULT SET :WS-LOC1
END-EXEC
PERFORM UNTIL SQLCODE = +100
EXEC SQL
FETCH TRANS_CSR
INTO :WS-TRANS-ID,
:WS-TRANS-DATE,
:WS-TRANS-AMT,
:WS-TRANS-DESC
END-EXEC
IF SQLCODE = 0
PERFORM 3000-PROCESS-TRANSACTION
END-IF
END-PERFORM
EXEC SQL CLOSE TRANS_CSR END-EXEC
END-IF
📊 Performance Note: Result sets are efficient for returning variable-length data. The cursor remains positioned in the stored procedure address space, and DB2 streams rows to the caller as they're fetched. This avoids materializing the entire result set in memory. For large result sets (millions of rows), this streaming behavior is critical.
Multiple Result Sets
A procedure can return multiple result sets by declaring multiple cursors WITH RETURN. The DYNAMIC RESULT SETS count in the DDL should match the maximum number of open cursors at return time. The caller receives them in the order they were opened.
Rob Nguyen at CNB uses this pattern for account statements: one result set for the summary header, one for the transaction detail, and one for the fee breakdown. Three cursors, one CALL, one network round-trip.
10.4 User-Defined Functions in COBOL
Scalar Functions
A scalar user-defined function (UDF) takes one or more input values and returns a single value. Unlike a stored procedure, a UDF can be used directly in SQL expressions — in SELECT lists, WHERE clauses, HAVING clauses, and ORDER BY.
This is extraordinarily powerful. It means you can embed COBOL business logic directly into SQL queries.
Consider Pinnacle Health's claims processing. Ahmad Patel's team needed to calculate the allowed amount for a medical claim based on the procedure code, the provider's contract tier, and the patient's plan type. The calculation involved a lookup table, three adjustment factors, and a cap. Before UDFs, every program that queried claims had to join to the lookup table and apply the adjustments inline. With a COBOL scalar UDF:
SELECT CLAIM_ID,
BILLED_AMOUNT,
PINNACLE.CALC_ALLOWED_AMT(PROC_CODE,
PROVIDER_TIER,
PLAN_TYPE) AS ALLOWED_AMOUNT,
BILLED_AMOUNT -
PINNACLE.CALC_ALLOWED_AMT(PROC_CODE,
PROVIDER_TIER,
PLAN_TYPE) AS PATIENT_RESP
FROM CLAIMS
WHERE CLAIM_STATUS = 'PENDING';
The calculation is encapsulated. Every query that needs the allowed amount calls the same function. One source of truth.
Implementing a COBOL Scalar UDF
A scalar UDF in COBOL is a program whose PROCEDURE DIVISION USING clause has a specific structure:
IDENTIFICATION DIVISION.
PROGRAM-ID. CALCALWD.
*================================================================*
* UDF: CALCULATE ALLOWED AMOUNT *
* SCALAR FUNCTION - RETURNS DECIMAL(11,2) *
*================================================================*
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
01 WS-BASE-RATE PIC S9(9)V99 COMP-3.
01 WS-TIER-FACTOR PIC S9(3)V9(4) COMP-3.
01 WS-PLAN-FACTOR PIC S9(3)V9(4) COMP-3.
01 WS-CALCULATED-AMT PIC S9(9)V99 COMP-3.
01 WS-PLAN-CAP PIC S9(9)V99 COMP-3.
LINKAGE SECTION.
* INPUT PARAMETERS
01 LS-PROC-CODE PIC X(7).
01 LS-PROVIDER-TIER PIC X(2).
01 LS-PLAN-TYPE PIC X(4).
* OUTPUT (THE RETURN VALUE)
01 LS-RESULT PIC S9(9)V99 COMP-3.
* NULL INDICATORS (ONE PER INPUT + ONE FOR RESULT)
01 LS-PROC-IND PIC S9(4) COMP.
01 LS-TIER-IND PIC S9(4) COMP.
01 LS-PLAN-IND PIC S9(4) COMP.
01 LS-RESULT-IND PIC S9(4) COMP.
* SQLSTATE
01 LS-SQLSTATE PIC X(5).
* QUALIFIED FUNCTION NAME
01 LS-FUNC-NAME PIC X(139).
* SPECIFIC FUNCTION NAME
01 LS-SPEC-NAME PIC X(128).
* DIAGNOSTIC MESSAGE
01 LS-DIAG-MSG PIC X(70).
PROCEDURE DIVISION USING LS-PROC-CODE
LS-PROVIDER-TIER
LS-PLAN-TYPE
LS-RESULT
LS-PROC-IND
LS-TIER-IND
LS-PLAN-IND
LS-RESULT-IND
LS-SQLSTATE
LS-FUNC-NAME
LS-SPEC-NAME
LS-DIAG-MSG.
0000-MAIN.
* CHECK FOR NULL INPUTS
IF LS-PROC-IND < 0 OR
LS-TIER-IND < 0 OR
LS-PLAN-IND < 0
MOVE -1 TO LS-RESULT-IND
MOVE '00000' TO LS-SQLSTATE
GOBACK
END-IF
PERFORM 1000-GET-BASE-RATE
PERFORM 2000-APPLY-ADJUSTMENTS
PERFORM 3000-APPLY-CAP
MOVE WS-CALCULATED-AMT TO LS-RESULT
MOVE 0 TO LS-RESULT-IND
MOVE '00000' TO LS-SQLSTATE
GOBACK
.
1000-GET-BASE-RATE.
EXEC SQL
SELECT BASE_RATE
INTO :WS-BASE-RATE
FROM PROCEDURE_RATE_SCHEDULE
WHERE PROCEDURE_CODE = :LS-PROC-CODE
AND EFFECTIVE_DATE <= CURRENT DATE
AND (EXPIRY_DATE IS NULL
OR EXPIRY_DATE > CURRENT DATE)
FETCH FIRST 1 ROWS ONLY
END-EXEC
IF SQLCODE NOT = 0
MOVE 0 TO WS-BASE-RATE
MOVE '38001' TO LS-SQLSTATE
STRING 'NO RATE FOUND FOR PROC CODE '
LS-PROC-CODE
DELIMITED BY SIZE
INTO LS-DIAG-MSG
END-IF
.
2000-APPLY-ADJUSTMENTS.
EXEC SQL
SELECT TIER_FACTOR
INTO :WS-TIER-FACTOR
FROM PROVIDER_TIER_TABLE
WHERE TIER_CODE = :LS-PROVIDER-TIER
END-EXEC
IF SQLCODE NOT = 0
MOVE 1.0 TO WS-TIER-FACTOR
END-IF
EXEC SQL
SELECT PLAN_FACTOR
INTO :WS-PLAN-FACTOR
FROM PLAN_ADJUSTMENT_TABLE
WHERE PLAN_TYPE = :LS-PLAN-TYPE
END-EXEC
IF SQLCODE NOT = 0
MOVE 1.0 TO WS-PLAN-FACTOR
END-IF
COMPUTE WS-CALCULATED-AMT =
WS-BASE-RATE * WS-TIER-FACTOR * WS-PLAN-FACTOR
.
3000-APPLY-CAP.
EXEC SQL
SELECT MAX_ALLOWED_AMOUNT
INTO :WS-PLAN-CAP
FROM PLAN_CAP_TABLE
WHERE PLAN_TYPE = :LS-PLAN-TYPE
AND PROCEDURE_CODE = :LS-PROC-CODE
END-EXEC
IF SQLCODE = 0
IF WS-CALCULATED-AMT > WS-PLAN-CAP
MOVE WS-PLAN-CAP TO WS-CALCULATED-AMT
END-IF
END-IF
.
Notice the parameter structure for UDFs is different from stored procedures:
- Input parameters first
- The result value
- Null indicators (one per input, one for result)
- SQLSTATE (CHAR(5))
- Qualified function name (CHAR(139))
- Specific function name (CHAR(128))
- Diagnostic message (CHAR(70))
This is mandatory for PARAMETER STYLE SQL, which is the required style for UDFs. You cannot use PARAMETER STYLE GENERAL for functions.
The CREATE FUNCTION DDL:
CREATE FUNCTION PINNACLE.CALC_ALLOWED_AMT
(P_PROC_CODE CHAR(7),
P_PROVIDER_TIER CHAR(2),
P_PLAN_TYPE CHAR(4))
RETURNS DECIMAL(11,2)
LANGUAGE COBOL
EXTERNAL NAME CALCALWD
PARAMETER STYLE SQL
NOT DETERMINISTIC
READS SQL DATA
SPECIFIC CALC_ALLOWED_V1
NO EXTERNAL ACTION
WLM ENVIRONMENT WLMENV2;
SPECIFIC CALC_ALLOWED_V1 — A unique name for this specific version of the function. Useful for dropping or altering a specific overloaded version without ambiguity.
NO EXTERNAL ACTION — The function has no side effects outside DB2 (no file I/O, no IMS calls, no MQ puts). Declaring this lets the optimizer make aggressive assumptions about execution order.
Additional Scalar UDF Examples
Date validation UDF. A common need across mainframe applications is validating and standardizing date formats. Instead of every program having its own date-parsing logic:
CREATE FUNCTION CNB.VALIDATE_DATE
(P_DATE_STRING CHAR(10),
P_FORMAT CHAR(3))
RETURNS DATE
LANGUAGE COBOL
EXTERNAL NAME VALDTUDF
PARAMETER STYLE SQL
DETERMINISTIC
NO SQL
SPECIFIC VALIDATE_DATE_V1
NO EXTERNAL ACTION
WLM ENVIRONMENT WLMENV2;
Usage in a query:
SELECT ACCT_NUM,
CNB.VALIDATE_DATE(INPUT_DATE, 'MDY') AS CLEAN_DATE
FROM STAGING_TABLE
WHERE CNB.VALIDATE_DATE(INPUT_DATE, 'MDY') IS NOT NULL;
Notice this UDF is declared DETERMINISTIC and NO SQL — the date validation is pure computation with no database access. These attributes let the optimizer cache results and potentially parallelize execution. For a UDF that is called millions of times in a batch with many duplicate date values, the caching of DETERMINISTIC results can reduce invocations by 80% or more.
Account classification UDF. CNB's regulatory reporting requires classifying accounts into risk tiers based on balance, transaction volume, and account age. The classification logic involves twelve rules and changes annually with new regulations:
CREATE FUNCTION CNB.CLASSIFY_RISK_TIER
(P_BALANCE DECIMAL(15,2),
P_TXN_COUNT INTEGER,
P_ACCT_AGE_DAYS INTEGER)
RETURNS CHAR(1)
LANGUAGE COBOL
EXTERNAL NAME RSKCLSUDF
PARAMETER STYLE SQL
NOT DETERMINISTIC
READS SQL DATA
SPECIFIC RISK_TIER_V4
NO EXTERNAL ACTION
WLM ENVIRONMENT WLMENV2;
This UDF is NOT DETERMINISTIC because the classification rules read from a configuration table that can change between invocations. It is READS SQL DATA because it performs SELECT statements against the rules table internally. The SPECIFIC name includes a version (V4) because this function is updated annually — V3 is still deployed for historical reporting queries that need the previous year's classification logic.
💡 Spaced Review: Chapter 6 — Optimizer and UDFs
From Chapter 6, recall that the DB2 optimizer uses the function attributes you declare — DETERMINISTIC, NO EXTERNAL ACTION, READS SQL DATA — to decide how aggressively it can optimize queries containing UDFs. A function declared DETERMINISTIC might be called once for a set of identical inputs. A function declared NO SQL enables parallel processing. Lying about these attributes doesn't cause compile errors — it causes wrong results in production. Be accurate.
Table Functions
A table function returns a set of rows. It can be used in the FROM clause of a SQL statement, just like a table or view. Table functions are the bridge between non-relational data and SQL.
Use cases for COBOL table functions:
- Reading a VSAM file and presenting it as a SQL table
- Parsing a complex copybook structure into relational rows
- Calling an external service and returning results as a table
- Implementing a complex algorithm that generates multiple rows
The COBOL implementation for a table function uses a state machine pattern. DB2 calls the function multiple times: once for OPEN, once for each FETCH, and once for CLOSE. The function must maintain state between calls.
WORKING-STORAGE SECTION.
01 WS-CALL-TYPE PIC X(10).
88 WS-OPEN VALUE 'OPEN'.
88 WS-FETCH VALUE 'FETCH'.
88 WS-CLOSE VALUE 'CLOSE'.
01 WS-SCRATCHPAD.
05 WS-SP-LENGTH PIC S9(9) COMP.
05 WS-SP-CURSOR-POS PIC S9(9) COMP.
05 WS-SP-ROW-COUNT PIC S9(9) COMP.
05 WS-SP-FILLER PIC X(88).
LINKAGE SECTION.
* INPUT PARAMETERS
01 LS-PROVIDER-ID PIC X(10).
* OUTPUT COLUMNS (THE TABLE COLUMNS)
01 LS-CLAIM-ID PIC X(15).
01 LS-CLAIM-DATE PIC X(10).
01 LS-CLAIM-AMOUNT PIC S9(9)V99 COMP-3.
01 LS-CLAIM-STATUS PIC X(2).
* NULL INDICATORS
01 LS-PROV-IND PIC S9(4) COMP.
01 LS-CLMID-IND PIC S9(4) COMP.
01 LS-CLMDT-IND PIC S9(4) COMP.
01 LS-CLMAMT-IND PIC S9(4) COMP.
01 LS-CLMST-IND PIC S9(4) COMP.
* SQLSTATE
01 LS-SQLSTATE PIC X(5).
* FUNCTION NAME
01 LS-FUNC-NAME PIC X(139).
* SPECIFIC NAME
01 LS-SPEC-NAME PIC X(128).
* DIAGNOSTIC MESSAGE
01 LS-DIAG-MSG PIC X(70).
* SCRATCHPAD
01 LS-SCRATCHPAD.
05 LS-SP-LENGTH PIC S9(9) COMP.
05 LS-SP-DATA PIC X(96).
* CALL TYPE
01 LS-CALL-TYPE PIC S9(9) COMP.
PROCEDURE DIVISION USING LS-PROVIDER-ID
LS-CLAIM-ID
LS-CLAIM-DATE
LS-CLAIM-AMOUNT
LS-CLAIM-STATUS
LS-PROV-IND
LS-CLMID-IND
LS-CLMDT-IND
LS-CLMAMT-IND
LS-CLMST-IND
LS-SQLSTATE
LS-FUNC-NAME
LS-SPEC-NAME
LS-DIAG-MSG
LS-SCRATCHPAD
LS-CALL-TYPE.
0000-MAIN.
EVALUATE LS-CALL-TYPE
WHEN -1
PERFORM 1000-OPEN
WHEN 0
PERFORM 2000-FETCH
WHEN 1
PERFORM 3000-CLOSE
END-EVALUATE
GOBACK
.
The CALL-TYPE values are: -1 (OPEN), 0 (FETCH), 1 (CLOSE). On OPEN, initialize your scratchpad and open any cursors. On FETCH, return the next row. On CLOSE, clean up resources. To signal end-of-data, set SQLSTATE to '02000'.
The corresponding DDL uses RETURNS TABLE:
CREATE FUNCTION PINNACLE.GET_PROVIDER_CLAIMS
(P_PROVIDER_ID CHAR(10))
RETURNS TABLE
(CLAIM_ID CHAR(15),
CLAIM_DATE DATE,
CLAIM_AMOUNT DECIMAL(11,2),
CLAIM_STATUS CHAR(2))
LANGUAGE COBOL
EXTERNAL NAME PVCLMTFN
PARAMETER STYLE SQL
NOT DETERMINISTIC
READS SQL DATA
SPECIFIC GET_PROV_CLAIMS_V1
NO EXTERNAL ACTION
SCRATCHPAD 100
FINAL CALL
DISALLOW PARALLEL
WLM ENVIRONMENT WLMENV2;
SCRATCHPAD 100 — Allocates 100 bytes of persistent storage across calls. Use it to maintain cursor position, counters, and state.
FINAL CALL — Guarantees DB2 will make the CLOSE call even if the query is cancelled. Without this, you risk resource leaks.
DISALLOW PARALLEL — Table functions that maintain state via scratchpad cannot be parallelized. The optimizer needs to know this.
Usage in SQL:
SELECT C.CLAIM_ID, C.CLAIM_DATE, C.CLAIM_AMOUNT
FROM TABLE(PINNACLE.GET_PROVIDER_CLAIMS('PROV00123')) AS C
WHERE C.CLAIM_STATUS = 'PE'
ORDER BY C.CLAIM_DATE DESC;
10.5 Performance Implications
Network Round-Trips
The most compelling performance argument for stored procedures is the elimination of network round-trips. Consider a CICS transaction that validates an account:
Without stored procedure (application-side logic): 1. SQL: SELECT from ACCOUNT_MASTER → 1 round-trip 2. SQL: SELECT from ACCOUNT_RESTRICTIONS → 1 round-trip 3. SQL: SELECT from CUSTOMER_PROFILE → 1 round-trip 4. COBOL: Apply business rules locally 5. SQL: UPDATE ACCOUNT_MASTER SET STATUS → 1 round-trip 6. SQL: INSERT into AUDIT_LOG → 1 round-trip
Total: 5 round-trips.
With stored procedure: 1. SQL: CALL VALIDATE_ACCOUNT(...) → 1 round-trip (all 5 SQL statements execute inside the procedure, server-side)
Total: 1 round-trip.
For a CICS transaction, each round-trip involves cross-memory communication between the CICS address space and DB2. The overhead per round-trip is small — perhaps 0.1-0.3 milliseconds — but it adds up. At CNB, Rob Nguyen measured account validation at 4.2ms with five round-trips and 1.1ms with a single stored procedure call. For a transaction executed 50,000 times per day, that's a meaningful reduction.
For distributed access — a Java application connecting via DRDA — the savings are even larger. Each round-trip involves TCP/IP network latency, potentially 1-5ms or more. Five round-trips become 5-25ms of pure network overhead.
📊 CNB Benchmark: Account Validation
| Metric | App-Side Logic | Stored Procedure | Improvement |
|---|---|---|---|
| Avg elapsed time | 4.2 ms | 1.1 ms | 74% |
| Network round-trips | 5 | 1 | 80% |
| CPU per transaction | 0.08 ms | 0.11 ms | -37% |
| Throughput (TPS) | 12,400 | 31,200 | 152% |
Notice that CPU per transaction actually increased with the stored procedure. This is the trade-off: you save elapsed time (fewer round-trips) but you may use more CPU (the procedure runs under DB2's CPU accounting, which has overhead). At CNB, the elapsed time reduction was worth the CPU increase because the bottleneck was throughput, not CPU.
WLM Scheduling Overhead
Every stored procedure call requires WLM to schedule a TCB in the stored procedure address space. If all TCBs are busy, the caller waits. This scheduling overhead is typically 0.01-0.05ms, but it can spike if:
- The WLM address space is underprovisioned (too few TCBs).
- The address space needs to be started (cold start penalty of 2-5 seconds).
- WLM is swapping the address space due to memory pressure.
Monitor WLM queue times using the DSNV402I message and the STORED_PROCEDURE_QUEUE_TIME column in DSN_STATEMNT_TABLE.
WLM Stored Procedure Address Space Management in Depth
WLM address space sizing is the most common operational failure in stored procedure deployments. The critical tuning parameter is NUMTCB — the number of task control blocks (essentially threads) available for concurrent stored procedure execution.
Sizing NUMTCB. Each TCB can execute one stored procedure invocation at a time. If you have 20 TCBs and 25 concurrent callers, 5 callers queue. The formula:
NUMTCB >= (peak concurrent callers) * (avg SP duration)
/ (avg time between calls)
For CNB's account validation procedure, called by 300 CICS transactions per second with an average duration of 1.1ms, the math is: 300 * 0.0011 = 0.33 concurrent invocations. Five TCBs would be more than enough. But the interest calculation procedure, called by a 20-thread batch job where each call takes 15ms, needs: 20 * 0.015 / 0.015 = 20 concurrent invocations — 20 TCBs minimum, 25 to avoid queueing during bursts.
Multiple WLM environments. Don't put all stored procedures in the same WLM environment. Separate them by workload profile:
| WLM Environment | Purpose | NUMTCB | Service Class |
|---|---|---|---|
| WLMENV1 | Online validation procedures (fast, high-volume) | 15 | High priority, 50ms goal |
| WLMENV2 | UDFs (called inline in SQL, short-lived) | 10 | High priority, 20ms goal |
| WLMENV3 | Batch procedures (longer-running, lower priority) | 30 | Medium priority, 5-second goal |
| WLMENV4 | Reporting procedures (long-running, interruptible) | 8 | Low priority, velocity goal |
Separating environments prevents a batch stored procedure that runs for 2 seconds from consuming a TCB that an online procedure needs for 1 millisecond. Without separation, a surge in batch procedure calls can exhaust the TCB pool and cause online callers to queue — exactly the kind of cross-workload interference that WLM was designed to prevent.
Address space recycling. WLM address spaces are long-lived. A stored procedure's WORKING-STORAGE persists between calls (unless you use PROGRAM TYPE SUB, which initializes storage on each call). This means: - Memory leaks in your COBOL program accumulate over thousands of calls. - Static data areas retain values from previous invocations unless explicitly initialized. - The address space can grow over time if your procedure allocates dynamic storage.
Kwame's team at CNB recycles WLM address spaces weekly using an automated job that issues VARY WLM,APPLENV=WLMENVn,REFRESH during the Sunday maintenance window. This clears accumulated storage and reloads load modules, ensuring any deployed updates take effect.
CPU Accounting
Stored procedure CPU is charged differently depending on your accounting class configuration:
- Accounting Class 1 — CPU time is attributed to the stored procedure's own work.
- Accounting Class 2 — CPU time includes wait time.
- Distributed Accounting — For DRDA callers, CPU is charged to the distributed thread.
For chargeback purposes, this matters. If department A's batch job calls department B's stored procedure, who pays for the CPU? Define this governance before deploying shared stored procedures.
UDF Performance: The Optimizer's Blind Spot
⚠️ Critical Performance Warning: Scalar UDFs in WHERE clauses can be performance killers. The optimizer cannot "see inside" an external UDF. It doesn't know the UDF's selectivity, and it can't push predicates down through it. Consider:
SELECT * FROM CLAIMS
WHERE PINNACLE.CALC_ALLOWED_AMT(PROC_CODE, PROVIDER_TIER, PLAN_TYPE) > 1000.00;
DB2 must call the UDF for every row in the CLAIMS table (or at least every row that survives other predicates). If CLAIMS has 50 million rows, that's 50 million UDF invocations. Each invocation has WLM scheduling overhead, Language Environment initialization overhead, and the cost of the SQL statements inside the UDF.
Diane's team at Pinnacle Health learned this the hard way. A developer put the allowed amount UDF in a WHERE clause on a 30-million-row claims table. The query ran for six hours before they killed it.
The solution: Use UDFs in SELECT lists, not WHERE clauses, unless the table has already been filtered to a small number of rows by other predicates. Or, materialize the calculated values into a column and maintain them via triggers or batch updates.
💡 Spaced Review: Chapter 8 — Locking in Stored Procedures
From Chapter 8, remember that stored procedures participate in the caller's unit of work (unless COMMIT ON RETURN YES is specified). This means locks acquired by the stored procedure are held until the caller commits or rolls back. If your stored procedure reads from a high-contention table and the caller doesn't commit promptly, you're holding locks longer than necessary. Design stored procedures to minimize lock duration: access shared resources late, use WITH UR where appropriate for read-only lookups, and document the commit expectations for callers.
10.6 Stored Procedure Governance
Versioning
Stored procedures are shared code. Multiple applications depend on them. You cannot change the parameter signature without breaking every caller. This requires governance that is more rigorous than application-level COBOL.
Naming Convention for Versioning:
At Federal Benefits, Sandra Kowalski implemented this convention:
Schema: FEDBENE
Procedure Name: CALC_ELIGIBILITY_V3
COBOL Program-ID: ELGCL3SP
Specific Name: CALC_ELIG_V3_20250115
The version number is in the procedure name. When a new version is needed, they create CALC_ELIGIBILITY_V4 while leaving V3 in place. Callers migrate at their own pace. After all callers have moved to V4, V3 is dropped.
This is a breaking change management pattern. For non-breaking changes (bug fixes, performance improvements that don't alter the parameter list or result format), Sandra updates the existing version's load module and rebinds.
The Deprecation Protocol:
- New version deployed and tested.
- All caller teams notified with migration deadline.
- Monitoring enabled: if V3 is called, log a warning.
- After migration deadline, V3 is marked as "deprecated" in the catalog (custom column in a governance table).
- After 90-day grace period, V3 is dropped.
Testing
Testing stored procedures is harder than testing application COBOL because:
- The execution environment is different. The procedure runs in a WLM address space, not a batch region or CICS. You can't just link it into your test program and call it.
- You need a DB2 instance. Unit testing stored procedures requires a live DB2 subsystem (or a test instance).
- Debugging tools are limited. You can't step through a stored procedure with XPEDITER the same way you can step through a CICS program. You need tools that attach to the WLM address space.
Practical testing strategy:
Layer 1: Unit Test the COBOL Logic Without DB2. Extract the business logic into a callable subroutine. Test the subroutine in a batch harness with mock data. This validates the calculation logic without DB2 overhead.
Layer 2: Integration Test Via SQL CALL. Deploy the procedure to a test DB2 subsystem. Write a COBOL test harness that: - Sets up test data (INSERT known values). - Calls the stored procedure. - Validates output parameters and result sets against expected values. - Cleans up test data (DELETE or ROLLBACK).
Layer 3: Performance Test Under Load. Use a driver program to issue concurrent CALL statements simulating production volume. Monitor WLM queue times, CPU consumption, and elapsed time.
Yuki Tanaka at SecureFirst implemented automated regression testing for stored procedures using a COBOL test framework that runs nightly. Each stored procedure has a corresponding test program (naming convention: test module = procedure module + 'T', so ACTVLDSP has test ACTVLDSPT). The test program is a standard batch COBOL program that calls the stored procedure via SQL CALL, checks results, and writes pass/fail to a report dataset.
Deployment Pipeline
A mature stored procedure deployment pipeline:
1. Developer codes COBOL program and DDL
2. Code review (COBOL + DDL reviewed together)
3. Precompile / Compile / Link-Edit
4. BIND PACKAGE to test subsystem
5. CREATE/ALTER PROCEDURE on test subsystem
6. Automated regression tests run
7. Promote load module and DBRM to QA libraries
8. BIND PACKAGE to QA subsystem
9. ALTER PROCEDURE on QA (or CREATE if new)
10. QA team executes test scripts
11. Change management approval
12. Promote to production libraries
13. BIND PACKAGE to production subsystem
14. ALTER PROCEDURE on production
15. REFRESH WLM application environment
16. Smoke test in production
17. Monitor for 24 hours
Every step is in the pipeline. No manual deployments. Carlos at SecureFirst learned this after a developer deployed a stored procedure directly to production from their TSO session and introduced a regression that cost $200,000 in incorrect interest calculations before it was caught.
Security
Stored procedures introduce a new security surface:
- EXECUTE privilege on the procedure — Controls who can call it.
- Underlying table privileges — The procedure's package runs with the authority of the package owner (via BIND PACKAGE with OWNER). Callers don't need direct SELECT/UPDATE on the underlying tables.
This is actually a security benefit. You can grant EXECUTE on the procedure without granting table-level access. The procedure becomes a controlled API to the data. At Federal Benefits, this is how they implement "need to know" — case workers can call the eligibility procedure but cannot query the income table directly.
GRANT EXECUTE ON PROCEDURE FEDBENE.CALC_ELIGIBILITY_V3
TO ROLE CASE_WORKER;
-- Case workers do NOT have:
-- GRANT SELECT ON INCOME_TABLE TO ROLE CASE_WORKER;
10.7 Decision Framework: Application vs. Database Logic
After six sections of mechanics, let's synthesize a decision framework. This is what Kwame Asante presented to CNB's architecture board when they were deciding which business rules to move into stored procedures.
The Scoring Matrix
For each candidate piece of logic, score it on five dimensions (1-5 scale):
| Dimension | 1 (Favor App) | 5 (Favor DB) |
|---|---|---|
| Access paths | Single program | 5+ callers across batch/online/distributed |
| Data intensity | Reads 1 table | Reads 5+ tables, complex joins |
| Change frequency | Weekly changes | Stable for years |
| Compute intensity | Heavy math, light data | Light compute, heavy data |
| Security requirement | Open access | Strict need-to-know on underlying data |
Score 20-25: Strong candidate for stored procedure. Score 13-19: Could go either way; consider other factors. Score 5-12: Keep in the application.
CNB's Results
Kwame's team scored 47 candidate business rules. Results:
- 11 scored 20+ and were moved to stored procedures. These included account validation, interest calculation, fee assessment, and regulatory reporting aggregations.
- 23 scored 13-19 and were evaluated case by case. Eight were moved; fifteen stayed in applications.
- 13 scored under 13 and stayed in applications. These were single-use batch calculations and CICS-specific formatting logic.
The Red Lines
Regardless of score, some logic should never be in a stored procedure:
- Presentation logic. Formatting for screens, reports, or print — keep this in the application.
- Session management. CICS transaction state, conversational pseudo-conversing — this is application territory.
- File I/O-heavy processing. If the primary work is reading/writing sequential files, VSAM files, or GDGs, a stored procedure is the wrong tool. (Table functions that read VSAM are an exception for data access patterns.)
- Logic that requires CICS services. TS queues, TD queues, START commands — these are not available in a WLM-managed stored procedure address space.
And some logic should always be considered for database placement:
- Cross-application referential integrity rules. If three applications must all enforce the same constraint, put it in a stored procedure or trigger.
- Audit logging. A stored procedure that writes audit records ensures consistent logging regardless of the caller.
- Rate calculations and fee schedules used by multiple systems.
10.8 Progressive Project: HA Banking System Stored Procedure Layer
Design Assignment
In this chapter's project checkpoint, you'll design the stored procedure layer for the HA Banking Transaction Processing System. Based on the transaction processing requirements established in previous chapters, identify which business logic should move to the database layer.
Requirements
The HA Banking system processes these transaction types: - Account balance inquiries (online and batch) - Fund transfers between accounts (online, requires two-phase update) - Interest calculations (nightly batch, all savings accounts) - Fee assessments (monthly batch, all checking accounts) - Regulatory reporting aggregations (monthly, consumed by three downstream systems)
Design Decisions
Apply the scoring matrix from Section 10.7:
Account Balance Inquiry — Score: 14. Multiple access paths (CICS, web service, IVR), but data intensity is low (one table). Keep in application with a shared copybook for the validation logic. The overhead of a stored procedure call isn't justified for a single-table read.
Fund Transfer — Score: 22. Multiple access paths, high data intensity (debit account, credit account, transaction log, fee calculation, hold check, fraud check — six tables), stable logic, security-sensitive. Strong stored procedure candidate.
Interest Calculation — Score: 18. Single access path (nightly batch), but high data intensity and used by regulatory reporting. Move the calculation formula to a scalar UDF so both the batch program and reporting can use it. The batch program calls the UDF inline in an UPDATE statement.
Fee Assessment — Score: 19. Two access paths (monthly batch and online inquiry), moderate data intensity. Implement as a stored procedure with a result set that returns the fee breakdown.
Regulatory Reporting Aggregation — Score: 23. Three downstream consumers, extremely data-intensive (joins across twelve tables), changes only when regulations change. Stored procedure with result sets.
Your Checkpoint Deliverable
Using the code templates from this chapter:
- Write the CREATE PROCEDURE DDL for
HA.TRANSFER_FUNDSwith appropriate parameters for a fund transfer (source account, target account, amount, currency, memo, status code, error message). - Write the COBOL external stored procedure skeleton for the transfer, including: balance check, hold check, debit, credit, transaction log insert, and fee calculation (calling the interest/fee UDF).
- Write the CREATE FUNCTION DDL for
HA.CALC_MONTHLY_FEEas a scalar UDF. - Document your WLM environment design: how many address spaces, how many TCBs, what service class goals.
Refer to code/project-checkpoint.md for the detailed checkpoint specification.
Chapter Summary
Stored procedures and user-defined functions move business logic from the application layer to the database layer. For COBOL shops, external stored procedures are COBOL programs that run in WLM-managed address spaces, registered via CREATE PROCEDURE DDL, and called via SQL CALL statements. User-defined functions — both scalar and table — extend SQL with COBOL-implemented logic that can appear directly in queries.
The performance trade-off is real: fewer network round-trips mean faster elapsed time, but WLM scheduling and DB2 CPU accounting add overhead. The governance trade-off is also real: centralized logic is easier to maintain but harder to deploy and debug.
The decision of what to move to the database should be systematic, not dogmatic. Use the scoring matrix: high access path count, high data intensity, low change frequency, and security requirements favor stored procedures. Single access paths, compute-heavy logic, and frequently changing rules favor application-level COBOL.
In the next chapter, we'll build on this foundation by examining dynamic SQL in COBOL — the ability to construct and execute SQL statements at runtime — and how it interacts with both application programs and stored procedures.
Key Terms Glossary
Stored Procedure — A named program registered in the DB2 catalog that can be invoked via the SQL CALL statement. Encapsulates business logic, SQL operations, and control flow at the database layer.
External Stored Procedure — A stored procedure implemented in a host language (COBOL, PL/I, C, Java) rather than in SQL PL. The compiled load module executes in a WLM-managed address space.
Native SQL Procedure — A stored procedure written entirely in SQL Procedural Language (SQL PL), compiled and executed by DB2 without an external language runtime.
CALL Statement (SQL) — The SQL statement used to invoke a stored procedure: CALL schema.procedure_name(parameter_list).
Parameter Modes (IN/OUT/INOUT) — Directionality modifiers on stored procedure parameters. IN passes data to the procedure, OUT returns data to the caller, INOUT does both.
Result Set — A set of rows returned from a stored procedure to the caller via a cursor declared WITH RETURN. The caller receives the result set using ASSOCIATE LOCATORS and ALLOCATE CURSOR.
WLM-Managed Stored Procedure Address Space — A z/OS address space started and managed by Workload Manager specifically for executing stored procedures. Provides isolation from the DB2 engine address space.
User-Defined Function (UDF) — A function registered in the DB2 catalog that extends SQL with custom logic. Can be used in SQL expressions (SELECT, WHERE, HAVING, etc.).
Scalar Function — A UDF that accepts one or more input values and returns a single value. Used inline in SQL expressions.
Table Function — A UDF that returns a set of rows (a table). Used in the FROM clause of SQL statements via the TABLE() syntax.
PARAMETER STYLE GENERAL — The parameter passing convention for COBOL stored procedures where DB2 passes parameters directly as host variables without null indicator arrays.
PARAMETER STYLE SQL — The parameter passing convention required for UDFs. Includes null indicators, SQLSTATE, function name, specific name, and diagnostic message parameters.
SPECIFIC — A unique identifier for a particular version of a function, used to distinguish overloaded functions with the same name but different parameter types.
DETERMINISTIC — A function attribute indicating that the function always returns the same result for the same input values. Enables optimizer caching.
READS SQL DATA — A stored procedure or function attribute indicating that the routine executes only SELECT statements (no INSERT, UPDATE, DELETE).
MODIFIES SQL DATA — A stored procedure attribute indicating that the routine may execute INSERT, UPDATE, or DELETE statements.
SCRATCHPAD — Persistent storage allocated by DB2 for table functions to maintain state across OPEN, FETCH, and CLOSE calls.
COMMIT ON RETURN — A CREATE PROCEDURE option. When YES, DB2 automatically commits after the procedure returns. When NO, the caller controls commit scope.
ASUTIME LIMIT — CPU time limit for a stored procedure, specified in service units. Prevents runaway procedures from consuming excessive CPU.
Next: Chapter 11 — Dynamic SQL in COBOL: Building and Executing Statements at Runtime