Chapter 7 Exercises

Multi-Row Operations

Exercise 1: Basic Multi-Row FETCH Conversion

Convert the following single-row FETCH program to use multi-row FETCH with a rowset size of 200. Include indicator arrays for all columns, proper SQLERRD(3) checking, and end-of-data handling.

EXEC SQL
    DECLARE CSR-CUST CURSOR FOR
    SELECT CUST_ID, CUST_NAME, CUST_STATUS, CREDIT_LIMIT
    FROM CUSTOMER_MASTER
    WHERE REGION_CODE = :WS-REGION
    ORDER BY CUST_ID
END-EXEC.

EXEC SQL OPEN CSR-CUST END-EXEC.

PERFORM UNTIL SQLCODE = 100
    EXEC SQL
        FETCH CSR-CUST
        INTO :WS-CUST-ID, :WS-CUST-NAME,
             :WS-CUST-STATUS, :WS-CREDIT-LIMIT
    END-EXEC
    IF SQLCODE = 0
        PERFORM PROCESS-CUSTOMER
    END-IF
END-PERFORM.

Deliverable: Complete WORKING-STORAGE and PROCEDURE DIVISION code for the multi-row version.

Exercise 2: Multi-Row INSERT with Accumulate-and-Flush

Write a COBOL program fragment that accumulates transaction log records into a 300-row array and flushes them using multi-row INSERT. The log table has columns: LOG_ID (CHAR(20)), LOG_TSTAMP (TIMESTAMP), LOG_TYPE (CHAR(3)), LOG_MSG (VARCHAR(200)), PROGRAM_ID (CHAR(8)).

Requirements: - Use NOT ATOMIC CONTINUE ON SQLEXCEPTION (logging should never abort the batch) - Track total rows inserted vs. total rows attempted - Handle the final flush after the main loop ends - Include error reporting when SQLCODE is not 0 or 100

Exercise 3: Rowset Size Analysis

CNB processes a table with rows averaging 450 bytes wide, with 8 columns (3 nullable). The program runs in a batch region with 256 MB virtual storage, of which approximately 180 MB is available after load module, DB2 interface, and other allocations.

Calculate the WORKING-STORAGE requirements for rowset sizes of 100, 500, 1000, and 2000. For each, compute: - Data array size (rowset * row_width) - Indicator array size (rowset * 2 bytes * 8 columns) - Total storage - Percentage of available region

Recommend a rowset size and justify your answer.

Exercise 4: Multi-Row FETCH with Control Break

Modify the multi-row FETCH pattern to handle a control break on BRANCH_CODE. The program must produce a branch total line whenever BRANCH_CODE changes. Remember that with multi-row FETCH, the control break can occur anywhere within a rowset — you must check every row, not just the first and last.

Challenge: What happens when a control break falls between the last row of one rowset and the first row of the next? Write the logic to handle this edge case.

Exercise 5: Atomic vs. Non-Atomic Decision Matrix

For each of the following scenarios, state whether you would use ATOMIC or NOT ATOMIC multi-row INSERT, and explain why:

a) Inserting daily posted transactions to a financial ledger table b) Writing diagnostic trace records during batch processing c) Inserting new customer records from a daily enrollment file d) Writing MQ message confirmations to a tracking table e) Inserting regulatory reporting records for OCC submission f) Writing performance statistics records for a monitoring dashboard

MERGE Operations

Exercise 6: Basic MERGE Implementation

Write a MERGE statement that maintains the PRODUCT_INVENTORY table from a daily shipment feed. The shipment feed is loaded into a global temporary table SESSION.DAILY_SHIPMENTS.

• If the product exists: UPDATE quantity by adding shipment quantity, update LAST_RECEIPT_DATE
• If the product doesn't exist: INSERT with shipment quantity as initial stock, current date as both FIRST_RECEIPT_DATE and LAST_RECEIPT_DATE
• Include the COBOL host variable declarations and the EXEC SQL block

Exercise 7: MERGE with Conditional Logic

Extend Exercise 6 so that the UPDATE branch behaves differently based on the shipment type: - If SHIPMENT_TYPE = 'NEW': Add to quantity - If SHIPMENT_TYPE = 'RETURN': Subtract from quantity - If SHIPMENT_TYPE = 'REPLACE': Set quantity to shipment quantity

Write the MERGE statement using CASE expressions in the UPDATE SET clause.

Exercise 8: MERGE Error Analysis

A developer writes the following MERGE and gets SQLCODE -788 in production. Diagnose the problem and write the corrected solution.

MERGE INTO ACCOUNT_SUMMARY AS TGT
USING DAILY_TRANSACTIONS AS SRC
ON TGT.ACCT_NUM = SRC.ACCT_NUM
WHEN MATCHED THEN
    UPDATE SET BALANCE = TGT.BALANCE + SRC.TXN_AMT
WHEN NOT MATCHED THEN
    INSERT (ACCT_NUM, BALANCE)
    VALUES (SRC.ACCT_NUM, SRC.TXN_AMT);

The DAILY_TRANSACTIONS table contains multiple transactions per account per day.

Exercise 9: MERGE Combined with Multi-Row Source

