Exercises: DB2 Application Patterns

Batch Pattern Exercises

Exercise 1: Commit Interval Calculation

Your batch program processes a table with an average row size of 800 bytes. The DB2 active log allocation is 2 GB. The lock escalation threshold (NUMLKTS) is set to 2,000 per tablespace. Calculate the maximum commit interval that avoids both lock escalation and consuming more than 25% of active log space. Show your work.

Exercise 2: Commit-Checkpoint Design

Write the WORKING-STORAGE and PROCEDURE DIVISION paragraphs for a batch program that: - Processes the EMPLOYEE_BENEFITS table (key: EMPLOYEE_ID CHAR(10)) - Recalculates benefit eligibility for employees with STATUS = 'ACTIVE' - Commits every 1,500 rows - Supports restart from the last committed checkpoint - Uses a BATCH_RESTART table with columns PROGRAM_ID, RUN_DATE, LAST_KEY, ROWS_COMMITTED, COMMIT_TS

Exercise 3: Restart Logic Validation

Given the following scenario: a batch program committed at EMPLOYEE_ID = '0045000000' (30,000 rows committed). The program then abended at EMPLOYEE_ID = '0045002347' (1,347 rows uncommitted). Write the restart logic that resumes processing correctly. Account for the rollback of the 1,347 uncommitted rows.

Exercise 4: Set-Based vs. Cursor-Based Decision

For each of the following scenarios, determine whether set-based or cursor-based processing is more appropriate. Justify your answer with specific technical reasons.

a) Update the STATUS column from 'PENDING' to 'ACTIVE' for 50,000 rows where APPROVAL_DATE is today. b) Process 8 million loan payments, where each payment requires calculating interest using a rate table, applying the payment, and logging to an audit table. c) Delete rows from a staging table where LOAD_DATE < today - 30 days. The table has 200,000 qualifying rows. d) Generate monthly statements for 3 million accounts, where each statement requires querying 5 tables and applying complex formatting rules.

Exercise 5: Partition-Aware Batch Design

Design the JCL job structure and COBOL program interface for a partition-aware batch process that: - Processes the TRANSACTIONS table (8 partitions, range-partitioned by ACCOUNT_ID) - Runs 8 parallel job steps, one per partition - Each step receives its partition's key range as SYSIN parameters - All steps share a common restart table - Describe the key range extraction and how each program instance uses it.

Exercise 6: Log Consumption Estimation

A batch program performs the following per row: - 1 UPDATE to ACCOUNTS (average row size 400 bytes) - 1 INSERT to TRANSACTION_LOG (average row size 250 bytes) - 1 UPDATE to ACCOUNTS_SUMMARY (average row size 200 bytes)

Estimate the log consumption per commit interval of 2,000 rows. Assume each log record has 100 bytes of overhead and that UPDATE log records contain both before and after images. Would a commit interval of 5,000 be safe with a 4 GB active log?

Cursor Management Exercises

Exercise 7: WITH HOLD Analysis

Explain the difference in behavior between the following two programs when a COMMIT is issued after fetching the 500th row:

Program A: DECLARE CUR-A CURSOR WITH HOLD FOR SELECT ... Program B: DECLARE CUR-B CURSOR FOR SELECT ...

What happens to the cursor position? What happens to locks? What must Program B do to continue processing after the COMMIT?

Exercise 8: Positioned vs. Searched Update

A batch program processes 5 million rows. It fetches each row, applies business logic, and updates 60% of the rows (3 million updates). Compare: a) Using a FOR UPDATE OF cursor with positioned updates (WHERE CURRENT OF) b) Using a FOR FETCH ONLY cursor with searched updates (WHERE PRIMARY_KEY = ?)

Analyze the performance implications of each approach, considering: access path availability, lock duration, and I/O patterns. Which would you recommend and why?

Exercise 9: Cursor Pool Implementation

Write the complete cursor pool implementation for a CICS patient lookup system that supports four search modes: a) By patient MRN (exact match) b) By last name and first name initial (range query) c) By date of birth and last name (exact match combination) d) By phone number (exact match)

Include: cursor declarations, the routing logic, OPEN/FETCH/CLOSE paragraphs, and result set size limits.

Exercise 10: Scrollable Cursor Paging

