Chapter 10: Key Takeaways

The Core Principle

Stored procedures and UDFs centralize business logic at the data layer. When multiple programs execute the same business rule, maintaining separate implementations guarantees divergence. A single stored procedure or UDF becomes the authoritative source of truth.

When to Move Logic to the Database

  1. Multiple access paths execute the same rule. If batch, online, web, and mobile all calculate the same thing, centralize it.

  2. The logic is data-intensive. If a calculation reads from five tables to produce one result, executing it server-side eliminates four network round-trips.

  3. Referential integrity requires server-side enforcement. Business rules that must be applied atomically regardless of caller belong at the database layer.

  4. Security requires controlled access. Stored procedures let you grant EXECUTE without granting direct table access — a "need to know" pattern.

When NOT to Move Logic to the Database

  1. Compute-intensive, data-light logic consumes DB2 CPU that could serve other SQL. Keep it in the application.

  2. Frequently changing logic incurs deployment overhead (BIND, WLM refresh, DBA coordination) on every change.

  3. Presentation logic, session management, and CICS-specific services do not belong in stored procedures.

COBOL External Stored Procedures

  1. An external stored procedure is a COBOL program registered in DB2 via CREATE PROCEDURE. It runs in a WLM-managed address space, not in CICS or batch.

  2. PROCEDURE DIVISION USING defines the parameter interface. Parameters appear in the LINKAGE SECTION. Use GOBACK, never STOP RUN.

  3. The CREATE PROCEDURE DDL is the contract. LANGUAGE COBOL, EXTERNAL NAME, PARAMETER STYLE, SQL data access level, WLM ENVIRONMENT, COMMIT ON RETURN, and ASUTIME LIMIT all matter. Get them wrong and you get runtime failures, not compile errors.

  4. WLM address space configuration is critical. The JCL procedure must include every required library in STEPLIB. Missing subroutine libraries are the number-one deployment failure.

  5. After deploying a new load module, refresh the WLM environment (-STOP/-START PROCEDURE or VARY WLM REFRESH). Otherwise, the old version may continue running from memory.

Parameter Passing and Result Sets

  1. IN parameters flow to the procedure; OUT parameters flow back; INOUT flows both ways. The COBOL program can read and write any parameter — DB2 enforces direction during marshaling.

  2. PARAMETER STYLE GENERAL WITH NULLS adds indicator arrays for nullable parameters. Always check indicators before using parameter values.

  3. Result sets are returned via cursors declared WITH RETURN. Open the cursor, do NOT close it, and GOBACK. The caller receives the result set using ASSOCIATE LOCATORS and ALLOCATE CURSOR.

  4. Multiple result sets are received in the order they were opened. Declare DYNAMIC RESULT SETS with the correct count.

User-Defined Functions

  1. Scalar UDFs return a single value and can be used inline in SQL — SELECT lists, WHERE clauses, HAVING, ORDER BY. This embeds COBOL business logic directly into queries.

  2. UDFs require PARAMETER STYLE SQL, which mandates null indicators, SQLSTATE, function name, specific name, and diagnostic message parameters — a more complex interface than stored procedures.

  3. Table functions return rows and are used in FROM clauses. They use a state machine (OPEN/FETCH/CLOSE) with SCRATCHPAD for persistent state across calls.

  4. Declare function attributes honestly. DETERMINISTIC, NO EXTERNAL ACTION, READS SQL DATA — lying about these doesn't cause compile errors; it causes wrong results or missed optimizations.

Performance

  1. The round-trip reduction is real. Replacing 5 SQL calls with 1 stored procedure call can cut elapsed time by 50-75% for high-volume operations.

  2. CPU per transaction may increase. WLM scheduling and DB2 accounting add overhead. The trade-off is favorable when the bottleneck is throughput or elapsed time, not CPU.

  3. Scalar UDFs in WHERE clauses are dangerous. The optimizer cannot see inside a UDF. If the table has millions of rows, the UDF is called for every candidate row. Use UDFs in SELECT lists on pre-filtered result sets, or materialize the results into columns.

  4. Monitor WLM queue times. If all TCBs are busy, callers wait. Underprovisioned NUMTCB creates queuing; overprovisioned NUMTCB wastes resources.

Governance

  1. Version stored procedure names for breaking changes (V1, V2, V3). Deploy the new version while keeping the old version active. Migrate callers incrementally.

  2. Test in three layers. Unit test the COBOL logic without DB2. Integration test via SQL CALL with known data. Performance test under production-like volume.

  3. Automate the deployment pipeline. Precompile, compile, link-edit, BIND, CREATE/ALTER PROCEDURE, REFRESH WLM — every step scripted, no manual deployments.

  4. The scoring matrix drives decisions. Score candidate logic on access paths, data intensity, change frequency, compute intensity, and security requirements. Score 20+: stored procedure. Score under 13: keep in application.

The Bottom Line

Stored procedures and UDFs are not a silver bullet. They trade application complexity for database-layer complexity. The right question is never "should we use stored procedures?" It's "for this specific logic, does centralization outweigh the governance and performance costs?" Use the scoring matrix. Measure before and after. Monitor in production.