Write the complete COBOL pattern for: 1. Multi-row INSERT 500 rows into a declared global temporary table 2. MERGE from the temporary table into a target table 3. DELETE the temporary table contents 4. Handle all SQLCODEs

Include the DECLARE GLOBAL TEMPORARY TABLE DDL.

Temporal Tables

Exercise 10: System-Period Temporal Table DDL

Write the complete DDL to create a system-period temporal table for EMPLOYEE_SALARY with columns: EMP_ID (CHAR(8)), SALARY (DECIMAL(11,2)), GRADE_LEVEL (CHAR(3)), EFFECTIVE_DATE (DATE), DEPT_CODE (CHAR(6)). Include the history table and the ALTER TABLE to enable versioning. Place the tables in tablespace DBHR.TSEMPSAL and DBHR.TSEMPHIST.

Exercise 11: Temporal Query Scenarios

Write SQL queries for each scenario using the EMPLOYEE_SALARY temporal table from Exercise 10:

a) What was employee E0048291's salary on January 15, 2025? b) Show all salary changes for employee E0048291 between January 1, 2024 and December 31, 2025. c) Find all employees whose salary was above $150,000 at any point during Q3 2025. d) Compare employee E0048291's current salary to their salary exactly one year ago (use both a temporal query and a current query, then compute the difference in COBOL).

Exercise 12: Application-Period Temporal Table

Design an application-period temporal table for insurance policy coverage. A policy can have different coverage levels during different time periods (e.g., basic coverage Jan–Jun, premium coverage Jul–Dec). Write the DDL and a query that finds what coverage a policy holder had on a specific date.

Exercise 13: Bi-Temporal Query

Using the CLAIMS_COVERAGE bi-temporal table from Section 7.4, write a query that answers: "According to what our system knew on March 1, 2025, what was patient P003928471's coverage during February 2025? Now compare that to what we know today about that same February period."

Write both queries and explain what it means if they return different results.

Exercise 14: History Table Purge Job

Write the SQL for a batch job that purges temporal history records older than 7 years from BENEFICIARY_STATUS_HIST. Consider: - The purge must not interfere with the versioning mechanism - Use DELETE with a WHERE clause on SYS_END (why SYS_END and not SYS_START?) - Calculate expected I/O impact if the history table has 500 million rows and 30% are older than 7 years

Recursive CTEs

Exercise 15: Basic Org Chart Traversal

Write a recursive CTE that produces a complete organizational chart from the EMPLOYEE_DIRECTORY table (columns: EMP_ID, EMP_NAME, TITLE, DEPT_CODE, MGR_ID). The output should include: - Employee hierarchy level (CEO = 1) - Full management path (e.g., "E001 > E015 > E089 > E234") - Direct report count for each manager (hint: use a second CTE or subquery) - Depth limit of 12 levels

Exercise 16: Account Hierarchy with Aggregation

CNB has a MASTER_ACCOUNT / SUB_ACCOUNT hierarchy up to 8 levels deep. Write a recursive CTE that: - Starts from a given master account - Traverses all sub-accounts - Aggregates the total balance across all levels - Returns each account with its own balance and the sum of all its descendants' balances

Table: ACCOUNT_STRUCTURE (ACCT_NUM, PARENT_ACCT, ACCT_NAME, ACCT_TYPE, BALANCE)

Exercise 17: Cycle Detection

Given the following data in ACCOUNT_STRUCTURE, determine which rows create a cycle and write a recursive CTE with cycle detection that handles this gracefully:

Write the CTE and explain what would happen without cycle detection.

Exercise 18: Regulatory Reporting Hierarchy

Federal Benefits has a reporting structure where benefit programs roll up through regional offices to state offices to federal divisions. Write a recursive CTE that: - Starts from a specific benefit program - Traverses up to the federal division - Collects the total beneficiary count and total disbursement amount at each level - Returns the complete rollup path

Table: REPORTING_HIERARCHY (UNIT_ID, PARENT_UNIT_ID, UNIT_NAME, UNIT_TYPE, BENEFICIARY_COUNT, DISBURSEMENT_AMT)

OLAP Functions

Exercise 19: Running Total Replacement

Rewrite the following COBOL cursor-loop logic as a single SQL query using OLAP functions:

MOVE SPACES TO WS-PREV-ACCT.
MOVE 0 TO WS-RUNNING-BAL.
MOVE 0 TO WS-TXN-COUNT.

PERFORM UNTIL SQLCODE = 100
    FETCH CSR-TXN INTO :WS-ACCT, :WS-AMT, :WS-DATE
    IF WS-ACCT NOT = WS-PREV-ACCT
        IF WS-PREV-ACCT NOT = SPACES
            PERFORM WRITE-ACCOUNT-SUMMARY
        END-IF
        MOVE 0 TO WS-RUNNING-BAL
        MOVE 0 TO WS-TXN-COUNT
        MOVE WS-ACCT TO WS-PREV-ACCT
    END-IF
    ADD WS-AMT TO WS-RUNNING-BAL
    ADD 1 TO WS-TXN-COUNT
END-PERFORM.

