Chapter 22 Exercises: Embedded SQL and DB2 Fundamentals

These exercises progress from foundational recall of embedded SQL syntax through the design of complete COBOL-DB2 applications. Work through each tier in order; later tiers assume mastery of the earlier ones. All scenarios use a retail banking domain unless stated otherwise.

Tier 1 — Recall

Objective: Verify that you can identify and reproduce the basic syntactic elements of embedded SQL inside a COBOL program.

Exercise 1.1 — EXEC SQL Delimiters

Write the minimal COBOL statements required to declare a host variable WS-ACCOUNT-NO as PIC X(10) inside an EXEC SQL declaration section.

Expected outcome: A syntactically correct EXEC SQL BEGIN DECLARE SECTION / EXEC SQL END DECLARE SECTION block with the host variable definition.

Hint: Remember that the declaration section lives in WORKING-STORAGE.

Exercise 1.2 — Simple SELECT INTO

Given the DB2 table ACCOUNTS with columns ACCT_NO CHAR(10), ACCT_NAME VARCHAR(40), and BALANCE DECIMAL(15,2), write an embedded SQL SELECT INTO statement that retrieves the balance for a single account whose number is stored in host variable WS-ACCOUNT-NO.

Expected outcome: A complete EXEC SQL SELECT ... INTO ... END-EXEC statement with the correct colon-prefixed host variable references.

Exercise 1.3 — SQLCODE Values

List the meaning of each of the following SQLCODE values:

SQLCODE Meaning
0 ?
+100 ?
-803 ?
-811 ?
-904 ?

Expected outcome: A completed table with accurate, one-line descriptions.

Exercise 1.4 — INCLUDE SQLCA

Explain where the EXEC SQL INCLUDE SQLCA END-EXEC statement must be placed in a COBOL program and what data items it provides.

Expected outcome: Identification of the correct division/section and at least three fields from the SQLCA (e.g., SQLCODE, SQLERRD, SQLWARN).

Exercise 1.5 — Host Variable Naming

True or false — answer each statement and correct any that are false.

  1. A host variable name in COBOL can be up to 30 characters long.
  2. Host variables are referenced in SQL statements with a leading colon (:).
  3. A COBOL group item can be used directly as a host variable in a SELECT INTO.
  4. COMP-3 (packed decimal) fields map naturally to DB2 DECIMAL columns.
  5. You must always declare indicator variables for nullable columns.

Expected outcome: Five answered statements with brief corrections for any false claims.

Exercise 1.6 — SQL Communication Area Fields

From memory, write the COBOL-equivalent data names for the following SQLCA fields and describe what each one stores:

  1. The primary return code field.
  2. The array that holds row-count and other diagnostic integers.
  3. The warning flag array.

Expected outcome: Correct names (SQLCODE, SQLERRD, SQLWARN) and a one-sentence description of each.

Exercise 1.7 — DCLGEN Purpose

Describe the purpose of the DB2 DCLGEN utility and the two outputs it produces.

Expected outcome: Mention of (a) the COBOL host-variable copybook and (b) the SQL DECLARE TABLE statement, along with an explanation of how they keep the program in sync with the database schema.

Tier 2 — Comprehension

Objective: Demonstrate that you can read and interpret COBOL-DB2 source code and explain what it does.

Exercise 2.1 — Trace the Cursor

Study the following program fragment and answer the questions below.

       EXEC SQL
           DECLARE CSR-OVERDUE CURSOR FOR
               SELECT ACCT_NO, ACCT_NAME, BALANCE
               FROM   ACCOUNTS
               WHERE  BALANCE < 0
               ORDER BY BALANCE ASC
       END-EXEC.

       OPEN-CURSOR.
           EXEC SQL OPEN CSR-OVERDUE END-EXEC.
           IF SQLCODE NOT = 0
               DISPLAY 'OPEN ERROR: ' SQLCODE
               STOP RUN
           END-IF.

       FETCH-LOOP.
           EXEC SQL
               FETCH CSR-OVERDUE
               INTO :WS-ACCT-NO, :WS-ACCT-NAME, :WS-BALANCE
           END-EXEC.
           IF SQLCODE = +100
               GO TO CLOSE-CURSOR
           END-IF.
           IF SQLCODE NOT = 0
               DISPLAY 'FETCH ERROR: ' SQLCODE
               GO TO CLOSE-CURSOR
           END-IF.
           DISPLAY WS-ACCT-NO ' ' WS-ACCT-NAME ' ' WS-BALANCE.
           GO TO FETCH-LOOP.

       CLOSE-CURSOR.
           EXEC SQL CLOSE CSR-OVERDUE END-EXEC.
  1. What rows will the cursor return?
  2. In what order will they appear?
  3. What causes the fetch loop to terminate normally?
  4. What happens if the ACCOUNTS table is empty?

