Chapter 12 Exercises: Indexed File Processing (VSAM KSDS)

Tier 1: Recall and Recognition (Exercises 1-8)

Exercise 1: SELECT Clause Identification

Write the complete SELECT clause for a VSAM KSDS employee file with the following characteristics: - File name: EMPLOYEE-MASTER - External name: EMPMAST - Primary key: EM-EMPLOYEE-ID - Access mode: Random - File status field: WS-EMP-STATUS

Exercise 2: File Status Code Matching

Match each file status code to its meaning:

Choose from: Successful completion, Duplicate alternate key, End of file, Key sequence error, Duplicate primary key, Record not found, Key boundary violation, File not found, No prior READ before REWRITE/DELETE.

Exercise 3: Access Mode Selection

For each scenario, identify the most appropriate ACCESS MODE:

a) Loading 100,000 records from a sorted sequential file into a new KSDS b) Looking up individual customer records by account number for a teller application c) Starting at a customer ID and browsing forward 20 records, then jumping to another ID d) Producing a report of all records in key order e) Processing random add/update/delete transactions against a master file

Exercise 4: OPEN Mode Requirements

For each operation, identify the minimum OPEN mode required:

a) READ a record by key b) WRITE a new record to an existing file c) REWRITE an existing record d) DELETE a record e) START to position the file f) Load records into a new empty file

Exercise 5: KSDS Loader Program

Write a complete COBOL program that loads a product inventory KSDS from a sorted sequential file. The product record is 100 bytes with an 8-byte product code as the primary key. Include proper error handling for duplicate keys and sequence errors.

Solution: See code/exercise-solutions.cob (EX05-PRODUCT-LOAD).

Exercise 6: IDCAMS DEFINE CLUSTER

Write the IDCAMS DEFINE CLUSTER command for the product inventory KSDS from Exercise 5. The file has: - 8-byte key at offset 0 - Fixed-length 100-byte records - 15% CI free space, 5% CA free space - 4096-byte CI size for data - Initial allocation of 2 cylinders with 1 cylinder secondary

Exercise 7: INVALID KEY Conditions

For each statement, list the file status codes that trigger INVALID KEY:

a) READ CUSTOMER-MASTER (random) b) WRITE CUSTOMER-MASTER-RECORD (sequential access, OUTPUT) c) WRITE CUSTOMER-MASTER-RECORD (random access, I-O) d) REWRITE CUSTOMER-MASTER-RECORD e) DELETE CUSTOMER-MASTER f) START CUSTOMER-MASTER KEY IS EQUAL TO CM-KEY

Exercise 8: True or False

State whether each statement is true or false:

a) In ACCESS MODE IS SEQUENTIAL, records can be written in any key order. b) REWRITE can change the primary key of a record. c) In ACCESS MODE IS RANDOM, DELETE requires a prior READ. d) The START statement reads the first matching record. e) File status '02' indicates an error condition. f) VSAM KSDS files require DCB parameters in JCL. g) FREESPACE(0 0) means no space is reserved for future inserts. h) A VSAM cluster consists of a data component and an index component.

Tier 2: Comprehension and Application (Exercises 9-16)

Exercise 9: Sequential Report Program

Write a complete COBOL program that reads a VSAM KSDS employee file sequentially and produces a report showing all employees in the 'SALES' department. Use START to position at the beginning of the file and READ NEXT to process records. Include page headers and a record count.

Exercise 10: Range Query with START

Write a program that reads all products in a key range from a KSDS. The program accepts a start key and end key, uses START to position at the beginning of the range, and reads forward until the end key is exceeded.

Solution: See code/exercise-solutions.cob (EX10-RANGE-QUERY).

Exercise 11: Random Inquiry Program

Write a program that reads customer IDs from SYSIN (or an inline DD) and looks up each one in a VSAM KSDS. Display the customer name and balance for found records, and "NOT FOUND" for missing records. Count and display totals.

Exercise 12: Complete the JCL

Given the following incomplete JCL, fill in the missing parts to define a VSAM KSDS for an order file with 150-byte records, a 12-byte key at offset 0, and appropriate free space:

//DEFORDS  EXEC PGM=_______
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  DEFINE _______ (
           NAME(USERHLQ.ORDERS.MASTER)
           _______
           KEYS(__ __)
           RECORDSIZE(___ ___)
           FREESPACE(__ __)
         )
         DATA (
           NAME(USERHLQ.ORDERS.MASTER._____)
           CYLINDERS(_ _)
         )
         INDEX (
           NAME(USERHLQ.ORDERS.MASTER._____)
           TRACKS(_ _)
         )
