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
 

 

Delete records from input file using count in Restart file

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
gotlamitla

New User


Joined: 31 Oct 2006
Posts: 11

PostPosted: Wed Feb 06, 2008 6:10 pm    Post subject: Delete records from input file using count in Restart file
Reply with quote

I have to process 10 million records from input file and insert into table. While doing so i am updating a file with the record count into a restart file for every commit on inserts. If job fails my intention is to continue to process & insert from the next record in the restart file.

To do this I would like to delete the Records in the input file till the committed Records if the job fails. I think ICETOOL will help me to do so.

At a Glance I can say that my requirement is to create a Step to Delete first X number of Records from Input file where X is the Value available in 1 to 10th Position of another Input File (Restart File).
Back to top
View user's profile Send private message

murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Wed Feb 06, 2008 8:32 pm    Post subject:
Reply with quote

Gotlamita,

Since you have count (say 'n') in a seperate file, why dont you use that value to build a dynamic sortcard and skip that number of records using STARTREC statement?
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Feb 06, 2008 9:36 pm    Post subject:
Reply with quote

Gotlamita,

You can use a DFSORT/ICETOOL job like the following to do what you asked for. It builds an OPTION SKIPREC=n statement using n from the count file, and uses it to delete the first n records from the input file.

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//CT  DD *
0000000005
/*
//IN  DD *
RECORD 01
RECORD 02
RECORD 03
RECORD 04
RECORD 05
RECORD 06
RECORD 07
RECORD 08
RECORD 09
/*
//CTL2CNTL DD DSN=&&C2,UNIT=SYSDA,SPACE=(TRK,(1,1)),DISP=(,PASS)
//OUT DD SYSOUT=*
//TOOLIN DD *
COPY FROM(CT) TO(CTL2CNTL) USING(CTL1)
COPY FROM(IN) TO(OUT) USING(CTL2)
/*
//CTL1CNTL DD *
  INREC BUILD=(C'  OPTION SKIPREC=',1,10,80:X)
/*


OUT will have:

Code:

RECORD 06
RECORD 07
RECORD 08
RECORD 09
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Wed Feb 06, 2008 11:06 pm    Post subject:
Reply with quote

Instead of reading the whole file and writing the records after the count to another file why not just skip them in the program when you do a restart.
Back to top
View user's profile Send private message
gotlamitla

New User


Joined: 31 Oct 2006
Posts: 11

PostPosted: Thu Feb 07, 2008 7:01 pm    Post subject:
Reply with quote

Hi Frank & Murali.Thanks for inputs

Creg

If my Jobs fails after 9 million records i have to perform un necessary reads in restart. If I can implement above logic i can start from First Record it self again.
Back to top
View user's profile Send private message
gotlamitla

New User


Joined: 31 Oct 2006
Posts: 11

PostPosted: Thu Feb 07, 2008 7:04 pm    Post subject:
Reply with quote

Murali

Can you explain how STARTREC will work.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Feb 07, 2008 7:09 pm    Post subject:
Reply with quote

gotlamitla wrote:
Hi Frank & Murali.Thanks for inputs

Creg

If my Jobs fails after 9 million records i have to perform un necessary reads in restart. If I can implement above logic i can start from First Record it self again.


Yes but in addition to reading the records to be skipped you also have to read and write to a new dataset the records remaining to be processed!
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Feb 07, 2008 9:30 pm    Post subject:
Reply with quote

Quote:
If my Jobs fails after 9 million records i have to perform un necessary reads in restart. If I can implement above logic i can start from First Record it self again.


With SKIPREC and STARTREC, the "skipped" records are still read - they just aren't "processed".

Quote:
Can you explain how STARTREC will work.


STARTREC is an operand of the OUTFIL statement:

Code:

    OUTFIL STARTREC=n


where n is the record you want to start from. So these are equivalent:

Code:

   OPTION SKIPREC=9000000


and

Code:

  OUTFIL STARTREC=9000001


You could use STARTREC instead of SKIPREC, but SKIPREC is generally more efficient.
Back to top
View user's profile Send private message
gotlamitla

New User


Joined: 31 Oct 2006
Posts: 11

PostPosted: Thu Feb 28, 2008 4:10 pm    Post subject: Reply to: Delete records from input file using count in Rest
Reply with quote

Thank you so much. I have one more requirement to delete n number rows from bottom of the file.I solved this using Count & Trailer1.Can we use above options (StartRec, Skip Rec) to delete N number of records records from bottom.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10231
Location: italy

PostPosted: Thu Feb 28, 2008 4:35 pm    Post subject: Reply to: Delete records from input file using count in Rest
Reply with quote

From an overall architectural point of view
modify the program in order to skip the already processed records is simpler...

in the other way You would have to ...
run a step to copy to a new dataset skipping the unwanted record
change the program jcl to read the new dataset

the counter will be absolute or relative ...
if the counter will be absolute then less problems
simply repeat the cycle

but if the counter is relative
You will have to modify the copy and the run jcl
in order to shift everithing

I stand by my opinion icon_biggrin.gif
use an absolute counter and skip the records
no additional jcls to create and modify in case of multiple reruns
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Feb 28, 2008 9:56 pm    Post subject:
Reply with quote

Quote:
Can we use above options (StartRec, Skip Rec) to delete N number of records records from bottom.


ENDREC or STOPAFT would be the option for that. For example, if you have 100 records and you want to delete the last 10, you could use:

Code:

   OPTION STOPAFT=90
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 -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Changing of LRECL of a file abdulrafi DFSORT/ICETOOL 2 Fri Mar 24, 2017 3:25 pm
No new posts splitting a file abdulrafi DFSORT/ICETOOL 3 Fri Mar 24, 2017 11:51 am
No new posts Count the length of the record & ... sreekusr DFSORT/ICETOOL 4 Thu Mar 23, 2017 7:52 pm
No new posts Receive a file using PCOMM macro Harald.v.K IBM Tools 0 Thu Mar 23, 2017 6:50 pm
No new posts Export flat file data into excel sheet murali.andaluri DFSORT/ICETOOL 2 Mon Mar 20, 2017 5:39 pm


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