Expected outcome: Four clear, correct answers referencing SQLCODE behavior.

Exercise 2.2 — Indicator Variables

Explain what the indicator variable WS-BAL-IND communicates in the following statement and what values it can take:

       EXEC SQL
           SELECT BALANCE
               INTO :WS-BALANCE :WS-BAL-IND
           FROM ACCOUNTS
           WHERE ACCT_NO = :WS-ACCOUNT-NO
       END-EXEC.

Expected outcome: Description of indicator values 0 (not null, not truncated), negative (null), and positive (truncated length for strings).

Exercise 2.3 — COMMIT and ROLLBACK Scope

A COBOL-DB2 program executes the following sequence:

  1. INSERT a row into TRANSACTIONS.
  2. UPDATE the ACCOUNTS balance.
  3. EXEC SQL COMMIT END-EXEC.
  4. INSERT a row into AUDIT_LOG.
  5. The program abends.

Which of the above operations are durable after the abend? Explain why.

Expected outcome: Statements 1-3 are committed; statement 4 is rolled back because no COMMIT followed it before the abend.

Exercise 2.4 — Static vs. Dynamic SQL

Compare static and dynamic SQL in DB2 by completing the table:

Criterion Static SQL Dynamic SQL
Bind time ? ?
Performance ? ?
Flexibility ? ?
COBOL statement ? ?

Expected outcome: Completed table showing that static SQL is bound at precompile/bind time, generally faster, less flexible, and uses EXEC SQL ... END-EXEC directly; dynamic uses PREPARE/EXECUTE or EXECUTE IMMEDIATE.

Exercise 2.5 — Reading a BIND Error

A developer runs the DB2 BIND step and receives the message:

DSNT408I SQLCODE = -204, ERROR: ACCOUNTS IS AN UNDEFINED NAME

What is the most likely cause, and how would you resolve it?

Expected outcome: The table or alias ACCOUNTS does not exist in the schema being bound against. Resolution: verify the schema qualifier, create the table, or correct the BIND QUALIFIER parameter.

Exercise 2.6 — WITH HOLD Cursors

What is the effect of adding WITH HOLD to a cursor declaration? Provide a scenario in a banking batch job where this option is essential.

Expected outcome: Explanation that WITH HOLD keeps the cursor open across COMMITs, which is necessary in long-running batch jobs that commit every N rows to release locks.

Exercise 2.7 — Host Structure Usage

Given the DCLGEN copybook below, explain how you would use the generated group item in a SELECT INTO:

       01  DCLACCOUNTS.
           10 ACCT-NO        PIC X(10).
           10 ACCT-NAME      PIC X(40).
           10 BALANCE        PIC S9(13)V9(2) COMP-3.

Expected outcome: Show the SELECT ... INTO :DCLACCOUNTS syntax and explain that DB2 maps columns positionally to the elementary items of the group.

Exercise 2.8 — Scrollable Cursors

Explain the difference between a forward-only cursor and a scrollable cursor in DB2. Which COBOL FETCH syntax options become available with a scrollable cursor?

Expected outcome: Mention FETCH FIRST, FETCH LAST, FETCH PRIOR, FETCH ABSOLUTE n, FETCH RELATIVE n, and the SCROLL keyword in the DECLARE CURSOR statement.

Tier 3 — Application

Objective: Write correct, complete COBOL-DB2 code to solve stated problems.

Exercise 3.1 — Account Balance Inquiry

Write a complete COBOL paragraph that accepts an account number from WS-ACCOUNT-NO, retrieves the account name and balance from the ACCOUNTS table, and displays them. Handle the cases where the account does not exist (SQLCODE +100) and where an unexpected error occurs.

