VSAMadvanced
Alternate Indexes
Understand how alternate indexes let a VSAM base cluster be accessed by a second, non-primary key without duplicating the underlying data.
A KSDS can be read by its primary key. An alternate index lets the same file be read by a different field, without a second copy of the data.
An alternate index is a second index card catalog at the same library, this one organized by author's last name instead of by call number, both catalogs pointing back to the exact same physical books on the exact same shelves — no book is ever duplicated, but every time a book is reshelved, both catalogs must be updated to stay accurate.
Key Concepts
1
The problem it solves. A customer file keyed by customer id, and the business also needs to find
customers by phone number.
2
without an AIX: a second file sorted by phone number
-> two copies to keep in step on every update
-> and they will diverge3
with an AIX: one data file, one extra index
-> VSAM maintains it automatically4
What the alternate index contains. Not the records — the alternate key, and the primary key it points
to:
5
phone 9876543210 -> customer C0004521
phone 9988776655 -> customer C00012346
A read by phone number finds the primary key and then the record.
7
A path connects them. A PATH is a catalog entry joining the alternate index to its base cluster. The
COBOL program opens the path, and reads through it behave as though the alternate key were the file's key.
8
DEFINE AIX (NAME(PROD.CUST.AIX) -
RELATE(PROD.CUSTOMER.KSDS) -
KEYS(10 40) -
UNIQUEKEY)
DEFINE PATH (NAME(PROD.CUST.PATH) -
PATHENTRY(PROD.CUST.AIX))
BLDINDEX INDATASET(PROD.CUSTOMER.KSDS) OUTDATASET(PROD.CUST.AIX)9
BLDINDEX populates it from the existing data. An AIX defined and never built is empty, and reads through
it find nothing.
BLDINDEX
10
Unique or non-unique. UNIQUEKEY means one base record per alternate key. NONUNIQUEKEY allows several
— which is what you want for a last name, where reading by key returns each match in turn.
UNIQUEKEYNONUNIQUEKEY
11
The cost is on every write. Each alternate index must be updated on every insert, update of the indexed
field, and delete against the base cluster.
12
base cluster with 3 alternate indexes
-> one INSERT does 4 pieces of index work13
That is the same trade as a secondary index in DB2, and drawing the parallel explicitly is worth doing —
it is the same structure solving the same problem with the same write penalty.
14
And the upgrade setting. An AIX defined as UPGRADE is maintained automatically; NOUPGRADE is not,
and must be rebuilt. The second is faster for bulk loading and is a quiet source of stale indexes when
somebody forgets the rebuild.
UPGRADENOUPGRADE
15
What the interviewer is probing.1. "What does an alternate index give you?" Probing: the purpose. Stalls: "Speed." *Moves
up:* access by a field other than the primary key, so the same file can be read by account number
and by customer name.
16
2. "What is a path?" Probing: the vocabulary. Stalls: "A route." Moves up: the object that
ties the alternate index to the base cluster, and what the program actually opens.
17
3. "What is the maintenance cost?" Probing: the trade. Stalls: "None." Moves up: updates
must maintain it — either automatically with the UPGRADE attribute or by running BLDINDEX, which is
where staleness creeps in.
18
4. "Can an alternate key be non-unique?" Probing: the detail. Stalls: "No." Moves up: yes,
with NONUNIQUEKEY — several base records can share it, and the path returns them in turn.