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

problem in appending record to a ksds file


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

Active User


Joined: 31 Jan 2008
Posts: 148
Location: Chennai, India

PostPosted: Wed Apr 22, 2009 5:15 pm
Reply with quote

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
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA

PostPosted: Wed Apr 22, 2009 5:25 pm
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Apr 22, 2009 5:34 pm
Reply with quote

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
View user's profile Send private message
murugan_mf

Active User


Joined: 31 Jan 2008
Posts: 148
Location: Chennai, India

PostPosted: Wed Apr 22, 2009 6:00 pm
Reply with quote

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
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table

PostPosted: Wed Apr 22, 2009 6:09 pm
Reply with quote

or an attempt was made to write or rewrite a record that would create a duplicate prime record key
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA

PostPosted: Wed Apr 22, 2009 6:35 pm
Reply with quote

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
View user's profile Send private message
murugan_mf

Active User


Joined: 31 Jan 2008
Posts: 148
Location: Chennai, India

PostPosted: Wed Apr 22, 2009 6:54 pm
Reply with quote

Robert,

Yes, the DISP=MOD.
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table

PostPosted: Wed Apr 22, 2009 7:08 pm
Reply with quote

murugan_mf wrote:
Yes, the DISP=MOD.
Well, there's your problem.....
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA

PostPosted: Wed Apr 22, 2009 7:14 pm
Reply with quote

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
View user's profile Send private message
murugan_mf

Active User


Joined: 31 Jan 2008
Posts: 148
Location: Chennai, India

PostPosted: Wed Apr 22, 2009 7:33 pm
Reply with quote

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
View user's profile Send private message
murugan_mf

Active User


Joined: 31 Jan 2008
Posts: 148
Location: Chennai, India

PostPosted: Wed Apr 22, 2009 7:43 pm
Reply with quote

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
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA

PostPosted: Wed Apr 22, 2009 7:58 pm
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Wed Apr 22, 2009 9:30 pm
Reply with quote

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
View user's profile Send private message
murugan_mf

Active User


Joined: 31 Jan 2008
Posts: 148
Location: Chennai, India

PostPosted: Fri Apr 24, 2009 5:40 pm
Reply with quote

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
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA

PostPosted: Fri Apr 24, 2009 6:03 pm
Reply with quote

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
View user's profile Send private message
murugan_mf

Active User


Joined: 31 Jan 2008
Posts: 148
Location: Chennai, India

PostPosted: Fri Apr 24, 2009 6:58 pm
Reply with quote

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
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA

PostPosted: Fri Apr 24, 2009 7:02 pm
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Apr 24, 2009 7:07 pm
Reply with quote

Direct link to VSAM Demystified
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 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
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
Search our Forums:

Back to Top