ALTERNATE INDEX VSAM Training Class 04 ALTERNATE INDEXES

ALTERNATE INDEX VSAM Training Class 04

ALTERNATE INDEXES EMPNO ENAME SALARY 101 102 103 104 RAJESH 5000 RAMESH 6000 RANDY 7000 SURESH 8000 ALTERNATE INDEXES Used whenever the data is required to be retrieved on the basis of more than one field § Can be defined for both KSDS & ESDS but not RRDS

Advantage/Disadvantage of AIX Advantage : • Reduce data redundancy w. Can have duplicates w. Easy to define using IDCAMS w. Allow datasets to be accessed sequentially or randomly w. Can be updated automatically Disadvantage : w. Performance degradation w. Complex update logic

Steps to Create an Alternate Index 1. Define the alternate index using the IDCAMS DEFINE AIX command. 2. Specify an alternate index path using the IDCAMS DEFINE PATH command. The path forms a connection between the alternate index and the base cluster. While a path is a VSAM object, it does not contain any records. However, the path name becomes a catalog entry. 3. Build the alternate index and populate it with records using the IDCAMS BLDINDEX command.

Sample JCL to create an AIX cluster //FSS 197 XA JOB MSGCLASS=Q, NOTIFY=&SYSUID //DEFKSDS EXEC PGM=IDCAMS //SYSPRINT DD SYSOUT=* //SYSIN DD * DEFINE AIX (NAME(FSS 197. TEST. AIX) – RELATE (FSS 197. TEST. KSDS) VOLUMES(S 7 SYS 1) KEYS(10, 35) TRACKS(2, 1) NONUNIQUEKEY RECORDSIZE(49, 49) UPGRADE CONTROLINTERVALSIZE(4096) FREESPACE(10, 20)) DATA(NAME((FSS 197. TEST. AIX. DATA)) INDEX(NAME((FSS 197. TEST. AIX. INDEX)) /*

Required Parameter’s • Each alternate record contains the alternate key value along with the primary key value. For non-unique alternate keys, several records with different primary keys can have the same alternate key. • RELATE used to specify the base cluster to which the AIX is defined for. • KEYS used to specify the alternate key field • UPGRADE specifies that records in AIX are to be updated automatically whenever the base cluster is updated • RECORDSIZE: 5 + ( primary key + alternate key)

DEFINE PATH // FSS 197 XA JOB MSGCLASS=Q, NOTIFY=&SYSUID //STEP 1 EXEC PGM = IDCAMS //SYSPRINT DD SYSOUT=* //SYSIN DD * DEFINE PATH ( NAME (FSS 197. TEST. AIX. PATH) – PATHENTRY(FSS 197. TEST. AIX) – UPDATE ) /* //

Step 3: Building The Index 1. The data component of the base cluster is read sequentially , and pairs of key pointers are extracted. 2. This work file is sorted in ascending alternate key sequence. There is now a one-to-one ratio of alternate key to primary key or RBA. Then Vsam performs a sort. 3. If DEFINE AIX specifies NONUNIQUEKEY , a merge operation takes place at this point. 4. All of the work up to now has been performed on the data component of AIX and now VSAM constructs the index component.

BUILD INDEX // FSS 197 XA JOB MSGCLASS=Q, NOTIFY=&SYSUID //STEP 1 EXEC PGM=IDCAMS //SYSPRINT DD SYSOUT=* //SYSIN DD * BLDINDEX - INDATASET(FSS 197. TEST. KSDS) OUTDATASET(FSS 197. TEST. AIX) /*

Alternate Index Example

Define BASE CLUSTER

LOADING DATA INTO BASE CLUSTER

LOADING DATA INTO BASE CLUSTER

LOADING DATA INTO BASE CLUSTER

EXECUTING A PROGRAM TO LOAD DATA

EXECUTING A PROGRAM TO LOAD DATA

DEFINING AIX

Create path for Bridge

Build index

READING RECORDS BY ALTERNATE INDEX

READING RECORD BY ALTERNATE KEY

READING RECORD BY ALTERNATE KEY

EXECUTE A READ PROGRAM BY ALTERNATE KEY

SPOOL OUTPUT

Thank You Polsani Anil Kumar