franclin
New User
Joined: 23 May 2003
Posts: 11
Location: Chennai, India
|
|
|
|
Hi Prasad,
What is Alternate Index:
Normally a VSAM file is accessed via Primary key. This primary key cannot have duplicate values. In practical applications, one would need to access the VSAM file by way of fields other than the primary key also. In such cases, Alternate index is used for accessing a VSAM file. Alternate index can have duplicate values also. Without the existence of Alternate index, one would have to browse through all the records in the VSAM file.
In this article, we are going to see the COBOL programming and JCL considerations for accessing Alternate Index based on 2 other fields other than the primary key.
Following are the steps in the creation of AIX:
1. Define AIX (One has to provide the alternate key position, key length, base cluster name apart from other necessary parameters)
2. Build Alternate Index (This will load the records from the base cluster to the AIX cluster)
3. Define Path (Relate this to Alternate Index file. This will be the link between Base and AIX)
Let me cash in on the World Cup fever gripping our nation by giving the following example. The following is how a base cluster looks:
ID-NO PLAYER-NAME COUNTRY-CODE FILLER SILL-CODE REST-OF-DATA
5/AN 17/AN 2/AN 1/AN 2/AN 53/AN
1------- 2---------------- 3----------- 4------- 5--------- 6-----------
10001 Adam Gilchrist 10 WK
10002 Sachin Tendulkar 20 BT
10003 Glen McGrath 10 BO
10004 Michael Bevan 10 BT
10005 Sourav Ganguly 20 BT
Each player is given a unique identification no (ID No) being the primary key.
Case I: One would like to query on this file for players belonging to a particular country. By using the AIX and path, one does not have to browse through all the records in the file. The AIX and path for the above base cluster with Country-code as the alternate key will look as below:
File-AID - Browse - TCS015.VSAM.TEST.AIX1
----+ -- --1----+----2----+----3
********************************
10 100011000310004
20 1000210005
File-AID - Browse - TCS015.VSAM.TEST.PATH1
----+----1----+----2-- -- +----3----+----4-
********************************* TOP OF DA
10001Adam Gilchrist 10 WK
10003Glen McGrath 10 BO
10004Michael Bevan 10 BT
10002Sachin Tendulkar 20 BT
10005Sourav Ganguly 20 BT
Case II: One would like to query on this file for the batsmen of all countries. The AIX and path with Skill Code as the alternate key will look as below:
File-AID - Browse - TCS015.VSAM.TEST.AIX2
----+ -- --1----+----2----+----3----+----4
********************************* TOP OF D
BO 10003
BT 100021000410005
WK 10001
File-AID - Browse - TCS015.VSAM.TEST.PATH2
----+----1----+----2----+ -- --3----+----4-
********************************* TOP OF DA
10003Glen McGrath 10 BO
10002Sachin Tendulkar 20 BT
10004Michael Bevan 10 BT
10005Sourav Ganguly 20 BT
10001Adam Gilchrist 10 WK
Please observe carefully the way the records are arranged in the above 2 cases in the AIX as well as the path. The AIX has the relationship established between the alternate key and primary key whereas the path has the records re-arranged in the order of the alternate key, thus establishing the logical link.
How does alternate index works:
The alternate index is initially searched for the alternate key and the primary key is obtained from that. Based on the primary key obtained from the alternate index, the records in the base cluster are searched. This involves more coding in the part of the programmer and this can be avoided by defining the path.
The path creates the logical link between the base and alternate index. Thus, the usage of path along with the base cluster, automatically opens the alternate index cluster and obtains the primary key, thereby avoiding any extra coding effort by the programmer.
Programming considerations:
For the above example of accessing the base cluster thru 2 alternate indices, piece of COBOL code is given below:
FILE-CONTROL.
SELECT TESTVSAM-FILE ASSIGN TO TESTVSAM
ACCESS IS DYNAMIC
ORGANIZATION IS INDEXED
RECORD KEY IS ID-NO
ALTERNATE KEY IS COUNTRY-CODE WITH DUPLICATES
ALTERNATE KEY IS SKILL-CODE WITH DUPLICATES
FILE STATUS IS TEST-FILE-STATUS.
Code to get the name of Australian Cricketers (10 being the country-code for Australia)
MOVE '10' TO COUNTRY-CODE WS-COUNTRY-CODE.
START TESTVSAM-FILE KEY IS EQUAL COUNTRY-CODE
DISPLAY 'AUSTRALIAN CRICKETERS'
PERFORM 2000-MAIN-PROCESS THRU 2000-EXIT
UNTIL NO-MORE-RECORDS = 'Y' OR EOF-FLAG = 'Y'.
Code to get the name of the Batsmen
MOVE 'BT' TO SKILL-CODE WS-SKILL-CODE.
START TESTVSAM-FILE KEY IS EQUAL SKILL-CODE.
DISPLAY 'BATSMEN'
PERFORM 2500-MAIN-PROCESS THRU 2500-EXIT
UNTIL NO-MORE-RECS = 'Y' OR EOF-FLAG = 'Y'.
2000-MAIN-PROCESS.
READ TESTVSAM-FILE NEXT
IF COUNTRY-CODE IS NOT EQUAL TO WS-COUNTRY-CODE
MOVE 'Y' TO NO-MORE-RECORDS
GO TO 2000-EXIT
END-IF
2500-MAIN-PROCESS.
READ TESTVSAM-FILE NEXT
IF SKILL-CODE IS NOT EQUAL TO WS-SKILL-CODE
MOVE 'Y' TO NO-MORE-RECS
GO TO 2500-EXIT
END-IF
SYSOUT(Result of the program execution)
AUSTRALIAN CRICKETERS
NAME OF THE PLAYER : Adam Gilchrist
NAME OF THE PLAYER : Glen McGrath
NAME OF THE PLAYER : Michael Bevan
BATSMEN
NAME OF THE PLAYER : Sachin Tendulkar
NAME OF THE PLAYER : Michael Bevan
NAME OF THE PLAYER : Sourav Ganguly
NAME OF THE PLAYER : Jhonty Rhodes
JCL Considerations:
There is no need for a File control entry for AIX and Paths in the program. The base cluster file and path names need to be provided in the JCL while executing the program as below:
//TESTVSAM DD DISP=SHR,DSN=TCS015.VSAM.TEST
//TESTVSA1 DD DISP=SHR,DSN=TCS015.VSAM.TEST.PATH1
//TESTVSA2 DD DISP=SHR,DSN=TCS015.VSAM.TEST.PATH2
The DD name of the paths should be named as first 7 characters of the DD name of the base cluster followed by 1,2 etc. If the DD name of the base cluster is less than 8 characters, then the DD name of the path should be DD name of the base cluster followed by 1,2 etc.
Note: 1. Alternate index can be allocated on a base cluster, only when the REUSE option of base cluster is set to NO.
2. Alternate index when created with Upgrade Option = "Y", will result in automatic updation of the AIX whenever a change happens in the base cluster. Else an explicit BLD INDEX need to be done.
3. For more details on the syntax and other rules relating to Alternate Index, please refer Chapter 17 of "The MVS JCL Primer" by Saba Zamir and Chander Ranade
Regards,
Francis Suman.S |
|
PrasadV
New User
Joined: 21 Mar 2004
Posts: 8
Location: Chennai
|
|
|
|
Hi Francis,
I am very thankful to u for sending such a wonderful reply to me.
i tried it and found working.
Thanx a lot.I would be very happy if u could let me know ur email-id,
for further clarifications and dicussions.
my e-mail id is <<email removed>> |
|
