How to Make an Index File Tutorial
The index command creates or re-creates an index file. It can be used in READY mode, or from a procedure file. This is useful for re-indexing a file after changes have been made through another program.
In the program we've been working on so far, you could create an index file based on a particular part of the file. Perhaps name, city, or Item 1 purchase.
The index command's syntax is as follows:
INDEX data-filename index-filename 1st key start pos 1st key length action
The available parameters for action are: LISTDUPKEYS, DUPKEYS, NATIVE, REORG, and REPLACE.
The following line would create an INDEX.FIL of the file called MASTER.FIL:
INDEX MASTER.FIL INDEX.FIL 10 5 REPLACE
This will create (or recreate if it already exists) an index file for the Master File, since REPLACE is specified. The key used is the section from position 10 to 15 of the file.
To create an index file, with a key of city, for the ORDERS.int file we are working on would be:
INDEX ORDERS.int cityindex.int 91 15 REPLACE
What would the index file be like if we based it on last name? Do this now.
Now what if there were two customers with the same last name? Using the DUPKEYS parameter will send you a message if there are duplicates:
INDEX ORDERS.int lastindex.int 31 30 REPLACE DUPKEYS
and the LISTDUPKEYS parameter will display a list of such duplicates:
INDEX ORDERS.int lastindex.int 31 30 REPLACE LISTDUPKEYS
To handle this, you can create an index file using two or more keys, perhaps with first and last name like so:
INDEX ORDERS.int firstandlast.int 31/1 30/30 REPLACE
the slash (/) separates the first and second second keys' start point and size (number of positions in the file).
Next, just to practice, create an index file based on state and city.
As you can imagine, it's important for files to have unique keys. Using time and date within a record is one way of differentiating each entry.
After you have created an index file, we can assume that new records are going to be added to the original file, and so of course we'd like to update the index at the same time. In order to do this, return to the original program and add the following syntax to the OPEN statement:
… kfname=indexname,kps=key-start-position,kln=key-length... keyed
where index name is the name of your index file, kps indicates key start position, and kln is the key length.
in our case it would be:
so that the complete OPEN statement in the main program is:
Open #1: "name=orders.int, recl=118,USE,kfname=lastfirst.int,kps=31/1,kln=30/30", internal, OUTIN, keyed
Since we created more than one index file, an OPEN statement must be added to each to open the main file with the index and add new records to each index file. The second, for example, will be:
Open #1: "name=orders.int, kfname=items.int,kps=116/117/118,kln=1/1/1", internal, OUTIN, keyed
Since INDEX is a command and not a statement, it needs the EXECUTE statement to run within a program. After you have added or made changes to the data file, you will need to replace the index file with a new one. For example, this one replaces the lastname/firstname key file.
5000 EXECUTE “INDEX ORDERS.int lastfirst.int 31/1 30/30 REPLACE”