15th COBOL Lecture

------------------------------------------------------------------

Creating an alternate indexes for a VSAM files requires
3 commands using the IDCAMS Utility program.

------------------------------------------------------------------
1) Define the alternate index component

//DEFINE   EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN    DD    *
    DEFINE AIX(NAME(alternate-index-filename) -
     RELATE(primary-filename) -
     RECORDS (primary secondary) VOLUME (volid) -
     KEYS (length offset) SHAREOPTIONS(option) -
     NONUNIQUEKEY)

2) Build the alternate index component.

    The BLDINDEX command will read the primary file and create records by
        copying the key and alternate key fields,
        sorting the data by alternate key field
        inserting the sorted records into the alternate index

//BUILD       EXEC   PGM=IDCAMS
//SYSPRINT DD SYSOUT=X
//SYSIN DD *
      BLDINDEX INFILE (PRIMEI) OUTFILE (ALTERI)
//PRIMEI DD DSN=primary-filename,DISP=SHR
//ALTERI DD DSN=alternate-index-filename,DISP=OLD

3) Define a path to associate the alternate index component
to the primary file (base cluster).

//PATH EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=X
//SYSIN DD *
DEFINE PATH (NAME(path-name) -
PATHENTRY (alternate-index-filename) )
---------------------------------------------------------------------
The IDCAMS utility program can print the base cluster In the sequence
of the alternate index.

//PRINT    EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//INDATA    DD DSN=path-name,DISP=SHR
//SYSIN     DD *
   PRINT INFILE(INDATA) CHARACTER
---------------------------------------------------------------------
ALTERNATE INDEXES / ALTERNATE KEYS

The alternate key records are stored in ascending order
based on the value of the alternate key.

May contain unique values or non-unique, repetitive values.

Locate a record or a set of records by some data field
other than the primary key.

Sequence and print a file by some field other than the primary key.

Like the primary key, the alternate key must have a fixed length
and a fixed position within each record.

Alternate index files reference the prime index
not the CI of the data component.

The file can be positioned to some point other than
the first record to begin sequential processing.

Updates to the data portion may also cause the system to
update separate index files.

-------------------------------------------------------------------------

Select statement for KSDS with a unique alternate key

SELECT File-name ASSIGN TO ddname
ORGANIZATION IS INDEXED
ACCESS IS [SEQUENTIAL | RANDOM | DYNAMIC ]
RECORD KEY IS primary-key-field
ALTERNATE RECORD KEY IS alternate-key-field
FILE STATUS IS ws-status-field.

----------------------------------------------------------

Select statement for KSDS with a non-unique alternate key

-----------------------------------------------------------
Select statement for KSDS with multiple alternate keys

SELECT File-name ASSIGN TO ddname
ORGANIZATION IS INDEXED
ACCESS IS DYNAMIC
RECORD KEY IS primary-key-field
ALTERNATE RECORD KEY IS alternate-key-field1
WITH DUPLICATES
ALTERNATE RECORD KEY IS alternate-key-field2
ALTERNATE RECORD KEY IS alternate-key-field3
WITH DUPLICATES
FILE STATUS IS ws-status-field.

-----------------------------------------------------------
Question: How many alternate keys does VS COBOL allow?
-----------------------------------------------------------

I/O statements that can be used with alternate keys.

READ (RANDOM)

To issue a random read, use the KEY IS phrase

        MOVE some-value TO alternate-key-field3
        READ File-name
            KEY IS alternate-key-field3
            INVALID KEY executes if the record is not found
            NOT INVALID KEY executes if record is found
        END-READ

        Random READ will always find the first occurrences
        of a duplicate key but never find any of the
        subsequent duplicated keys.

START can name an alternate keys

        MOVE some-value TO alternate-key-field1
        START file-name KEY [ > | = | >= | NOT < ] alternate-key-field1
            INVALID KEY executes if the record is not found
            NOT INVALID KEY executes if record is found
        END-START

        Use start statements to position before the first occurrence
        of a duplicate key then issue READ … NEXT to get the subsequent
        duplicated key records.

READ (SEQUENTIAL)

        READ file-name NEXT RECORD
            AT END will execute when the end of the file is reached
            NOT AT END will execute if not at end of the file
        END-READ

A sequential READ is done after START positioning to the first record.
-----------------------------------------------------------
I/O statements that do not use alternate keys.

DELETE

Will occur if a record with that primary key exists,

The content value of the secondary keys has no effect
on the delete operation.

REWRITE

The primary key cannot be altered.

        The alternate key may be modified by the program
        and the system can update the data record and affected indexes
        (delete/insert)

WRITE

The primary key cannot be a duplicate of one that exists in the file.

The alternate index keys may not be duplicates if the index is unique.

-----------------------------------------------------------
JCL to refer to a master and its alternate indexes

SELECT File-name ASSIGN TO aname
ORGANIZATION IS INDEXED
ACCESS IS DYNAMIC
RECORD KEY IS primary-key-field
ALTERNATE RECORD KEY IS alternate-key-field1
WITH DUPLICATES
ALTERNATE RECORD KEY IS alternate-key-field2
ALTERNATE RECORD KEY IS alternate-key-field3
WITH DUPLICATES
FILE STATUS IS ws-status-field.

//aname DD DISP=SHR,DSN=primary-filename
//aname1 DD DISP=SHR,DSN=path-name1
//aname2 DD DISP=SHR,DSN=path-name2
//aname3 DD DISP=SHR,DSN=path-name3

-----------------------------------------------------------