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

searching for a record in vsam


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Arch

New User


Joined: 30 Apr 2010
Posts: 11
Location: USA

PostPosted: Tue Jul 27, 2010 2:36 am
Reply with quote

Hi, I have a vsam file and a sequential input file. In vsam, I have a unique id (as key) and a non key field which will have name+address.

My requirement is, I need to read the sequential file which will have name+address details, go to the vsam file and search for this value(non key field). If this value is present in vsam, i need to extract the corresponding unique id and write into a output file. If search found no record, i need to create the unique id, and insert unique id and name+address detail into vsam file. I am doing this whole process with cobol.
The problem I am facing here is while doing a search. I cannot read the vsam file sequentially because it will have 40-50 million records. I cannot do binary search as the data in vsam is not sorted.

can you please help me to solve this search issue?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Jul 27, 2010 2:49 am
Reply with quote

Hello,

Why can you not directly read the vsam file with the key from the sequential file?

Is the vsam file a ksds?
Back to top
View user's profile Send private message
Arch

New User


Joined: 30 Apr 2010
Posts: 11
Location: USA

PostPosted: Tue Jul 27, 2010 2:54 am
Reply with quote

I will not have the Key in sequential file. I will only get non key(name+address) in sequential file.

Yes, VSAM is KSDS.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Jul 27, 2010 3:02 am
Reply with quote

Hello,

Then create an alternate index for the name/address that is in both files.

Or, copy the vsam data to a sequential file, sort this by name/address and match this against the sequential data, creating a file for the new inserts.

How will a new unique key be generated for the records that need to be added to the vsam file?
Back to top
View user's profile Send private message
Arch

New User


Joined: 30 Apr 2010
Posts: 11
Location: USA

PostPosted: Tue Jul 27, 2010 3:20 am
Reply with quote

Hi thanks for the quick response.

copying and sorting would affect the performance right?
I don't know much about vsam. when I searched in google, I got something about this alternate key, but I didn't get any pseudo code or examples on how to do read and compare with input record using alternate key.

I know something like this:

PERFORM UNTIL VSAM-EOF OR WS-FOUND
READ VSAM-FILE
IF WS-addr-value =addr-value
set WS-FOUND TO true
end if
end perform
but this is a sequential search and end up searching whole file right?
Please let me know how i can search using alternate key.

creating unique id: unique id starts with ACXNA-000000001. Every time the program executes, it will first read the last ID and then starts incrementing as ACXNA-00000002, ACXNA-00000003 etc.. to read the last record, I am moving high value to key, and doing read prev.

Thanks
Back to top
View user's profile Send private message
Prasun De

New User


Joined: 17 Jan 2008
Posts: 28
Location: Kolkata, INDIA

PostPosted: Tue Jul 27, 2010 7:13 am
Reply with quote

You can always search IBM manuals for syntax....Anyway as stated earlier...
you should use AIX to solve your purpose.

Dump your cluster into sequential file & sort on the column you will search on and load it back to the cluster..

Define alternate index and use it normally for search.. AIX is like any other cluster. read it with the alternate key, it will read the first matching row.
Then do read next to get the other records.

Refer http://ibmmainframes.com/about148.html to get the syntax.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Jul 27, 2010 9:02 am
Reply with quote

Hello,

Quote:
copying and sorting would affect the performance right?
Maybe, maybe not so much. . .

How many records are in the vsam file? How long are the reords?

Which sort product is used on the system?

There are multiple topics in the forum that discuss alternate index. There is a forum SEARCH function (above in the blue line).
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Tue Jul 27, 2010 11:24 am
Reply with quote

Prasun De wrote:
Dump your cluster into sequential file & sort on the column you will search on and load it back to the cluster.
Huh. What would this achieve. The cluster would still retain the key sequence that it was constructed with. I am sure that you meant to contribute something usefull here, but not quite sure of what it is.
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


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

PostPosted: Tue Jul 27, 2010 11:42 am
Reply with quote

Dick,

Quote:

How many records are in the vsam file?


40-50 million records, like the TS stated in his first post. That will be
quite a sort run. So like stated an AIX would do the job. But if there
are millions of duplicates there will be a problem in building the AIX.
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Tue Jul 27, 2010 12:01 pm
Reply with quote

PeterHolland wrote:
40-50 million records, like the TS stated in his first post. That will be
quite a sort run. So like stated an AIX would do the job. But if there
are millions of duplicates there will be a problem in building the AIX.
So maybe use ye olde method of post code / zip code for comparison would do the trick.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Jul 27, 2010 7:21 pm
Reply with quote

Hi Peter,

Quote:
40-50 million records, like the TS stated in his first post.
Missed/forgot that as the dialog continued. . . icon_redface.gif

Yup, that would probably impact performance icon_smile.gif

The only reservation i had about the alternate index (not posted until now) is if there are too many duplicate addresses. . . The length of the key will be substantial and if there many duplicates, the max occurrances could be exceeded.
Back to top
View user's profile Send private message
Arch

New User


Joined: 30 Apr 2010
Posts: 11
Location: USA

PostPosted: Tue Jul 27, 2010 9:00 pm
Reply with quote

Hi, thanks for the replies.. I am trying to put it as AIX. One more thing is, I will have unique values.. there will be no duplicates records.. I am just thinking what if i declare that as partial key? will it work?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Jul 27, 2010 9:19 pm
Reply with quote

Hello,

Quote:
will it work?
This will depend on how much of the address is needed to ensure the value is unique. . .

Possibly there is something i misunderstand, but i'd probably use the entire value for the key.
Back to top
View user's profile Send private message
Arch

New User


Joined: 30 Apr 2010
Posts: 11
Location: USA

PostPosted: Tue Jul 27, 2010 9:38 pm
Reply with quote

I am using the complete address (addr line1, line2,city,state,zip) as key. But what I am doing here is, before inserting into vsam, i am creating this as a string-addr line1|line2|city|state|zip. So my vsam will have 2 columns. One having unique id which will be sequence number, and this address string. Initially i thought of making sequence number alone as key, but now instead of AIX, I am thinking of putting address string as partial key. This string will be maximum of 200 bytes.

Please let me know.
Back to top
View user's profile Send private message
Arch

New User


Joined: 30 Apr 2010
Posts: 11
Location: USA

PostPosted: Tue Jul 27, 2010 10:58 pm
Reply with quote

I have one more question regarding this. Does max function work for vsam file? can i use like max (unique-id) in cobol program where unique-id is one of the field in vsam file.
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 -> JCL & VSAM

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 7
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 FINDREP - Only first record from give... DFSORT/ICETOOL 3
No new posts To find whether record count are true... DFSORT/ICETOOL 6
Search our Forums:

Back to Top