Chapter 12: Views, Triggers, and Stored Procedures — Encapsulating Logic in the Database

Should this logic live in the application or the database? This chapter gives you the framework to decide -- and the skills to implement either way. By the end, you will have built views that shield branch managers from raw table complexity, triggers that silently record every data change for regulatory audit, and stored procedures that execute multi-step banking operations as atomic units. More importantly, you will understand when each of these tools is the right choice -- and when it is the wrong one.

Learning Objectives

After completing this chapter, you will be able to:

1. Create and use views for security, abstraction, and convenience.
2. Distinguish updateable views from read-only views, and use WITH CHECK OPTION to enforce constraints through views.
3. Write BEFORE and AFTER triggers for audit, validation, and automation on both DB2 for z/OS and DB2 for LUW.
4. Create basic stored procedures using SQL PL, including parameters, control flow, cursors, and error handling.
5. Evaluate the trade-offs between putting logic in the database versus in the application layer.
6. Implement audit trails and business rules for the Meridian National Bank project.

12.1 Views -- Virtual Tables for Security and Abstraction

A view is a named SELECT statement stored in the database catalog. It does not store data. Every time you query a view, DB2 executes the underlying SELECT and returns the result as if it were a table. This single concept -- a named query that behaves like a table -- solves a surprising number of real-world problems.

Why Views Exist

Consider three scenarios at Meridian National Bank:

1. Security: Branch managers need to see customer accounts, but only for their branch. They should never see accounts at other branches, and they should never see internal risk scores or fraud flags. You could enforce this in every application that managers use, but that means trusting every application developer to implement the filter correctly. A view enforces it once, at the database level.

2. Abstraction: The schema has been normalized into CUSTOMER, ACCOUNT, ACCOUNT_TYPE, and BRANCH tables. Application developers writing reports constantly join these four tables with the same join conditions. A view encapsulates that join, giving developers a single "table" to query.

3. Stability: The DBA needs to restructure the CUSTOMER table -- splitting it into CUSTOMER and CUSTOMER_CONTACT. If applications query a view rather than the base table, the DBA can redefine the view to join the two new tables, and applications never notice the change.

These are not hypothetical benefits. They are the reasons views have been a core part of SQL since the original standard.

CREATE VIEW Syntax

The basic syntax is identical on z/OS and LUW:

CREATE VIEW schema.view_name (column1, column2, ...)
AS
    SELECT ...
    FROM ...
    WHERE ...
WITH CHECK OPTION;   -- optional, discussed in Section 12.2

The column list after the view name is optional. If you omit it, DB2 uses the column names from the SELECT. You must supply column names if the SELECT includes computed columns without aliases, or if two columns from different tables share the same name.

Simple Views

The simplest view just filters rows or columns from a single table:

-- View: Only active accounts
CREATE VIEW meridian.active_accounts AS
    SELECT account_id, customer_id, account_type_code,
           balance, open_date, branch_id
    FROM meridian.account
    WHERE status = 'ACTIVE';

-- View: Customer names and contact info only (hides SSN, risk score, etc.)
CREATE VIEW meridian.customer_directory AS
    SELECT customer_id, first_name, last_name,
           email, phone, city, state
    FROM meridian.customer;

Querying a view works exactly like querying a table:

SELECT account_id, balance
FROM meridian.active_accounts
WHERE balance > 10000;

DB2's optimizer merges the view definition with your query, producing an access plan equivalent to:

SELECT account_id, balance
FROM meridian.account
WHERE status = 'ACTIVE'
  AND balance > 10000;

This is called view merging, and it means that in most cases, using a view has zero performance cost compared to writing the query directly against the base tables. The optimizer sees through the abstraction.

Views with Joins

Views become genuinely powerful when they encapsulate joins:

CREATE VIEW meridian.account_summary AS
    SELECT a.account_id,
           c.first_name || ' ' || c.last_name AS customer_name,
           at.type_description AS account_type,
           a.balance,
           a.open_date,
           b.branch_name,
           b.city AS branch_city
    FROM meridian.account a
    JOIN meridian.customer c ON a.customer_id = c.customer_id
    JOIN meridian.account_type at ON a.account_type_code = at.type_code
    JOIN meridian.branch b ON a.branch_id = b.branch_id
    WHERE a.status = 'ACTIVE';

Now any report writer can query meridian.account_summary without knowing the underlying schema. If the DBA later renames a column in the ACCOUNT table, only the view definition needs to change -- not the dozens of reports that depend on it.

Views with Aggregation

Views can include GROUP BY, aggregate functions, and even subqueries:

CREATE VIEW meridian.branch_totals AS
    SELECT b.branch_id,
           b.branch_name,
           COUNT(a.account_id) AS total_accounts,
           SUM(a.balance) AS total_deposits,
           AVG(a.balance) AS avg_balance,
           MIN(a.open_date) AS oldest_account
    FROM meridian.branch b
    LEFT JOIN meridian.account a
        ON b.branch_id = a.branch_id
        AND a.status = 'ACTIVE'
    GROUP BY b.branch_id, b.branch_name;

This view gives each branch manager an instant snapshot of their branch's position. Note the LEFT JOIN -- branches with no active accounts still appear, with zeros and NULLs.

Views on Views

You can build views on top of other views:

CREATE VIEW meridian.large_branches AS
    SELECT branch_id, branch_name, total_accounts, total_deposits
    FROM meridian.branch_totals
    WHERE total_deposits > 10000000;

This works, but use it sparingly. Deeply nested views -- views built on views built on views -- become difficult to understand and can occasionally confuse the optimizer into producing suboptimal plans. Two levels of nesting is common and safe. Five levels is a sign that something has gone wrong in your design.

Platform Differences for Views

The core CREATE VIEW syntax is the same on z/OS and LUW. A few differences worth noting:

On both platforms, dropping or altering a table that a view depends on will invalidate the view. On LUW, the view becomes INOPERATIVE and must be explicitly recreated. On z/OS, dependent packages are invalidated and automatically rebound at next execution.

12.2 Updateable Views and WITH CHECK OPTION

Not all views are read-only. Some views allow INSERT, UPDATE, and DELETE operations, which DB2 translates into operations on the underlying base table. Understanding which views are updateable -- and how to control updates through views -- is essential for using views as a security layer.

Rules for Updateable Views

A view is updateable if DB2 can unambiguously map each row in the view to exactly one row in a base table. The precise rules are:

1. The FROM clause references exactly one base table (or one updateable view).
2. The SELECT list does not contain: aggregate functions (SUM, COUNT, etc.), DISTINCT, GROUP BY, HAVING, UNION/EXCEPT/INTERSECT, or scalar fullselects.
3. Every column in the base table that does not have a DEFAULT value or allow NULLs must be included in the view (for INSERT operations).
4. No derived columns -- each column in the view must correspond directly to a column in the base table.

If any of these rules is violated, the view is read-only. You can query it, but INSERT, UPDATE, and DELETE will fail.

-- This view IS updateable (single table, no aggregation, direct columns)
CREATE VIEW meridian.savings_accounts AS
    SELECT account_id, customer_id, balance, open_date, branch_id, status
    FROM meridian.account
    WHERE account_type_code = 'SAV';

-- This view is NOT updateable (contains a join)
CREATE VIEW meridian.account_with_customer AS
    SELECT a.account_id, a.balance, c.first_name, c.last_name
    FROM meridian.account a
    JOIN meridian.customer c ON a.customer_id = c.customer_id;

-- This view is NOT updateable (contains aggregation)
CREATE VIEW meridian.branch_totals AS
    SELECT branch_id, SUM(balance) AS total_balance
    FROM meridian.account
    GROUP BY branch_id;