Expected outcome: A fully coded paragraph with proper host variable declarations, SELECT INTO, and branching on SQLCODE.

Exercise 3.2 — Insert a Transaction Record

The table TRANSACTIONS has columns:

Column Type
TXN_ID INTEGER (identity)
ACCT_NO CHAR(10)
TXN_TYPE CHAR(1)
TXN_AMOUNT DECIMAL(15,2)
TXN_DATE DATE
DESCRIPTION VARCHAR(80)

Write the COBOL code to insert a deposit transaction. The date should be the current date (CURRENT DATE in DB2). Handle SQLCODE -803 (duplicate) gracefully.

Expected outcome: A complete EXEC SQL INSERT with host variables and error handling, including the use of CURRENT DATE.

Exercise 3.3 — Update Account Balance

Write a COBOL paragraph that increases the balance in the ACCOUNTS table by the amount in WS-TXN-AMOUNT for the account in WS-ACCOUNT-NO. After the UPDATE, check SQLERRD(3) to verify that exactly one row was affected.

Expected outcome: An EXEC SQL UPDATE with arithmetic in the SET clause and a post-update row-count check.

Hint: In COBOL, SQLERRD(3) corresponds to the third element of the SQLERRD array in the SQLCA.

Exercise 3.4 — Delete Dormant Accounts

Write a COBOL program fragment that deletes all accounts from the ACCOUNTS table where LAST_ACTIVITY_DATE is more than seven years in the past. Display the number of rows deleted.

Expected outcome: An EXEC SQL DELETE with a date comparison using CURRENT DATE - 7 YEARS, followed by displaying SQLERRD(3).

Exercise 3.5 — Cursor-Based Statement Printing

Write a complete COBOL section that uses a cursor to retrieve all transactions for a given account in the last 30 days, formats each row as a line on a mini-statement, and writes the lines to an output file using standard COBOL file I/O. Include OPEN, FETCH loop, and CLOSE.

Expected outcome: Roughly 40-60 lines of COBOL covering cursor declaration, file FD, OPEN/FETCH/CLOSE, and WRITE statements.

Exercise 3.6 — Batch Transfer Processing

Write a COBOL paragraph that reads a sequential file of fund-transfer records (from-account, to-account, amount). For each record, debit the source account and credit the destination account using two UPDATE statements inside a logical unit of work. COMMIT after every successful transfer; ROLLBACK if either UPDATE fails.

Expected outcome: Complete code showing file READ, two UPDATEs, SQLCODE checks, and conditional COMMIT/ROLLBACK logic.

Exercise 3.7 — Dynamic SQL Execution

Write a COBOL paragraph that builds an SQL SELECT statement at runtime based on user-supplied search criteria (account name pattern). Use PREPARE and a cursor with OPEN ... USING to execute the dynamic query.

Expected outcome: Working code demonstrating EXEC SQL PREPARE, EXEC SQL DECLARE ... CURSOR FOR, EXEC SQL OPEN ... USING, and FETCH.

Exercise 3.8 — Multi-Row FETCH

Write COBOL code that uses a multi-row FETCH (rowset cursor) to retrieve 100 rows at a time from the TRANSACTIONS table into a host variable array. Process each batch and continue until all rows are fetched.

Expected outcome: Declaration of host variable arrays with OCCURS, DECLARE CURSOR ... WITH ROWSET POSITIONING, and FETCH ROWSET syntax.

Tier 4 — Analysis

Objective: Debug faulty code, evaluate performance, and critique error-handling strategies.

Exercise 4.1 — Diagnose the -818

A COBOL-DB2 program compiles and links successfully, but at runtime it abends with SQLCODE -818. The program has not been changed recently, but the DBA altered a column's length in the ACCOUNTS table last night.

  1. What does SQLCODE -818 indicate?
  2. What is the root cause in this scenario?
  3. What steps must the developer take to resolve it?

Expected outcome: Identification of a timestamp mismatch between the DBRM and the plan/package. Resolution involves regenerating the DCLGEN, precompiling, compiling, and rebinding.

Exercise 4.2 — Deadlock Analysis

Two COBOL-DB2 programs run concurrently:

  • Program A: Updates ACCOUNTS row 1001, then updates ACCOUNTS row 1002.
  • Program B: Updates ACCOUNTS row 1002, then updates ACCOUNTS row 1001.

