View previous topic :: View next topic
Author
Message
D Rishi New User Joined: 01 Mar 2010Posts: 21 Location: India
Hi,
Here is my two files say File1 & File2 & both the files are having Duplicate records. File1 & File 2 are of different Record lengths.
(only the first 29 bytes which is the matching field for the two files)
File1:
004085993|560000260|20110126|
004085993|560000260|20110126|
004085993|560000260|20110126|
004064634|560000264|20110126|
004064634|560000264|20110126|
004064634|560000264|20110126|
004064634|560000264|20110126|
004060475|560001338|20110126|
004060475|560001338|20110126|
004060475|560001338|20110126|
File2:
004020438|560000052|20110228|
004020438|560000052|20110228|
004085993|560000260|20110126|
004064634|560000264|20110126|
004064634|560000264|20110126|
004062810|560000272|20110316|
004062810|560000272|20110316|
I need only the records from the File1 that are not matching with File2 records with File1's format..
Here the output should be only
004060475|560001338|20110126|
004060475|560001338|20110126|
004060475|560001338|20110126|
I tried with the jcl:
Code:
//SORTJNF1 DD DSN=File1,DISP=SHR
//SORTJNF2 DD DSN=File2,DISP=SHR
//SORTOUT DD DSN=SORTOUT,
// UNIT=SYSDA,
// SPACE=(CYL,(500,50),RLSE),
// DISP=(,CATLG,CATLG)
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(1,29,A)
JOINKEYS FILES=F2,FIELDS=(1,29,A)
JOIN UNPAIRED,F1,ONLY
REFORMAT FIELDS=(F1:1,144)
OPTION COPY
But giving positional error so need help..
1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R5 - 03:47
JOINKEYS FILES=F1,FIELDS=(1,29,A)
$
0 STATEMENT DEFINER ERROR
JOINKEYS FILES=F2,FIELDS=(1,29,A)
$
0 STATEMENT DEFINER ERROR
JOIN UNPAIRED,F1,ONLY
$
0 STATEMENT DEFINER ERROR
REFORMAT FIELDS=(F1:1,144)
$
0 STATEMENT DEFINER ERROR
OPTION COPY
Back to top
Escapa Senior Member Joined: 16 Feb 2007Posts: 1399 Location: IL, USA
Back to top
Escapa Senior Member Joined: 16 Feb 2007Posts: 1399 Location: IL, USA
Also,
LRECL\RECFM of input file1?
LRECL\RECFM of input file2?
LRECL\RECFM of output file?
Back to top
D Rishi New User Joined: 01 Mar 2010Posts: 21 Location: India
File1: RECFM = FB / LRECL = 144
File2: RECFM = FB / LRECL = 133 or 152 (not decided yet)
Output is equal to File1
RECFM = FB / LRECL = 144
Back to top
dbzTHEdinosauer Global Moderator Joined: 20 Oct 2006Posts: 6966 Location: porcelain throne
Quote:
1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R5 - 03:47
Back to top
Escapa Senior Member Joined: 16 Feb 2007Posts: 1399 Location: IL, USA
dbzTHEdinosauer wrote:
Quote:
1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R5 - 03:47
I don't think thats enough to find which PTFs are applied..
Code:
//S1 EXEC PGM=ICEMAN
//SYSOUT DD SYSOUT=*
//SORTIN DD *
RECORD
/*
//SORTOUT DD DUMMY
//SYSIN DD *
OPTION COPY
/*
D Rishi, Execute above at your shop and show us SYSOUT.(please use code tag while posting...
Back to top
D Rishi New User Joined: 01 Mar 2010Posts: 21 Location: India
Hi Sambhaji,
Thanx for the info.. Seems ICEtool is working.. as per the links test code you sent.
Can you please give the code if its handy for you in ICETOOL..
Thanks,
D Rishi
Back to top
Nic Clouston Global Moderator Joined: 10 May 2007Posts: 2455 Location: Hampshire, UK
You still have not posted the output that Escapa requested. It is required to see what level of DFSORT PTFs you have installed. Once that is known then the experts can give you a solution that will work with your level.
Back to top
D Rishi New User Joined: 01 Mar 2010Posts: 21 Location: India
ICE250I 0 VISIT www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES A
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R5 - 05:15 ON THU AP
OPTION COPY
ICE201I F RECORD TYPE IS F - DATA STARTS IN POSITION 1
ICE751I 0 C5-K90013 C6-K90013 C7-K90000 C8-K90013 E9-K90013 C9-BASE E5-K31980
ICE142I 0 SRTMSG NOT FOUND - SYSOUT USED
ICE193I 0 ICEAM1 ENVIRONMENT IN EFFECT - ICEAM1 INSTALLATION MODULE SELECTED
ICE088I 0 I04273BX.STEP020 . , INPUT LRECL = 80, BLKSIZE = 80, TYPE = FB
ICE093I 0 MAIN STORAGE = (MAX,6291456,6278238)
ICE156I 0 MAIN STORAGE ABOVE 16MB = (6200798,6200798)
ICE127I 0 OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y
ICE128I 0 OPTIONS: SIZE=6291456,MAXLIM=1048576,MINLIM=450560,EQUALS=N,LIST=Y,ER
ICE129I 0 OPTIONS: VIO=N,RESDNT=ALL ,SMF=FULL ,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT
ICE130I 0 OPTIONS: RESALL=4096,RESINV=0,SVC=109 ,CHECK=Y,WRKREL=Y,OUTREL=Y,CKPT
ICE131I 0 OPTIONS: TMAXLIM=6291456,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=Y,CFW
ICE132I 0 OPTIONS: VLSHRT=N,ZDPRINT=Y,IEXIT=N,TEXIT=N,LISTX=N,EFS=NONE ,EXIT
ICE133I 0 OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=MAX ,ODMAXBF=0,SOLRF=Y,VLLONG=N,VSAM
ICE235I 0 OPTIONS: NULLOUT=RC0
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTOUT
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTIN
ICE751I 1 EF-K10929 F0-K30362 E8-K90013
ICE090I 0 OUTPUT LRECL = 80, BLKSIZE = 80, TYPE = FB
ICE055I 0 INSERT 0, DELETE 0
ICE054I 0 RECORDS - IN: 1, OUT: 1
ICE052I 0 END OF DFSORT
Here is the SYSOUT msg i am copy pasting. Don't know if its okay for you to apprehend.
Thank you Nic.[/img]
Back to top
Garry Carroll Senior Member Joined: 08 May 2006Posts: 1193 Location: Dublin, Ireland
DFSORT V1.5 introduced JOINKEYS by PTF s UK51706 and UK51707. Are these applied to your installation?
Garry.
Back to top
Frank Yaeger DFSORT Developer Joined: 15 Feb 2005Posts: 7129 Location: San Jose, CA
D R,
The ICE201I F message indicates you do NOT have the JOINKEYS PTF. So here's a DFSORT/ICETOOL SPLICE job that will do what you asked for:
Code:
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=... input file1 (FB/144)
//IN2 DD DSN=... input file2 (FB/?)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=... output file (FB/144)
//TOOLIN DD *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,29,CH) WITHALL KEEPNODUPS KEEPBASE -
WITH(1,145) USING(CTL3)
/*
//CTL1CNTL DD *
INREC OVERLAY=(145:C'BB')
/*
//CTL2CNTL DD *
INREC OVERLAY=(145:C'VV')
/*
//CTL3CNTL DD *
OUTFIL FNAMES=OUT,INCLUDE=(145,2,CH,EQ,C'VV'),
BUILD=(1,144)
/*
Back to top
D Rishi New User Joined: 01 Mar 2010Posts: 21 Location: India
Hi Frank,
I tried with this query but seems to be not working as required as all the records of both the files have merged.
What I need is that the only those records from File1 which all are not matching with File2.
(In other words: File2 may have many records but only those records that are also in File1 should be excluded from File1)
Don't know If my requirement is clear now.
Can you please help..
Thanks,
D R
Back to top
D Rishi New User Joined: 01 Mar 2010Posts: 21 Location: India
Hi,
In the above code I tried to do a little change like..
SPLICE FROM(T1) TO(OUT) ON(1,29,CH) WITHALL KEEPNODUPS KEEPBASE -
WITH(1,145) USING(CTL1)
&
//CTL1CNTL DD *
INREC OVERLAY=(145:C'BB')
OUTFIL FNAMES=T1,REMOVECC
But it also not worked. I don't have much idea Please help!!
Thanks,
D R.
Back to top
Escapa Senior Member Joined: 16 Feb 2007Posts: 1399 Location: IL, USA
D Rishi wrote:
Hi Frank,
I tried with this query but seems to be not working as required as all the records of both the files have merged.
What I need is that the only those records from File1 which all are not matching with File2.
(In other words: File2 may have many records but only those records that are also in File1 should be excluded from File1)
Don't know If my requirement is clear now.
Can you please help..
Thanks,
D R
Solution given by Frank works as per your requirement.
Did you notice, IN2 is copied to temp file first and then IN1?
Back to top
D Rishi New User Joined: 01 Mar 2010Posts: 21 Location: India
Thank you Sambhaji, for pointing out...
I got the difference & it is working fine...
Frank, Like every time before, again thanking you but with my sincere apologies for previously statement as I was somehow manually validating the files & found my mistakes.
Thanks,
D R.
Back to top
Frank Yaeger DFSORT Developer Joined: 15 Feb 2005Posts: 7129 Location: San Jose, CA
Well, I'm glad you reported that you found your mistake before I read through this thread. Saved me some time.
Back to top
Please enable JavaScript!