WITH CHECK OPTION

When you update or insert through an updateable view, a subtle problem arises. Consider:

CREATE VIEW meridian.savings_accounts AS
    SELECT account_id, customer_id, balance, open_date, branch_id, status
    FROM meridian.account
    WHERE account_type_code = 'SAV';

What happens if someone executes this through the view?

UPDATE meridian.savings_accounts
SET account_type_code = 'CHK'
WHERE account_id = 1001;

Wait -- account_type_code is not even in the view's SELECT list. But the UPDATE reaches the base table, and DB2 will happily change the type code. The row then disappears from the view because it no longer satisfies account_type_code = 'SAV'. The row has "migrated" out of the view. This is almost certainly a bug.

WITH CHECK OPTION prevents this:

CREATE VIEW meridian.savings_accounts AS
    SELECT account_id, customer_id, balance, open_date, branch_id, status
    FROM meridian.account
    WHERE account_type_code = 'SAV'
WITH CHECK OPTION;

Now, any INSERT or UPDATE through this view must produce a row that still satisfies the view's WHERE clause. If an operation would cause a row to disappear from the view, DB2 rejects it with SQLSTATE 44000.

There are two flavors:

• WITH CASCADED CHECK OPTION (the default): The check applies to this view and all views it is based on.
• WITH LOCAL CHECK OPTION: The check applies only to this view's own WHERE clause, not to underlying views.

Always use WITH CHECK OPTION (cascaded) on updateable views that serve as security boundaries. Without it, the view provides the illusion of a constraint without actually enforcing it.

INSTEAD OF Triggers

What about views that are inherently read-only -- those with joins or aggregation -- but where you still want to allow modifications? This is the purpose of INSTEAD OF triggers.

An INSTEAD OF trigger intercepts an INSERT, UPDATE, or DELETE on a view and executes your custom logic instead of DB2's default (which would be to reject the operation). You write the trigger body to perform the actual base-table modifications.

-- Make the joined view "updateable" for balance changes
CREATE TRIGGER meridian.trg_update_account_view
INSTEAD OF UPDATE ON meridian.account_with_customer
REFERENCING NEW AS n OLD AS o
FOR EACH ROW
BEGIN ATOMIC
    UPDATE meridian.account
    SET balance = n.balance
    WHERE account_id = o.account_id;
END;

Now when an application issues UPDATE meridian.account_with_customer SET balance = 5000 WHERE account_id = 1001, DB2 does not try to update the joined view directly. Instead, it fires the INSTEAD OF trigger, which updates only the ACCOUNT base table.

INSTEAD OF triggers are available on both z/OS (DB2 10 and later) and LUW. They are the standard solution for making complex views writable, and they are heavily used in applications that present a simplified "API" to the database through views.

12.3 Materialized Query Tables (MQTs) -- Precomputed Views

Regular views are virtual -- they store no data. Every query against a view executes the underlying SELECT from scratch. For most queries, this is fine. But what about a complex aggregation that joins five tables and scans millions of rows? Running that every time someone queries the view is wasteful if the underlying data does not change frequently.

Materialized Query Tables (MQTs) solve this by physically storing the result of a query. They are sometimes called "materialized views" in other database systems.

Creating an MQT

-- z/OS syntax
CREATE TABLE meridian.branch_monthly_summary AS (
    SELECT b.branch_id,
           b.branch_name,
           YEAR(t.txn_date) AS txn_year,
           MONTH(t.txn_date) AS txn_month,
           COUNT(*) AS txn_count,
           SUM(CASE WHEN t.txn_type = 'CREDIT' THEN t.amount ELSE 0 END)
               AS total_credits,
           SUM(CASE WHEN t.txn_type = 'DEBIT' THEN t.amount ELSE 0 END)
               AS total_debits
    FROM meridian.branch b
    JOIN meridian.account a ON b.branch_id = a.branch_id
    JOIN meridian.transaction t ON a.account_id = t.account_id
    GROUP BY b.branch_id, b.branch_name,
             YEAR(t.txn_date), MONTH(t.txn_date)
)
DATA INITIALLY DEFERRED
REFRESH DEFERRED
MAINTAINED BY SYSTEM;

On LUW, the syntax is nearly identical, but you may also see:

-- LUW syntax variant
CREATE TABLE meridian.branch_monthly_summary AS (
    SELECT ...
)
DATA INITIALLY DEFERRED
REFRESH DEFERRED;

Key clauses:

• DATA INITIALLY DEFERRED: The MQT is empty when created. You must explicitly refresh it.
• REFRESH DEFERRED: The MQT is refreshed only when you explicitly run a REFRESH TABLE statement. The data may be stale between refreshes.
• REFRESH IMMEDIATE: The MQT is updated automatically whenever the base tables change. This keeps the MQT current but adds overhead to every INSERT, UPDATE, and DELETE on the base tables.
• MAINTAINED BY SYSTEM: DB2 manages the refresh (as opposed to MAINTAINED BY USER, where you populate the table yourself).

Refreshing an MQT

REFRESH TABLE meridian.branch_monthly_summary;

On z/OS, you can also use the LOAD utility or the REFRESH option of REORG to refresh MQTs during batch windows.

Query Rewrite by the Optimizer

The real power of MQTs comes from automatic query routing. When the optimizer sees a query that could be answered by an MQT, it rewrites the query to use the MQT instead of scanning the base tables. This is called query rewrite or automatic query routing.

For this to work:

1. The MQT must be refreshed (not empty).
2. On LUW, the CURRENT REFRESH AGE special register must be set to allow stale data (e.g., SET CURRENT REFRESH AGE = ANY).
3. On z/OS, the CURRENT REFRESH AGE must be set and the QUERY OPTIMIZATION setting must permit MQT routing.
4. The optimizer must determine that the MQT can satisfy the query.

-- This query scans millions of transaction rows...
SELECT branch_name, SUM(amount)
FROM meridian.branch b
JOIN meridian.account a ON b.branch_id = a.branch_id
JOIN meridian.transaction t ON a.account_id = t.account_id
WHERE YEAR(t.txn_date) = 2025
GROUP BY branch_name;

-- ...but the optimizer may rewrite it to use the MQT:
-- SELECT branch_name, SUM(total_credits) + SUM(total_debits)
-- FROM meridian.branch_monthly_summary
-- WHERE txn_year = 2025
-- GROUP BY branch_name;

The query runs in milliseconds instead of seconds. The application did not need to change. The optimizer made the decision.

When to Use MQTs

Use MQTs when:

• A complex aggregation query runs frequently against large tables.
• The underlying data changes infrequently relative to query frequency.
• You can tolerate slightly stale data (REFRESH DEFERRED) or you are willing to pay the overhead of REFRESH IMMEDIATE.

Do not use MQTs when:

• The underlying data changes constantly and freshness is critical.
• The query is simple enough that the base table scan is already fast.
• Storage is severely constrained (MQTs consume physical space).

MQTs are a performance tool, not a logical modeling tool. They duplicate data for speed. Treat them like indexes -- useful when needed, wasteful when overused.

12.4 Triggers -- Automatic Actions on Data Events

A trigger is a piece of procedural logic that DB2 executes automatically when a specific data modification event occurs on a table. Triggers are invisible to the application that caused the event -- the application inserts a row, and the trigger fires silently, performing whatever additional logic you have defined.

This is both their greatest strength and their greatest danger. Strength, because you can enforce business rules and record audit trails without relying on every application to implement them. Danger, because hidden logic makes systems harder to understand, debug, and maintain.

