|
View previous topic :: View next topic
|
| Author |
Message |
santosh100678
New User
Joined: 21 Sep 2009
Posts: 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
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| where did you splice? |
|
| Back to top |
|
|
santosh100678
New User
Joined: 21 Sep 2009
Posts: 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 2006
Posts: 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 2007
Posts: 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 2009
Posts: 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 2009
Posts: 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 2007
Posts: 65
Location: NS, CA
|
|
|
|
| I would put 11 on those instead of 10. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 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 2009
Posts: 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 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Why cant you make use of JOINKEYS? |
|
| Back to top |
|
|
santosh100678
New User
Joined: 21 Sep 2009
Posts: 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 2006
Posts: 2482
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 2009
Posts: 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 2006
Posts: 2482
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 |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
