VSAM Alternate-Key Duplicate Status Stops the Promotion Extract
Ridgeline Retail's nightly promotion extract reads a VSAM KSDS customer file. The primary key is CUST-ID. An alternate key, REGION-CODE, allows duplicates. The program must write every customer in region 'NE'. It issues START on REGION-CODE with key 'NE', then loops with READ NEXT. The FILE STATUS field is tested after each I/O. In test with one NE customer, it works. In production, only the first NE customer is written. When the operator removes the duplicate-key condition by splitting regions into single-customer test files, all records appear. The program also occasionally writes a 'SE' customer just after the NE block. The current code treats any status other than '00' as end-of-file. The file has 48 NE customers; 12 of them share a region code with another customer, and region 'SE' follows 'NE' in collating sequence.
1. Diagnose why only the first NE customer is written and why an SE customer can leak into the output.
Read the worked answer
On a VSAM alternate index that allows duplicates, a READ NEXT after the first duplicate returns file status 02, meaning a duplicate alternate key was encountered. Status 02 is a successful read, not end-of-file. Because the program treats any status other than 00 as end, it stops at the first duplicate. That explains why only the first NE customer appears: the next NE customer sharing the same region key triggers 02.
The SE leak is a separate boundary problem. START positions at the first alternate key greater than or equal to 'NE'. The loop does not compare the returned REGION-CODE with 'NE'; it keeps reading until an error status or end-of-file. After the last NE record, the next alternate key is 'SE', which is a normal status 00 read, so the program writes it. The missing key comparison and the incorrect treatment of 02 combine to produce both symptoms.
2. Write the corrected loop logic, including status handling and the region boundary.
Read the worked answer
The loop must treat 00 and 02 as successful reads, stop on true end-of-file, and stop when the alternate key leaves 'NE':
MOVE 'NE' TO WS-REGION-KEY
MOVE 'N' TO WS-DONE
START CUSTOMER-FILE KEY IS >= WS-REGION-KEY
INVALID KEY MOVE 'Y' TO WS-DONE
END-START
PERFORM UNTIL WS-DONE = 'Y'
READ CUSTOMER-FILE NEXT
AT END MOVE 'Y' TO WS-DONE
NOT AT END
EVALUATE WS-FILE-STATUS
WHEN '00' CONTINUE
WHEN '02' CONTINUE
WHEN OTHER MOVE 'Y' TO WS-DONE
END-EVALUATE
IF WS-DONE = 'N'
IF REGION-CODE = 'NE'
PERFORM 500-WRITE-PROMOTION
ELSE
MOVE 'Y' TO WS-DONE
END-IF
END-IF
END-READ
END-PERFORM
Status 23 on START means no matching key; 10 on READ NEXT means end-of-file. The region comparison must occur after a successful read and before writing. A single NE customer test cannot exercise duplicate status or the boundary, so test with at least three NE customers, two sharing a region, and an SE record immediately after.
3. The file owner proposes making REGION-CODE a unique alternate key. Evaluate that proposal.
Read the worked answer
A unique alternate key would be wrong for this data. Multiple customers legitimately belong to one region, so the alternate index must allow duplicates. Making it unique would either fail when the index is built or force the upstream to invent artificial region values, breaking the business meaning of the key.
The correct fix is to handle duplicate status 02 and enforce the region boundary in the program. If the requirement were truly one customer per region, a unique key would be appropriate, but the 48 NE customers and 12 duplicates show the opposite. If performance is a concern, the program could read the entire file sequentially and filter, but that is usually worse than a properly coded alternate-index loop. A separate work file or in-memory table of NE customers could be considered only if the extract needs multiple passes or sorting.