CREATE TRIGGER Syntax

CREATE TRIGGER schema.trigger_name
    { BEFORE | AFTER | INSTEAD OF }
    { INSERT | UPDATE | DELETE }
    ON schema.table_or_view_name
    REFERENCING { OLD AS o | NEW AS n | OLD AS o NEW AS n }
    FOR EACH { ROW | STATEMENT }
    [ WHEN (condition) ]
    BEGIN ATOMIC
        -- trigger body: SQL statements
    END;

Let us break down each component.

BEFORE vs. AFTER

BEFORE triggers fire before the row is modified. They can inspect and change the NEW values before they reach the table. They cannot modify other tables (on most platforms). Use BEFORE triggers for:

• Validation: Reject invalid data by signaling an error.
• Derivation: Compute default values or transform data before insertion.
• Normalization: Force values to uppercase, trim whitespace, etc.

-- BEFORE trigger: Ensure email is lowercase
CREATE TRIGGER meridian.trg_customer_email_lower
BEFORE INSERT ON meridian.customer
REFERENCING NEW AS n
FOR EACH ROW
BEGIN ATOMIC
    SET n.email = LOWER(n.email);
END;

AFTER triggers fire after the row has been successfully modified (but before the transaction commits). They can read the OLD and NEW values and can modify other tables. Use AFTER triggers for:

• Audit logging: Record what changed, when, and by whom.
• Cascading updates: Update related tables based on the change.
• Notification: Insert records into a queue table for downstream processing.

-- AFTER trigger: Log every balance change
CREATE TRIGGER meridian.trg_audit_balance_change
AFTER UPDATE OF balance ON meridian.account
REFERENCING OLD AS o NEW AS n
FOR EACH ROW
WHEN (o.balance <> n.balance)
BEGIN ATOMIC
    INSERT INTO meridian.account_audit
        (account_id, old_balance, new_balance, change_amount,
         changed_by, changed_at)
    VALUES
        (o.account_id, o.balance, n.balance, n.balance - o.balance,
         CURRENT USER, CURRENT TIMESTAMP);
END;

FOR EACH ROW vs. FOR EACH STATEMENT

FOR EACH ROW: The trigger fires once for every row affected by the statement. If an UPDATE modifies 500 rows, a FOR EACH ROW trigger fires 500 times. This is the most common granularity and is required when you need to reference OLD and NEW transition variables.

FOR EACH STATEMENT: The trigger fires once per statement, regardless of how many rows are affected. This is useful for operations that should happen once (e.g., logging that a batch update occurred) but do not need per-row detail. On z/OS, statement-level triggers use transition tables (OLD TABLE and NEW TABLE) instead of transition variables.

-- Statement-level trigger with transition tables (z/OS and LUW)
CREATE TRIGGER meridian.trg_mass_update_log
AFTER UPDATE ON meridian.account
REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows
FOR EACH STATEMENT
BEGIN ATOMIC
    INSERT INTO meridian.batch_audit_log
        (event_type, rows_affected, event_timestamp)
    VALUES
        ('ACCOUNT_UPDATE',
         (SELECT COUNT(*) FROM new_rows),
         CURRENT TIMESTAMP);
END;

Transition Variables: OLD and NEW

• On INSERT: Only NEW is available (there is no old row).
• On DELETE: Only OLD is available (there is no new row).
• On UPDATE: Both OLD and NEW are available.

You reference them using the alias you define in the REFERENCING clause: n.balance for the new value, o.balance for the old value.

Platform Differences for Triggers

On z/OS, triggers defined with native SQL procedures get compiled into native code and execute efficiently. On LUW, triggers are compiled into sections in the system package cache.

12.5 Trigger Use Cases

Triggers are a tool, and like any tool, they shine in some situations and create problems in others. Here are the use cases where triggers genuinely earn their keep, illustrated with Meridian Bank examples.

Use Case 1: Audit Logging

This is the most universally accepted use of triggers. Regulatory requirements (SOX, GDPR, PCI-DSS, banking regulations) demand that you record who changed what data, when, and what the old and new values were. Triggers are the ideal mechanism because they fire regardless of how the data is modified -- whether by an application, a DBA running ad-hoc SQL, or a batch job.

-- Comprehensive audit trigger for the CUSTOMER table
CREATE TRIGGER meridian.trg_customer_audit
AFTER UPDATE ON meridian.customer
REFERENCING OLD AS o NEW AS n
FOR EACH ROW
BEGIN ATOMIC
    INSERT INTO meridian.customer_audit
        (audit_id, customer_id, field_name, old_value, new_value,
         changed_by, changed_at, change_source)
    VALUES
        (DEFAULT, o.customer_id, 'COMPOSITE',
         o.first_name || '|' || o.last_name || '|' || o.email || '|' || o.phone,
         n.first_name || '|' || n.last_name || '|' || n.email || '|' || n.phone,
         CURRENT USER, CURRENT TIMESTAMP, CURRENT CLIENT_APPLNAME);
END;

A more granular approach logs each changed column individually. This uses multiple conditional inserts:

CREATE TRIGGER meridian.trg_customer_audit_granular
AFTER UPDATE ON meridian.customer
REFERENCING OLD AS o NEW AS n
FOR EACH ROW
BEGIN ATOMIC
    IF o.email <> n.email OR (o.email IS NULL AND n.email IS NOT NULL)
       OR (o.email IS NOT NULL AND n.email IS NULL) THEN
        INSERT INTO meridian.customer_audit
            (customer_id, field_name, old_value, new_value,
             changed_by, changed_at)
        VALUES (o.customer_id, 'EMAIL', o.email, n.email,
                CURRENT USER, CURRENT TIMESTAMP);
    END IF;

    IF o.phone <> n.phone OR (o.phone IS NULL AND n.phone IS NOT NULL)
       OR (o.phone IS NOT NULL AND n.phone IS NULL) THEN
        INSERT INTO meridian.customer_audit
            (customer_id, field_name, old_value, new_value,
             changed_by, changed_at)
        VALUES (o.customer_id, 'PHONE', o.phone, n.phone,
                CURRENT USER, CURRENT TIMESTAMP);
    END IF;
END;

Use Case 2: Derived Column Maintenance

Some columns are derived from other columns and must always stay in sync. Rather than trusting every application to compute them correctly, a BEFORE trigger can do it automatically:

-- Maintain a full_name column derived from first + last
CREATE TRIGGER meridian.trg_customer_fullname
BEFORE INSERT ON meridian.customer
REFERENCING NEW AS n
FOR EACH ROW
BEGIN ATOMIC
    SET n.full_name = TRIM(n.first_name) || ' ' || TRIM(n.last_name);
END;

-- Same trigger for UPDATE
CREATE TRIGGER meridian.trg_customer_fullname_upd
BEFORE UPDATE OF first_name, last_name ON meridian.customer
REFERENCING NEW AS n
FOR EACH ROW
BEGIN ATOMIC
    SET n.full_name = TRIM(n.first_name) || ' ' || TRIM(n.last_name);
END;

Use Case 3: Cross-Table Validation

Sometimes a business rule involves data from multiple tables. A BEFORE trigger can check the rule and reject the operation if violated:

-- Prevent overdraft: reject withdrawal if balance would go negative
CREATE TRIGGER meridian.trg_prevent_overdraft
BEFORE INSERT ON meridian.transaction
REFERENCING NEW AS n
FOR EACH ROW
WHEN (n.txn_type = 'DEBIT')
BEGIN ATOMIC
    DECLARE current_bal DECIMAL(15,2);
    SET current_bal = (SELECT balance FROM meridian.account
                       WHERE account_id = n.account_id);
    IF current_bal < n.amount THEN
        SIGNAL SQLSTATE '75001'
            SET MESSAGE_TEXT = 'Insufficient funds: withdrawal exceeds balance';
    END IF;
