Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Retrieve records in the order of alternate index

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> Compuware & Other Tools
View previous topic :: :: View next topic  
Author Message
jacobdng

New User


Joined: 27 Aug 2006
Posts: 49
Location: Taiwan

PostPosted: Fri Jul 31, 2015 8:16 am    Post subject: Retrieve records in the order of alternate index
Reply with quote

I need to retrieve certain records in the order of an alternate index.
The target VSAM file has 5 million records.
The first screen is as the following:
Quote:
File-AID ------------- Browse - Dataset Specificati FUNCTION ENDED-ATTENTION
COMMAND ===>

Browse Mode ===> C (F=Fmt; C=Char; V=Vfmt; U=Unfmt)

Specify Browse Information:
Dataset name or HFS path ===> 'NBSDDSN2.ZBNBTZM.PATH
Member name ===> (Blank or pattern for member list)
Volume serial ===> (If dataset is not cataloged)


Specify Record Layout and XREF Information:
Record layout usage ===> S (S = Single; X = XREF; N = None)
Record layout dataset ===> 'NBSPCMN.FNETBOOK.LIBRYCPY
Member name ===> ZBNBTZMC (Blank or pattern for member list)
XREF dataset name ===>
Member name ===> (Blank or pattern for member list)

Specify Selection Criteria Information: (E = Existing; T = Temporary;
Selection criteria usage ===> Q M = Modify; Q = Quick; N = None)
Selection dataset name ===>
Member name ===> (Blank or pattern for member list)


The second screen is
Quote:
File-AID ------------- Unformatted Selection Criteria ------ Row 1 to 1 of 1
COMMAND ===> SCROLL ===> PAGE

Use END to continue, CANCEL to return to main screen.

AND
Cmd /OR Position Length RO Data Value
--- --- -------- ------ -- ----------------------------------------------------
___ 15 8 EQ T'20150731'
************************** END OF SELECTION CRITERIA **************************


Please advise what the best way is to retrieve records via a VSAM path.
Back to top
View user's profile Send private message

Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8154
Location: East Dubuque, Illinois, USA

PostPosted: Fri Jul 31, 2015 8:38 am    Post subject:
Reply with quote

What do you mean by "best way"? When you retrieve records from a KSDS, you've got really three options:
1. sequentially read them from the first to the last which gives them to you in primary key sequence
2. randomly retrieve them based on something other than key sequence
3. use the alternate index path to retrieve them in the alternate index sequence (this option is not always available -- not all VSAM KSDS have alternate indexes defined)

Where is your problem? What have you tried? What is not working? Do you want to retrieve the data online (hint: 5 million records will take a LOT of enter keys), or through a batch program (and if batch, are you writing one yourself or using a utility)?
Back to top
View user's profile Send private message
jacobdng

New User


Joined: 27 Aug 2006
Posts: 49
Location: Taiwan

PostPosted: Mon Aug 03, 2015 12:30 pm    Post subject:
Reply with quote

I want to retrieve records in the alternate index sequence using File-Aid. But, TSO had been logged off automatically long before the outcome appeared on the screen. I had been waiting for 45 minutes at least.

However, it took less that 15 minutes to retrieve the 64 records out of 4 million if I changed the dataset name to KSDS itself instead of the alternate index. The selecting criteria was only one field(date).

Did I miss something in the operation process of File-Aid when the dataset name is an alternate index?
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2441
Location: Netherlands, Amstelveen

PostPosted: Mon Aug 03, 2015 12:41 pm    Post subject: Reply to: Retrieve records in the order of alternate index
Reply with quote

Did you use the alternate key?
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8154
Location: East Dubuque, Illinois, USA

PostPosted: Mon Aug 03, 2015 6:04 pm    Post subject:
Reply with quote

Is the selection field the alternate index? If not, you are attempting to sequentially read all 5 million records and that could take a while. You are not explaining very much about what you want to do, so the quality of your answers will be poorer than if you provided a full and complete explanation.

Have you contacted the vendor for assistance? They are MUCH more likely to be able to answer questions of this type since they know the product much better than anyone on this forum.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Mon Aug 03, 2015 6:30 pm    Post subject: Reply to: Retrieve records in the order of alternate index
Reply with quote

To serially process 5m records on the alternate index, assuming that each record has an alternate-index entry, requires 5m keyed-reads.

This is going to be very slow, and use a lot of IO and use a lot of CPU.

In short, don't do it.

It will be much more resource-efficient to read the file sequentially on the main key, SORT on the alternate key and process the output from the SORT.

The alternate-index is a key to a key. The data is then accessed using the key to the key to the data. Toss in three or more levels of index.

Keep your alternate-index usage to "look-up" and preferably only in the online or with as minimal actual accesses as possible.
Back to top
View user's profile Send private message
jacobdng

New User


Joined: 27 Aug 2006
Posts: 49
Location: Taiwan

PostPosted: Tue Aug 04, 2015 1:09 pm    Post subject:
Reply with quote

Answer to Robert & Peter:
I did use the alternate index in the selection field.
Please refer to my initial description, there is a copy screen of File-Aid Browse, which I specifically use the name "NBSDDSN2.ZBNBTZM.PATH " as the brwosing target and it is the alternate index.

>>>>>>>>>>>>>>>

I understand that DFSORT should be involved in batch programming in terms of retrieving records out of huge volume.

I thought that File-Aid had magic to retrieve data swiftly in the order of an alternate index. Obviously, I was wrong.

Thanks for your info.
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2441
Location: Netherlands, Amstelveen

PostPosted: Tue Aug 04, 2015 1:29 pm    Post subject: Reply to: Retrieve records in the order of alternate index
Reply with quote

My question was : Did you use the alternate key?

That is not the same as the "index"
Back to top
View user's profile Send private message
jacobdng

New User


Joined: 27 Aug 2006
Posts: 49
Location: Taiwan

PostPosted: Tue Aug 04, 2015 2:15 pm    Post subject:
Reply with quote

Peter,

Please refer to my second quote :

The item (15,8) is a date field which is part of the alternate key (15,24).

Jacob
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> Compuware & Other Tools All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Confusion b/w index and subscript Deepak kumar25 Mainframe Interview Questions 7 Thu Aug 31, 2017 6:50 am
No new posts Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 21 Sun Aug 27, 2017 10:35 pm
No new posts Sorting on text - but in non-alphabet... Roy Ware SYNCSORT 5 Wed Aug 23, 2017 9:15 pm
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us