6. Creating Alternate Indexes

An Alternate Index AIX provides a view of data different from the one offered by the primary key. For example for a KSDS dataset Employee, you may have a Record Key index on Employee-no and an Alternate Index on Employee-Name . You can now browse and even update the same KSDS in logical sequence by Employee-Name.

Alternate Indexes may be defined on one or more than one Alternate Key(s) i.e. Field(s) other than primary key. Alternate Key(s) need not be unique. Each alternate index itself is a KSDS with data and index component.

Alternate Index greatly reduces redundancy. There is no need to keep a separate dataset for different views like Employees’ Social Security No. The records may be accessed sequentially or randomly based on the alternate record keys.

They can be updated automatically when the base cluster is updated.

Alternate Indexes do not support a reusable base cluster. So NOREUSE which is the default, should be specified.

Too many Alternate Indexes built on a KSDS may lead to performance Degradation as access by alternate key requires twice as many I/O’s . VSAM first locates the primary key from the alternate index and then locates the Control Interval information from the record key index.


For ESDS, VSAM builds AIX by mapping one field to the record’s RBA.

Steps for defining and building alternate indexes:
DEFINE AIX Command
Define the Alternate Index Cluster using the IDCAMS DEFINE AIX command.




//STEP1 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT =*
//SYSIN DD *
DEFINE AIX -
(NAME(DA0001T.LIB.KSDS.AUTHNAME.AIX)
-
VOLUMES (BS3013) -
RELATE(DA0001T.LIB.KSDS.CLUSTER)
-
UPGRADE -
TRACKS(10 1)
-
KEYS(25 9) -
RECORDSIZE(70 110)
FREESPACE(20 10)
-
SHAREOPTIONS(1) -
NONUNIQUEKEY) -
)
DATA(NAME(DA000A1T.LIB.KSDS.AUTHNAME.DATA)) -
INDEX(NAME(DA0001T.LIB.KSDS.AUTHNAME.INDEX)
/*
//

Example 6.1 JCL to define AIX


Pathname is the dataset name in JCL (DSN=PATHNAME)
RELATE Parameter

Format:
RELATE(base cluster name)

This parameter establishes the relationship between the base cluster and the alternate index via the use of the base cluster name. It is unique to the DEFINE AIX command, and it is required.

The RECORDSIZE Parameter

Format:
RECORDSIZE(avg max)

This parameter specifies the average and maximum length of each alternate index record. There are two types of alternate indexes.

KSDS unique alternate index: You can create a unique alternate index by specifying the UNIQUEKEY parameter. The records of unique alternate indexes are of fixed length. The length of a unique alternate index built over a KSDS is derived as follows:








Figure 6.1 Contents of KSDS unique alternate index

For example if an unique alternate index on Soc-Sec-No is built on our KSDS cluster Employee then the RECORDSIZE will be calculated as follows:-

5 Bytes fro HouseKeeping + size of alternate key + Size of Primary Key that the alternate

= 5 + 9 + 8 = 22

Therefore recordsize parameter will be coded as RECORDSIZE(20 20)


KSDS non-unique alternate index: An alternate index created with a NONUNIQUEKEY parameter has variable length records. The RECORDSIZE is calculated as follows:-

Avgerage Record length = 5 bytes for House Keeping + size of the alternate key + size of the primary key x average no of records the alternate index key can point to

Maximum Record length = 5 bytes for House Keeping + size of the alternate key + size of the primary key x maximum no of records the alternate index key can point to

DEFINE PATH Command:

Define an Alternate Index Path using the IDCAMS DEFINE PATH command. The path forms a connection between the alternate index and the base cluster. Path name becomes a catalog entry but path does not contain any records. The path name is specified in the JCL for applications that access records via the alternate index.




//STEP1 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT =*
//SYSIN DD *
DEFINE PATH -
NAME(DA0001T.LIB.KSDS.AUTHNAME.PATH) -
PATHENTRY(DA0001T.LIB.KSDS.AUTHNAME.AIX) -
UPDATE -
)
/*
//


Example 6.2 JCL to define PATH for the AIX

UPDATE vs NOUPDATE

Records may be accessed by applications by the alternate index path alone, without opening the base cluster. In such cases any changes made to data will be reflected in the alternate index records if the UPDATE option is specified. If NOUPDATE is specified then the alternate index records will not be automatically updated.
UPGRADE vs. NOUPGRADE

The UPDATE/NOUPDATE option of DEFINE PATH works in tandem with the UPGRADE / NOUPGRADE of the DEFINE AIX command.

UPGRADE specifies that any changes made in the base cluster records will be reflected immediately in the alternate index records if the base cluster is opened in the application. Fortunately UPGRADE and UPDATE are defaults for their respective commands.

Building Alternate Indexes

The final step in creating an alternate index is to actually build and populate it with records.
The BLDINDEX command does the following:

• The data component of the base cluster is read sequentially and pairs of key pointers are extracted. These pairs consist of the alternate key field and its corresponding primary key field. VSAM creates a temporary file with these records.

• This temporary file is sorted in ascending alternate key sequence.

• If NONUNIQUEKEY option is specified then a merge operation takes place, which will merge all records with the same alternate key into a single record.

• These records are the data component of the Alternate Index. VSAM now constructs the index component just as it does for the KSDS.

Note: The Alternate Index can be built only after the base cluster has been both defined and loaded with atleast 1 record.


//STEP1 EXEC PG=IDCAMS
//SYSPRINT DD SYSOUT =*
//DD1 DD DSN=DA0001T.LIB.KSDS.CLUSTER,
// DISP=OLD
//IDCUT1 DD UNIT=SYSDA,SPACE=(TRK, (2, 1))
//IDCUT2 DD UNIT=SYSDA,SPACE=(TRK, (2, 1))
// SYSIN DD *
BLDINDEX -
INFILE(DD1) -
OUTDATASET(DA0001T.LIB.KSDS.AUTHNAME.AIX) -
INTERNALSORT
/*
//
Example 6.3 JCL to build Alternate Index


Disposition of base cluster is DISP=OLD as the BLDINDEX needs absolute control of the base cluster.Output dataset can be Alternate index cluster or pathname

The INTERNALSORT uses virtual storage whereas EXTERNAL SORT uses disk space. INTERNALSORT is the default. If you want an external sort to be performed then include IDCUT1 and IDCUT2 DD statements in your JCL and specify EXTERNALSORT in the BLDINDEX command.


DEFINE Cluster
(NAME(DA0001T.LIB.KSDS.CLUSTER)
.
)
DEFINE AIX
(NAME(DA0001T.LIB.KSDS.AUTHNAME.AIX) RELATE(DA0001T.LIB.KSDS.CLUSTER)
.
)
DEFINE PATH (NAME(DA0001T.LIB.KSDSK.AUTHNAME.PATH) PATHENTRY(DA0001T.LIB.KSDS.AUTHNAME.AIX)
.
)
BLDINDEX
INDATASET(DA0001T.LIB.KSDS.CLUSTER) OUTDATASET(DA0001T.LIB.KSDS.AUTHNAME.AIX)
.
)

Example 6.4 Steps for creating and building AIX

Back to VSAM index