END;

The SIGNAL statement raises a custom error that the application receives as a SQL exception. The INSERT is rejected, and the transaction can be rolled back.

Use Case 4: Cascading Business Rules

After a transaction is recorded, multiple downstream actions may be needed:

-- After a transaction, update the account balance
CREATE TRIGGER meridian.trg_update_balance
AFTER INSERT ON meridian.transaction
REFERENCING NEW AS n
FOR EACH ROW
BEGIN ATOMIC
    UPDATE meridian.account
    SET balance = CASE
            WHEN n.txn_type = 'CREDIT' THEN balance + n.amount
            WHEN n.txn_type = 'DEBIT'  THEN balance - n.amount
            ELSE balance
        END,
        last_activity_date = CURRENT DATE
    WHERE account_id = n.account_id;
END;

This is powerful but dangerous. The UPDATE on ACCOUNT may fire its own triggers (e.g., the balance audit trigger from Section 12.4). You now have a trigger chain: the insert trigger on TRANSACTION fires an update on ACCOUNT, which fires the audit trigger on ACCOUNT. This is manageable when you plan for it, but it can spiral into confusion if triggers are added over time without coordination.

12.6 Trigger Pitfalls and Performance

Triggers are one of the most misused features in relational databases. Every experienced DBA has a horror story about a system brought to its knees by trigger chains, or a bug that took days to find because logic was hidden in a trigger. This section is about learning from those stories.

Pitfall 1: Trigger Chains

A trigger on Table A modifies Table B. A trigger on Table B modifies Table C. A trigger on Table C modifies Table A. Congratulations, you have a recursive trigger chain. DB2 will detect the recursion and terminate it after a platform-specific maximum depth (16 levels on z/OS, 16 on LUW by default), raising SQLSTATE 54038.

Even non-recursive chains are problematic. If modifying one row in TRANSACTION causes updates to ACCOUNT, which causes inserts into ACCOUNT_AUDIT, which causes updates to AUDIT_SUMMARY, you have four tables involved in what the application thought was a single INSERT. The transaction holds locks on all four tables for the duration. The application experiences latency it did not expect. The DBA sees lock contention they cannot explain.

Guideline: Keep trigger chains to a maximum depth of two. If you need more complex cascading logic, use a stored procedure instead, where the flow is visible and controllable.

Pitfall 2: Performance Overhead

Every trigger adds overhead to the DML statement that fires it. This overhead includes:

• Plan compilation: The trigger body must be compiled and cached.
• Execution time: The trigger body runs inside the transaction.
• Lock acquisition: If the trigger modifies other tables, additional locks are acquired.
• Log records: Every modification the trigger makes generates log records.

For OLTP systems processing thousands of transactions per second, trigger overhead is measurable. A BEFORE trigger that sets a derived column adds microseconds. An AFTER trigger that inserts into an audit table adds milliseconds. A trigger chain that touches four tables can add tens of milliseconds -- an eternity in a system processing 5,000 TPS.

Guideline: Benchmark trigger overhead under realistic load. On z/OS, use DB2 accounting trace class 3 to see trigger execution time. On LUW, use db2pd -tcbstats or event monitors. If trigger overhead exceeds your latency budget, consider alternatives: change data capture (CDC), asynchronous audit via queues, or application-level logic.

Pitfall 3: Hidden Logic and Debugging

When a developer writes INSERT INTO transaction VALUES (...) and the system raises an error about insufficient funds, the developer's first instinct is to look at the INSERT statement and the TRANSACTION table constraints. The BEFORE trigger that performs the balance check is invisible to the developer unless they know to look for it.

This is a training and documentation problem, not a technical one. But it is real. Triggers hide logic in the database that does not appear in application code, application logs, or application debuggers.

Guideline: Document all triggers in a central registry. Include the trigger name, the table it applies to, the event (INSERT/UPDATE/DELETE), whether it is BEFORE or AFTER, and a one-sentence description of what it does. Keep this registry in version control alongside your DDL scripts.

Pitfall 4: Trigger Ordering

When multiple triggers are defined for the same event on the same table, the order in which they fire matters. DB2 supports the ORDER clause to control trigger ordering:

CREATE TRIGGER meridian.trg_validate_amount
BEFORE INSERT ON meridian.transaction
REFERENCING NEW AS n
FOR EACH ROW
ORDER 1
BEGIN ATOMIC
    IF n.amount <= 0 THEN
        SIGNAL SQLSTATE '75002'
            SET MESSAGE_TEXT = 'Transaction amount must be positive';
    END IF;
END;

CREATE TRIGGER meridian.trg_set_defaults
BEFORE INSERT ON meridian.transaction
REFERENCING NEW AS n
FOR EACH ROW
ORDER 2
BEGIN ATOMIC
    SET n.txn_timestamp = CURRENT TIMESTAMP;
    SET n.processed_flag = 'N';
END;

Without explicit ORDER values, trigger execution order is determined by creation time, which is fragile. Always use the ORDER clause when multiple triggers exist on the same table for the same event.

Pitfall 5: Trigger Impact on Bulk Operations

A FOR EACH ROW trigger fires once per row. A batch INSERT of 100,000 rows fires the trigger 100,000 times. If the trigger performs a query (e.g., checking a balance), that query executes 100,000 times.

For bulk operations, consider:

1. Disabling triggers during bulk loads (ALTER TRIGGER ... DISABLE on LUW; you may need to drop and recreate on z/OS).
2. Using SET INTEGRITY on LUW to temporarily suspend trigger checking.
3. Using LOAD utility on z/OS, which bypasses triggers by design (this is intentional -- LOAD is a utility, not a SQL statement).
4. Performing the equivalent logic in a batch stored procedure before or after the bulk load.

12.7 Introduction to Stored Procedures

A stored procedure is a named program stored in the database that encapsulates one or more SQL statements along with procedural logic. Unlike views (which are single SELECT statements) and triggers (which fire automatically), stored procedures are explicitly called by applications.

Why Stored Procedures Exist

The fundamental argument for stored procedures is reducing round trips. Consider a fund transfer at Meridian Bank:

1. Verify the source account exists and is active.
2. Verify the destination account exists and is active.
3. Check that the source account has sufficient funds.
4. Debit the source account.
5. Credit the destination account.
6. Record the debit transaction.
7. Record the credit transaction.
8. Return the new balances.

If the application executes each step as a separate SQL statement, that is eight round trips between the application server and the database server. Each round trip incurs network latency (typically 0.5-2ms on a LAN, 10-50ms across a WAN). Eight round trips add 4-400ms of pure network overhead.

A stored procedure executes all eight steps inside the database engine, requiring only one round trip: the CALL statement. The procedural logic runs at the speed of local memory access, not network I/O.

Beyond performance, stored procedures provide:

• Security: You can GRANT EXECUTE on a procedure without granting direct access to the underlying tables. The application cannot run arbitrary SQL; it can only call the procedures you define.
• Consistency: Business logic is implemented once, in the procedure. Every application that transfers funds calls the same procedure, guaranteeing the same validation, the same audit trail, the same error handling.
• Encapsulation: If the table structure changes, you modify the procedure. Applications that call the procedure do not need to change.

CREATE PROCEDURE Syntax

