The concept of Index, Primary key and Alternative key and Alternative index creates many questions, much confusion in the mind of COBOL programmers. There are many simple and powerful solutions for Alternative Key and Alternative index files.
Alternative Index file:
An alternate index file consists of a file that is keyed by your desired key and contains a pointer to the primary key in your base file.
You will have to write the program that creates this alternate index file. To use the alternate index file, you perform your start and read operations first. Then, using the primary key pointer information you find, access the base file to obtain the real record.
The alternate index file does not need to contain any more information than the two related keys.
If this alternate index file is to be kept synchronized with your base file, you will have to be sure to code your programs accordingly. If the alternate index file is used infrequently, it may be more efficient to create it each time it is required for use.
Why we need to go for Alternative index
01 The-Record. 03 Prime-Key. 05 Last-Name-1 PIC X(30). 05 First-Name-1 PIC X(20). 03 Address-1 PIC X(50). 03 City-1 PIC X(30). 03 State-1 PIC X(2). 03 Postal-Code-1 PIC X(10).
You might want to access this data via a key that is Postal-Code, Address, First-Name, Last-Name. You may not be able to define an alternate key using these fields because they are not contiguous in your data record. An alternate index data file would be ideal.
Different methods accessing COBOL file in reverse order:
- Use what commonly is called the 9s complement. The key field must be numeric and less than 18 digits for this to work. What you do is subtract all 9s from the key field, and use the result as the key field value. For example, if the key is PIC S9(5), you subtract 99999 from the key. The resultant numbers will be in reverse key sequence.
- Another method works whether or not the key is numeric. The key must be less than eight characters long. If it is, you can move the primary key field to a signed binary field and multiply the contents by –1. This works as long as none of the key fields has a low value in it.
- One final method involves first sorting the key values in descending order, and then assigning a sequence number as the key in the alternate index file. You should leave large gaps in the numbers assigned, however, to allow room for future file additions. If you take this approach, you will be responsible for calculating and assigning a new sequence number to keys that are added.
- Remember that when you create a separate alternate index file, you are responsible for keeping it in sync with the other files. Also, do not confuse alternate index files with VSAM alternate indexes. VSAM alternate indexes are closely associated with the base file and provide the standard alternate record key support found in COBOL.
One final method
A START statement first must be issued, followed by a READ. Some use READ PREVIOUS RECORD, and others use READ PRIOR RECORD. In IBM’s CICS, an EXEC CICS command is provided to read records in reverse key order.
- Accessing a record from other than primary key is called Alternative key
- Alternative keys performance wise bad compared to Alternative indexed files