Exercises: Advanced DB2 Programming

Exercise 28.1: Dynamic SQL Report Generator (Introductory)

Write a COBOL program that accepts three parameters from a PARM field or SYSIN: - Table name (ACCOUNT or TXN_HISTORY) - Column to filter on (e.g., ACCT_STATUS, TXN_TYPE) - Filter value (e.g., 'A', 'CR')

The program should construct a dynamic SQL SELECT statement, PREPARE it, declare and open a cursor, and display all matching rows.

Requirements: 1. Validate the table name against an allowed list (only ACCOUNT and TXN_HISTORY) 2. Validate the column name against allowed columns for each table 3. Use a parameter marker for the filter value (not string concatenation) 4. Display the number of rows retrieved 5. Handle SQLCODE errors including +100 (no data)

Hint: Use EVALUATE to map valid table/column combinations, and STRING to build the SQL statement.

Exercise 28.2: Scrollable Cursor Browser (Introductory)

Create a COBOL program that opens a scrollable cursor on the ACCOUNT table and allows the user to navigate through results by reading commands from SYSIN:

• F — FETCH FIRST row
• N — FETCH NEXT row
• P — FETCH PRIOR row
• L — FETCH LAST row
• Annn — FETCH ABSOLUTE row number nnn
• Q — Quit

Requirements: 1. Declare the cursor as SCROLL with FOR FETCH ONLY 2. Display the current row data and row position after each FETCH 3. Handle SQLCODE +100 (moved past end or before beginning) 4. Display an appropriate message for each boundary condition

Exercise 28.3: NULL-Aware Data Loader (Intermediate)

Write a program that reads a flat file with pipe-delimited fields and inserts rows into a table with several nullable columns. Empty fields in the input should be stored as NULL using indicator variables.

Input file format:

ACCT001|John Smith|SA|15000.00|A|2024-01-15||BR01
ACCT002|Jane Doe|CH|2500.50|A|2023-06-01|2024-12-31|BR02

Note: Field 7 (CLOSE_DATE) is empty in the first row, meaning it should be NULL.

Requirements: 1. Parse each pipe-delimited field using UNSTRING 2. For each nullable field, set the indicator variable to -1 if the field is empty, or 0 if it contains data 3. Use indicator variables on the INSERT statement 4. Display a count of NULLs inserted per column at the end of processing

Exercise 28.4: Multi-Row FETCH Batch Processor (Intermediate)

Create a batch program that processes all rows in the CLAIM table using multi-row FETCH with a rowset size of 50. For each claim: 1. Check if CLAIM_AMOUNT exceeds the member's coverage limit 2. If so, set CLAIM_STATUS to 'DENIED' and store the reason 3. If not, set CLAIM_STATUS to 'APPROVED' 4. COMMIT every 500 rows

Requirements: 1. Use a cursor WITH HOLD and WITH ROWSET POSITIONING 2. Process the rowset array with a PERFORM VARYING loop 3. Track and display: total processed, approved, denied 4. Save the last committed CLAIM_NUMBER for restart purposes 5. Display a commit message with the checkpoint key

Exercise 28.5: Stored Procedure Caller (Intermediate)

Write a COBOL program that calls a stored procedure named SCHEMA.CALC_INTEREST with the following parameters:

Requirements: 1. Read account numbers from SYSIN, one per line 2. Call the stored procedure for each account with a fixed rate of 0.0425 and 30 days 3. Handle SQLCODE from the CALL (including -471 for procedure not found) 4. Handle the procedure's own return codes (0=success, 1=not found, 2=frozen) 5. Display results in a formatted report

Exercise 28.6: Deadlock-Resilient Updater (Advanced)

Write a program that updates ACCOUNT balances by applying interest accrual. The program must handle deadlocks (-911) and lock timeouts (-904) gracefully.

Requirements: 1. Read accounts using a cursor WITH HOLD 2. For each account, calculate interest and UPDATE the balance 3. If SQLCODE = -911 (deadlock), wait briefly and retry up to 3 times 4. If SQLCODE = -904 (timeout), retry up to 3 times 5. Log all retries with account number and attempt count 6. If all retries are exhausted, skip the row and continue with the next 7. COMMIT every 100 rows 8. At the end, display: processed, updated, retried, skipped counts

Exercise 28.7: Performance Analysis (Advanced)

Given the following query and PLAN_TABLE output, answer the analysis questions below:

SELECT C.CLAIM_NUMBER, C.CLAIM_AMOUNT,
       M.MEMBER_NAME, P.PROVIDER_NAME
FROM CLAIM C
JOIN MEMBER M ON C.MEMBER_ID = M.MEMBER_ID
JOIN PROVIDER P ON C.PROVIDER_ID = P.PROVIDER_ID
WHERE C.CLAIM_STATUS = 'PENDING'
AND C.SUBMIT_DATE >= CURRENT DATE - 90 DAYS
ORDER BY C.CLAIM_AMOUNT DESC

PLAN_TABLE output:

Questions: 1. What is the most significant performance problem in this plan? 2. What index would you create to improve the CLAIM table access? 3. After creating the index, what MATCHCOLS value would you expect? 4. Would changing the column order in the new index affect MATCHCOLS? Explain. 5. Write the CREATE INDEX DDL for your recommended index. 6. Could the ORDER BY sort be eliminated with a different index design? What trade-offs would that involve?

Exercise 28.8: Complete Dynamic SQL Application (Advanced)

Build a complete "mini-application" that combines multiple concepts from this chapter. The program should:

1. Read a control file that specifies: operation type (SELECT/UPDATE/DELETE), table name, filter criteria
2. Validate all inputs against an allowed list
3. For SELECT operations: - Build and PREPARE the dynamic query with parameter markers - Open a scrollable cursor - Display the first 10 rows - Display total row count
4. For UPDATE operations: - Build and PREPARE the dynamic UPDATE - EXECUTE with parameters - Display rows affected (SQLERRD(3)) - Prompt for COMMIT or ROLLBACK
5. For DELETE operations: - Same as UPDATE but with confirmation
6. Log all operations to a TXN_LOG table including: timestamp, operation, table, row count, user ID

Deliverables: - Complete COBOL program - JCL to compile and run - Test control file with at least 5 operations - Expected output for each operation