CREATE PROCEDURE schema.procedure_name (
    IN   param1    datatype,
    OUT  param2    datatype,
    INOUT param3   datatype
)
LANGUAGE SQL
BEGIN
    -- procedure body: SQL PL statements
END;

Parameter modes:

• IN: Input only. The caller passes a value; the procedure reads it.
• OUT: Output only. The procedure sets a value; the caller reads it after the call.
• INOUT: Both. The caller passes an initial value, and the procedure can modify it.

A First Procedure

CREATE PROCEDURE meridian.get_account_balance (
    IN  p_account_id  INTEGER,
    OUT p_balance      DECIMAL(15,2),
    OUT p_status       VARCHAR(20)
)
LANGUAGE SQL
BEGIN
    SELECT balance, status
    INTO p_balance, p_status
    FROM meridian.account
    WHERE account_id = p_account_id;
END;

Calling it:

-- From CLP or an application
CALL meridian.get_account_balance(1001, ?, ?);

The ? placeholders receive the OUT parameter values.

Returning Result Sets

Stored procedures can return result sets -- complete tables of data that the calling application iterates through, just like a SELECT result:

CREATE PROCEDURE meridian.get_branch_accounts (
    IN p_branch_id INTEGER
)
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE c1 CURSOR WITH RETURN FOR
        SELECT account_id, customer_id, balance, status
        FROM meridian.account
        WHERE branch_id = p_branch_id
        ORDER BY balance DESC;

    OPEN c1;
    -- Do NOT close the cursor; leaving it open returns the result set
END;

The DYNAMIC RESULT SETS 1 clause tells DB2 that this procedure will return one result set. A procedure can return multiple result sets by declaring and opening multiple cursors.

12.8 SQL PL Essentials

SQL PL (SQL Procedural Language) is DB2's procedural extension to SQL. It provides the control flow, variables, and error handling needed to write non-trivial stored procedures, functions, and trigger bodies. SQL PL is based on the SQL/PSM standard (SQL Persistent Stored Modules) and is very similar across z/OS and LUW.

Variable Declaration

Variables are declared at the beginning of a compound statement (BEGIN...END block):

BEGIN
    DECLARE v_balance      DECIMAL(15,2) DEFAULT 0;
    DECLARE v_customer_name VARCHAR(100);
    DECLARE v_count         INTEGER;
    DECLARE v_today         DATE DEFAULT CURRENT DATE;
    DECLARE v_found         BOOLEAN DEFAULT FALSE;  -- LUW only; z/OS uses SMALLINT

    -- Statements that use these variables
END;

Variables declared with DEFAULT are initialized to that value. Variables without DEFAULT are initialized to NULL.

Assignment

SET v_balance = 1000.00;
SET v_customer_name = 'Jane Doe';

-- Assignment from a query
SELECT balance INTO v_balance
FROM meridian.account
WHERE account_id = p_account_id;

-- Assignment with expression
SET v_balance = v_balance * 1.05;

Conditional Logic: IF / CASE

-- IF statement
IF v_balance < 0 THEN
    SET v_status = 'OVERDRAWN';
ELSEIF v_balance = 0 THEN
    SET v_status = 'ZERO';
ELSE
    SET v_status = 'POSITIVE';
END IF;

-- CASE statement (searched form)
SET v_fee = CASE
    WHEN v_account_type = 'PREMIUM' THEN 0.00
    WHEN v_account_type = 'STANDARD' THEN 5.00
    WHEN v_account_type = 'BASIC' THEN 10.00
    ELSE 15.00
END;

Loops

-- WHILE loop
DECLARE v_counter INTEGER DEFAULT 1;
WHILE v_counter <= 12 DO
    -- process month v_counter
    INSERT INTO meridian.monthly_report (report_month, report_year)
    VALUES (v_counter, YEAR(CURRENT DATE));
    SET v_counter = v_counter + 1;
END WHILE;

-- FOR loop (iterates over a query result)
FOR v_row AS
    SELECT account_id, balance
    FROM meridian.account
    WHERE branch_id = p_branch_id
DO
    -- v_row.account_id and v_row.balance are available here
    IF v_row.balance < 100 THEN
        UPDATE meridian.account
        SET status = 'LOW_BALANCE'
        WHERE account_id = v_row.account_id;
    END IF;
END FOR;

The FOR loop is particularly useful because it implicitly declares a cursor, fetches each row, and closes the cursor. It is cleaner than manual cursor management for simple iterations.

Cursors

For more complex cursor operations -- repositionable cursors, update/delete through cursor, or multiple cursors open simultaneously -- you use explicit cursor management:

DECLARE c_accounts CURSOR FOR
    SELECT account_id, balance, status
    FROM meridian.account
    WHERE branch_id = p_branch_id;

DECLARE v_acct_id   INTEGER;
DECLARE v_bal       DECIMAL(15,2);
DECLARE v_stat      VARCHAR(20);
DECLARE v_done      INTEGER DEFAULT 0;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

OPEN c_accounts;

fetch_loop: LOOP
    FETCH c_accounts INTO v_acct_id, v_bal, v_stat;
    IF v_done = 1 THEN
        LEAVE fetch_loop;
    END IF;
    -- Process the row
END LOOP fetch_loop;

CLOSE c_accounts;

Error Handling with DECLARE HANDLER

SQL PL provides structured error handling through condition handlers:

-- Continue handler: executes the handler body and continues
DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET v_done = 1;

-- Exit handler: executes the handler body and exits the compound statement
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    SET p_out_status = 'ERROR';
    SET p_out_message = 'An unexpected error occurred';
END;

-- Handler for a specific SQLSTATE
DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
    SET v_duplicate = 1;

Handler types:

• CONTINUE: After the handler body executes, control returns to the statement after the one that caused the condition.
• EXIT: After the handler body executes, control exits the current BEGIN...END block.
• UNDO: (LUW only) Like EXIT, but also rolls back any changes made in the current compound statement.

Conditions you can handle:

• NOT FOUND: SQLSTATE '02000' -- a SELECT INTO or FETCH found no row.
• SQLEXCEPTION: Any SQLSTATE starting with something other than '00' (success), '01' (warning), or '02' (not found).
• SQLWARNING: Any SQLSTATE starting with '01'.
• Specific SQLSTATE: E.g., '23505' for unique constraint violation.

Putting It Together: A Complete Procedure

CREATE PROCEDURE meridian.close_account (
    IN  p_account_id   INTEGER,
    OUT p_final_balance DECIMAL(15,2),
    OUT p_status_code   INTEGER,
    OUT p_message       VARCHAR(200)
)
LANGUAGE SQL
BEGIN
    DECLARE v_current_balance DECIMAL(15,2);
    DECLARE v_current_status  VARCHAR(20);

    -- Error handler
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET p_status_code = -1;
        SET p_message = 'Unexpected database error during account closure';
    END;

    -- Retrieve current account state
    SELECT balance, status INTO v_current_balance, v_current_status
    FROM meridian.account
    WHERE account_id = p_account_id;

    -- Validate: account must exist (handled by NOT FOUND if missing)
    -- Validate: account must be active
    IF v_current_status <> 'ACTIVE' THEN
        SET p_status_code = -2;
        SET p_message = 'Account is not active; cannot close';
        SET p_final_balance = v_current_balance;
        RETURN;
    END IF;

    -- Validate: balance must be zero
    IF v_current_balance <> 0 THEN
        SET p_status_code = -3;
        SET p_message = 'Account has non-zero balance; withdraw or transfer first';
        SET p_final_balance = v_current_balance;
        RETURN;
    END IF;

    -- Close the account
    UPDATE meridian.account
    SET status = 'CLOSED',
        close_date = CURRENT DATE
    WHERE account_id = p_account_id;

    -- Return success
    SET p_final_balance = 0;
    SET p_status_code = 0;
    SET p_message = 'Account closed successfully';
