05 - Vsam Alternate Indexes

VSAM Alternate Indexes

Department of Computer Science Northern Illinois University August 2005

Introduction to Alternate Indexes • Alternate Indexes (AIX) – Used in KSDS – similar to prime index but indexes on values of some field other than the prime key – can provide an additional way of retrieving the data in a KSDS

2

Introduction to Alternate Indexes • Alternate Indexes Example – Student Information • primary key: • alternate index: • alternate index:

Student-ID Student-Name Student-Major

3

Introduction to Alternate Indexes • Records – are maintained in logical sequence based upon the primary key values via the prime index – are also maintained in logical sequence based upon values of alternate key via additional indexes called alternate indexes

4

Introduction to Alternate Indexes • Alternate indexes are created using the DEFINE command in IDCAMS

5

KSDS Base Cluster

6

KSDS Alternate Index

7

KSDS Alternate Index • Alternate Index Component – Each pointer record contains • the primary key value • pointer into AIX data component

8

Processing via KSDS Alternate Index • Program passes alternate key value to VSAM • VSAM proceeds: – searches the index component index to locate the appropriate CI in the alternate index data component

9

Processing via KSDS Alternate Index • VSAM proceeds: – reads in the CI from the AIX data component and locates the record that corresponds to the supplied alternate key value • this record contains the primary key value of the desired base cluster record

– the primary key value just obtained is used to search the primary index as normal 10

Processing via KSDS Alternate Index • VSAM proceeds: – the CI is read from the base cluster and the desired record is extracted from it and passed to the application program

• VSAM performs 2 separate index searches to locate the desired record – first the AIX – second the primary index 11

ESDS Alternate Index • ESDS clusters can also be referenced by alternate indexes – Note: RRDS cannot have AIX

• Structure is similar to that of the KSDS AIX

12

ESDS Alternate Index

13

ESDS Alternate Index • VSAM: – searches the index component of the AIX to locate the appropriate CI in the AIX data component – reads in the CI and locates the particular record in the data component that corresponds to the supplied alternate key value • this record contains a direct RBA pointer to the desired record in the data component of ESDS 14 base cluster

ESDS Alternate Index • VSAM: – the CI that contains the desired record is read from the data component of the ESDS is extracted from it based on the RBA value obtained from the AIX

15

Paths and AIX • Path – combination of an AIX and the base cluster – has separate set of attributes defined separately from attributes of AIX or base cluster – use DEFINE PATH to create a path – define both base cluster and AIX first

16

Paths and AIX

17

Paths and AIX • If an application program refers to base cluster itself – AIX is not used

• If an application program refers to an AIX itself – records are retrieved from the data component of the AIX – base cluster is not referenced 18

Paths and AIX • If an application program wants to access records from a base cluster via an AIX – use a path

• The program supplies an AIX key value and VSAM searches the AIX to find the corresponding AIX / pointer pair • A path must be defined for each AIX – actual DEFINE covered later

19

Nonunique Alternate Key Values • In KSDS – primary key must have unique values – when an insertion of a duplicate key value is attempted an invalid key is issued

• AIX may be either – unique or – nonunique 20

Nonunique Alternate Key Values • If AIX is defined as unique, VSAM does not allow duplicate AIX key values • If AIX is defined as nonunique VSAM allows records to be loaded and inserted that have the same value in the AIX field

21

Nonunique Alternate Key Values • In AIX – there is one record in the data component for each unique AIX key value – has multiple pointer to associated records in the base cluster

22

Nonunique Alternate Key Values

23

Upgrade in AIX • When updates to base cluster - AIX may need updating • VSAM can be directed to perform all AIX updates automatically • Specify UPGRADE option in DEFINE of AIX • All AIX of a cluster with upgrade option are known as the base cluster’s upgrade set 24

Upgrade in AIX • Upgrade Set – when base cluster is updated, the corresponding updates are applied to all AIXs in the upgrade set

• Application program has to update any AIXs not in the upgrade set

25

Defining a KSDS Alternate Index • Using IDCAMS – DEFINE ALTERNATEINDEX • creates a catalog entry for the alternate index • allocates space for the AIX • associate the AIX with a base cluster

26

