Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 41
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: 7913
Location: Bellevue, IA

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: 41
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: 2422
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: 7913
Location: Bellevue, IA

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: 7236

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: 41
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: 2422
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: 41
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 Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us