END;

This procedure validates preconditions, performs the operation, and returns a structured result. The calling application checks p_status_code to determine what happened. No raw SQL errors leak to the user interface.

12.9 Stored Procedures vs. Application Logic -- The Eternal Debate

This is one of the most contentious topics in software architecture. Experienced professionals hold strong opinions on both sides. Rather than declare a winner, let us lay out the trade-offs honestly so you can make informed decisions for your specific context.

Arguments for Database Logic (Stored Procedures + Triggers)

1. Reduced Network Latency

A stored procedure executes multiple SQL statements in a single round trip. For operations involving 5-20 SQL statements, the latency savings can be significant, especially over WAN connections. In Meridian Bank's fund transfer, executing eight SQL statements as a stored procedure eliminates seven round trips.

2. Centralized Business Rules

When business logic lives in the database, every application that accesses the data -- the web app, the mobile app, the batch system, the data warehouse ETL, the DBA's ad-hoc queries -- goes through the same rules. You write the logic once. This is particularly valuable in regulated industries where consistency is legally required.

3. Security Boundary

Granting EXECUTE on a procedure without granting SELECT/INSERT/UPDATE/DELETE on the underlying tables creates a strict API. Applications can only do what the procedures allow. This is a genuine security advantage.

4. Performance for Set-Based Operations

SQL PL executes inside the database engine, operating on data without network transfer. For operations that process many rows with conditional logic, this can be orders of magnitude faster than fetching rows to the application and processing them there.

Arguments for Application Logic

1. Testability

Application code can be unit-tested with mocking frameworks, run in CI/CD pipelines, and covered by code coverage tools. Stored procedures are harder to test. You need a live database, test data, and specialized testing tools. Most development teams find that application-level unit tests are cheaper and faster.

2. Version Control and Deployment

Application code lives in Git, goes through pull requests, and deploys through CI/CD pipelines. Stored procedures live in the database, require DDL scripts to deploy, and their change history depends on the discipline of your DDL management. Many organizations struggle to maintain stored procedure code in version control.

3. Debugging

Application developers have sophisticated debuggers -- breakpoints, step-through, variable inspection, stack traces. SQL PL debugging tools exist but are less mature. When a stored procedure has a bug, finding it often involves adding diagnostic INSERT statements or reading trace output.

4. Scalability Model

Application servers scale horizontally -- you add more instances. Database servers scale vertically (or through data sharing/partitioning). Business logic in the database means the database does more work per transaction, which can limit throughput. Moving logic to the application layer offloads the database.

5. Developer Familiarity

Most application developers are more fluent in Java, Python, C#, or JavaScript than in SQL PL. Putting logic in the language that the development team knows best leads to better code quality, faster development, and easier maintenance.

6. Portability

SQL PL is DB2-specific. If you ever migrate to PostgreSQL, Oracle, or SQL Server, every stored procedure must be rewritten. Application logic in a standard programming language is portable across databases (assuming you use an ORM or a database abstraction layer).

The Practical Framework

Here is a decision framework used by practitioners:

The Meridian Bank Approach

For this book's project, we use a hybrid approach that reflects real-world practice:

• Triggers for audit logging and data integrity rules that must be absolute.
• Stored procedures for multi-step banking operations (transfers, interest calculation) where consistency and security are paramount.
• Views for security and abstraction (branch-specific data, customer-safe views).
• Application logic for workflow orchestration, user interface logic, and features that change frequently.

This is not a compromise. It is an architecture that puts each type of logic where it works best.

12.10 User-Defined Functions Preview

DB2 allows you to create your own functions that can be used in SQL statements just like built-in functions (UPPER, SUBSTR, COALESCE, etc.). These are User-Defined Functions (UDFs). Chapter 34 covers UDFs in depth. This section provides a brief introduction so you can see how they fit alongside views, triggers, and stored procedures.

Scalar UDFs

A scalar UDF takes one or more input values and returns a single value. It can be used anywhere a scalar expression is valid -- in SELECT lists, WHERE clauses, ORDER BY, etc.

CREATE FUNCTION meridian.mask_ssn (p_ssn VARCHAR(11))
RETURNS VARCHAR(11)
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN 'XXX-XX-' || RIGHT(p_ssn, 4);

Usage:

SELECT customer_id, first_name, last_name,
       meridian.mask_ssn(ssn) AS masked_ssn
FROM meridian.customer;

The function is DETERMINISTIC (same input always produces same output) and has NO EXTERNAL ACTION (no side effects). These declarations help the optimizer make better decisions.

Table UDFs

A table UDF returns a table -- a set of rows and columns. It is used in the FROM clause like a table or view:

CREATE FUNCTION meridian.get_recent_transactions (
    p_account_id INTEGER,
    p_days       INTEGER
)
RETURNS TABLE (
    txn_id        INTEGER,
    txn_date      DATE,
    txn_type      VARCHAR(10),
    amount        DECIMAL(15,2),
    description   VARCHAR(200)
)
LANGUAGE SQL
READS SQL DATA
RETURN
    SELECT txn_id, txn_date, txn_type, amount, description
    FROM meridian.transaction
    WHERE account_id = p_account_id
      AND txn_date >= CURRENT DATE - p_days DAYS;

Usage:

SELECT t.*
FROM TABLE(meridian.get_recent_transactions(1001, 30)) AS t
ORDER BY t.txn_date DESC;

Functions vs. Procedures

The key difference: functions return values and can be used inside SQL statements. Procedures are called with CALL and cannot be embedded in a SELECT. Use functions when you need a reusable computation within SQL. Use procedures when you need to perform actions (modifications, multi-step operations, returning multiple result sets).

12.11 Meridian Bank Business Logic

Let us bring everything together by implementing a set of views, triggers, and stored procedures for Meridian National Bank. These examples represent the kind of database logic you would find in a real banking application.

Branch Manager Views

Each branch manager should see only their branch's data. We create parameterized views using session variables.

First, the view for a specific branch (in practice, the application sets the branch context):

-- View: Account summary for a specific branch
-- The application sets the branch_id as a session variable
CREATE VIEW meridian.v_branch_accounts AS
    SELECT a.account_id,
           c.first_name || ' ' || c.last_name AS customer_name,
           at.type_description AS account_type,
           a.balance,
           a.open_date,
           a.status,
           a.last_activity_date
    FROM meridian.account a
    JOIN meridian.customer c ON a.customer_id = c.customer_id
    JOIN meridian.account_type at ON a.account_type_code = at.type_code
    WHERE a.branch_id = (
        SELECT branch_id FROM meridian.branch
        WHERE branch_manager_id = CURRENT USER
    );

A complementary view hides sensitive customer details:

-- View: Customer directory with masked sensitive data
CREATE VIEW meridian.v_customer_safe AS
    SELECT customer_id,
           first_name,
           last_name,
           'XXX-XX-' || RIGHT(ssn, 4) AS masked_ssn,
           email,
           phone,
           city,
           state,
           date_of_birth
    FROM meridian.customer;

And a summary view for branch performance dashboards:

