In VSAM, Alternate index is little confusing concept for many mainframe programmers. Since VSAM is a legacy concept and many people are not using it. Now a days people are using DB2.
Why you need to go for alternate index…
Let me explain it with an example. Employee number is primary index. If I want to search a record with SSN(social security number), then I need to define SSN as secondary index.
How alternate index works
- Alternate index lets you access the records of a key-sequenced file in a sequence other than that provided by the file’s primary key (or base key).
- To understand how alternate indexes work, Here, the base cluster (the file over which an alternate index exists) is a KSDS containing employee records. Each record in the base cluster contains three fields: employee number, social security number, and department number. The primary key for the base cluster is employee number.
- As a result, you can access the base cluster sequentially by employee number using the browse commands, Or, you can read any record directly if you know the record’s employee number.
In addition to the primary key, the employee file has an alternate index for the social security number.
Joke-How do two programmers make money? One writes viruses, the other anti-viruses.
As you can see, this alternate index relates each alternate key value to a primary key value. So, as the shading indicates, when you tell VSAM to retrieve the record for the employee whose social security number is 565-37-1111, VSAM searches the alternate index, retrieves the primary key (1008), and uses that value to locate the correct record in the base cluster.
Because the alternate index is maintained in alternate key sequence, you can use it to process the employee file in social security number sequence.
What is unique key
In the first example, each alternate key is associated with a single primary key and, therefore, a single record. This type of alternate key is called a unique key.
What is duplicate key
In contrast, the second example an alternate index with non-unique, or duplicate, keys. Here, the alternate key is the department number, which can be related to one or more employee records.
To see how duplicate keys work, consider the alternate index record for department number 101.
Here, four employee numbers are specified: 1004, 1011, 1019, and 1023. When you use browse commands to process this alternate index sequentially, all four of these employee records are retrieved in primary key sequence.
When you process duplicate keys sequentially, you need to know how to determine whether there are additional records with the same key.
To do that, you use the DUPKEY exceptional condition. This condition is raised whenever you issue a READ, READNEXT, or READPREV command and at least one more record—not counting the one currently being read—exists with the same alternate key value.
If there are no more records with the same alternate key value, CICS returns the NORMAL condition instead.
One base cluster can have many alternate indexes(253). Each alternate index cluster will have relation with base cluster through path
Diagrammatically I will show you here, how these are related:
- When we want alternate indexes updated when base cluster is updated, Only indexes that are defined as upgradable are updated.
- Because upgrading alternate indexes requires a considerable amount of overhead, most alternate indexes are not upgradable. In that case, the alternate indexes must be recreated on a regular basis so they contain up-to-date entries.
Srinimf 
2 thoughts
Comments are closed.