Chapter 32 Exercises: Embedded SQL for COBOL and C

Exercise 32.1: Host Variable Declarations

Objective: Practice mapping DB2 column types to COBOL host variables.

Given the following table definition:

CREATE TABLE MERIDIAN.LOAN_ACCOUNTS (
    LOAN_NUM        CHAR(12)        NOT NULL,
    CUST_ID         INTEGER         NOT NULL,
    PRINCIPAL       DECIMAL(15,2)   NOT NULL,
    INTEREST_RATE   DECIMAL(5,4),
    TERM_MONTHS     SMALLINT        NOT NULL,
    PAYMENT_AMT     DECIMAL(11,2),
    NEXT_DUE_DATE   DATE,
    ORIGINATION_DT  TIMESTAMP       NOT NULL,
    LOAN_STATUS     CHAR(1)         NOT NULL,
    DESCRIPTION     VARCHAR(200)
);

Tasks:

  1. Write the complete EXEC SQL BEGIN DECLARE SECTION / EXEC SQL END DECLARE SECTION block with host variables for every column.
  2. Include indicator variables for all nullable columns.
  3. Write the VARCHAR host variable using the correct level-49 structure.

Expected Output: A complete COBOL WORKING-STORAGE SECTION excerpt with all host variables and indicators properly declared.

Exercise 32.2: SELECT INTO with Error Handling

Objective: Write a single-row retrieval with comprehensive error handling.

Task: Write a COBOL paragraph that:

  1. Accepts a loan number in HV-LOAN-NUM
  2. Retrieves the principal, interest rate, and payment amount from MERIDIAN.LOAN_ACCOUNTS
  3. Handles all three possible outcomes: - SQLCODE = 0 (found) - SQLCODE = +100 (not found) - SQLCODE < 0 (error)
  4. Properly checks indicator variables for nullable columns before using the values
  5. Displays appropriate messages for each case

Exercise 32.3: Cursor Processing with Periodic Commits

Objective: Build a complete cursor-based processing loop with WITH HOLD and periodic commits.

Scenario: Write a COBOL program fragment that processes all overdue loans (where NEXT_DUE_DATE < CURRENT DATE and LOAN_STATUS = 'A'). For each overdue loan:

  1. Calculate a late fee of 2% of the payment amount
  2. Insert a record into MERIDIAN.LATE_FEES (LOAN_NUM, FEE_DATE, FEE_AMOUNT)
  3. Update the LOAN_STATUS to 'D' (delinquent)
  4. Commit every 500 rows

Requirements: - Use a WITH HOLD cursor - Use FOR UPDATE OF for the status column - Use positioned UPDATE - Handle errors gracefully — do not let one bad row stop the entire batch

Exercise 32.4: Multi-Row FETCH

Objective: Convert a single-row FETCH loop to a multi-row FETCH for better performance.

Given: A single-row fetch loop that processes 100,000 accounts:

       EXEC SQL
           DECLARE CSR-ACCTS CURSOR FOR
               SELECT ACCOUNT_NUM, ACCT_NAME, BALANCE
               FROM MERIDIAN.ACCOUNTS
               WHERE BRANCH_ID = :HV-BRANCH-ID
               ORDER BY ACCOUNT_NUM
       END-EXEC.

Task: 1. Rewrite the cursor declaration for rowset positioning 2. Declare appropriate host variable arrays (rowset size of 100) 3. Write the FETCH loop using FETCH NEXT ROWSET ... FOR 100 ROWS 4. Write the inner loop that processes each row within the rowset 5. Handle the last rowset (which may have fewer than 100 rows)

Exercise 32.5: Static vs Dynamic SQL Decision

Objective: Analyze requirements and choose between static and dynamic SQL.

Scenario: Meridian Bank needs the following five SQL operations in a new COBOL program. For each, decide whether static SQL or dynamic SQL is more appropriate, and explain your reasoning:

  1. A SELECT that always queries MERIDIAN.ACCOUNTS by ACCOUNT_NUM (primary key)
  2. A query where the user specifies which columns to retrieve at runtime
  3. A nightly batch UPDATE that adds interest to all savings accounts
  4. A CREATE INDEX statement that runs during a maintenance window
  5. A SELECT where the WHERE clause columns vary based on input parameters (sometimes filtering by branch, sometimes by status, sometimes by both)

