Chapter 34: Stored Procedures, UDFs, and Application Logic in the Database

"The question is never whether logic can live in the database — it always could. The question is whether it should, and if so, how to build it so the next person who maintains it doesn't curse your name." — A senior DBA at a Fortune 100 bank, speaking at a DB2 user group

Every application that touches DB2 eventually confronts a fundamental architectural decision: where should business logic execute? In the application tier, where developers have full-featured languages and familiar debugging tools? Or in the database tier, where logic runs closest to the data, eliminates network round-trips, and can enforce rules that no application can bypass?

The answer, as with most engineering decisions, is "it depends." But the consequences of getting this decision wrong are severe — poor performance, inconsistent data, security vulnerabilities, and maintenance nightmares. This chapter gives you the frameworks, patterns, and concrete DB2 skills to make that decision wisely and implement it expertly.

We will build Meridian National Bank's stored procedure library from scratch, implementing the core banking operations — account transfers, loan payment processing, fee calculations, and customer onboarding — as robust, production-ready database logic. Along the way, you will master SQL PL's advanced features, learn to build external procedures in Java and COBOL, create user-defined functions that extend DB2's capabilities, and establish the version management and testing practices that separate professional database development from ad-hoc scripting.

34.1 When to Put Logic in the Database

Before writing a single line of SQL PL, you need a decision framework. I have seen teams swing between two extremes: "everything in stored procedures" (common in the early 2000s) and "the database is just a dumb store" (common in the microservices era). Both extremes create problems.

34.1.1 Arguments for Database Logic

Latency Reduction. Every network round-trip between application and database adds latency. A procedure that performs ten SQL statements in a single call eliminates nine round-trips. On z/OS, where DB2 and application code may share the same LPAR, this advantage is smaller but still measurable. On distributed systems where the database server is across a network boundary, the savings can be dramatic — I have seen transaction times drop from 200ms to 15ms by moving multi-step logic into a stored procedure.

Data Integrity Enforcement. Application code can be bypassed. Multiple applications may access the same database. A stored procedure that encapsulates a business rule — such as "every debit must have a corresponding credit" — enforces that rule regardless of which application initiates the operation. Triggers provide even stronger enforcement but at the cost of transparency.

Security Encapsulation. DB2's authorization model can grant EXECUTE privilege on a procedure without granting direct access to underlying tables. This is powerful: application users can perform business operations without having INSERT, UPDATE, or DELETE authority on any table. At Meridian Bank, tellers can execute PROC_TRANSFER_FUNDS but cannot directly modify the ACCOUNTS or TRANSACTIONS tables.

Reduced Data Movement. When logic requires reading large volumes of data to produce a small result — think aggregation, filtering, or pattern detection — executing that logic in the database avoids shipping rows across the network only to discard most of them.

Transactional Atomicity. A stored procedure executes within a single database connection and can manage its own transaction boundaries. Complex multi-statement operations that must be atomic are naturally expressed as procedures.

34.1.2 Arguments Against Database Logic

Limited Language Features. SQL PL is not Java, Python, or C#. It lacks extensive standard libraries, sophisticated data structures, and the rich ecosystem of frameworks. Complex string parsing, XML/JSON manipulation (beyond DB2's built-in support), or integration with external systems can be awkward in SQL PL.

Debugging Difficulty. Despite improvements in tools like IBM Data Studio, debugging stored procedures is harder than debugging application code in IntelliJ or Visual Studio Code. Step-through debugging, breakpoints, and variable inspection are available but less polished.

Version Management Challenges. Stored procedures live in the database catalog, not in your Git repository (at least, not naturally). Without discipline, you end up with procedures deployed to production that differ from what is in source control.

Scalability Constraints. Application tiers scale horizontally by adding servers. Database tiers are harder to scale. Putting CPU-intensive business logic in stored procedures concentrates load on a resource that is more expensive to expand.

Portability. SQL PL is DB2-specific. If there is any chance of migrating to another RDBMS, heavy reliance on stored procedures creates lock-in.

34.1.3 The Decision Framework

Use this framework at Meridian Bank and in your own projects:

The best architectures use a layered approach: stored procedures for core data operations and integrity enforcement, application code for presentation logic, workflow orchestration, and external integration.

34.2 SQL PL Advanced Features

SQL PL (SQL Procedural Language) is DB2's native procedural extension to SQL. If you have written basic stored procedures with IF statements and simple loops, this section takes you significantly further.

34.2.1 Compound Statements

The foundation of SQL PL is the compound statement — a BEGIN ... END block that can contain variable declarations, condition handlers, cursors, and executable statements.

CREATE OR REPLACE PROCEDURE MERIDIAN.PROC_EXAMPLE()
LANGUAGE SQL
SPECIFIC PROC_EXAMPLE
BEGIN
    -- Declarations must come first
    DECLARE v_count INTEGER DEFAULT 0;
    DECLARE v_msg VARCHAR(200);

    -- Executable statements follow
    SELECT COUNT(*) INTO v_count FROM MERIDIAN.ACCOUNTS;
    SET v_msg = 'Total accounts: ' || CHAR(v_count);

    -- Nested compound statements (anonymous blocks)
    BEGIN
        DECLARE v_inner INTEGER;  -- scope limited to this block
        SET v_inner = v_count * 2;
    END;
    -- v_inner is not accessible here
END
@

Atomic compound statements wrap all operations in an implicit savepoint. If any statement fails, all changes within the block are rolled back:

BEGIN ATOMIC
    UPDATE MERIDIAN.ACCOUNTS SET BALANCE = BALANCE - 500.00 WHERE ACCOUNT_ID = 1001;
    UPDATE MERIDIAN.ACCOUNTS SET BALANCE = BALANCE + 500.00 WHERE ACCOUNT_ID = 1002;
    INSERT INTO MERIDIAN.TRANSACTIONS (FROM_ACCT, TO_ACCT, AMOUNT) VALUES (1001, 1002, 500.00);
END
@

