View previous topic :: View next topic
|
Author |
Message |
murugan_mf
Active User
Joined: 31 Jan 2008 Posts: 148 Location: Chennai, India
|
|
|
|
Hi,
My requirement is to append records to a ksds file. I am unloading data from db2 table to a flat file and sorting the data in ascending order based on the key columns. Then i read the record from the flat file and tried appending to the ksds file.
I got record out of sequence error(file status 21). I figured out the cause for the error as the ksds file already contains record whose key value is greater than the key value of the first record in the flat file. So i am unable to append records from the flat file to the ksds file.
I have planned to proceed by copying the data from ksds file to a temporary sequential file and concatenate with the flat file which contains the data unloaded from db2 table and sort them in ascending order. Then open the sortout file in output mode and write the records into the ksds file.
My concern is when the volume of records in the ksds file is going to be large it will be a overhead to copy it to a temp sequential file and then concatenating & sorting. Is there any other better way to achieve the requirement. |
|
Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
|
|
|
|
I'd like to hear more about how you're planning on appending records to a KSDS file -- what exactly do you mean by "append records to a KSDS file"?
Once a KSDS file is built, you can REPLACE records in the file, you can INSERT records into the file, or you can DELETE records from the file. I've not heard of APPEND records to a KSDS file, though. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Robert,
I think that is just another example of 'miss-use of terms'.
Murugan L,
why not have the KSDS as one of the inputs to the sort,
and output to a KSDS file.
then sort has the overhead of 'so many records'. |
|
Back to top |
|
|
murugan_mf
Active User
Joined: 31 Jan 2008 Posts: 148 Location: Chennai, India
|
|
|
|
Robert,
Appending in the sense i will be opening the ksds file in the EXTEND mode
and insert records from the flat file into ksds file. Sorry for the confusion. |
|
Back to top |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007 Posts: 2146 Location: At my coffee table
|
|
Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
|
|
|
|
Quote: |
Appending in the sense i will be opening the ksds file in the EXTEND mode
and insert records from the flat file into ksds file. Sorry for the confusion.
|
And does this mean you're going to try to use DISP=MOD on a VSAM KSDS? |
|
Back to top |
|
|
murugan_mf
Active User
Joined: 31 Jan 2008 Posts: 148 Location: Chennai, India
|
|
|
|
Robert,
Yes, the DISP=MOD. |
|
Back to top |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007 Posts: 2146 Location: At my coffee table
|
|
|
|
murugan_mf wrote: |
Yes, the DISP=MOD. |
Well, there's your problem..... |
|
Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
|
|
|
|
From the JCL Language Reference manual, link at the top of the page:
Quote: |
MOD
Indicates one of the following:
* The data set exists and records are to be added to the end of it. The data set must be sequential.
* A new data set is to be created.
In either case, MOD specifies exclusive (unshared) use of the data set. |
We know the second condition doesn't apply -- the VSAM KSDS is not a new data set to be be created. We know the data set is not sequential, so the first condition does not apply. So exactly why is it that you think you can specify DISP=MOD on a VSAM KSDS? Obviously not from reading the manual!
DISP=OLD is an option although I am not sure if you can insert records other than at the end of the file; that would require some testing I can't do at this time.
Your other option would be to write a program to insert the records -- not a lot of lines of COBOL, and pretty straightforward to boot.
Or you can unload, sort in the new records with the unloaded records, delete, define, reload the file via IDCAMS. |
|
Back to top |
|
|
murugan_mf
Active User
Joined: 31 Jan 2008 Posts: 148 Location: Chennai, India
|
|
|
|
Hi,
I thought if i am going to open the ksds file in OUTPUT mode the cursor will be pointing to the first record and the records from the flat file will override the old records in the ksds file. But by doing so i ended up with an error. I think i need to empty the Ksds file before writing records into it. |
|
Back to top |
|
|
murugan_mf
Active User
Joined: 31 Jan 2008 Posts: 148 Location: Chennai, India
|
|
|
|
Robert,
I tried with DISP=OLD, but that too ended in error. I have written a program to insert records to the KSDS file and i am trying to empty
to ksds file before inserting records. I will let you know what i find. |
|
Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
|
|
|
|
First, DB2 uses cursors. VSAM doesn't.
Second, to overwrite (not override) the records in the KSDS file, you must read for update and then rewrite the record. If you just attempt to write the record, you'll get a duplicate key error.
Third, KSDS files do not have to be empty to be updated with new and replaced records -- I've written (and seen) many, many, many programs that do just that (both in batch and CICS). However, the logic must be done correctly or you'll not get the data updated.
One possible way (there are others):
Code: |
Read flat file record
If not EOF
Move flat file data to KSDS record area
Write KSDS record
IF duplicate key error
READ KSDS record for update
Move flat file data to KSDS record area
REWRITE KSDS record
EndIf
EndIf |
|
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Suggest you try the following:
Sort the "new records" in the appropriate sequence.
Unload the vsam file to a qsam file.
Delete/define the vsam file.
Merge the 2 files into the vsam file (there is no need to sort the existing vsam records).
I suspect the sort will do this much faster than code. Either way, you may have to deal with duplicates. |
|
Back to top |
|
|
murugan_mf
Active User
Joined: 31 Jan 2008 Posts: 148 Location: Chennai, India
|
|
|
|
Dick,
Instead of Delete/Define the vsam ksds , can i empty the vsam by copying a Dummy dataset to vsam ksds using repro command.
Particularly when the volume of records is going to be large please let me know which of above said ways will be effective. |
|
Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
|
|
|
|
VSAM files are not the same as sequential files. Using REPRO to copy a dummy dataset to a VSAM file does not erase the VSAM file. It is merely a very quick copy since the REPRO hits the end-of-file marker and quits before writing any data.
You can write a program to read and delete every record in the file, or you can use IDCAMS DELETE / DEFINE -- but if you want that file empty you absolutely must do something about every record. One of the tradeoffs between VSAM and sequential files is that VSAM doesn't let you take some of the shortcuts you can use with a sequential file -- but then you can go directly to a key and read the data without reading anything else in the file. |
|
Back to top |
|
|
murugan_mf
Active User
Joined: 31 Jan 2008 Posts: 148 Location: Chennai, India
|
|
|
|
Robert,
Thanks for the explanation. I am new to VSAM but trying to understand the concepts as quick as possible. Thanks again for the food for thought. |
|
Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
|
|
|
|
Murugan: If you're new to VSAM, go to the IBM Redbooks site www.redbooks.ibm.com and find the manual SG24-6105, VSAM Demystified. It's not a small book but it will give you a solid foundation in VSAM. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
Back to top |
|
|
|