|
View previous topic :: View next topic
|
| Author |
Message |
Arch
New User
Joined: 30 Apr 2010
Posts: 11
Location: USA
|
|
|
|
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 |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Arch
New User
Joined: 30 Apr 2010
Posts: 11
Location: USA
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Arch
New User
Joined: 30 Apr 2010
Posts: 11
Location: USA
|
|
|
|
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 |
|
|
Prasun De
New User
Joined: 17 Jan 2008
Posts: 28
Location: Kolkata, INDIA
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8796
Location: Welsh Wales
|
|
|
|
| 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 |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen
|
|
|
|
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 |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8796
Location: Welsh Wales
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hi Peter,
| Quote: |
| 40-50 million records, like the TS stated in his first post. |
Missed/forgot that as the dialog continued. . . 
Yup, that would probably impact performance 
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 |
|
|
Arch
New User
Joined: 30 Apr 2010
Posts: 11
Location: USA
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
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 |
|
|
Arch
New User
Joined: 30 Apr 2010
Posts: 11
Location: USA
|
|
|
|
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 |
|
|
Arch
New User
Joined: 30 Apr 2010
Posts: 11
Location: USA
|
|
|
|
| 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 |
|
|
|
|
