Chapter 27 Exercises: Embedded SQL Fundamentals

Exercise 27.1: Single-Row Query

Difficulty: Beginner

Write a COBOL program that:

  1. Accepts a provider ID from the terminal
  2. Uses SELECT INTO to retrieve the provider's name, specialty, and NPI number from PROVIDER_MASTER
  3. Checks SQLCODE for success, not found, and error conditions
  4. Displays the result in a formatted layout

Include the SQLCA and appropriate host variable declarations. Use null indicator variables for any nullable columns.

Exercise 27.2: Host Variable Declarations

Difficulty: Beginner

Given the following DB2 table definition, write the COBOL host variable declarations (as DCLGEN would produce them):

CREATE TABLE EMPLOYEE (
    EMP_ID        CHAR(8)       NOT NULL,
    EMP_NAME      VARCHAR(50)   NOT NULL,
    DEPT_CODE     CHAR(4)       NOT NULL,
    HIRE_DATE     DATE          NOT NULL,
    SALARY        DECIMAL(9,2)  NOT NULL,
    COMMISSION    DECIMAL(7,2),
    MANAGER_ID    CHAR(8),
    PHONE_EXT     SMALLINT
);

For each column, specify: - The COBOL PIC clause - Whether a null indicator is needed - The USAGE clause (COMP, COMP-3, or DISPLAY)

Exercise 27.3: Cursor Report

Difficulty: Intermediate

Write a complete COBOL program that produces a department roster report:

  1. DECLARE a cursor that selects employees for a given department, ordered by name
  2. Accept a department code from the terminal
  3. OPEN the cursor
  4. FETCH each row in a loop, displaying employee name, hire date, and salary
  5. Track totals: number of employees, total salary, average salary
  6. CLOSE the cursor
  7. Display the totals at the end

Handle all SQLCODE values: 0 (success), +100 (end of data), and negative (error). Include a proper SQL-ERROR-HANDLER paragraph.

Exercise 27.4: INSERT with Validation

Difficulty: Intermediate

Write a program that inserts a new claim record into CLAIM_MASTER. Before the INSERT:

  1. Validate the member ID exists in MEMBER_MASTER (use SELECT INTO)
  2. Validate the provider ID exists in PROVIDER_MASTER
  3. Validate the billed amount is positive
  4. Check for duplicate claim IDs (handle SQLCODE -803)

If any validation fails, display a specific error message and do not perform the INSERT. If the INSERT succeeds, COMMIT. If it fails, ROLLBACK.

Exercise 27.5: Positioned UPDATE

Difficulty: Intermediate

Write a program that applies a 2% interest credit to all savings accounts:

  1. DECLARE a cursor FOR UPDATE OF ACCT_BALANCE selecting savings accounts (ACCT_TYPE = 'SV')
  2. OPEN the cursor
  3. FETCH each row
  4. Calculate the monthly interest (balance * 0.02 / 12)
  5. UPDATE WHERE CURRENT OF the cursor to add the interest
  6. Track total interest applied and number of accounts processed
  7. COMMIT every 500 rows
  8. CLOSE the cursor and display totals

Exercise 27.6: Transaction with Rollback

Difficulty: Intermediate

Write a fund transfer program that:

  1. Accepts source account, target account, and transfer amount
  2. Verifies both accounts exist and are active (two SELECT INTO statements)
  3. Verifies the source account has sufficient balance
  4. UPDATEs the source account (debit)
  5. UPDATEs the target account (credit)
  6. INSERTs a transaction log record
  7. COMMITs only if all three DML operations succeed
  8. ROLLBACKs if any operation fails, with a specific error code

This is a classic atomicity exercise. Pay careful attention to the order of operations and error checking.

Exercise 27.7: WHENEVER vs. Manual Checking

Difficulty: Intermediate (analytical)

Take the cursor report program from Exercise 27.3 and rewrite it using WHENEVER:

  • WHENEVER SQLERROR GO TO SQL-ERROR-EXIT
  • WHENEVER NOT FOUND GO TO END-OF-DATA

Compare the two versions: 1. Which is shorter? 2. Which is easier to understand? 3. Which gives you more control over error handling? 4. What happens if the NOT FOUND condition occurs in the validation SELECTs (not just the cursor FETCH)?

Write a 300-word analysis of which approach you prefer and why.

Exercise 27.8: Batch Processing with Error Recovery

Difficulty: Advanced

Write a batch program that reads a sequential file of account updates and applies them to DB2:

  1. Read each record from the input file
  2. Use the update type field to determine the operation (INSERT, UPDATE, DELETE)
  3. Execute the appropriate SQL statement
  4. If the SQL fails, write the failed record to an error file with the SQLCODE
  5. COMMIT every 250 successful records
  6. At the end, display: records read, records processed, records failed, total committed

The error recovery requirement is key: a single failed record should not abort the entire batch. Think carefully about when to COMMIT and how to handle errors within a commit group.