Both programs hold their locks until COMMIT.

  1. Explain how a deadlock can occur.
  2. Which SQLCODE will DB2 return to the victim program?
  3. Propose two design changes that would prevent this deadlock.

Expected outcome: Clear deadlock explanation, identification of SQLCODE -911 (with reason code 2) or -913, and two valid prevention strategies (e.g., consistent lock ordering, shorter unit-of-work scope).

Exercise 4.3 — Missing CLOSE Cursor

A batch program opens a cursor, fetches all rows, but never issues EXEC SQL CLOSE. The program then attempts to OPEN the same cursor again.

  1. What SQLCODE will the second OPEN return?
  2. What resource implications does the missing CLOSE have?

Expected outcome: SQLCODE -502 (cursor already open) and discussion of held resources (locks, thread storage).

Exercise 4.4 — Inefficient Cursor Design

Review the following cursor and suggest at least three improvements:

       EXEC SQL
           DECLARE CSR-ALL CURSOR FOR
               SELECT *
               FROM   TRANSACTIONS
               WHERE  TXN_DATE >= '2024-01-01'
       END-EXEC.

The program only needs TXN_ID, ACCT_NO, and TXN_AMOUNT, and it never updates through this cursor.

Expected outcome: Recommendations including (1) select only needed columns, (2) add FOR FETCH ONLY / FOR READ ONLY, (3) consider adding an index on TXN_DATE, and optionally (4) use OPTIMIZE FOR n ROWS if only a subset is needed.

Exercise 4.5 — SQLCODE Handling Audit

The following error-handling paragraph is used throughout a production program:

       CHECK-SQL.
           IF SQLCODE NOT = 0
               DISPLAY 'SQL ERROR: ' SQLCODE
               STOP RUN
           END-IF.

Critique this approach. Identify at least four shortcomings and propose a more robust design.

Expected outcome: Critique covering (1) does not distinguish warnings (positive SQLCODE) from errors, (2) does not handle +100 as a normal condition, (3) provides no SQLSTATE or SQLERRMC diagnostic detail, (4) STOP RUN terminates without cleanup or ROLLBACK. Proposed design should include ROLLBACK, logging, differentiated handling for +100 and positive codes, and graceful shutdown.

Exercise 4.6 — Plan vs. Package Binding

A shop has 200 COBOL-DB2 programs that are all bound into a single DB2 plan. Any time one program is rebound, the entire plan is invalidated.

  1. Explain the performance and operational risks of this approach.
  2. Recommend a better binding strategy and justify it.

Expected outcome: Discussion of single-plan invalidation, long rebind times, and contention. Recommendation to use packages (one per program) bound into a plan via PKLIST(*), allowing independent rebinding.

Exercise 4.7 — Concurrency and Isolation Levels

A banking application reads account balances for display on an ATM screen. The current cursor uses the default isolation level CURSOR STABILITY.

  1. Describe a scenario where CURSOR STABILITY could return inconsistent data across multiple fetches from the same cursor.
  2. Would REPEATABLE READ solve this? What trade-off does it introduce?

Expected outcome: Explanation of phantom reads under CS, confirmation that RR prevents them, and discussion of the increased lock duration and reduced concurrency under RR.

Exercise 4.8 — Null Handling Bug

A program retrieves MIDDLE_NAME (a nullable VARCHAR(30) column) without an indicator variable. When the column is NULL, the program abends with SQLCODE -305.

  1. Explain the cause of -305.
  2. Write the corrected COBOL code.
  3. Show how the program should check the indicator variable before using the host variable.

Expected outcome: Explanation that -305 means a null value was fetched into a host variable without an indicator. Corrected code with an indicator variable and an IF WS-MID-IND < 0 check.

Tier 5 — Synthesis

Objective: Design and outline complete COBOL-DB2 applications for realistic banking scenarios.

Exercise 5.1 — Overnight Interest Calculation Batch

Design a COBOL-DB2 batch program that runs nightly to calculate and post interest to savings accounts. The program must:

  • Read all savings accounts with a positive balance from the ACCOUNTS table.
  • Calculate daily interest using an annual rate stored in a RATES table.
  • Insert an interest-credit transaction into the TRANSACTIONS table.
  • Update the account balance.
  • Commit every 500 accounts to limit lock duration.
  • Produce a summary report showing total interest posted.
  • Handle all SQL errors with a centralized error routine that logs diagnostics and performs an orderly shutdown.

