View previous topic :: View next topic
Author
Message
santosh100678 New User Joined: 21 Sep 2009Posts: 55 Location: Kolkatta
Hi ,
I have two file one is IN1 and second is IN2
File IN1 conatins
1234567890
3456789012
5678901234
File IN2 Contains
3456789012
7890123456
8901234567
Now i want to create three files using File IN1 and IN2
My output file 1 contains IN1-IN2 records
MY output file 2 conatins IN2-IN1 records
MY output file 3 conatins IN2 equal = IN1 records
I have a jcl but not getting desired output
Code:
//STEP01 EXEC PGM=SYNCTOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//TEMPX DD SYSOUT=*
//TEMPY DD SYSOUT=*
//IN1 DD *
1234567890
3456789012
5678901234
//IN2 DD *
3456789012
7890123456
8901234567
//TEMP1 DD DSN=&TEMP1,SPACE=(CYL,(5,5),RLSE),DISP=(,PASS)
//TEMP2 DD DSN=&TEMP2,SPACE=(CYL,(5,5),RLSE),DISP=(,PASS)
//INT DD DSN=*.TEMP1,DISP=(OLD,PASS),VOL=REF=*.TEMP1
// DD DSN=*.TEMP2,DISP=(OLD,PASS),VOL=REF=*.TEMP2
//OUT12 DD SYSOUT=*
//OUT11 DD SYSOUT=*
//OUT22 DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN1) TO(TEMP1) USING(CTL1)
COPY FROM(IN2) TO(TEMP2) USING(CTL2)
* COPY FROM(TEMP2) TO(TEMPY)
COPY FROM(INT) TO(OUT12) USING(CTL3)
/*
//CTL1CNTL DD *
OUTREC FIELDS=(1,10,11:C'11')
/*
//CTL2CNTL DD *
OUTREC FIELDS=(1,10,11:C'22')
/*
//CTL3CNTL DD *
OUTFIL FNAMES=OUT12,INCLUDE=(10,2,CH,EQ,C'12')
OUTFIL FNAMES=OUT11,INCLUDE=(10,2,CH,EQ,C'11')
OUTFIL FNAMES=OUT22,INCLUDE=(10,2,CH,EQ,C'22')
/*
Back to top
Pandora-Box Global Moderator Joined: 07 Sep 2006Posts: 1592 Location: Andromeda Galaxy
where did you splice?
Back to top
santosh100678 New User Joined: 21 Sep 2009Posts: 55 Location: Kolkatta
sorry by mistake i have written COPY FROM(INT) TO(OUT12) USING(CTL3) actual code is
SPLICE FROM(CONCAT) TO(OUT12) ON(1,10,CH) WITH(12,1) -
USING(CTL3) KEEPNODUPS
Back to top
Pandora-Box Global Moderator Joined: 07 Sep 2006Posts: 1592 Location: Andromeda Galaxy
Your job would have been simple if you had searched here or read DFSORT Tricks
Back to top
dominickim New User Joined: 28 Feb 2007Posts: 65 Location: NS, CA
Please check
//CTL3CNTL DD *
OUTFIL FNAMES=OUT12,INCLUDE=(10 ,2,CH,EQ,C'12')
OUTFIL FNAMES=OUT11,INCLUDE=(10 ,2,CH,EQ,C'11')
OUTFIL FNAMES=OUT22,INCLUDE=(10 ,2,CH,EQ,C'22')
/*
Back to top
santosh100678 New User Joined: 21 Sep 2009Posts: 55 Location: Kolkatta
Hi,
I have tried so many things but still i am facing problem,could you please help me out regarding this
Back to top
santosh100678 New User Joined: 21 Sep 2009Posts: 55 Location: Kolkatta
Hi dominickim,
In above statement i write same thing
//CTL3CNTL DD *
OUTFIL FNAMES=OUT12,INCLUDE=(10,2,CH,EQ,C'12')
OUTFIL FNAMES=OUT11,INCLUDE=(10,2,CH,EQ,C'11')
OUTFIL FNAMES=OUT22,INCLUDE=(10,2,CH,EQ,C'22')
/*
Back to top
dominickim New User Joined: 28 Feb 2007Posts: 65 Location: NS, CA
I would put 11 on those instead of 10.
Back to top
Pandora-Box Global Moderator Joined: 07 Sep 2006Posts: 1592 Location: Andromeda Galaxy
You havent read the DFSORT tricks and searched forum still
Code:
//STEP01 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD *
1234567890
3456789012
5678901234
//IN2 DD *
3456789012
7890123456
8901234567
//TEMP1 DD DSN=&&TEMP1,SPACE=(CYL,(5,5),RLSE),DISP=(MOD,PASS)
//OUT12 DD SYSOUT=*
//OUT11 DD SYSOUT=*
//OUT22 DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN1) TO(TEMP1) USING(CTL1)
COPY FROM(IN2) TO(TEMP1) USING(CTL2)
SPLICE FROM(TEMP1) TO(OUT12) ON(1,10,CH) WITH(13,1) -
USING(CTL3) KEEPNODUPS
/*
//CTL1CNTL DD *
INREC OVERLAY=(1,12,11:C'11')
/*
//CTL2CNTL DD *
INREC OVERLAY=(1,12,11:C'22')
/*
//CTL3CNTL DD *
OUTFIL FNAMES=OUT12,INCLUDE=(12,2,CH,EQ,C'12'),BUILD=(1,10)
OUTFIL FNAMES=OUT11,INCLUDE=(12,2,CH,EQ,C'11'),BUILD=(1,10)
OUTFIL FNAMES=OUT22,INCLUDE=(12,2,CH,EQ,C'22'),BUILD=(1,10)
/*
Back to top
santosh100678 New User Joined: 21 Sep 2009Posts: 55 Location: Kolkatta
Hi ,
I have tried above JCL as you mentined facing eroor S000 016
dump of this
SYNCSORT LICENSED FOR CPU SERIAL NUMBER 5DE12, MODEL 2096 N04 LICEN
CTL1CNTL : OUTREC FIELDS=(1,12,12:C'11')
PARMLIST :
OPTION RESINV=0,ARESINV=0,MSGDDN=DFSMSG,SORTIN=IN1,SORTOUT=TEMP1,SORTDD=CTL1,CO
Y
WER428I CALLER-PROVIDED IDENTIFIER IS "0001"
I have tried below JOB then my 11file and 22 file is created but 12 file is not created
Code:
//STEP01 EXEC PGM=SYNCTOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//TEMPX DD SYSOUT=*
//TEMPY DD SYSOUT=*
//IN1 DD *
1234567890
3456789012
5678901234
//IN2 DD *
3456789012
7890123456
8901234567
//TEMP1 DD DSN=&TEMP1,SPACE=(CYL,(5,5),RLSE),DISP=(,PASS)
//TEMP2 DD DSN=&TEMP2,SPACE=(CYL,(5,5),RLSE),DISP=(,PASS)
//INT DD DSN=*.TEMP1,DISP=(OLD,PASS),VOL=REF=*.TEMP1
// DD DSN=*.TEMP2,DISP=(OLD,PASS),VOL=REF=*.TEMP2
//OUT12 DD SYSOUT=*
//OUT11 DD SYSOUT=*
//OUT22 DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN1) TO(TEMP1) USING(CTL1)
COPY FROM(IN2) TO(TEMP2) USING(CTL2)
SPLICE FROM(CONCAT) TO(OUT12) ON(1,10,CH) WITH(12,1) -
USING(CTL3) KEEPNODUPS
/*
//CTL1CNTL DD *
OUTREC FIELDS=(1,10,11:C'11')
/*
//CTL2CNTL DD *
OUTREC FIELDS=(1,10,11:C'22')
/*
//CTL3CNTL DD *
OUTFIL FNAMES=OUT12,INCLUDE=(10,2,CH,EQ,C'12')
OUTFIL FNAMES=OUT11,INCLUDE=(10,2,CH,EQ,C'11')
OUTFIL FNAMES=OUT22,INCLUDE=(10,2,CH,EQ,C'22')
/*
Back to top
Pandora-Box Global Moderator Joined: 07 Sep 2006Posts: 1592 Location: Andromeda Galaxy
Why cant you make use of JOINKEYS?
Back to top
santosh100678 New User Joined: 21 Sep 2009Posts: 55 Location: Kolkatta
Hi
I am unawre of using joinkeys could you please guide me reagrding this.
Could you please provide example how i can use
Back to top
Arun Raj Moderator Joined: 17 Oct 2006Posts: 2481 Location: @my desk
Quote:
Could you please provide example how i can use
Here's an example for the same.
Code:
//STEP00 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD *
1234567890
3456789012
5678901234
//SORTJNF2 DD *
3456789012
7890123456
8901234567
//MATCH DD SYSOUT=*
//ONLYF1 DD SYSOUT=*
//ONLYF2 DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,10,A)
JOINKEYS FILE=F2,FIELDS=(1,10,A)
JOIN UNPAIRED
REFORMAT FIELDS=(F1:1,10,F2:1,10)
SORT FIELDS=COPY
OUTFIL FNAMES=ONLYF1,INCLUDE=(11,1,CH,EQ,C' '),BUILD=(1,10)
OUTFIL FNAMES=ONLYF2,INCLUDE=(1,1,CH,EQ,C' '),BUILD=(11,10)
OUTFIL FNAMES=MATCH,SAVE,BUILD=(1,10)
Back to top
santosh100678 New User Joined: 21 Sep 2009Posts: 55 Location: Kolkatta
thanks you very much arun
i got desire result,
Please explian me regarding this join keys :
I am unable to undestand about word JOIN UNPAIRED
& also explain me about the fomatting part
REFORMAT FIELDS=(F1:1,10,F2:1,10)
SORT FIELDS=COPY
OUTFIL FNAMES=ONLYF1,INCLUDE=(11,1,CH,EQ,C' '),BUILD=(1,10)
OUTFIL FNAMES=ONLYF2,INCLUDE=(1,1,CH,EQ,C' '),BUILD=(11,10)
OUTFIL FNAMES=MATCH,SAVE,BUILD=(1,10)
Could you please expalin me
Back to top
Arun Raj Moderator Joined: 17 Oct 2006Posts: 2481 Location: @my desk
Hello,
'JOIN UNPAIRED' is similar to a FULL OUTER JOIN in DB2 SQL. It will extract matching records as well as non-matching records from both the files.
REFORMAT statement tells from which file, which all fields need to be extracted to output. After the REFORMAT you will have a 10+10=20 byte record.
If a particular key is missing in a file, by default you'll have SPACES over there.
If file1-key is missing in file2, the record will have SPACES from pos 11-20.
If file2-key is missing in file1, the record will have SPACES from pos 1-10.
If a particular key is present in both the files, you'll have the same key present at pos 1-10 and at pos 11-20.
The first 2 OUTFIL statements INCLUDEs the NON-MATCHing records based on the above criteria and the SAVE parameter extracts the remaining records which are nothing but the MATCHing records.
SyncSort manual explains in detail about each of these control statements. If you dont have one, send a PM to Alissa Margulies, She's the SyncSort representative in this forum.
Back to top
Please enable JavaScript!