Design a CICS program that displays claim records in pages of 15 rows, supporting forward (PF8) and backward (PF7) paging. The user enters a provider ID and date range. Address: - How do you limit the result set to prevent workfile explosion? - What happens if the user pages backward past the first row? - How do you display "Page X of Y"? - What are the resource implications of holding a scrollable cursor across pseudo-conversational boundaries? (Trick question — explain why this doesn't work and what the alternative is.)

Exercise 11: Drain Lock Management

Your batch program opens a WITH HOLD cursor on TABLESPACE ACCTS_TS and processes for 4 hours. The DBA needs to run REORG on ACCTS_TS, but REORG is waiting for the drain lock. Design a cursor lifecycle management strategy that: - Maintains the commit-checkpoint architecture - Periodically releases the drain lock - Does not lose cursor position - Includes monitoring output so the DBA can predict when the next release window occurs.

Dynamic SQL Exercises

Exercise 12: Safe Dynamic SQL Construction

A report generator allows users to specify: - One or more account types (from a list of 8 valid types) - An optional minimum balance - An optional date range for last activity - Sort order (by name, by balance, or by date)

Write the COBOL code to construct the dynamic SQL statement safely, using parameter markers for all user-supplied values. Handle the variable number of account types using an IN clause with the appropriate number of parameter markers.

Exercise 13: SQL Injection Identification

Review the following code fragments and identify all SQL injection vulnerabilities. For each, explain the attack vector and provide the corrected code.

Fragment A:

STRING 'DELETE FROM TEMP_RESULTS WHERE USER_ID = '''
       WS-USER-ID
       ''''
       DELIMITED SIZE INTO WS-SQL

Fragment B:

STRING 'SELECT * FROM ' WS-TABLE-NAME
       ' WHERE STATUS = ?'
       DELIMITED SIZE INTO WS-SQL

Fragment C:

STRING 'SELECT * FROM CLAIMS WHERE CLAIM_ID = ?'
       ' ORDER BY ' WS-SORT-COLUMN
       DELIMITED SIZE INTO WS-SQL

Exercise 14: DESCRIBE Implementation

Write a utility program that accepts a SQL SELECT statement as input, uses PREPARE and DESCRIBE to determine the column names, types, and lengths, and displays a formatted metadata report. Handle at least these column types: CHAR, VARCHAR, INTEGER, DECIMAL, DATE, TIMESTAMP.

Exercise 15: Dynamic SQL Audit Framework

Design and code a reusable audit framework for dynamic SQL that: - Logs every PREPARE with the SQL text - Logs every EXECUTE with the parameter values - Records the execution SQLCODE and elapsed time - Stores audit records in a table (provide the DDL) - Can be called from any COBOL program with a single PERFORM - Includes a purge mechanism for audit records older than 90 days

Exercise 16: Prepared Statement Cache

Implement a prepared statement cache for a report generator that executes the same SQL structure repeatedly with different parameters. The cache should: - Store up to 10 prepared statements - Track which statements are prepared - Re-prepare only when the SQL text changes - Include usage statistics (preparation count, execution count, cache hit rate)

CICS-DB2 Thread Management Exercises

Exercise 17: DB2CONN Sizing

Given the following transaction profile for a CICS region: - ACCTINQ: 4,000 trans/hour, avg DB2 time 5ms, plan ACCTPLN - ACCUPD: 800 trans/hour, avg DB2 time 25ms, plan ACCTPLN - CLMINQ: 2,000 trans/hour, avg DB2 time 8ms, plan CLMPLN - CLMADJ: 200 trans/hour, avg DB2 time 50ms, plan CLMPLN - RPTGEN: 50 trans/hour, avg DB2 time 200ms, plan RPTPLN

Calculate: a) The average concurrent thread requirement for each plan b) The peak thread requirement (assuming 2x peak-to-average ratio) c) Recommended THREADLIMIT for DB2CONN d) Recommended PROTECTNUM for each DB2ENTRY

Exercise 18: Thread Reuse Optimization

A CICS transaction currently executes in this order: 1. EXEC SQL SELECT from CUSTOMER (5ms) 2. EXEC CICS LINK to MQSEND program (2ms) 3. EXEC CICS LINK to MQRECV program (wait up to 500ms) 4. EXEC SQL UPDATE CUSTOMER (3ms) 5. EXEC SQL INSERT into AUDIT_LOG (2ms) 6. EXEC CICS SYNCPOINT

Redesign the transaction to minimize DB2 thread hold time. Show the restructured sequence and calculate the reduction in thread hold time.

Exercise 19: Plan Consolidation

Your shop has 150 COBOL programs, each bound to its own DB2 plan. There are 150 plans with a total of 300 packages. Transactions experience thread pool contention because threads are plan-specific. Design a consolidation strategy: a) How would you group programs into plans? b) How many plans would you target? c) What is the impact on BIND management? d) What are the risks of over-consolidation?

Data Architecture Exercises

Exercise 20: Temporal Table Migration

An existing system uses application-managed history (the COBOL program inserts into a HISTORY table before each update). There are 15 programs that update the ACCOUNTS table, each with its own history-insertion logic. Design a migration plan to move to DB2 temporal tables: a) Write the DDL to convert ACCOUNTS and ACCOUNTS_HISTORY to temporal b) Identify what changes to the 15 COBOL programs c) Describe the data migration strategy for existing history records d) What happens to the existing HISTORY table data?

Exercise 21: Archival Strategy Design

Design the complete archival strategy for the HA Banking System's TRANSACTIONS table: - 200 million new rows per year - 7-year regulatory retention requirement - Monthly partitioning - Must not impact online availability

Provide: partitioning DDL, archival batch job logic, space management calculations, and the COBOL program that performs the archive-and-verify process.

Exercise 22: Partition-Aligned Index Design

The TRANSACTIONS table is range-partitioned by TRANS_DATE (monthly). Design indexes for the following query patterns, specifying which should be partitioned (DPSI) and which should be non-partitioned (NPSI): a) Lookup by TRANSACTION_ID (unique) b) Range scan by ACCOUNT_ID + TRANS_DATE c) Range scan by TRANS_DATE + TRANS_TYPE (for monthly reports) d) Lookup by EXTERNAL_REF_NUM (unique, used for reconciliation)

Justify each choice considering partition independence, utility performance, and query access paths.

Integration and Anti-Pattern Exercises

Exercise 23: Anti-Pattern Refactoring

The following program is a production batch program at a fictional company. It contains at least six anti-patterns from section 12.7. Identify each anti-pattern, explain why it's dangerous, and provide the corrected code.

       PROCEDURE DIVISION.
       MAIN-PARA.
           EXEC SQL
               DECLARE CUR1 CURSOR FOR
               SELECT * FROM ACCOUNTS
               WHERE (:WS-REGION = '' OR REGION = :WS-REGION)
               AND   (:WS-TYPE = '' OR ACCT_TYPE = :WS-TYPE)
               ORDER BY CUSTOMER_NAME
           END-EXEC
           EXEC SQL OPEN CUR1 END-EXEC
           PERFORM UNTIL SQLCODE = 100
               EXEC SQL FETCH CUR1 INTO :WS-REC END-EXEC
               IF SQLCODE = 0
                   PERFORM PROCESS-ROW
               ELSE
                   DISPLAY 'ERROR: ' SQLCODE
                   STOP RUN
               END-IF
           END-PERFORM
           EXEC SQL COMMIT END-EXEC
           STOP RUN.

Exercise 24: Complete Batch Program Design

Design and code a complete batch program that applies monthly service charges to checking accounts at CNB. Requirements: - Process the ACCOUNTS table (8 partitions, 12 million rows) - Service charge = $12.00 for balances < $1,000, $5.00 for balances $1,000-$5,000, $0.00 for balances > $5,000 - Insert a TRANSACTION record for each charge - Commit every 2,000 rows with restart capability - Log all processing statistics - Handle SQLCODE -911/-913 with retry logic - Skip and log accounts with data errors (don't abend the batch)

Include: complete WORKING-STORAGE, all PROCEDURE DIVISION paragraphs, cursor declarations, commit-checkpoint logic, restart logic, and error handling.

Exercise 25: Security Audit Checklist

You are conducting a security audit of dynamic SQL usage across 30 COBOL programs. Create a comprehensive checklist (minimum 15 items) that auditors should verify for each program. For each item, provide: a) What to check b) How to check it (what to look for in the source code) c) The risk if the check fails d) The remediation action

Exercise 26: Performance Diagnostic

A batch program that was completing in 45 minutes last month now takes 3 hours and 20 minutes. It processes the same table with a WITH HOLD cursor, commits every 1,000 rows, and updates 80% of the rows it reads. The table has grown from 5 million to 8 million rows. Provide a systematic diagnostic plan: a) What DB2 performance data would you collect? b) What are the five most likely causes? c) For each likely cause, what is the evidence and fix? d) How would you verify the fix without disrupting production?

Exercise 27: Cross-Chapter Integration

Design the complete DB2 access layer for a CICS balance transfer transaction that: - Debits one account and credits another - Must be atomic (both succeed or both fail) - Must handle concurrent transfers to the same account (locking strategy from Ch. 8) - Must use an optimal access path (optimizer considerations from Ch. 6) - Must log to a history table - Must complete in under 200ms

Integrate concepts from Chapters 6 (optimizer), 7 (SQL), 8 (locking), and 12 (this chapter). Show the complete COBOL code including SQL, error handling, and CICS SYNCPOINT logic.