If the INSERT fails, both UPDATEs are undone. This is different from a regular BEGIN...END, where each statement commits or rolls back independently (subject to the connection's auto-commit setting).

34.2.2 Control Flow: IF, CASE, LOOP

Nested IF statements work as you would expect but watch for readability:

IF v_account_type = 'SAVINGS' THEN
    IF v_balance < v_minimum_balance THEN
        SET v_fee = 25.00;
    ELSEIF v_balance < v_minimum_balance * 2 THEN
        SET v_fee = 10.00;
    ELSE
        SET v_fee = 0.00;
    END IF;
ELSEIF v_account_type = 'CHECKING' THEN
    SET v_fee = CASE
        WHEN v_transaction_count > 50 THEN 0.00
        WHEN v_transaction_count > 20 THEN 5.00
        ELSE 12.00
    END;
END IF;

The searched CASE statement is often cleaner than nested IFs:

CASE
    WHEN v_credit_score >= 750 THEN SET v_rate = 3.25;
    WHEN v_credit_score >= 700 THEN SET v_rate = 4.50;
    WHEN v_credit_score >= 650 THEN SET v_rate = 6.75;
    ELSE SET v_rate = 9.99;
END CASE;

Loop constructs — DB2 SQL PL supports LOOP, WHILE, REPEAT, and FOR:

-- Simple LOOP with LEAVE
proc_loop: LOOP
    FETCH cur_accounts INTO v_acct_id, v_balance;
    IF v_sqlcode = 100 THEN
        LEAVE proc_loop;
    END IF;
    -- process row
END LOOP proc_loop;

-- WHILE loop
WHILE v_counter < 100 DO
    SET v_total = v_total + v_counter;
    SET v_counter = v_counter + 1;
END WHILE;

-- REPEAT (executes at least once)
REPEAT
    SET v_counter = v_counter + 1;
    CALL MERIDIAN.PROC_PROCESS_BATCH(v_counter, v_status);
UNTIL v_status = 'COMPLETE'
END REPEAT;

-- FOR loop (iterates over a query)
FOR v_row AS
    SELECT ACCOUNT_ID, BALANCE, ACCOUNT_TYPE
    FROM MERIDIAN.ACCOUNTS
    WHERE STATUS = 'ACTIVE'
DO
    -- v_row.ACCOUNT_ID, v_row.BALANCE, v_row.ACCOUNT_TYPE available
    IF v_row.BALANCE < 0 THEN
        CALL MERIDIAN.PROC_SEND_ALERT(v_row.ACCOUNT_ID, 'NEGATIVE_BALANCE');
    END IF;
END FOR;

The FOR loop is particularly powerful because it implicitly declares a cursor and loop variable, reducing boilerplate.

34.2.3 Cursor Variables and Dynamic SQL

Cursor variables allow you to work with cursors more flexibly:

DECLARE cur_accounts CURSOR FOR
    SELECT ACCOUNT_ID, BALANCE
    FROM MERIDIAN.ACCOUNTS
    WHERE BRANCH_ID = p_branch_id
    FOR UPDATE OF BALANCE;

OPEN cur_accounts;

fetch_loop: LOOP
    FETCH cur_accounts INTO v_acct_id, v_balance;
    IF v_sqlcode <> 0 THEN
        LEAVE fetch_loop;
    END IF;

    -- Positioned update using cursor
    UPDATE MERIDIAN.ACCOUNTS
    SET BALANCE = v_balance * (1 + v_interest_rate)
    WHERE CURRENT OF cur_accounts;
END LOOP fetch_loop;

CLOSE cur_accounts;

Dynamic SQL using PREPARE and EXECUTE is essential when the SQL text is not known at compile time:

CREATE OR REPLACE PROCEDURE MERIDIAN.PROC_DYNAMIC_QUERY(
    IN p_table_name VARCHAR(128),
    IN p_where_clause VARCHAR(4000),
    OUT p_count INTEGER
)
LANGUAGE SQL
SPECIFIC PROC_DYNAMIC_QUERY
BEGIN
    DECLARE v_sql VARCHAR(8000);
    DECLARE v_stmt STATEMENT;

    SET v_sql = 'SELECT COUNT(*) FROM MERIDIAN.' || p_table_name;

    IF p_where_clause IS NOT NULL AND LENGTH(p_where_clause) > 0 THEN
        SET v_sql = v_sql || ' WHERE ' || p_where_clause;
    END IF;

    PREPARE v_stmt FROM v_sql;
    EXECUTE v_stmt INTO p_count;
END
@

Warning: Dynamic SQL opens the door to SQL injection. Always validate inputs. In the example above, p_table_name should be validated against SYSCAT.TABLES, and p_where_clause should be constructed using parameter markers where possible:

-- Safer: use parameter markers
SET v_sql = 'SELECT COUNT(*) FROM MERIDIAN.ACCOUNTS WHERE BRANCH_ID = ?';
PREPARE v_stmt FROM v_sql;
EXECUTE v_stmt INTO p_count USING p_branch_id;

34.2.4 Arrays and Associative Arrays

DB2 supports array types in SQL PL, which are useful for passing collections into and out of procedures:

-- Create an array type
CREATE TYPE MERIDIAN.ACCOUNT_ID_ARRAY AS INTEGER ARRAY[100];

-- Use in a procedure
CREATE OR REPLACE PROCEDURE MERIDIAN.PROC_BATCH_UPDATE(
    IN p_accounts MERIDIAN.ACCOUNT_ID_ARRAY,
    IN p_new_status VARCHAR(20)
)
LANGUAGE SQL
BEGIN
    DECLARE v_idx INTEGER DEFAULT 1;
    DECLARE v_max INTEGER;

    SET v_max = CARDINALITY(p_accounts);

    WHILE v_idx <= v_max DO
        UPDATE MERIDIAN.ACCOUNTS
        SET STATUS = p_new_status
        WHERE ACCOUNT_ID = p_accounts[v_idx];

        SET v_idx = v_idx + 1;
    END WHILE;
END
@

Associative arrays (available in DB2 11.1+) use arbitrary keys:

CREATE TYPE MERIDIAN.STRING_MAP AS VARCHAR(200) ARRAY[VARCHAR(50)];

-- In a procedure
DECLARE v_config MERIDIAN.STRING_MAP;
SET v_config['max_transfer'] = '50000.00';
SET v_config['daily_limit'] = '100000.00';
SET v_config['currency'] = 'USD';

34.3 Error Handling in SQL PL

Robust error handling separates production-ready procedures from prototypes. DB2's handler mechanism is powerful but has subtleties that trip up even experienced developers.

34.3.1 DECLARE HANDLER

Handlers intercept conditions (errors, warnings, not-found) and execute specified actions:

CREATE OR REPLACE PROCEDURE MERIDIAN.PROC_SAFE_INSERT(
    IN p_acct_id INTEGER,
    IN p_acct_type VARCHAR(20),
    OUT p_result VARCHAR(50)
)
LANGUAGE SQL
BEGIN
    -- Handler for duplicate key
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
    BEGIN
        SET p_result = 'DUPLICATE_ACCOUNT';
    END;

    -- Handler for any other SQL exception
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET p_result = 'UNEXPECTED_ERROR';
        -- Could also log the error here
    END;

    INSERT INTO MERIDIAN.ACCOUNTS (ACCOUNT_ID, ACCOUNT_TYPE, STATUS)
    VALUES (p_acct_id, p_acct_type, 'ACTIVE');

    SET p_result = 'SUCCESS';
END
@

There are three handler types:

• CONTINUE — after the handler executes, control returns to the statement after the one that caused the condition.
• EXIT — after the handler executes, control exits the compound statement where the handler was declared.
• UNDO — like EXIT, but also rolls back all changes made in the compound statement (requires BEGIN ATOMIC).

34.3.2 Condition Names

For readability, define named conditions:

BEGIN
    DECLARE duplicate_key CONDITION FOR SQLSTATE '23505';
    DECLARE foreign_key_violation CONDITION FOR SQLSTATE '23503';
    DECLARE check_violation CONDITION FOR SQLSTATE '23513';

    DECLARE CONTINUE HANDLER FOR duplicate_key
        SET v_error = 'DUPLICATE';

    DECLARE CONTINUE HANDLER FOR foreign_key_violation
        SET v_error = 'INVALID_REFERENCE';

    DECLARE CONTINUE HANDLER FOR check_violation
        SET v_error = 'CONSTRAINT_VIOLATED';

    -- ... procedure body
END

34.3.3 SIGNAL and RESIGNAL

SIGNAL raises a custom condition — the stored procedure equivalent of throwing an exception:

IF v_balance < p_amount THEN
    SIGNAL SQLSTATE '75001'
    SET MESSAGE_TEXT = 'Insufficient funds: balance='
        || CHAR(v_balance)
        || ', requested='
        || CHAR(p_amount);
END IF;

RESIGNAL re-raises a caught condition, optionally modifying its properties:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    -- Log the error
    INSERT INTO MERIDIAN.ERROR_LOG (PROC_NAME, ERROR_TIME, SQLSTATE, MESSAGE)
    VALUES ('PROC_TRANSFER', CURRENT TIMESTAMP, v_sqlstate, v_msg);

    -- Re-raise so the caller knows something went wrong
    RESIGNAL;
END;

34.3.4 GET DIAGNOSTICS

GET DIAGNOSTICS retrieves detailed information about the most recent SQL operation:

DECLARE v_sqlcode INTEGER;
DECLARE v_sqlstate CHAR(5);
DECLARE v_msg VARCHAR(1000);
DECLARE v_rows_affected INTEGER;

-- After an SQL statement:
GET DIAGNOSTICS v_rows_affected = ROW_COUNT;

-- After an error (in a handler):
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    GET DIAGNOSTICS EXCEPTION 1
        v_sqlcode = DB2_RETURNED_SQLCODE,
        v_sqlstate = RETURNED_SQLSTATE,
        v_msg = MESSAGE_TEXT;

    INSERT INTO MERIDIAN.ERROR_LOG
        (PROC_NAME, ERROR_TS, SQLCODE_VAL, SQLSTATE_VAL, MESSAGE)
    VALUES
        ('PROC_TRANSFER', CURRENT TIMESTAMP, v_sqlcode, v_sqlstate, v_msg);
END;

34.3.5 Building Robust Error Handling: The Pattern

Here is the error handling pattern we use throughout Meridian Bank's procedures:

CREATE OR REPLACE PROCEDURE MERIDIAN.PROC_ROBUST_EXAMPLE(
    IN  p_input_param  INTEGER,
    OUT p_status_code  INTEGER,     -- 0=success, negative=error
    OUT p_status_msg   VARCHAR(500)
)
LANGUAGE SQL
SPECIFIC PROC_ROBUST_EXAMPLE
MODIFIES SQL DATA
BEGIN
    DECLARE v_sqlcode INTEGER DEFAULT 0;
    DECLARE v_sqlstate CHAR(5) DEFAULT '00000';
    DECLARE v_errmsg VARCHAR(500) DEFAULT '';
    DECLARE SQLCODE INTEGER DEFAULT 0;
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS EXCEPTION 1
            v_sqlcode = DB2_RETURNED_SQLCODE,
            v_sqlstate = RETURNED_SQLSTATE,
            v_errmsg = MESSAGE_TEXT;

        SET p_status_code = v_sqlcode;
        SET p_status_msg = 'SQL Error [' || v_sqlstate || ']: ' || v_errmsg;

        -- Log to error table
        INSERT INTO MERIDIAN.ERROR_LOG
            (PROC_NAME, ERROR_TS, SQLCODE_VAL, SQLSTATE_VAL, MESSAGE, INPUT_PARAMS)
        VALUES
            ('PROC_ROBUST_EXAMPLE', CURRENT TIMESTAMP, v_sqlcode, v_sqlstate,
             v_errmsg, 'p_input_param=' || CHAR(p_input_param));
    END;

    -- Validate inputs
    IF p_input_param IS NULL THEN
        SIGNAL SQLSTATE '75000'
            SET MESSAGE_TEXT = 'Input parameter cannot be null';
    END IF;

    -- ... actual logic ...

    SET p_status_code = 0;
    SET p_status_msg = 'Success';
END
@

34.4 Result Sets from Stored Procedures

Stored procedures often need to return query results to the calling application. DB2 supports this through cursors declared WITH RETURN.

34.4.1 Single Result Set

CREATE OR REPLACE PROCEDURE MERIDIAN.PROC_GET_ACCOUNT_HISTORY(
    IN p_account_id INTEGER,
    IN p_start_date DATE,
    IN p_end_date DATE
)
LANGUAGE SQL
SPECIFIC PROC_GET_ACCT_HIST
READS SQL DATA
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE cur_history CURSOR WITH RETURN TO CLIENT FOR
        SELECT
            t.TRANSACTION_ID,
            t.TRANSACTION_DATE,
            t.TRANSACTION_TYPE,
            t.AMOUNT,
            t.RUNNING_BALANCE,
            t.DESCRIPTION
        FROM MERIDIAN.TRANSACTIONS t
        WHERE t.ACCOUNT_ID = p_account_id
          AND t.TRANSACTION_DATE BETWEEN p_start_date AND p_end_date
        ORDER BY t.TRANSACTION_DATE DESC, t.TRANSACTION_ID DESC;

    OPEN cur_history;
    -- Do NOT close the cursor; it is returned to the client
END
@

The key points:

1. DYNAMIC RESULT SETS 1 in the procedure declaration tells DB2 how many result sets to expect.
2. WITH RETURN TO CLIENT (or WITH RETURN TO CALLER) specifies who receives the result set. TO CLIENT passes it to the ultimate client application; TO CALLER passes it to the calling procedure.
3. The cursor must be opened but not closed — DB2 returns the open cursor as a result set.

34.4.2 Multiple Result Sets

CREATE OR REPLACE PROCEDURE MERIDIAN.PROC_ACCOUNT_DASHBOARD(
    IN p_customer_id INTEGER
)
LANGUAGE SQL
DYNAMIC RESULT SETS 3
READS SQL DATA
BEGIN
    -- Result set 1: Account summaries
    DECLARE cur_accounts CURSOR WITH RETURN TO CLIENT FOR
        SELECT ACCOUNT_ID, ACCOUNT_TYPE, BALANCE, STATUS
        FROM MERIDIAN.ACCOUNTS
        WHERE CUSTOMER_ID = p_customer_id;

    -- Result set 2: Recent transactions (last 30 days)
    DECLARE cur_recent CURSOR WITH RETURN TO CLIENT FOR
        SELECT t.TRANSACTION_DATE, t.AMOUNT, t.DESCRIPTION, a.ACCOUNT_TYPE
        FROM MERIDIAN.TRANSACTIONS t
        JOIN MERIDIAN.ACCOUNTS a ON t.ACCOUNT_ID = a.ACCOUNT_ID
        WHERE a.CUSTOMER_ID = p_customer_id
          AND t.TRANSACTION_DATE >= CURRENT DATE - 30 DAYS
        ORDER BY t.TRANSACTION_DATE DESC
        FETCH FIRST 20 ROWS ONLY;

    -- Result set 3: Active loans
    DECLARE cur_loans CURSOR WITH RETURN TO CLIENT FOR
        SELECT LOAN_ID, LOAN_TYPE, PRINCIPAL, INTEREST_RATE, MATURITY_DATE
        FROM MERIDIAN.LOANS
        WHERE CUSTOMER_ID = p_customer_id
          AND STATUS = 'ACTIVE';

    OPEN cur_accounts;
    OPEN cur_recent;
    OPEN cur_loans;
END
@

34.4.3 Client-Side Consumption

From JDBC (Java), consuming multiple result sets looks like this:

CallableStatement cs = conn.prepareCall("CALL MERIDIAN.PROC_ACCOUNT_DASHBOARD(?)");
cs.setInt(1, customerId);
boolean hasResults = cs.execute();

// First result set: accounts
if (hasResults) {
    ResultSet rs1 = cs.getResultSet();
    while (rs1.next()) {
        // process account summary
    }
    rs1.close();
}

// Second result set: recent transactions
if (cs.getMoreResults()) {
    ResultSet rs2 = cs.getResultSet();
    while (rs2.next()) {
        // process transaction
    }
    rs2.close();
}

// Third result set: loans
if (cs.getMoreResults()) {
    ResultSet rs3 = cs.getResultSet();
    while (rs3.next()) {
        // process loan
    }
    rs3.close();
}

34.5 External Stored Procedures — Java

When SQL PL is not enough — when you need complex parsing, external system calls, advanced math, or access to Java libraries — external stored procedures bridge the gap.

34.5.1 Creating a Java Stored Procedure

The process involves three steps: write the Java class, install it in DB2, and register the procedure.

Step 1: Write the Java class

package com.meridian.db2.procedures;

import java.sql.*;

public class CreditScoreProc {

    /**
     * Calculate a credit score based on account history.
     * Parameters map to the CREATE PROCEDURE definition.
     */
    public static void calculateCreditScore(
            int customerId,
            int[] creditScore,          // OUT parameter
            String[] riskCategory,      // OUT parameter
            ResultSet[] resultSet1      // Result set
    ) throws SQLException, Exception {

        // Get the default connection (runs in the DB2 JVM)
        Connection conn = DriverManager.getConnection("jdbc:default:connection");

        try {
            // Query account history
            PreparedStatement ps = conn.prepareStatement(
                "SELECT AVG(BALANCE) as AVG_BAL, " +
                "       COUNT(*) as TXN_COUNT, " +
                "       SUM(CASE WHEN AMOUNT < 0 THEN 1 ELSE 0 END) as OVERDRAFTS " +
                "FROM MERIDIAN.ACCOUNT_HISTORY " +
                "WHERE CUSTOMER_ID = ? AND HISTORY_DATE >= CURRENT DATE - 2 YEARS"
            );
            ps.setInt(1, customerId);
            ResultSet rs = ps.executeQuery();

            int score = 600; // base score
            if (rs.next()) {
                double avgBalance = rs.getDouble("AVG_BAL");
                int txnCount = rs.getInt("TXN_COUNT");
                int overdrafts = rs.getInt("OVERDRAFTS");

                // Scoring algorithm
                if (avgBalance > 10000) score += 100;
                else if (avgBalance > 5000) score += 50;
                else if (avgBalance > 1000) score += 25;

                if (overdrafts == 0) score += 50;
                else score -= (overdrafts * 10);

                if (txnCount > 100) score += 25;
            }
            rs.close();
            ps.close();

            // Set OUT parameters
            creditScore[0] = Math.min(850, Math.max(300, score));
            riskCategory[0] = score >= 750 ? "LOW" : score >= 650 ? "MEDIUM" : "HIGH";

            // Return a result set with score components
            PreparedStatement ps2 = conn.prepareStatement(
                "SELECT 'Credit Score' AS COMPONENT, CHAR(?) AS VALUE FROM SYSIBM.SYSDUMMY1 " +
                "UNION ALL " +
                "SELECT 'Risk Category', ? FROM SYSIBM.SYSDUMMY1"
            );
            ps2.setInt(1, creditScore[0]);
            ps2.setString(2, riskCategory[0]);
            resultSet1[0] = ps2.executeQuery();
            // Do NOT close ps2 or resultSet1 — DB2 returns it to the caller

        } catch (SQLException e) {
            throw e;
        }
    }
}

Step 2: Install the JAR

-- Install the JAR file into DB2
CALL SQLJ.INSTALL_JAR('file:/opt/meridian/lib/credit-score-proc.jar', 'MERIDIAN.CREDIT_SCORE_JAR', 0);

-- Update the JAR if it already exists
CALL SQLJ.REPLACE_JAR('file:/opt/meridian/lib/credit-score-proc.jar', 'MERIDIAN.CREDIT_SCORE_JAR');

-- Set the classpath for the schema
CALL SQLJ.DB2_UPDATE_CLASPATH('MERIDIAN', 'MERIDIAN.CREDIT_SCORE_JAR');

Step 3: Register the procedure

CREATE OR REPLACE PROCEDURE MERIDIAN.PROC_CREDIT_SCORE(
    IN  p_customer_id   INTEGER,
    OUT p_credit_score   INTEGER,
    OUT p_risk_category  VARCHAR(20)
)
LANGUAGE JAVA
EXTERNAL NAME 'com.meridian.db2.procedures.CreditScoreProc.calculateCreditScore'
PARAMETER STYLE JAVA
DYNAMIC RESULT SETS 1
NOT DETERMINISTIC
MODIFIES SQL DATA;

34.5.2 Parameter Style and Conventions

PARAMETER STYLE JAVA is the standard for Java procedures. DB2 maps SQL types to Java types:

OUT and INOUT parameters use single-element arrays so the method can modify the reference.

34.5.3 JVM Configuration and Debugging

DB2 runs Java procedures in a JVM that is part of the database engine (fenced or unfenced). Key configuration:

-- Update JVM heap for stored procedures (LUW)
db2 update dbm cfg using JAVA_HEAP_SZ 2048

-- View current JVM settings
db2 get dbm cfg | grep -i java

For debugging, write to the DB2 diagnostic log or use a logging table:

// Logging pattern for Java stored procedures
public static void logDebug(Connection conn, String procName, String message)
        throws SQLException {
    PreparedStatement ps = conn.prepareStatement(
        "INSERT INTO MERIDIAN.PROC_DEBUG_LOG (PROC_NAME, LOG_TS, MESSAGE) " +
        "VALUES (?, CURRENT TIMESTAMP, ?)"
    );
    ps.setString(1, procName);
    ps.setString(2, message);
    ps.executeUpdate();
    ps.close();
}

34.6 External Stored Procedures — COBOL and C

34.6.1 [z/OS] COBOL Stored Procedures

On z/OS, COBOL stored procedures are the backbone of mainframe banking systems. Meridian Bank's core banking system, like many real-world banks, has decades of COBOL business logic that can be exposed as DB2 stored procedures.

The architecture: On z/OS, external stored procedures run in WLM-managed (Workload Manager) stored procedure address spaces. This provides isolation, resource management, and the ability to control concurrency.

-- Register a COBOL stored procedure on z/OS
CREATE PROCEDURE MERIDIAN.PROC_LOAN_PAYMENT(
    IN  p_loan_id        CHAR(12),
    IN  p_payment_amount DECIMAL(15,2),
    IN  p_payment_date   DATE,
    OUT p_new_balance    DECIMAL(15,2),
    OUT p_interest_paid  DECIMAL(15,2),
    OUT p_principal_paid DECIMAL(15,2),
    OUT p_return_code    INTEGER,
    OUT p_return_msg     CHAR(80)
)
LANGUAGE COBOL
EXTERNAL NAME 'LNPAYMT'
PARAMETER STYLE GENERAL WITH NULLS
WLM ENVIRONMENT MERIDWLM
COLLID MERIDCOL
PROGRAM TYPE SUB
STAY RESIDENT YES;

Key z/OS-specific clauses:

• WLM ENVIRONMENT — specifies the WLM application environment where the procedure runs. This determines the address space characteristics (region size, language environment options).
• COLLID — the package collection ID for static SQL within the COBOL program.
• PROGRAM TYPE SUB — indicates a subroutine (as opposed to MAIN). SUB is typical for stored procedures.
• STAY RESIDENT YES — keeps the load module in memory between calls, reducing load overhead.

The COBOL program follows standard embedded SQL patterns:

       IDENTIFICATION DIVISION.
       PROGRAM-ID. LNPAYMT.

       DATA DIVISION.
       WORKING-STORAGE SECTION.
           EXEC SQL INCLUDE SQLCA END-EXEC.

       01 WS-INTEREST-RATE   PIC S9(3)V9(6) COMP-3.
       01 WS-MONTHLY-RATE    PIC S9(3)V9(10) COMP-3.

       LINKAGE SECTION.
       01 LS-LOAN-ID          PIC X(12).
       01 LS-PAYMENT-AMT      PIC S9(13)V99 COMP-3.
       01 LS-PAYMENT-DATE     PIC X(10).
       01 LS-NEW-BALANCE      PIC S9(13)V99 COMP-3.
       01 LS-INTEREST-PAID    PIC S9(13)V99 COMP-3.
       01 LS-PRINCIPAL-PAID   PIC S9(13)V99 COMP-3.
       01 LS-RETURN-CODE      PIC S9(9) COMP.
       01 LS-RETURN-MSG       PIC X(80).
       01 LS-IND-ARRAY.
           05 LS-IND          PIC S9(4) COMP OCCURS 8.

       PROCEDURE DIVISION USING
           LS-LOAN-ID, LS-PAYMENT-AMT, LS-PAYMENT-DATE,
           LS-NEW-BALANCE, LS-INTEREST-PAID, LS-PRINCIPAL-PAID,
           LS-RETURN-CODE, LS-RETURN-MSG,
           LS-IND-ARRAY.

       MAIN-LOGIC.
           EXEC SQL
               SELECT INTEREST_RATE
               INTO :WS-INTEREST-RATE
               FROM MERIDIAN.LOANS
               WHERE LOAN_ID = :LS-LOAN-ID
           END-EXEC.

           IF SQLCODE NOT = 0
               MOVE -1 TO LS-RETURN-CODE
               MOVE 'Loan not found' TO LS-RETURN-MSG
               GOBACK
           END-IF.

           COMPUTE WS-MONTHLY-RATE = WS-INTEREST-RATE / 12 / 100.

           GOBACK.

34.6.2 C Stored Procedures

C stored procedures are used for performance-critical operations and system-level integration:

CREATE PROCEDURE MERIDIAN.PROC_ENCRYPT_SSN(
    IN  p_plain_ssn    VARCHAR(11),
    OUT p_encrypted_ssn VARCHAR(256),
    OUT p_status        INTEGER
)
LANGUAGE C
EXTERNAL NAME 'encrypt_lib!encrypt_ssn'
PARAMETER STYLE SQL
NOT DETERMINISTIC
FENCED
NO SQL;

The PARAMETER STYLE SQL passes null indicators and SQLSTATE alongside each parameter. The C function signature:

#include <sqludf.h>
#include <sqlca.h>

SQL_API_RC SQL_API_FN encrypt_ssn(
    SQLUDF_VARCHAR *plain_ssn,      /* IN  */
    SQLUDF_VARCHAR *encrypted_ssn,  /* OUT */
    SQLUDF_INTEGER *status,         /* OUT */
    SQLUDF_SMALLINT *plain_null,    /* null indicator */
    SQLUDF_SMALLINT *enc_null,      /* null indicator */
    SQLUDF_SMALLINT *status_null,   /* null indicator */
    SQLUDF_TRAIL_ARGS               /* standard trailing args */
)
{
    /* Implementation */
    if (*plain_null < 0) {
        *enc_null = -1;   /* NULL in, NULL out */
        *status = -1;
        *status_null = 0;
        return 0;
    }

    /* ... encryption logic ... */

    *status = 0;
    *status_null = 0;
    *enc_null = 0;
    return 0;
}

34.7 Scalar User-Defined Functions

Scalar UDFs accept zero or more inputs and return a single value. They extend DB2's built-in function library with domain-specific calculations.

34.7.1 SQL Scalar Functions

-- Calculate compound interest
CREATE OR REPLACE FUNCTION MERIDIAN.FN_COMPOUND_INTEREST(
    p_principal DECIMAL(15,2),
    p_rate      DECIMAL(7,5),
    p_periods   INTEGER
)
RETURNS DECIMAL(15,2)
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN p_principal * POWER(1 + p_rate, p_periods) - p_principal;

Usage is identical to built-in functions:

SELECT
    LOAN_ID,
    PRINCIPAL,
    INTEREST_RATE,
    MERIDIAN.FN_COMPOUND_INTEREST(PRINCIPAL, INTEREST_RATE / 12, TERM_MONTHS)
        AS TOTAL_INTEREST
FROM MERIDIAN.LOANS
WHERE STATUS = 'ACTIVE';

34.7.2 Key Attributes

DETERMINISTIC vs NOT DETERMINISTIC:

A deterministic function always returns the same result for the same inputs. DB2 can cache results, reorder evaluations, and apply optimizations. Use DETERMINISTIC for pure calculations. Use NOT DETERMINISTIC for functions that read tables, use CURRENT TIMESTAMP, or have side effects.

-- DETERMINISTIC: pure calculation
CREATE FUNCTION MERIDIAN.FN_MASK_ACCOUNT(p_acct VARCHAR(20))
RETURNS VARCHAR(20)
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN 'XXXX-' || RIGHT(p_acct, 4);

-- NOT DETERMINISTIC: reads current data
CREATE FUNCTION MERIDIAN.FN_CURRENT_RATE(p_loan_type VARCHAR(20))
RETURNS DECIMAL(7,5)
NOT DETERMINISTIC
READS SQL DATA
RETURN (
    SELECT CURRENT_RATE
    FROM MERIDIAN.INTEREST_RATES
    WHERE LOAN_TYPE = p_loan_type
      AND EFFECTIVE_DATE <= CURRENT DATE
    ORDER BY EFFECTIVE_DATE DESC
    FETCH FIRST 1 ROW ONLY
);

RETURNS NULL ON NULL INPUT tells DB2 to skip calling the function entirely if any input is NULL, returning NULL directly. This is a performance optimization:

CREATE FUNCTION MERIDIAN.FN_FORMAT_CURRENCY(p_amount DECIMAL(15,2))
RETURNS VARCHAR(20)
DETERMINISTIC
RETURNS NULL ON NULL INPUT
NO EXTERNAL ACTION
CONTAINS SQL
RETURN '$' || STRIP(CHAR(p_amount), LEADING) ;

CALLED ON NULL INPUT (the default) means the function is called even with NULL arguments. Use this when you need to handle NULLs explicitly.

34.7.3 Performance Implications

Scalar UDFs in WHERE clauses can prevent index usage if DB2 cannot push the evaluation down. Consider:

-- This prevents index use on ACCOUNT_NUMBER:
SELECT * FROM MERIDIAN.ACCOUNTS
WHERE MERIDIAN.FN_MASK_ACCOUNT(ACCOUNT_NUMBER) = 'XXXX-1234';

-- This allows index use:
SELECT * FROM MERIDIAN.ACCOUNTS
WHERE RIGHT(ACCOUNT_NUMBER, 4) = '1234';

When a scalar UDF is called for every row in a large result set, the overhead of function invocation multiplied by millions of rows becomes significant. For performance-critical paths, consider inlining the logic or using generated columns.

34.8 Table User-Defined Functions

Table UDFs return a table (a set of rows and columns) and can be used anywhere a table reference is valid — in FROM clauses, JOIN operations, and subqueries.

34.8.1 SQL Table Functions

CREATE OR REPLACE FUNCTION MERIDIAN.FN_ACCOUNT_STATEMENTS(
    p_account_id INTEGER,
    p_year       INTEGER,
    p_month      INTEGER
)
RETURNS TABLE (
    STATEMENT_DATE  DATE,
    DESCRIPTION     VARCHAR(200),
    DEBIT_AMOUNT    DECIMAL(15,2),
    CREDIT_AMOUNT   DECIMAL(15,2),
    RUNNING_BALANCE DECIMAL(15,2)
)
LANGUAGE SQL
READS SQL DATA
NOT DETERMINISTIC
NO EXTERNAL ACTION
RETURN
    WITH ordered_txns AS (
        SELECT
            TRANSACTION_DATE,
            DESCRIPTION,
            CASE WHEN AMOUNT < 0 THEN ABS(AMOUNT) ELSE 0 END AS DEBIT,
            CASE WHEN AMOUNT >= 0 THEN AMOUNT ELSE 0 END AS CREDIT,
            SUM(AMOUNT) OVER (
                ORDER BY TRANSACTION_DATE, TRANSACTION_ID
                ROWS UNBOUNDED PRECEDING
            ) AS RUNNING_BAL
        FROM MERIDIAN.TRANSACTIONS
        WHERE ACCOUNT_ID = p_account_id
          AND YEAR(TRANSACTION_DATE) = p_year
          AND MONTH(TRANSACTION_DATE) = p_month
    )
    SELECT TRANSACTION_DATE, DESCRIPTION, DEBIT, CREDIT, RUNNING_BAL
    FROM ordered_txns;

Usage with the TABLE() function:

SELECT s.*
FROM TABLE(MERIDIAN.FN_ACCOUNT_STATEMENTS(1001, 2025, 6)) AS s
ORDER BY s.STATEMENT_DATE;

34.8.2 Pipeline Table Functions (External)

For complex row generation that cannot be expressed in a single SQL statement, external table functions provide row-at-a-time generation:

CREATE FUNCTION MERIDIAN.FN_AMORTIZATION_SCHEDULE(
    p_principal DECIMAL(15,2),
    p_annual_rate DECIMAL(7,5),
    p_term_months INTEGER
)
RETURNS TABLE (
    PAYMENT_NUM     INTEGER,
    PAYMENT_DATE    DATE,
    PAYMENT_AMOUNT  DECIMAL(15,2),
    PRINCIPAL_PART  DECIMAL(15,2),
    INTEREST_PART   DECIMAL(15,2),
    REMAINING_BAL   DECIMAL(15,2)
)
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
    WITH RECURSIVE schedule (pmt_num, pmt_date, pmt_amt, principal_pmt, interest_pmt, remaining) AS (
        -- Anchor: calculate the fixed monthly payment
        SELECT
            1,
            CURRENT DATE + 1 MONTH,
            CAST(
                p_principal * (p_annual_rate/1200) * POWER(1 + p_annual_rate/1200, p_term_months)
                / (POWER(1 + p_annual_rate/1200, p_term_months) - 1)
            AS DECIMAL(15,2)),
            CAST(0 AS DECIMAL(15,2)),  -- placeholder
            CAST(0 AS DECIMAL(15,2)),  -- placeholder
            p_principal
        FROM SYSIBM.SYSDUMMY1

        UNION ALL

        SELECT
            pmt_num + 1,
            pmt_date + 1 MONTH,
            pmt_amt,
            CAST(pmt_amt - (remaining * p_annual_rate / 1200) AS DECIMAL(15,2)),
            CAST(remaining * p_annual_rate / 1200 AS DECIMAL(15,2)),
            CAST(remaining - (pmt_amt - remaining * p_annual_rate / 1200) AS DECIMAL(15,2))
        FROM schedule
        WHERE pmt_num < p_term_months
          AND remaining > 0
    )
    SELECT pmt_num, pmt_date, pmt_amt, principal_pmt, interest_pmt, remaining
    FROM schedule
    WHERE pmt_num > 1;  -- skip the anchor row

34.8.3 Table Functions vs. Views

Table functions have several advantages over views:

1. Parameterization. Views cannot accept parameters; table functions can.
2. Row generation. Table functions can create rows that do not exist in any table.
3. Complex logic. Table functions can incorporate procedural logic (in external implementations).

Use views for simple projections and filters; use table functions when parameterization or row generation is needed.

34.9 User-Defined Types

User-defined types (UDTs) enable domain modeling within DB2's type system, adding semantic meaning and type safety.

34.9.1 Distinct Types

A distinct type is a new type based on a built-in type but not directly interchangeable with it:

-- Define distinct types for financial amounts
CREATE DISTINCT TYPE MERIDIAN.USD_AMOUNT AS DECIMAL(15,2) WITH COMPARISONS;
CREATE DISTINCT TYPE MERIDIAN.EUR_AMOUNT AS DECIMAL(15,2) WITH COMPARISONS;
CREATE DISTINCT TYPE MERIDIAN.INTEREST_RATE AS DECIMAL(7,5) WITH COMPARISONS;
CREATE DISTINCT TYPE MERIDIAN.ACCOUNT_NUM AS CHAR(12) WITH COMPARISONS;

-- Use in table definitions
CREATE TABLE MERIDIAN.ACCOUNTS_V2 (
    ACCOUNT_NUMBER  MERIDIAN.ACCOUNT_NUM NOT NULL,
    BALANCE_USD     MERIDIAN.USD_AMOUNT NOT NULL DEFAULT MERIDIAN.USD_AMOUNT(0.00),
    INTEREST_RATE   MERIDIAN.INTEREST_RATE,
    PRIMARY KEY (ACCOUNT_NUMBER)
);

The key benefit is type safety — you cannot accidentally add USD_AMOUNT to EUR_AMOUNT without an explicit cast:

-- This fails with a type error:
SELECT BALANCE_USD + CAST(100.00 AS MERIDIAN.EUR_AMOUNT) FROM MERIDIAN.ACCOUNTS_V2;

-- This requires explicit conversion:
SELECT BALANCE_USD + CAST(CAST(eur_val AS DECIMAL(15,2)) AS MERIDIAN.USD_AMOUNT)
FROM ...;

Why does this matter in a banking context? Consider a program that calculates an exchange rate conversion. Without distinct types, a developer might accidentally add a USD balance to a EUR amount — the code compiles, the SQL executes, and the result is silently wrong. With distinct types, DB2 catches the error at compile time. I have seen exactly this bug in production at a multinational bank; it took three weeks to identify and cost $240,000 in reconciliation effort. Distinct types would have prevented it in the first place.

Casting between distinct types and their base types:

-- From distinct type to base type:
CAST(v_balance AS DECIMAL(15,2))

-- From base type to distinct type:
CAST(500.00 AS MERIDIAN.USD_AMOUNT)

-- Between two distinct types sharing the same base type:
-- Must go through the base type (two casts):
CAST(CAST(v_usd_balance AS DECIMAL(15,2)) AS MERIDIAN.EUR_AMOUNT)

34.9.2 Sourced Functions for Distinct Types

When you create a distinct type, arithmetic and comparison operators are created automatically (with WITH COMPARISONS). But you may also want to create sourced functions — functions that leverage existing built-in functions:

-- Allow SUM and AVG on USD_AMOUNT
CREATE FUNCTION MERIDIAN.SUM(MERIDIAN.USD_AMOUNT)
    RETURNS MERIDIAN.USD_AMOUNT
    SOURCE SYSIBM.SUM(DECIMAL());

CREATE FUNCTION MERIDIAN.AVG(MERIDIAN.USD_AMOUNT)
    RETURNS MERIDIAN.USD_AMOUNT
    SOURCE SYSIBM.AVG(DECIMAL());

34.9.3 Structured Types

DB2 also supports structured types for object-relational modeling, though they are less commonly used in practice:

CREATE TYPE MERIDIAN.ADDRESS_TYPE AS (
    STREET_LINE1  VARCHAR(100),
    STREET_LINE2  VARCHAR(100),
    CITY          VARCHAR(50),
    STATE_CODE    CHAR(2),
    ZIP_CODE      CHAR(10),
    COUNTRY       CHAR(3)
) MODE DB2SQL;

Structured types can have methods, participate in type hierarchies, and be used as column types. However, most practitioners prefer flattened column designs for simplicity and query compatibility.

34.9.4 When to Use UDTs

Use distinct types when: - Two columns share the same base type but represent fundamentally different domains (USD vs. EUR, account number vs. routing number). - You want compile-time protection against accidental mixing. - You want to create domain-specific aggregate functions.

Avoid distinct types when: - The overhead of casting is not justified by the safety benefit. - You are working in an environment where third-party tools struggle with non-standard types. - The types will be used heavily in dynamic SQL, where casting adds complexity.

At Meridian Bank, we use distinct types for all financial amounts (preventing currency mixing), account identifiers (preventing confusion between account numbers, routing numbers, and loan IDs), and for SSN storage (ensuring that SSN values are never accidentally displayed without masking).

34.10 Debugging and Testing Database Logic

34.10.1 IBM Data Studio Debugger

IBM Data Studio provides a visual debugger for SQL PL procedures:

1. Set breakpoints by clicking in the margin next to SQL PL statements.
2. Step through execution with Step Into (F5), Step Over (F6), and Step Return (F7).
3. Inspect variables in the Variables view — all declared variables and their current values.
4. Evaluate expressions in the Expressions view.
5. View the call stack for nested procedure calls.

To enable debugging, the procedure must be compiled with debug information:

-- Enable debug mode
ALTER PROCEDURE MERIDIAN.PROC_TRANSFER
    ALLOW DEBUG MODE;

34.10.2 Unit Testing Procedures

Without a built-in testing framework, we create our own:

CREATE OR REPLACE PROCEDURE MERIDIAN.TEST_PROC_TRANSFER()
LANGUAGE SQL
BEGIN
    DECLARE v_status_code INTEGER;
    DECLARE v_status_msg VARCHAR(500);
    DECLARE v_balance_from DECIMAL(15,2);
    DECLARE v_balance_to DECIMAL(15,2);
    DECLARE v_test_count INTEGER DEFAULT 0;
    DECLARE v_pass_count INTEGER DEFAULT 0;

    -- Setup: create test data
    DELETE FROM MERIDIAN.ACCOUNTS WHERE ACCOUNT_ID IN (99901, 99902);
    INSERT INTO MERIDIAN.ACCOUNTS (ACCOUNT_ID, CUSTOMER_ID, ACCOUNT_TYPE, BALANCE, STATUS)
    VALUES (99901, 9999, 'CHECKING', 1000.00, 'ACTIVE');
    INSERT INTO MERIDIAN.ACCOUNTS (ACCOUNT_ID, CUSTOMER_ID, ACCOUNT_TYPE, BALANCE, STATUS)
    VALUES (99902, 9999, 'SAVINGS', 500.00, 'ACTIVE');

    -- Test 1: Normal transfer
    SET v_test_count = v_test_count + 1;
    CALL MERIDIAN.PROC_TRANSFER(99901, 99902, 200.00, v_status_code, v_status_msg);

    SELECT BALANCE INTO v_balance_from FROM MERIDIAN.ACCOUNTS WHERE ACCOUNT_ID = 99901;
    SELECT BALANCE INTO v_balance_to FROM MERIDIAN.ACCOUNTS WHERE ACCOUNT_ID = 99902;

    IF v_status_code = 0 AND v_balance_from = 800.00 AND v_balance_to = 700.00 THEN
        SET v_pass_count = v_pass_count + 1;
        INSERT INTO MERIDIAN.TEST_RESULTS VALUES ('TEST_TRANSFER', 1, 'PASS', 'Normal transfer', CURRENT TIMESTAMP);
    ELSE
        INSERT INTO MERIDIAN.TEST_RESULTS VALUES ('TEST_TRANSFER', 1, 'FAIL',
            'Expected 800/700, got ' || CHAR(v_balance_from) || '/' || CHAR(v_balance_to),
            CURRENT TIMESTAMP);
    END IF;

    -- Test 2: Insufficient funds
    SET v_test_count = v_test_count + 1;
    CALL MERIDIAN.PROC_TRANSFER(99901, 99902, 999999.00, v_status_code, v_status_msg);

    IF v_status_code < 0 THEN
        SET v_pass_count = v_pass_count + 1;
        INSERT INTO MERIDIAN.TEST_RESULTS VALUES ('TEST_TRANSFER', 2, 'PASS', 'Insufficient funds rejected', CURRENT TIMESTAMP);
    ELSE
        INSERT INTO MERIDIAN.TEST_RESULTS VALUES ('TEST_TRANSFER', 2, 'FAIL', 'Should have rejected', CURRENT TIMESTAMP);
    END IF;

    -- Teardown
    DELETE FROM MERIDIAN.ACCOUNTS WHERE ACCOUNT_ID IN (99901, 99902);

    -- Summary
    INSERT INTO MERIDIAN.TEST_RESULTS VALUES ('TEST_TRANSFER', 0,
        CASE WHEN v_pass_count = v_test_count THEN 'ALL_PASS' ELSE 'FAILURES' END,
        CHAR(v_pass_count) || '/' || CHAR(v_test_count) || ' passed',
        CURRENT TIMESTAMP);
END
@

34.10.3 Test Data Management

For repeatable testing, use savepoints:

-- In the test procedure:
SAVEPOINT test_start ON ROLLBACK RETAIN CURSORS;

-- ... run tests ...

ROLLBACK TO SAVEPOINT test_start;  -- clean up all test data

This ensures test data never leaks into production tables, even if the test fails partway through.

34.10.4 Test-Driven Development for Stored Procedures

While test-driven development (TDD) is standard practice in application code, it is rarely applied to stored procedures. This is a missed opportunity. The pattern works as follows:

1. Write the test first. Define the expected behavior: "When I call PROC_TRANSFER_FUNDS with valid accounts and sufficient balance, the source balance decreases by the transfer amount, the destination balance increases by the same amount, and a transaction record is created."

2. Create a skeleton procedure. The procedure exists but returns a failure status for all calls.

3. Run the test. It fails (as expected).

4. Implement the procedure logic. Write the SQL PL to satisfy the test.

5. Run the test again. It passes.

6. Add the next test case. "When I call PROC_TRANSFER_FUNDS with insufficient balance, the procedure returns status code -4 and neither account balance changes."

7. Repeat. Each iteration adds a new scenario and the corresponding logic.

This approach is especially valuable for complex procedures like the fee calculation engine, where the number of rule combinations makes it easy to miss edge cases. At Meridian Bank, the fee calculation procedure has 23 test cases covering every combination of account type, balance tier, transaction count, and fee waiver condition. When a new fee rule is added, the corresponding test case is written first.

34.10.5 Performance Testing Procedures

Beyond functional correctness, stored procedures need performance testing. A procedure that works perfectly for one account might degrade when called 10,000 times per minute during peak hours.

Key performance tests: - Throughput: Call the procedure from 50 concurrent connections and measure transactions per second. - Latency distribution: Measure p50, p95, and p99 response times under load. - Lock contention: Monitor lock waits during concurrent transfer tests (especially with the deadlock prevention pattern). - Resource consumption: Check DB2 CPU consumption, buffer pool hit ratios, and log write rates during sustained procedure execution.

-- Simple timing test
DECLARE v_start TIMESTAMP;
DECLARE v_end TIMESTAMP;
DECLARE v_elapsed DECIMAL(10,3);

SET v_start = CURRENT TIMESTAMP;

-- Call the procedure 1000 times in a loop
-- ...

SET v_end = CURRENT TIMESTAMP;
SET v_elapsed = TIMESTAMPDIFF(1, CHAR(v_end - v_start)) / 1000000.0;

INSERT INTO MERIDIAN.PERF_RESULTS (TEST_NAME, ITERATIONS, ELAPSED_SECONDS, TPS)
VALUES ('PROC_TRANSFER_FUNDS', 1000, v_elapsed, 1000 / v_elapsed);

34.11 Version Management

34.11.1 Naming Conventions

Establish consistent naming from the start:

Procedures:  PROC_{ACTION}_{ENTITY}       e.g., PROC_TRANSFER_FUNDS
Functions:   FN_{DESCRIPTION}              e.g., FN_COMPOUND_INTEREST
Types:       {DOMAIN}_TYPE or {DOMAIN}     e.g., USD_AMOUNT, ADDRESS_TYPE
Test procs:  TEST_{PROC_NAME}              e.g., TEST_PROC_TRANSFER_FUNDS

34.11.2 Schema-Based Versioning

Use schemas to maintain multiple versions simultaneously:

-- Current production version
CREATE PROCEDURE MERIDIAN_V3.PROC_TRANSFER_FUNDS(...) ...

-- New version under development
CREATE PROCEDURE MERIDIAN_V4.PROC_TRANSFER_FUNDS(...) ...

-- Switch applications by changing CURRENT SCHEMA or path
SET CURRENT SCHEMA = 'MERIDIAN_V4';
-- or
SET CURRENT PATH = 'MERIDIAN_V4', 'MERIDIAN_V3', 'SYSIBM', 'SYSFUN';

34.11.3 Source Control Integration

Every database object should have a corresponding file in version control:

db/
  procedures/
    proc_transfer_funds.sql
    proc_loan_payment.sql
  functions/
    fn_compound_interest.sql
    fn_account_statements.sql
  types/
    usd_amount.sql
    account_num.sql
  deploy/
    deploy_v4.sql          -- orchestrates deployment
    rollback_v4.sql        -- undoes deployment

The deployment script applies objects in dependency order:

-- deploy_v4.sql
-- 1. Types first (functions and procedures depend on them)
\i types/usd_amount.sql
\i types/account_num.sql

-- 2. Functions (procedures may call them)
\i functions/fn_compound_interest.sql
\i functions/fn_account_statements.sql

-- 3. Procedures
\i procedures/proc_transfer_funds.sql
\i procedures/proc_loan_payment.sql

-- 4. Grants
GRANT EXECUTE ON PROCEDURE MERIDIAN.PROC_TRANSFER_FUNDS TO ROLE MERIDIAN_APP;
GRANT EXECUTE ON FUNCTION MERIDIAN.FN_COMPOUND_INTEREST TO ROLE MERIDIAN_APP;

34.11.4 Backward Compatibility

When modifying a procedure's signature, maintain backward compatibility by creating a wrapper:

-- Original signature (V3)
CREATE PROCEDURE MERIDIAN.PROC_TRANSFER_V3(
    IN p_from_acct INTEGER,
    IN p_to_acct   INTEGER,
    IN p_amount    DECIMAL(15,2)
) ...

-- New signature (V4) adds currency parameter
CREATE PROCEDURE MERIDIAN.PROC_TRANSFER_V4(
    IN p_from_acct INTEGER,
    IN p_to_acct   INTEGER,
    IN p_amount    DECIMAL(15,2),
    IN p_currency  CHAR(3) DEFAULT 'USD'
) ...

-- Wrapper maintains backward compatibility
CREATE OR REPLACE PROCEDURE MERIDIAN.PROC_TRANSFER(
    IN p_from_acct INTEGER,
    IN p_to_acct   INTEGER,
    IN p_amount    DECIMAL(15,2)
)
LANGUAGE SQL
BEGIN
    CALL MERIDIAN.PROC_TRANSFER_V4(p_from_acct, p_to_acct, p_amount, 'USD');
END
@

34.12 Meridian Bank Procedure Library

Now we bring everything together. This section presents the core procedures that form Meridian National Bank's database logic layer.

34.12.1 Account Transfer Procedure

CREATE OR REPLACE PROCEDURE MERIDIAN.PROC_TRANSFER_FUNDS(
    IN  p_from_account  INTEGER,
    IN  p_to_account    INTEGER,
    IN  p_amount        DECIMAL(15,2),
    IN  p_description   VARCHAR(200),
    OUT p_txn_id        BIGINT,
    OUT p_status_code   INTEGER,
    OUT p_status_msg    VARCHAR(500)
)
LANGUAGE SQL
SPECIFIC PROC_TRANSFER_FUNDS
MODIFIES SQL DATA
BEGIN
    DECLARE v_from_balance DECIMAL(15,2);
    DECLARE v_from_status  VARCHAR(20);
    DECLARE v_to_status    VARCHAR(20);
    DECLARE v_sqlcode      INTEGER DEFAULT 0;
    DECLARE v_sqlstate     CHAR(5) DEFAULT '00000';
    DECLARE v_errmsg       VARCHAR(500) DEFAULT '';

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS EXCEPTION 1
            v_sqlcode = DB2_RETURNED_SQLCODE,
            v_sqlstate = RETURNED_SQLSTATE,
            v_errmsg = MESSAGE_TEXT;
        SET p_status_code = v_sqlcode;
        SET p_status_msg = 'Transfer failed: ' || v_errmsg;
        SET p_txn_id = NULL;
    END;

    -- Validate amount
    IF p_amount <= 0 THEN
        SET p_status_code = -1;
        SET p_status_msg = 'Transfer amount must be positive';
        RETURN;
    END IF;

    -- Validate accounts exist and are active
    SELECT BALANCE, STATUS INTO v_from_balance, v_from_status
    FROM MERIDIAN.ACCOUNTS WHERE ACCOUNT_ID = p_from_account;

    IF v_from_status <> 'ACTIVE' THEN
        SET p_status_code = -2;
        SET p_status_msg = 'Source account is not active';
        RETURN;
    END IF;

    SELECT STATUS INTO v_to_status
    FROM MERIDIAN.ACCOUNTS WHERE ACCOUNT_ID = p_to_account;

    IF v_to_status <> 'ACTIVE' THEN
        SET p_status_code = -3;
        SET p_status_msg = 'Destination account is not active';
        RETURN;
    END IF;

    -- Check sufficient funds
    IF v_from_balance < p_amount THEN
        SET p_status_code = -4;
        SET p_status_msg = 'Insufficient funds. Available: ' || CHAR(v_from_balance);
        RETURN;
    END IF;

    -- Perform transfer (ordered by account_id to prevent deadlocks)
    IF p_from_account < p_to_account THEN
        UPDATE MERIDIAN.ACCOUNTS SET BALANCE = BALANCE - p_amount WHERE ACCOUNT_ID = p_from_account;
        UPDATE MERIDIAN.ACCOUNTS SET BALANCE = BALANCE + p_amount WHERE ACCOUNT_ID = p_to_account;
    ELSE
        UPDATE MERIDIAN.ACCOUNTS SET BALANCE = BALANCE + p_amount WHERE ACCOUNT_ID = p_to_account;
        UPDATE MERIDIAN.ACCOUNTS SET BALANCE = BALANCE - p_amount WHERE ACCOUNT_ID = p_from_account;
    END IF;

    -- Record transaction
    INSERT INTO MERIDIAN.TRANSACTIONS
        (FROM_ACCOUNT, TO_ACCOUNT, AMOUNT, TRANSACTION_TYPE, DESCRIPTION, TRANSACTION_DATE)
    VALUES
        (p_from_account, p_to_account, p_amount, 'TRANSFER', p_description, CURRENT TIMESTAMP);

    -- Get the generated transaction ID
    SET p_txn_id = IDENTITY_VAL_LOCAL();
    SET p_status_code = 0;
    SET p_status_msg = 'Transfer completed successfully';
END
@

Note the deadlock prevention: updates are always performed in ascending account ID order, regardless of which is the source and which is the destination.

34.12.2 Loan Payment Processing

CREATE OR REPLACE PROCEDURE MERIDIAN.PROC_PROCESS_LOAN_PAYMENT(
    IN  p_loan_id        INTEGER,
    IN  p_payment_amount DECIMAL(15,2),
    OUT p_interest_paid  DECIMAL(15,2),
    OUT p_principal_paid DECIMAL(15,2),
    OUT p_new_balance    DECIMAL(15,2),
    OUT p_status_code    INTEGER,
    OUT p_status_msg     VARCHAR(500)
)
LANGUAGE SQL
SPECIFIC PROC_LOAN_PAYMENT
MODIFIES SQL DATA
BEGIN
    DECLARE v_current_balance DECIMAL(15,2);
    DECLARE v_interest_rate   DECIMAL(7,5);
    DECLARE v_last_payment    DATE;
    DECLARE v_days_since      INTEGER;
    DECLARE v_daily_rate      DECIMAL(15,10);
    DECLARE v_accrued_interest DECIMAL(15,2);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        DECLARE v_ec INTEGER;
        DECLARE v_es CHAR(5);
        DECLARE v_em VARCHAR(500);
        GET DIAGNOSTICS EXCEPTION 1
            v_ec = DB2_RETURNED_SQLCODE,
            v_es = RETURNED_SQLSTATE,
            v_em = MESSAGE_TEXT;
        SET p_status_code = v_ec;
        SET p_status_msg = 'Payment failed: ' || v_em;
    END;

    -- Get loan details
    SELECT CURRENT_BALANCE, ANNUAL_INTEREST_RATE, LAST_PAYMENT_DATE
    INTO v_current_balance, v_interest_rate, v_last_payment
    FROM MERIDIAN.LOANS
    WHERE LOAN_ID = p_loan_id AND STATUS = 'ACTIVE';

    -- Calculate accrued interest
    SET v_days_since = DAYS(CURRENT DATE) - DAYS(COALESCE(v_last_payment, CURRENT DATE - 30 DAYS));
    SET v_daily_rate = v_interest_rate / 36500;
    SET v_accrued_interest = CAST(v_current_balance * v_daily_rate * v_days_since AS DECIMAL(15,2));

    -- Apply payment: interest first, then principal
    IF p_payment_amount <= v_accrued_interest THEN
        SET p_interest_paid = p_payment_amount;
        SET p_principal_paid = 0.00;
    ELSE
        SET p_interest_paid = v_accrued_interest;
        SET p_principal_paid = p_payment_amount - v_accrued_interest;
    END IF;

    SET p_new_balance = v_current_balance - p_principal_paid;

    -- Update loan
    UPDATE MERIDIAN.LOANS
    SET CURRENT_BALANCE = p_new_balance,
        LAST_PAYMENT_DATE = CURRENT DATE,
        TOTAL_INTEREST_PAID = TOTAL_INTEREST_PAID + p_interest_paid,
        TOTAL_PRINCIPAL_PAID = TOTAL_PRINCIPAL_PAID + p_principal_paid
    WHERE LOAN_ID = p_loan_id;

    -- Record payment
    INSERT INTO MERIDIAN.LOAN_PAYMENTS
        (LOAN_ID, PAYMENT_DATE, TOTAL_AMOUNT, INTEREST_PORTION, PRINCIPAL_PORTION, REMAINING_BALANCE)
    VALUES
        (p_loan_id, CURRENT DATE, p_payment_amount, p_interest_paid, p_principal_paid, p_new_balance);

    -- Check if loan is paid off
    IF p_new_balance <= 0 THEN
        UPDATE MERIDIAN.LOANS SET STATUS = 'PAID_OFF' WHERE LOAN_ID = p_loan_id;
        SET p_status_msg = 'Loan fully paid off!';
        SET p_new_balance = 0.00;
    ELSE
        SET p_status_msg = 'Payment applied successfully';
    END IF;

    SET p_status_code = 0;
END
@

34.12.3 Fee Calculation Engine

CREATE OR REPLACE PROCEDURE MERIDIAN.PROC_CALCULATE_MONTHLY_FEES(
    IN  p_account_id  INTEGER,
    OUT p_total_fees   DECIMAL(15,2),
    OUT p_fee_details  VARCHAR(2000),
    OUT p_status_code  INTEGER,
    OUT p_status_msg   VARCHAR(500)
)
LANGUAGE SQL
SPECIFIC PROC_MONTHLY_FEES
MODIFIES SQL DATA
BEGIN
    DECLARE v_acct_type VARCHAR(20);
    DECLARE v_balance DECIMAL(15,2);
    DECLARE v_txn_count INTEGER;
    DECLARE v_has_direct_dep SMALLINT;
    DECLARE v_maintenance_fee DECIMAL(15,2) DEFAULT 0;
    DECLARE v_overdraft_fees DECIMAL(15,2) DEFAULT 0;
    DECLARE v_atm_fees DECIMAL(15,2) DEFAULT 0;
    DECLARE v_overdraft_count INTEGER DEFAULT 0;
    DECLARE v_foreign_atm_count INTEGER DEFAULT 0;

    -- Get account info
    SELECT ACCOUNT_TYPE, BALANCE INTO v_acct_type, v_balance
    FROM MERIDIAN.ACCOUNTS WHERE ACCOUNT_ID = p_account_id;

    -- Count transactions this month
    SELECT COUNT(*) INTO v_txn_count
    FROM MERIDIAN.TRANSACTIONS
    WHERE (FROM_ACCOUNT = p_account_id OR TO_ACCOUNT = p_account_id)
      AND MONTH(TRANSACTION_DATE) = MONTH(CURRENT DATE)
      AND YEAR(TRANSACTION_DATE) = YEAR(CURRENT DATE);

    -- Check for direct deposit
    SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END INTO v_has_direct_dep
    FROM MERIDIAN.TRANSACTIONS
    WHERE TO_ACCOUNT = p_account_id
      AND TRANSACTION_TYPE = 'DIRECT_DEPOSIT'
      AND MONTH(TRANSACTION_DATE) = MONTH(CURRENT DATE);

    -- Maintenance fee logic
    CASE v_acct_type
        WHEN 'CHECKING' THEN
            IF v_balance < 1500 AND v_has_direct_dep = 0 THEN
                SET v_maintenance_fee = 12.00;
            ELSE
                SET v_maintenance_fee = 0.00;
            END IF;
        WHEN 'SAVINGS' THEN
            IF v_balance < 300 THEN
                SET v_maintenance_fee = 5.00;
            END IF;
        WHEN 'PREMIUM' THEN
            SET v_maintenance_fee = 0.00;  -- Premium accounts: no maintenance fee
        ELSE
            SET v_maintenance_fee = 0.00;
    END CASE;

    -- Count overdrafts
    SELECT COUNT(*) INTO v_overdraft_count
    FROM MERIDIAN.OVERDRAFT_EVENTS
    WHERE ACCOUNT_ID = p_account_id
      AND MONTH(EVENT_DATE) = MONTH(CURRENT DATE);
    SET v_overdraft_fees = v_overdraft_count * 35.00;

    -- Foreign ATM fees
    SELECT COUNT(*) INTO v_foreign_atm_count
    FROM MERIDIAN.ATM_TRANSACTIONS
    WHERE ACCOUNT_ID = p_account_id
      AND ATM_NETWORK <> 'MERIDIAN'
      AND MONTH(TXN_DATE) = MONTH(CURRENT DATE);
    IF v_foreign_atm_count > 2 THEN
        SET v_atm_fees = (v_foreign_atm_count - 2) * 3.00;  -- first 2 are free
    END IF;

    -- Total
    SET p_total_fees = v_maintenance_fee + v_overdraft_fees + v_atm_fees;
    SET p_fee_details = 'Maintenance: $' || CHAR(v_maintenance_fee)
        || ' | Overdraft(' || CHAR(v_overdraft_count) || '): $' || CHAR(v_overdraft_fees)
        || ' | Foreign ATM(' || CHAR(v_foreign_atm_count) || '): $' || CHAR(v_atm_fees);

    -- Apply fees if any
    IF p_total_fees > 0 THEN
        UPDATE MERIDIAN.ACCOUNTS
        SET BALANCE = BALANCE - p_total_fees
        WHERE ACCOUNT_ID = p_account_id;

        INSERT INTO MERIDIAN.TRANSACTIONS
            (FROM_ACCOUNT, AMOUNT, TRANSACTION_TYPE, DESCRIPTION, TRANSACTION_DATE)
        VALUES
            (p_account_id, -p_total_fees, 'FEE', p_fee_details, CURRENT TIMESTAMP);
    END IF;

    SET p_status_code = 0;
    SET p_status_msg = 'Fees calculated and applied';
END
@

34.12.4 Customer Onboarding Workflow

CREATE OR REPLACE PROCEDURE MERIDIAN.PROC_ONBOARD_CUSTOMER(
    IN  p_first_name    VARCHAR(50),
    IN  p_last_name     VARCHAR(50),
    IN  p_ssn           CHAR(11),
    IN  p_email         VARCHAR(100),
    IN  p_phone         VARCHAR(20),
    IN  p_address_line1 VARCHAR(100),
    IN  p_city          VARCHAR(50),
    IN  p_state         CHAR(2),
    IN  p_zip           CHAR(10),
    IN  p_initial_deposit DECIMAL(15,2),
    IN  p_account_type  VARCHAR(20),
    OUT p_customer_id   INTEGER,
    OUT p_account_id    INTEGER,
    OUT p_status_code   INTEGER,
    OUT p_status_msg    VARCHAR(500)
)
LANGUAGE SQL
SPECIFIC PROC_ONBOARD_CUSTOMER
MODIFIES SQL DATA
BEGIN
    DECLARE v_existing INTEGER DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        DECLARE v_ec INTEGER;
        DECLARE v_em VARCHAR(500);
        GET DIAGNOSTICS EXCEPTION 1
            v_ec = DB2_RETURNED_SQLCODE,
            v_em = MESSAGE_TEXT;
        SET p_status_code = v_ec;
        SET p_status_msg = 'Onboarding failed: ' || v_em;
    END;

    -- Check for duplicate SSN
    SELECT COUNT(*) INTO v_existing
    FROM MERIDIAN.CUSTOMERS WHERE SSN_ENCRYPTED = MERIDIAN.FN_ENCRYPT(p_ssn);

    IF v_existing > 0 THEN
        SET p_status_code = -1;
        SET p_status_msg = 'Customer with this SSN already exists';
        RETURN;
    END IF;

    -- Validate minimum deposit
    IF p_initial_deposit < 25.00 THEN
        SET p_status_code = -2;
        SET p_status_msg = 'Minimum opening deposit is $25.00';
        RETURN;
    END IF;

    -- Create customer record
    INSERT INTO MERIDIAN.CUSTOMERS
        (FIRST_NAME, LAST_NAME, SSN_ENCRYPTED, EMAIL, PHONE,
         ADDRESS_LINE1, CITY, STATE_CODE, ZIP_CODE,
         CREATED_DATE, STATUS)
    VALUES
        (p_first_name, p_last_name, MERIDIAN.FN_ENCRYPT(p_ssn), p_email, p_phone,
         p_address_line1, p_city, p_state, p_zip,
         CURRENT DATE, 'ACTIVE');

    SET p_customer_id = IDENTITY_VAL_LOCAL();

    -- Create account
    INSERT INTO MERIDIAN.ACCOUNTS
        (CUSTOMER_ID, ACCOUNT_TYPE, BALANCE, STATUS, OPENED_DATE)
    VALUES
        (p_customer_id, p_account_type, p_initial_deposit, 'ACTIVE', CURRENT DATE);

    SET p_account_id = IDENTITY_VAL_LOCAL();

    -- Record initial deposit as transaction
    INSERT INTO MERIDIAN.TRANSACTIONS
        (TO_ACCOUNT, AMOUNT, TRANSACTION_TYPE, DESCRIPTION, TRANSACTION_DATE)
    VALUES
        (p_account_id, p_initial_deposit, 'DEPOSIT', 'Initial deposit - account opening',
         CURRENT TIMESTAMP);

    -- Create audit trail
    INSERT INTO MERIDIAN.AUDIT_LOG
        (EVENT_TYPE, ENTITY_TYPE, ENTITY_ID, DESCRIPTION, EVENT_TIMESTAMP)
    VALUES
        ('CUSTOMER_ONBOARDING', 'CUSTOMER', p_customer_id,
         'New customer onboarded: ' || p_first_name || ' ' || p_last_name
         || ', Account Type: ' || p_account_type,
         CURRENT TIMESTAMP);

    SET p_status_code = 0;
    SET p_status_msg = 'Customer onboarded successfully. Customer ID: '
        || CHAR(p_customer_id) || ', Account ID: ' || CHAR(p_account_id);
END
@

Spaced Review: Connecting to Previous Chapters

From Chapter 12 (Transactions and Concurrency): Stored procedures execute within the caller's transaction context (unless they explicitly commit). The deadlock prevention pattern in PROC_TRANSFER_FUNDS — always updating accounts in ascending ID order — directly applies Chapter 12's lock ordering principle. Remember that BEGIN ATOMIC creates a savepoint, not a new transaction.

Consider this scenario: Application A calls PROC_TRANSFER_FUNDS to move $500 from account 1001 to account 1002. Simultaneously, Application B calls the same procedure to move $300 from account 1002 to account 1001. Without lock ordering, A locks account 1001 (to debit) while B locks account 1002 (to debit). Then A tries to lock account 1002 (blocked by B) while B tries to lock account 1001 (blocked by A) — deadlock. With our ascending-ID ordering, both A and B start by locking the lower-numbered account (1001), so one must wait for the other. No deadlock.

Review question: If a stored procedure calls another stored procedure, and the inner procedure has BEGIN ATOMIC, does an error in the inner procedure roll back the outer procedure's changes as well? Answer: No. The inner BEGIN ATOMIC creates a savepoint. An error inside it rolls back only to that savepoint. The outer procedure's changes are unaffected unless the outer procedure's error handler also triggers a rollback.

From Chapter 22 (Query Optimization): The performance considerations for scalar UDFs in WHERE clauses connect to Chapter 22's predicate pushdown discussion. When DB2 cannot evaluate a UDF during index access, it must perform a table scan and evaluate the UDF for every row. Use EXPLAIN to verify that UDFs are not defeating your carefully designed access paths.

Recall the access path selection process from Chapter 22. DB2's optimizer evaluates predicates and determines which ones can be used as index start/stop keys, which can be used as index screening predicates, and which must be evaluated as residual predicates (post-fetch). A scalar UDF in a WHERE clause is almost always a residual predicate because the optimizer cannot reason about the UDF's output range. This means the UDF is evaluated for every row that passes the other predicates — potentially millions of times.

Review question: You have an index on MERIDIAN.ACCOUNTS(ACCOUNT_TYPE, BALANCE). Which of these queries can use the index efficiently? - (a) WHERE ACCOUNT_TYPE = 'SAVINGS' AND BALANCE > 1000 - (b) WHERE MERIDIAN.FN_RISK_SCORE(BALANCE, OVERDRAFTS, DAYS_OPEN) > 75 - (c) WHERE ACCOUNT_TYPE = 'SAVINGS' AND MERIDIAN.FN_FORMAT_CURRENCY(BALANCE) = '$1,000.00'

Answer: (a) uses the index for both predicates (start/stop on ACCOUNT_TYPE, screening on BALANCE). (b) cannot use the index at all. (c) uses the index for ACCOUNT_TYPE but must evaluate the UDF for every SAVINGS row.

From Chapter 32 (Backup and Recovery): Stored procedures and UDFs are catalog objects — they are included in database backups. However, always maintain source control as your primary recovery mechanism. A database restore might recover an older version of a procedure; your Git repository always has the definitive version history.

Consider this disaster scenario: A DBA accidentally drops PROC_TRANSFER_FUNDS on a Friday afternoon. The most recent backup was taken at 2:00 AM. If the procedure was modified at 10:00 AM (after the backup), restoring from backup would recover the old version, not the current one. With Git, you can always retrieve the current version and redeploy. This is why the version management discipline described in Section 34.11 is not optional — it is your safety net.

Review question: After restoring a DB2 database from backup, you notice that PROC_TRANSFER_FUNDS still has the old version. What is the fastest way to update it to the current version? Answer: Run the deployment script from Git that contains the current CREATE OR REPLACE PROCEDURE statement. This is why every procedure change should be committed to source control before deployment.

Summary

This chapter has covered the full spectrum of DB2's server-side programming capabilities. You learned to make informed decisions about where to place business logic, mastered SQL PL's advanced features including arrays and dynamic SQL, built robust error handling patterns, and implemented Meridian Bank's core procedure library.

The decision framework in Section 34.1 gives you a principled basis for choosing between database-tier and application-tier logic. The SQL PL features in Section 34.2 — compound statements, cursor variables, dynamic SQL, and arrays — provide a powerful procedural programming environment within the database engine. The error handling pattern in Section 34.3 ensures that every procedure reports meaningful results to its callers, whether the operation succeeds or fails.

External stored procedures in Java (Section 34.5) and COBOL (Section 34.6) bridge the gap when SQL PL is insufficient. Java provides access to modern libraries and complex algorithms; COBOL leverages decades of existing mainframe business logic. User-defined functions (Sections 34.7 and 34.8) extend DB2's vocabulary with domain-specific calculations that can be used directly in SQL queries — compound interest, risk scoring, data masking, and amortization schedules. User-defined types (Section 34.9) add compile-time type safety that prevents subtle bugs in financial calculations.

The procedures in Section 34.12 are not just examples — they are the foundation of a real banking system's database layer. The transfer procedure with deadlock prevention, the loan payment processor with proper interest accrual, the fee calculation engine with its rule-based logic, and the customer onboarding workflow with its validation and audit trail — these represent the patterns you will encounter and implement in enterprise database development.

Testing (Section 34.10) and version management (Section 34.11) complete the professional practice of database development. Without automated tests, you cannot refactor with confidence. Without version control, you cannot deploy with safety. Together, they transform stored procedure development from ad-hoc scripting into a disciplined engineering practice.

In the next chapter, we shift from the database tier to the API tier, exploring how to expose DB2 data through RESTful APIs, GraphQL endpoints, and event streaming — connecting the powerful database logic you built here to the modern application architectures that consume it.