Defining a KSDS Alternate Index DEFINE ALTERNATEINDEX (NAME (znumber.aixname) RELATE (zumber.KSDSname) VOLUMES(ACA301) RECORDSIZE(avgsize maxsize) KEYS(length displacement) TRACKS(1 1) )

-

27

Calculating RECORDSIZE • Calculating Maximum RECORDSIZE – RecordSize = a + b + (n * c) • where – – – – –

a=5 (the control bytes in a CI) b = alternate key length c = primary key length n= 1 OR n= estimated maximum number of primary keys associated with one AIX value

28

Calculating RECORDSIZE • Calculating Average RECORDSIZE – RecordSize = a + b + (n * c) • where – – – – –

a=5 (the control bytes in a CI) b = alternate key length c = primary key length n= 1 OR n= estimated average number of primary keys associated with one AIX value

29

Defining a Path for an AIX • Using IDCAMS – DEFINE PATH • gives name to a path • specifies the name of the alternate index it references • base cluster is not specified because the AIX definition specifies the name of the base cluster

30

Defining a Path for an AIX DEFINE PATH ( NAME (znumber.pathname) PATHENTRY(znumber.aixname) )

-

31

Building an AIX • Using IDCAMS – BLDINDEX • loads the alternate index • AMS – reads the base cluster sequentially and constructs alternate key/pointer pairs for each record – sorts the alternate key/pointer pairs into ascending alternate key sequence – uses the sorted alternate key/pointer pairs to load records into the data component of the AIX 32

Defining a KSDS Alternate Index • Go over page 41 of course notes showing – DEFINE ALTERNATEINDEX – DEFINE PATH – BLDINDEX

33

Processing KSDS in COBOL Using AIX • A KSDS with an AIX can be processed – sequentially – randomly – dynamically

34

JCL for Using AIX • Need to specify – KSDS data set • //ddname DD DSN=….

– AIX date set(s) • • • •

//ddname1 DD DSN=…. //ddname2 DD DSN=…. …. //ddnamen DD DSN=…. 35

JCL for Using AIX • Example JCL //GO.MASTER DD DSN=znumber.KSDS,DISP=SHR //GO.MASTER1 DD DSN=zumber.PATH2,DISP=SHR //GO.MASTER2 DD DSN=znumber.PATH2,DISP=SHR //….

36

File-Control for AIX in KSDS •

File-Control. SELECT file-name ASSIGN to DA-ddname ORGANIZATION IS INDEXED ACCESS MODE IS [SEQUENTIAL | RANDOM | DYNAMIC RECORD KEY IS recordKeyName ALTERNATE RECORD KEY IS aixKey1 [WITH DUPLICATES] ALTERNATE RECORD KEY IS aixKey2 [WITH DUPLICATES] …. ALTERNATE RECORD KEY IS aixKeyn [WITH DUPLICATES] FILE STATUS IS statusCodeField. 37

File-Control for AIX in KSDS • ALTERNATE RECORD KEY IS aixKey1 [WITH DUPLICATES] – specifies the name of an alternate key field for the KSDS file – one per AIX that will be used in the program in same order as specified in the IDCAMS (not all have to be coded all the time) – WITH DUPLICATES specifies that the AIX 38 key value is non-unique

AIX Sequential Processing • Specify key to use in the START statement MOVE spaces/zeros TO aixKey1. START ksdsFileName KEY NOT < aixKey1. – Where aixKey1 is the name of the alternate key as specified in ALTERNATE RECORD KEY IS aixKey1

• Subsequent READ statements retrieve the records in alternate key sequence 39

AIX Sequential Processing • When AIX key has duplicate values the status code contains values – 00 - last record with AIX key value has been successfully read – 02 - a record has been read successfully and there are more with this AIX key value – 23 - no record found with the specified AIX key value 40

AIX Sequential Processing • PROCEDURE DIVISION code for processing sequential reads of duplicate AIX key values – Read page 45 in class notes

41

AIX Random Processing • Only the READ and REWRITE statements are coded differently – READ statement change READ ksds-fileName RECORD INTO identifier KEY IS aixKey1 INVALID KEY imperative-statement.

– To use AIX key, the KEY IS clause must be coded 42

AIX Random Processing • Only the READ and REWRITE statements are coded differently – REWRITE statement change • the AIX key value can be changed via the rewrite • Note: primary key value cannot be changed this way - thus the change

43