Your SQL must produce: ACCT_NUM, TXN_DATE, TXN_AMT, RUNNING_BALANCE, TXN_COUNT_SO_FAR.

Exercise 20: LAG/LEAD for Change Detection

Write an SQL query that identifies transactions where the amount differs from the previous transaction by more than 200% (potential fraud indicator). Use LAG() to access the previous transaction amount. Include: - The account number - Current and previous transaction amounts - The percentage change - A flag column ('ALERT' or 'NORMAL')

Exercise 21: RANK for Top-N per Group

Write an SQL query that finds the top 3 highest-value transactions per branch per day. Use RANK() or DENSE_RANK() and explain your choice. The result should include only the top-3 ranked transactions (use a CTE with the ranking, then filter).

Exercise 22: Moving Average

Write an OLAP function query that computes a 7-day moving average of transaction amounts per account. Use RANGE BETWEEN to handle days with no transactions correctly (explain why ROWS BETWEEN would give incorrect results for this use case).

Exercise 23: Multiple Window Functions

Write a single SQL query that produces a daily branch performance report with: - Branch total for the day (SUM with PARTITION BY branch) - Running daily total across all branches (SUM with ORDER BY date) - Branch rank by daily volume (RANK with PARTITION BY date, ORDER BY sum) - Previous day's branch total (LAG on the branch daily sum) - Percent change from previous day

This requires nested OLAP functions or CTEs with OLAP functions. Design the approach and write the SQL.

Decision Framework and Integration

Exercise 24: Set-Based vs. Cursor-Based Analysis

For each of the following batch program requirements, classify as set-based, cursor-based, or hybrid. Justify your choice.

a) Calculate interest on 2 million savings accounts using a tiered rate schedule (different rate for each $10,000 bracket) b) Process 50,000 wire transfer requests, each requiring OFAC sanctions screening via an external service call c) Generate month-end statements for 500,000 accounts d) Reconcile 10 million transactions against an external clearing house file e) Apply a regulatory rate change to all fixed-rate mortgage accounts f) Process returned check items, each requiring individual notification and fee assessment

Exercise 25: Complete Batch Program Design

Design (outline level, not full code) a batch program for the HA Banking system that processes daily ACH (Automated Clearing House) transactions. The program must:

1. Read ACH items from an input queue (multi-row FETCH)
2. Validate each item (account exists, sufficient funds, not blocked)
3. Apply the transaction (MERGE into account balance)
4. Write audit trail (multi-row INSERT, non-atomic)
5. Update account summary with running daily total (OLAP function)
6. Handle exceptions individually (cursor-based error processing)
7. Track all balance changes temporally (temporal table)

For each step, specify: - Which technique(s) from this chapter - Why that technique fits - What SQLCODE handling is needed - What the recovery strategy is if the step fails

Exercise 26: Performance Estimation

A batch program currently processes 20 million rows using single-row FETCH, single-row UPDATE, and single-row INSERT (one audit record per transaction). Average thread switch time is 0.04 ms. Average SQL execution time per statement is 0.15 ms.

Calculate: a) Current total elapsed time for thread switches only b) Current total elapsed time for SQL execution only c) Projected thread-switch time with multi-row operations (rowset 500) d) Projected SQL execution time (assume multi-row SQL time = single-row time * 1.2 per batch due to array processing overhead) e) Total projected savings in hours

Exercise 27: Migration Risk Assessment

You're tasked with converting a 15-year-old cursor-loop program to use multi-row operations. The program has 4,200 lines of COBOL, processes 30 million rows nightly, and has not been modified in 3 years. List:

a) Five specific risks of the conversion b) Your testing strategy for each risk c) The rollback plan if the converted program fails in production d) How you would measure success (specific metrics)

Exercise 28: Temporal Table Storage Planning

Pinnacle Health's CLAIMS table has 50 million current rows, each averaging 800 bytes. The table averages 3 updates per row per year. They want to implement system-period temporal with a 10-year retention policy.

Calculate: a) History table row count after 1 year, 5 years, and 10 years (steady state) b) History table storage requirements at each interval (include 30% overhead for index and free space) c) Annual purge job I/O volume d) Impact on DASD capacity planning

Exercise 29: Recursive CTE Performance Testing

Write a test plan for validating a recursive CTE that traverses a 200,000-node hierarchy. Include:

a) Test data generation strategy (how to create a realistic hierarchy) b) Specific test cases for edge conditions (single-node hierarchy, maximum depth, wide trees, narrow trees) c) Performance benchmarks to validate (response time, TEMP space usage, CPU time) d) What EXPLAIN output you would check and what you'd look for

Exercise 30: OLAP Function vs. COBOL Logic Comparison

Write both versions of the following requirement and compare them:

Requirement: For each account, flag the first transaction that causes the running balance to exceed $100,000 (the "high-value threshold crossing").

Version 1: COBOL cursor loop with running balance accumulation and threshold check. Version 2: SQL with SUM() OVER for running balance, LAG() on the running balance to detect the crossing, and a CASE expression for the flag.

Compare: lines of code, number of SQL statements, thread switches for 1 million rows, maintainability, and testability.