IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Searching a ksds file using an alternate index


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
PrasadV

New User


Joined: 21 Mar 2004
Posts: 8
Location: Chennai

PostPosted: Sun Mar 21, 2004 6:15 pm
Reply with quote

Sir,

I have a cobol program with the following file declaration.(KSDS).

01 cust.

02 cno x(5).
02 filler x(5).
02 cname x(14).

my cname is the alternate key.
i have created an AIX in VSAM.

how shall i search a record from my aix using cname.
pls. do answer me with an example program.
Back to top
View user's profile Send private message
franclin

New User


Joined: 23 May 2003
Posts: 11
Location: Chennai, India

PostPosted: Mon Mar 22, 2004 8:58 am
Reply with quote

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
Back to top
View user's profile Send private message
PrasadV

New User


Joined: 21 Mar 2004
Posts: 8
Location: Chennai

PostPosted: Wed Mar 24, 2004 5:00 pm
Reply with quote

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>>
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Access to non cataloged VSAM file JCL & VSAM 18
No new posts Need help for File Aid JCL to extract... Compuware & Other Tools 23
Search our Forums:

Back to Top