/*

Exercise 13: REWRITE Sequence

The following code has errors. Identify and fix them:

           MOVE 'C000010000' TO CM-CUSTOMER-ID
           MOVE 'NEW ADDRESS' TO CM-ADDRESS
           REWRITE CUSTOMER-MASTER-RECORD
               INVALID KEY
                   DISPLAY 'UPDATE FAILED'
           END-REWRITE

Exercise 14: Dynamic Access Browse

Write a COBOL paragraph that implements a "browse" function: given a starting key in WS-START-KEY and a count in WS-MAX-RECORDS, use dynamic access to position at the key and read forward the specified number of records. Handle the case where the starting key does not exist (position at the next higher key).

Exercise 15: Batch Update with Audit

Write a program that processes a transaction file against an employee KSDS master file. Transactions include salary changes (S), department transfers (D), and terminations (T). Log before and after images to an audit file.

Solution: See code/exercise-solutions.cob (EX15-BATCH-UPDATE).

Exercise 16: DELETE with Verification

Write a COBOL paragraph that deletes a record from a VSAM KSDS, but first reads the record to verify it exists, displays the record contents for confirmation, and writes the deleted record to an archive file before deleting.

Tier 3: Analysis and Problem Solving (Exercises 17-24)

Exercise 17: Access Mode Comparison

Write three short programs (or three sections of one program) that all accomplish the same task -- counting the number of records in a KSDS file. Use each of the three access modes. Explain which is most efficient and why.

Exercise 18: Error Handler Module

Design and write a reusable COBOL paragraph that interprets any VSAM file status code and: - Displays a human-readable error message - Classifies the error as INFO, WARNING, ERROR, or SEVERE - Sets a return code field appropriately - Can be called from any program by COPYing a copybook

Exercise 19: IDCAMS Script Analysis

Given the following IDCAMS commands, explain what each step does and identify any potential issues:

  DELETE PROD.CUSTOMER.MASTER CLUSTER
  DEFINE CLUSTER (
           NAME(PROD.CUSTOMER.MASTER)
           INDEXED
           KEYS(10 0)
           RECORDSIZE(200 500)
           FREESPACE(0 0)
         )
         DATA (
           NAME(PROD.CUSTOMER.MASTER.DATA)
           TRACKS(10 1)
         )
  REPRO INFILE(SEQDD) OUTFILE(VSAMDD)

Issues to find: no PURGE on DELETE, no MAXCC handling, variable record size without reason, no free space, small secondary allocation, missing INDEX component specification.

Exercise 20: Cross-File Lookup

Write a program that reads a product master file sequentially and, for each product below its reorder point, looks up the supplier information from a separate supplier KSDS (random access). Produce a reorder report showing product details and supplier contact information.

Solution: See code/exercise-solutions.cob (EX20-REORDER-REPORT).

Exercise 21: Performance Analysis

A VSAM KSDS was defined with FREESPACE(0 0) and has been in production for 6 months with heavy insert activity. LISTCAT shows: - 50,000 records - 12,000 CI splits - 800 CA splits - 3 index levels

Diagnose the performance problems and recommend: a) Immediate actions to fix current issues b) DEFINE CLUSTER parameters for the reorganized file c) A maintenance schedule going forward

Exercise 22: Alternate Key Design

A library system needs to look up books by: - ISBN (unique) - Title (non-unique) - Author last name (non-unique) - Publication year (non-unique)

Design the VSAM KSDS and alternate index structure. Write: a) The COBOL SELECT clause b) The IDCAMS commands to define all objects c) A brief analysis of the performance impact of four alternate indexes on write operations

Exercise 23: Sequential vs. Random Trade-off

A batch program processes 100,000 transactions against a KSDS master file of 10,000,000 records. The transactions are sorted in key order. Compare two approaches: a) ACCESS MODE IS RANDOM: read each master record randomly b) ACCESS MODE IS SEQUENTIAL: read the master sequentially, matching against transactions

Calculate the approximate number of I/O operations for each approach. Under what conditions would each be preferred?

Exercise 24: File Status 02 Handling

Write a program that loads records into a KSDS with a non-unique alternate key. Properly handle file status '02' (duplicate alternate key) as a successful condition, while still reporting status '22' (duplicate primary key) as an error. Track and display counts of records with duplicate alternate keys.

Tier 4: Synthesis and Design (Exercises 25-32)

Exercise 25: Comprehensive Error Handler

Design and implement a complete VSAM error-handling framework that can be used across multiple programs. Include: - A copybook for file status fields and condition names - A reusable error-handling paragraph - Support for multiple VSAM files in the same program - Error logging to a standard error file format

Solution: See code/exercise-solutions.cob (EX25-STATUS-HANDLER).

Exercise 26: Master File Maintenance System

Design and write a complete master file maintenance program for a hospital patient records system. The KSDS has: - Primary key: Patient ID (8 bytes) - Alternate key: SSN (11 bytes, unique) - Alternate key: Last name (20 bytes, with duplicates) - Record size: 250 bytes

The program must handle: new patient registration, information updates, discharge processing, record lookup by any key, and date-range queries on admission date.

Exercise 27: JCL Procedure Design

Create a JCL cataloged procedure (PROC) for VSAM KSDS maintenance that includes: - Backup step (REPRO to sequential) - Maintenance step (run COBOL program) - Verification step (LISTCAT) - Conditional reorganization step (REPRO out and back in)

Include symbolic parameters for the data set names and the COBOL program name.

Exercise 28: Migration Tool

Write a program that migrates data from one KSDS to another with a different record layout. The source file has 150-byte records and the target has 200-byte records with additional fields. Handle the layout transformation, maintain data integrity, and produce a migration report showing record counts and any conversion errors.

Exercise 29: Multi-File Join

Write a program that performs a join operation between two KSDS files: - CUSTOMER-MASTER (primary key: customer ID) - ORDER-MASTER (primary key: order ID, contains customer ID field)

Read ORDER-MASTER sequentially and look up each customer from CUSTOMER-MASTER (random). Produce a combined report showing order details with customer name and address. Handle orphan orders (orders with no matching customer).

Exercise 30: Online Simulation

Design and write a program that simulates an online CICS-like transaction processing environment. The program reads commands from a file: - INQ xxxxxxxxxx -- Inquiry by key - BRW xxxxxxxxxx nnn -- Browse from key for n records - ADD xxxxxxxxxx data... -- Add a new record - UPD xxxxxxxxxx data... -- Update a record - DEL xxxxxxxxxx -- Delete a record

Process each command against a KSDS and produce a transaction log showing the command, result, and timing.

Exercise 31: Reorganization Analysis Tool

Write a program that reads a VSAM KSDS sequentially and analyzes its condition by: - Counting total records - Calculating the key distribution (first 100, next 100, etc.) - Identifying key gaps (ranges with no records) - Computing average record density - Recommending whether reorganization is needed

Exercise 32: Backup and Recovery

Design a complete backup and recovery strategy for a critical VSAM KSDS master file. Write: a) JCL for daily incremental backup (track changes since last backup) b) JCL for weekly full backup c) JCL for point-in-time recovery from backup d) A COBOL program that reads the audit trail and applies forward recovery

Tier 5: Critical Thinking and Real-World Scenarios (Exercises 33-40)

Exercise 33: VSAM vs. DB2 Decision

Your organization is designing a new customer information system. Management asks whether to use VSAM KSDS files or DB2 tables. Write a detailed analysis comparing the two approaches across these dimensions: - Development effort and complexity - Runtime performance for different workloads - Data integrity and recovery - Flexibility for ad-hoc queries - Operational support requirements - Cost considerations

Under what circumstances would you recommend each approach?

Exercise 34: Legacy Modernization

You inherit a 30-year-old VSAM-based inventory system with: - 5 KSDS master files - 12 alternate indexes - 47 batch COBOL programs - 8 CICS online programs - No documentation

Develop a strategy for understanding, documenting, and potentially modernizing this system. What tools and techniques would you use? What risks must you manage?

Exercise 35: Disaster Recovery Design

Design a disaster recovery procedure for a banking system that uses 20 VSAM KSDS files with a total of 500 million records across them. The recovery point objective (RPO) is 15 minutes and the recovery time objective (RTO) is 2 hours. Address: - Backup frequency and method - Journal/log management - Recovery procedures - Testing strategy - Capacity planning for the DR site

Exercise 36: Performance Crisis

A production VSAM KSDS file has suddenly started experiencing severe performance degradation. Batch jobs that normally complete in 30 minutes are now taking 4 hours. Online response times have increased from 200ms to 3 seconds. You have access to LISTCAT, SMF records, and RMF reports. Describe your diagnostic approach step by step.

Exercise 37: Concurrent Access Design

Five batch programs and three CICS regions need to access the same VSAM KSDS simultaneously. Some programs only read; others update. Design the SHAREOPTIONS, opening modes, and operational scheduling to maximize throughput while maintaining data integrity. Address the risks of read integrity and write integrity.

Exercise 38: Data Migration Project

You are migrating 50 VSAM KSDS files from one z/OS LPAR to another. Some files have alternate indexes. Some are referenced by CICS file control tables. Write a comprehensive migration plan that includes: - Inventory and dependency analysis - Definition migration (IDCAMS commands) - Data migration (REPRO or other methods) - Validation procedures - Cutover plan - Rollback procedures

Exercise 39: Capacity Planning

A VSAM KSDS customer file currently has 2 million records of 500 bytes each. The business expects 20% growth per year for 5 years, with 10,000 new records added daily and 500 deleted. Calculate: a) Required DASD space for each year (including free space) b) Optimal FREESPACE parameters for the growth pattern c) Recommended reorganization frequency d) Alternate index space requirements

Exercise 40: Teaching Exercise

Prepare a 30-minute training session for junior COBOL programmers on indexed file processing. Create: a) A one-page cheat sheet covering SELECT, OPEN, READ, WRITE, REWRITE, DELETE, and START b) A hands-on lab exercise they can complete in 15 minutes c) Three common mistakes to avoid, with code examples d) A troubleshooting guide for the five most common file status errors