View previous topic :: View next topic
|
Author |
Message |
gotlamitla
New User
Joined: 31 Oct 2006 Posts: 11
|
|
|
|
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 |
|
|
murmohk1
Senior Member
Joined: 29 Jun 2006 Posts: 1436 Location: Bangalore,India
|
|
|
|
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 |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
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 |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
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 |
|
|
gotlamitla
New User
Joined: 31 Oct 2006 Posts: 11
|
|
|
|
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 |
|
|
gotlamitla
New User
Joined: 31 Oct 2006 Posts: 11
|
|
|
|
Murali
Can you explain how STARTREC will work. |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
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 |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
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:
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 |
|
|
gotlamitla
New User
Joined: 31 Oct 2006 Posts: 11
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10873 Location: italy
|
|
|
|
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
use an absolute counter and skip the records
no additional jcls to create and modify in case of multiple reruns |
|
Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
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:
|
|
Back to top |
|
|
|