-- View: Branch performance dashboard
CREATE VIEW meridian.v_branch_dashboard AS
    SELECT b.branch_id,
           b.branch_name,
           b.city,
           COUNT(DISTINCT a.customer_id) AS unique_customers,
           COUNT(a.account_id) AS total_accounts,
           SUM(CASE WHEN a.status = 'ACTIVE' THEN 1 ELSE 0 END) AS active_accounts,
           SUM(a.balance) AS total_deposits,
           AVG(a.balance) AS avg_balance,
           MAX(a.open_date) AS newest_account,
           COUNT(CASE WHEN a.open_date >= CURRENT DATE - 30 DAYS THEN 1 END)
               AS new_accounts_30d
    FROM meridian.branch b
    LEFT JOIN meridian.account a ON b.branch_id = a.branch_id
    GROUP BY b.branch_id, b.branch_name, b.city;

Audit Trail Triggers

The regulatory environment requires that every modification to customer and account data is recorded:

-- Audit table for account changes
-- (Assume this table already exists from DDL in Chapter 11)
-- CREATE TABLE meridian.account_audit (
--     audit_id       INTEGER GENERATED ALWAYS AS IDENTITY,
--     account_id     INTEGER NOT NULL,
--     action_type    CHAR(1) NOT NULL,  -- I=Insert, U=Update, D=Delete
--     field_name     VARCHAR(50),
--     old_value      VARCHAR(200),
--     new_value      VARCHAR(200),
--     changed_by     VARCHAR(128),
--     changed_at     TIMESTAMP,
--     application_id VARCHAR(50)
-- );

-- Trigger: Audit INSERT on account
CREATE TRIGGER meridian.trg_account_audit_ins
AFTER INSERT ON meridian.account
REFERENCING NEW AS n
FOR EACH ROW
BEGIN ATOMIC
    INSERT INTO meridian.account_audit
        (account_id, action_type, field_name, old_value, new_value,
         changed_by, changed_at, application_id)
    VALUES
        (n.account_id, 'I', 'NEW_ACCOUNT', NULL,
         'Type=' || n.account_type_code || ' Balance=' || CHAR(n.balance),
         CURRENT USER, CURRENT TIMESTAMP, CURRENT CLIENT_APPLNAME);
END;

-- Trigger: Audit UPDATE on account (balance changes)
CREATE TRIGGER meridian.trg_account_audit_upd_bal
AFTER UPDATE OF balance ON meridian.account
REFERENCING OLD AS o NEW AS n
FOR EACH ROW
WHEN (o.balance <> n.balance)
BEGIN ATOMIC
    INSERT INTO meridian.account_audit
        (account_id, action_type, field_name, old_value, new_value,
         changed_by, changed_at, application_id)
    VALUES
        (o.account_id, 'U', 'BALANCE',
         CHAR(o.balance), CHAR(n.balance),
         CURRENT USER, CURRENT TIMESTAMP, CURRENT CLIENT_APPLNAME);
END;

-- Trigger: Audit UPDATE on account (status changes)
CREATE TRIGGER meridian.trg_account_audit_upd_status
AFTER UPDATE OF status ON meridian.account
REFERENCING OLD AS o NEW AS n
FOR EACH ROW
WHEN (o.status <> n.status)
BEGIN ATOMIC
    INSERT INTO meridian.account_audit
        (account_id, action_type, field_name, old_value, new_value,
         changed_by, changed_at, application_id)
    VALUES
        (o.account_id, 'U', 'STATUS',
         o.status, n.status,
         CURRENT USER, CURRENT TIMESTAMP, CURRENT CLIENT_APPLNAME);
END;

-- Trigger: Audit DELETE on account
CREATE TRIGGER meridian.trg_account_audit_del
AFTER DELETE ON meridian.account
REFERENCING OLD AS o
FOR EACH ROW
BEGIN ATOMIC
    INSERT INTO meridian.account_audit
        (account_id, action_type, field_name, old_value, new_value,
         changed_by, changed_at, application_id)
    VALUES
        (o.account_id, 'D', 'ACCOUNT_DELETED',
         'Type=' || o.account_type_code || ' Balance=' || CHAR(o.balance),
         NULL,
         CURRENT USER, CURRENT TIMESTAMP, CURRENT CLIENT_APPLNAME);
END;

Fund Transfer Stored Procedure

This is the most important stored procedure at any bank -- moving money between accounts:

CREATE PROCEDURE meridian.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_status_code    INTEGER,
    OUT p_message        VARCHAR(500),
    OUT p_from_new_bal   DECIMAL(15,2),
    OUT p_to_new_bal     DECIMAL(15,2)
)
LANGUAGE SQL
BEGIN
    DECLARE v_from_balance  DECIMAL(15,2);
    DECLARE v_to_balance    DECIMAL(15,2);
    DECLARE v_from_status   VARCHAR(20);
    DECLARE v_to_status     VARCHAR(20);
    DECLARE v_from_exists   INTEGER DEFAULT 0;
    DECLARE v_to_exists     INTEGER DEFAULT 0;

    -- Error handler for unexpected errors
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET p_status_code = -99;
        SET p_message = 'Unexpected database error during transfer';
        ROLLBACK;
    END;

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

    IF p_from_account = p_to_account THEN
        SET p_status_code = -2;
        SET p_message = 'Source and destination accounts must be different';
        RETURN;
    END IF;

    -- Retrieve source account (with row lock to prevent concurrent modification)
    SELECT balance, status INTO v_from_balance, v_from_status
    FROM meridian.account
    WHERE account_id = p_from_account;

    GET DIAGNOSTICS v_from_exists = ROW_COUNT;

    IF v_from_exists = 0 THEN
        SET p_status_code = -3;
        SET p_message = 'Source account does not exist';
        RETURN;
    END IF;

    IF v_from_status <> 'ACTIVE' THEN
        SET p_status_code = -4;
        SET p_message = 'Source account is not active (status: ' || v_from_status || ')';
        RETURN;
    END IF;

    IF v_from_balance < p_amount THEN
        SET p_status_code = -5;
        SET p_message = 'Insufficient funds. Available: ' || CHAR(v_from_balance)
                       || ', Requested: ' || CHAR(p_amount);
        RETURN;
    END IF;

    -- Retrieve destination account
    SELECT balance, status INTO v_to_balance, v_to_status
    FROM meridian.account
    WHERE account_id = p_to_account;

    GET DIAGNOSTICS v_to_exists = ROW_COUNT;

    IF v_to_exists = 0 THEN
        SET p_status_code = -6;
        SET p_message = 'Destination account does not exist';
        RETURN;
    END IF;

    IF v_to_status <> 'ACTIVE' THEN
        SET p_status_code = -7;
        SET p_message = 'Destination account is not active (status: ' || v_to_status || ')';
        RETURN;
    END IF;

    -- Perform the transfer
    UPDATE meridian.account
    SET balance = balance - p_amount,
        last_activity_date = CURRENT DATE
    WHERE account_id = p_from_account;

    UPDATE meridian.account
    SET balance = balance + p_amount,
        last_activity_date = CURRENT DATE
    WHERE account_id = p_to_account;

    -- Record transactions
    INSERT INTO meridian.transaction
        (account_id, txn_type, amount, txn_date, txn_timestamp,
         description, related_account_id)
    VALUES
        (p_from_account, 'DEBIT', p_amount, CURRENT DATE, CURRENT TIMESTAMP,
         'Transfer to account ' || CHAR(p_to_account) || ': ' || p_description,
         p_to_account);

    INSERT INTO meridian.transaction
        (account_id, txn_type, amount, txn_date, txn_timestamp,
         description, related_account_id)
    VALUES
        (p_to_account, 'CREDIT', p_amount, CURRENT DATE, CURRENT TIMESTAMP,
         'Transfer from account ' || CHAR(p_from_account) || ': ' || p_description,
         p_from_account);

    -- Retrieve new balances
    SELECT balance INTO p_from_new_bal
    FROM meridian.account WHERE account_id = p_from_account;

    SELECT balance INTO p_to_new_bal
    FROM meridian.account WHERE account_id = p_to_account;

    -- Success
    SET p_status_code = 0;
    SET p_message = 'Transfer of ' || CHAR(p_amount) || ' completed successfully';

    COMMIT;