Provide:

  1. A pseudocode outline of the program structure (divisions, sections, paragraphs).
  2. The complete WORKING-STORAGE host variable declarations.
  3. The cursor declaration.
  4. The main processing loop with COMMIT logic.
  5. The error-handling paragraph.

Expected outcome: A detailed, multi-page design document with production- quality COBOL code fragments totaling at least 80 lines.

Exercise 5.2 — Online Account Inquiry CICS-DB2 Transaction

Design a CICS-DB2 online transaction that allows a teller to enter an account number on a BMS map and receive the customer name, current balance, and last five transactions.

Provide:

  1. The BMS map field definitions (symbolic names and attributes).
  2. The COBOL PROCEDURE DIVISION logic using EXEC CICS and EXEC SQL statements.
  3. The cursor for the last five transactions (use FETCH FIRST 5 ROWS ONLY or ORDER BY ... FETCH FIRST 5 ROWS ONLY).
  4. Error handling for both CICS and DB2 failures.

Expected outcome: A complete design with BMS map definitions, COBOL paragraphs covering RECEIVE MAP, SQL retrieval, SEND MAP, and error handling.

Hint: Use EXEC CICS HANDLE ABEND or EXEC CICS RESP for CICS error management alongside SQLCODE checks for DB2.

Exercise 5.3 — End-of-Day Reconciliation Program

A bank must reconcile its general ledger with the sum of all account balances at end of day. Design a COBOL-DB2 program that:

  • Uses a cursor with WITH HOLD to sum balances by account type (checking, savings, loan) from the ACCOUNTS table.
  • Reads the expected totals from a GENERAL_LEDGER table.
  • Compares actual sums against expected totals.
  • Writes discrepancies to a RECONCILIATION_EXCEPTIONS table.
  • Produces a printed report of results.
  • Uses REPEATABLE READ isolation to ensure consistent data.

Provide:

  1. DDL for the RECONCILIATION_EXCEPTIONS table.
  2. The aggregation cursor with GROUP BY and isolation level specification.
  3. The comparison logic with tolerances (differences under $0.01 are ignored).
  4. The exception INSERT statement.
  5. The report layout and WRITE statements.

Expected outcome: A production-grade design with DDL, COBOL code fragments, and clear processing logic.

Exercise 5.4 — Customer Data Migration Utility

A bank is migrating customer data from a legacy flat file to a new DB2 schema. Design a COBOL-DB2 utility that:

  • Reads a sequential file with pipe-delimited fields (account number, name, address, balance, open date).
  • Validates each field (numeric checks, date format, non-blank name).
  • Inserts valid records into ACCOUNTS and CUSTOMERS tables within the same unit of work.
  • Writes rejected records to an error file with reason codes.
  • Commits every 1000 rows.
  • Provides a final summary (records read, inserted, rejected).

Provide:

  1. The FD entries for input and error files.
  2. The field-validation paragraphs.
  3. The INSERT statements for both tables.
  4. The commit-interval logic.
  5. The summary report display.

Expected outcome: A comprehensive utility design suitable for a real data migration project, with at least 100 lines of COBOL code fragments.

Exercise 5.5 — Multi-Table Reporting with Joins

Design a COBOL-DB2 batch program that produces a monthly customer activity report by joining the CUSTOMERS, ACCOUNTS, and TRANSACTIONS tables. The report must:

  • List each customer with all their accounts and transactions for the month.
  • Include subtotals per account and grand totals per customer.
  • Handle customers with no transactions (outer join).
  • Page-break after each customer.
  • Use a cursor with FOR FETCH ONLY and OPTIMIZE FOR 100 ROWS.

Provide:

  1. The SQL cursor with the three-table join (use LEFT OUTER JOIN for transactions).
  2. Control-break logic for customer and account boundaries.
  3. The report record layouts (header, detail, subtotal, grand total lines).
  4. The complete main processing loop.

Expected outcome: A fully designed report program demonstrating mastery of multi-table cursors, control-break processing, and report formatting.