Exercise 32.6: BIND Options Analysis

Objective: Choose appropriate BIND options for different scenarios.

Scenario: Recommend BIND options for each of the following programs at Meridian Bank:

  1. Read-only reporting program that generates a snapshot report. It must see consistent data even if other programs are updating. It runs for 2 hours. - Recommended ISOLATION level? - CURRENTDATA setting? - RELEASE setting?

  2. High-volume transaction posting program that updates millions of rows. It runs concurrently with online transactions. - Recommended ISOLATION level? - RELEASE setting? - ACQUIRE setting?

  3. Ad-hoc query program where users type SQL at runtime. The program owner should control authorization, not the runtime user. - Static or dynamic SQL? - DYNAMICRULES setting?

Exercise 32.7: Error Recovery

Objective: Write robust error handling for deadlock and timeout scenarios.

Task: Write a COBOL paragraph called DEADLOCK-RETRY that:

  1. Is invoked when SQLCODE = -911 (deadlock, unit of work rolled back)
  2. Logs the error with the account number being processed
  3. Waits briefly (use a counter loop since COBOL has no SLEEP)
  4. Repositions the cursor to the last successfully committed point
  5. Retries up to 3 times before giving up
  6. If all retries fail, writes the account number to a reject file and continues

Hint: After a -911, the cursor is closed (the unit of work was rolled back). You need to re-OPEN the cursor and reposition it.

Exercise 32.8: Complete COBOL Program — Monthly Fee Assessment

Objective: Write a complete embedded SQL COBOL program from scratch.

Scenario: Meridian Bank assesses a monthly maintenance fee on checking accounts with balances below $1,500. Write a complete COBOL program (FEEASMT) that:

  1. Opens a cursor for all active checking accounts (ACCT_TYPE = 'CHK') with BALANCE < 1500.00
  2. For each account: - Deducts a $12.00 maintenance fee from the balance - Inserts a transaction record into MERIDIAN.TRANSACTIONS with TXN_TYPE = 'FE' and description 'MONTHLY MAINTENANCE FEE' - If the balance would go negative, skip the fee and log the account number
  3. Commits every 1,000 rows
  4. At the end, displays: - Total accounts assessed - Total fees collected - Total accounts skipped (insufficient balance)
  5. Sets the return code: 0 if no errors, 4 if some accounts were skipped, 16 if SQL errors occurred

Include: - Full IDENTIFICATION DIVISION, DATA DIVISION, and PROCEDURE DIVISION - SQLCA - Host variables and indicator variables - WITH HOLD cursor - Comprehensive error handling

Exercise 32.9: Embedded SQL in C

Objective: Translate a COBOL embedded SQL program to C.

Task: Take the account inquiry pattern from Section 32.10 and expand it into a complete C program that:

  1. Accepts an account number from command-line arguments
  2. Queries MERIDIAN.ACCOUNTS for the account details
  3. Prints the account name, balance, and status
  4. If the account is found, queries MERIDIAN.TRANSACTIONS for the last 10 transactions using a cursor
  5. Prints each transaction in a formatted table
  6. Handles all error conditions

Exercise 32.10: Precompile and Bind JCL

Objective: Write the complete JCL to precompile, compile, link-edit, and bind an embedded SQL COBOL program.

Task: Write a multi-step JCL job for the FEEASMT program from Exercise 32.8 that includes:

  1. Step 1 (PC): Precompile with DSNHPC, producing a DBRM and modified COBOL source
  2. Step 2 (COB): Compile the modified source with Enterprise COBOL
  3. Step 3 (LKED): Link-edit with the DB2 language interface module
  4. Step 4 (BIND): Bind the DBRM into a package in collection MERIDIAN_BATCH
  5. Step 5 (PLAN): Bind a plan referencing the collection

Include appropriate DSN names, DISP parameters, and condition codes for step execution control.