END;

Interest Calculation Procedure

Monthly interest calculation is a batch operation that touches every eligible account:

CREATE PROCEDURE meridian.calculate_monthly_interest (
    IN  p_rate_annual    DECIMAL(7,4),  -- e.g., 0.0425 for 4.25%
    IN  p_account_type   CHAR(3),       -- e.g., 'SAV' for savings
    OUT p_accounts_processed INTEGER,
    OUT p_total_interest     DECIMAL(15,2),
    OUT p_status_code        INTEGER,
    OUT p_message            VARCHAR(500)
)
LANGUAGE SQL
BEGIN
    DECLARE v_monthly_rate  DECIMAL(15,10);
    DECLARE v_interest      DECIMAL(15,2);
    DECLARE v_acct_id       INTEGER;
    DECLARE v_balance       DECIMAL(15,2);
    DECLARE v_done          INTEGER DEFAULT 0;

    DECLARE c_eligible CURSOR FOR
        SELECT account_id, balance
        FROM meridian.account
        WHERE account_type_code = p_account_type
          AND status = 'ACTIVE'
          AND balance > 0;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET p_status_code = -1;
        SET p_message = 'Error during interest calculation. '
                       || 'Processed ' || CHAR(p_accounts_processed)
                       || ' accounts before failure.';
        ROLLBACK;
    END;

    SET v_monthly_rate = p_rate_annual / 12;
    SET p_accounts_processed = 0;
    SET p_total_interest = 0;

    OPEN c_eligible;

    interest_loop: LOOP
        FETCH c_eligible INTO v_acct_id, v_balance;
        IF v_done = 1 THEN
            LEAVE interest_loop;
        END IF;

        -- Calculate interest (simple interest for this example)
        SET v_interest = ROUND(v_balance * v_monthly_rate, 2);

        -- Credit interest to account
        UPDATE meridian.account
        SET balance = balance + v_interest,
            last_activity_date = CURRENT DATE
        WHERE account_id = v_acct_id;

        -- Record the interest transaction
        INSERT INTO meridian.transaction
            (account_id, txn_type, amount, txn_date, txn_timestamp,
             description)
        VALUES
            (v_acct_id, 'CREDIT', v_interest, CURRENT DATE,
             CURRENT TIMESTAMP,
             'Monthly interest at ' || CHAR(p_rate_annual * 100) || '% APR');

        SET p_accounts_processed = p_accounts_processed + 1;
        SET p_total_interest = p_total_interest + v_interest;
    END LOOP interest_loop;

    CLOSE c_eligible;

    SET p_status_code = 0;
    SET p_message = 'Interest calculation complete. '
                   || CHAR(p_accounts_processed) || ' accounts processed. '
                   || 'Total interest: $' || CHAR(p_total_interest);
    COMMIT;
END;

This procedure uses a cursor loop to process each eligible account individually. In production, you might optimize this with a set-based UPDATE (updating all accounts in a single statement) for better performance. The cursor approach shown here is pedagogically clear and gives you fine-grained control for per-account logic.

12.12 Part II Conclusion -- Your SQL Toolkit Is Complete

You have now completed Part II of this book. Let us take stock of what you have learned since Chapter 5.

Chapter 5: SQL Fundamentals -- You learned SELECT, WHERE, ORDER BY, and the basic shape of every query you will ever write.

Chapter 6: Joining Tables -- You learned INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN, and cross joins, enabling queries that span multiple tables.

Chapter 7: Aggregation and Grouping -- You learned GROUP BY, HAVING, and aggregate functions (COUNT, SUM, AVG, MIN, MAX), transforming detail rows into summary information.

Chapter 8: Subqueries and CTEs -- You learned scalar subqueries, correlated subqueries, EXISTS, and Common Table Expressions, giving you the ability to compose complex queries from simpler building blocks.

Chapter 9: Data Modification -- You learned INSERT, UPDATE, DELETE, and MERGE, transitioning from reading data to changing it.

Chapter 10: Advanced SQL -- You learned window functions, recursive CTEs, CASE expressions, and temporal queries, handling sophisticated analytical requirements.

Chapter 11: DDL -- You learned CREATE TABLE, ALTER TABLE, constraints, indexes, and tablespace management, taking control of database structure.

Chapter 12: Views, Triggers, and Stored Procedures -- You learned to encapsulate logic in the database itself, creating security boundaries, audit trails, and reusable business operations.

Together, these eight chapters give you a comprehensive SQL toolkit. You can query, aggregate, modify, structure, and automate data in DB2. You can make informed decisions about where logic should live. You can implement both the data layer and the business rule layer of a banking application.

What Comes Next

Part III shifts from SQL skills to database design and architecture. You will learn:

• Normalization and Denormalization -- structuring data for integrity and performance.
• Physical Database Design -- tablespaces, partitioning, compression, and storage optimization.
• Security -- authentication, authorization, roles, and row-level security.
• Concurrency and Locking -- isolation levels, lock escalation, and deadlock resolution.

These topics build on everything you have learned in Part II. The SQL you write will shape the design decisions you make. The design decisions you make will determine how well your SQL performs.

Spaced Review: Chapters 5, 7, and 10

The following review questions revisit concepts from earlier chapters to strengthen long-term retention.

From Chapter 5: SQL Fundamentals

Review 1: Write a SELECT statement that retrieves all customers whose last name starts with 'M' and who live in either 'New York' or 'Chicago'. Use both the LIKE operator and the IN operator.

SELECT customer_id, first_name, last_name, city
FROM meridian.customer
WHERE last_name LIKE 'M%'
  AND city IN ('New York', 'Chicago')
ORDER BY last_name, first_name;

Review 2: What is the difference between WHERE and HAVING? Can you use a column alias defined in the SELECT list in a WHERE clause?

From Chapter 7: Aggregation and Grouping

Review 3: Write a query that shows each branch's name and the number of accounts opened in the last 90 days, but only for branches that have opened more than 5 accounts in that period.

SELECT b.branch_name,
       COUNT(a.account_id) AS recent_accounts
FROM meridian.branch b
JOIN meridian.account a ON b.branch_id = a.branch_id
WHERE a.open_date >= CURRENT DATE - 90 DAYS
GROUP BY b.branch_name
HAVING COUNT(a.account_id) > 5
ORDER BY recent_accounts DESC;

Review 4: What does COUNT(*) count versus COUNT(column_name)? When does the difference matter?

From Chapter 10: Advanced SQL

Review 5: Write a query using a window function that shows each transaction along with the running balance for its account, ordered by transaction date.

SELECT t.account_id,
       t.txn_date,
       t.txn_type,
       t.amount,
       SUM(CASE WHEN t.txn_type = 'CREDIT' THEN t.amount
                WHEN t.txn_type = 'DEBIT' THEN -t.amount
                ELSE 0 END)
           OVER (PARTITION BY t.account_id
                 ORDER BY t.txn_date, t.txn_id
                 ROWS UNBOUNDED PRECEDING) AS running_balance
FROM meridian.transaction t
ORDER BY t.account_id, t.txn_date;

Review 6: What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()? Give a scenario where each would be the appropriate choice.

Return to Part II Overview | Continue to Exercises | Quiz | Case Study 1