View previous topic :: View next topic
Author
Message
srajanbose New User Joined: 11 Oct 2004Posts: 69 Location: chennai
I have an requirement as below
Input file:
input File 1: With Record length - 80 and Record Format - FB
111 222
111 223
111 242
111 252
input File 2: With Record length - 80 and Record Format - FB
111 333
222 333
555 334
777 363
expected output:
111 222
111 223
111 242
111 252
222 333
555 334
777 363
could you please let me know how to acheive this?
Regards,
Rajan BOSE
Back to top
expat Global Moderator Joined: 14 Mar 2007Posts: 8797 Location: Welsh Wales
It will be mightly helpful if you actually explain the rules of getting the result you wish.
Back to top
smijoss Active User Joined: 30 Aug 2007Posts: 114 Location: pune
your requirement is not clear .... will you put it into words ?
what abt the record "111 333"
Back to top
srajanbose New User Joined: 11 Oct 2004Posts: 69 Location: chennai
Hi,
my requirement is to copy two input files in to an output file by eliminating the records(matched key value,in my case its column 1-3) present in the second input file.
for example my second input file which have the value 111 333 is to be eliminated in the output file.
Let me know if you need more information.
Regards,
Rajan BOSE.
Back to top
smijoss Active User Joined: 30 Aug 2007Posts: 114 Location: pune
does your DFSORT support JOINKEYS ?
Back to top
srajanbose New User Joined: 11 Oct 2004Posts: 69 Location: chennai
Hi,
Yes my DFSORT supports JOINKEYS
Regards,
Rajan BOSE
Back to top
sqlcode1 Active Member Joined: 08 Apr 2010Posts: 577 Location: USA
Rajan,
Use below...
Code:
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//INA DD *
111 222
111 223
111 242
111 252
//INB DD *
111 333
222 333
555 334
777 363
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
JOINKEYS F1=INA,FIELDS=(1,3,A)
JOINKEYS F2=INB,FIELDS=(1,3,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,80,F2:1,80),FILL=C' '
INREC IFTHEN=(WHEN=(1,80,CH,EQ,C' '),OVERLAY=(1:81,80))
SORT FIELDS=COPY
OUTREC BUILD=(1,80)
/*
OUTPUT
Code:
111 222
111 223
111 242
111 252
222 333
555 334
777 363
Thanks,
Back to top
srajanbose New User Joined: 11 Oct 2004Posts: 69 Location: chennai
Hi,
I tried with the above code and it works fine.But in my case the requirement as i mentioned already is when my key value matches in both the file, the records in Input file1 should be taken for that key value and not the one present in Input File2.
Input File1:
111 764
111 876
111 678
111 345
Input file2:
111 333
111 567
222 333
555 334
777 363
Expected Output:
111 764
111 876
111 678
111 345
222 333
555 334
777 363
But with the above code the output is:
111 764
111 764
111 876
111 876
111 678
111 678
111 345
111 345
222 333
555 334
777 363
Back to top
smijoss Active User Joined: 30 Aug 2007Posts: 114 Location: pune
might not be efficient as others but gave me the result :
Code:
//JS001 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//TEMP1 DD DSN=&&TEMP1,DISP=(NEW,PASS),UNIT=DISK,
// SPACE=(27998,(4000,1000),RLSE)
//TEMP2 DD DSN=&&TEMP2,DISP=(NEW,PASS),UNIT=DISK,
// SPACE=(27998,(4000,1000),RLSE)
//TEMP3 DD DSN=&&TEMP3,DISP=(NEW,PASS),UNIT=DISK,
// SPACE=(27998,(4000,1000),RLSE)
//CONCAT DD DSN=*.TEMP1,VOL=REF=*.TEMP1,DISP=OLD
// DD DSN=*.TEMP2,VOL=REF=*.TEMP2,DISP=OLD
//*
//IN1 DD *
111 764
111 876
111 678
111 777
111 345
/*
//IN2 DD *
111 333
111 567
111 444
222 333
555 334
777 363
/*
//TMP1 DD DSN=TEMP.TLM.FTBDATE.OUT,
// DISP=(NEW,CATLG),
// SPACE=(CYL,(1,1),RLSE),UNIT=TSTDA
//*
//TOOLIN DD *
COPY FROM(IN1) TO(TEMP1) USING(CPY1)
COPY FROM(IN2) TO(TEMP2) USING(CPY2)
SPLICE FROM(CONCAT) TO(TEMP3) ON(1,3,CH) -
WITH(5,3) WITH(9,3) WITHALL -
KEEPNODUPS KEEPBASE
COPY FROM(TEMP3) TO(TMP1) USING(CPY3)
//CPY1CNTL DD *
INREC OVERLAY=(51:C'1')
/*
//CPY2CNTL DD *
INREC OVERLAY=(9:5,3,5:3X,51:C'2')
/*
//CPY3CNTL DD *
OMIT COND=(5,3,CH,EQ,C' ',AND,51,1,CH,EQ,C'1')
INREC IFTHEN=(WHEN=INIT,BUILD=(1,50,80:X)),
IFTHEN=(WHEN=(5,3,CH,EQ,C' '),BUILD=(1,4,9,3,80:X))
/*
output of job :
111 764
111 876
111 678
111 777
111 345
222 333
555 334
777 363
Back to top
Frank Yaeger DFSORT Developer Joined: 15 Feb 2005Posts: 7129 Location: San Jose, CA
srajanbose,
You don't need all the passes smijoss used. You can do it more efficiently with a DFSORT JOINKEYS job like the following:
Code:
//S2 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//INA DD *
111 764
111 876
111 678
111 345
//INB DD *
111 333
111 567
222 333
555 334
777 363
//OUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS F1=INA,FIELDS=(1,3,A)
JOINKEYS F2=INB,FIELDS=(1,3,A)
JOIN UNPAIRED,F2
REFORMAT FIELDS=(F1:1,80,F2:1,80,?)
INREC IFTHEN=(WHEN=(161,1,CH,EQ,C'B'),
BUILD=(1,80)),
IFTHEN=(WHEN=NONE,
BUILD=(81,80))
OPTION COPY
OUTFIL FNAMES=OUT,REMOVECC,NODETAIL,
SECTIONS=(1,7,
TRAILER3=(1,80))
/*
Back to top
sqlcode1 Active Member Joined: 08 Apr 2010Posts: 577 Location: USA
Frank,
Wow...Don't know if OP is still interested in solution or not but thanks for posting solution. I was lost after JOIN UNPAIRED,F2.
Thanks,
Back to top
smijoss Active User Joined: 30 Aug 2007Posts: 114 Location: pune
hey frank this is good. got to learn something new.
especially the difference between
JOIN UNPAIRED,F2 JOIN UNPAIRED,F1 and JOIN UNPAIRED,F1,F2
one doubt :
is there any way to handle duplicate records too
like in this case if
//INA DD *
111 764
111 764
/*
only the 2nd record is moved to output.
Back to top
sqlcode1 Active Member Joined: 08 Apr 2010Posts: 577 Location: USA
smijoss,
Can you have more than 2 records? if more than 2 records are possible how do you want the output be?
Thanks,
Back to top
srajanbose New User Joined: 11 Oct 2004Posts: 69 Location: chennai
Hi Frank,
I tried the above code and got MAXXCC=0016 with below information in the spool:
JOINKEYS REFORMAT RECORD LENGTH= 160, TYPE = F
INREC RECORD LENGTH = 80
OUT : RECFM=FB ; LRECL= 80; BLKSIZE= 80
INCLUDE/OMIT FIELD BEYOND RECORD
SYNCSMF CALLED BY SYNCSORT; RC=0000
SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE
JNF1 STATISTICS
Your are using Join keys for pairing the key value in both the file and reformatting the file with 80 bytes but i couldnt understand the importance of ? in reformat field.I tried to find the same in many documents but couldnt get a clear idea.
Can you please explain
Back to top
smijoss Active User Joined: 30 Aug 2007Posts: 114 Location: pune
hi sqlcode1,
i am refering to to FRANK's Job mentioned above
if i change the INA dd to
//INA DD *
111 764
111 764
/*
output should have been :
111 764
111 764
222 333
555 334
777 363
but with Franks Sort card i get
111 764
222 333
555 334
777 363
Back to top
smijoss Active User Joined: 30 Aug 2007Posts: 114 Location: pune
Hi srajanbose,
the JCL is perfect
did you read this in your trace :
SYNCSMF CALLED BY SYNCSORT; RC=0000
SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE
Back to top
srajanbose New User Joined: 11 Oct 2004Posts: 69 Location: chennai
Hi Frank,
Forgot to add one more point I got the above return code as i removed the ? from Reformat Fields.
If i have the ? in Reformat fields I get the below error message:
REFORMAT FIELDS=(F1:1,80,F2:1,80,?)
*
INREC IFTHEN=(WHEN=(161,1,CH,EQ,C'B'),
BUILD=(1,80)),
IFTHEN=(WHEN=NONE,
BUILD=(81,80))
OPTION COPY
OUTFIL FNAMES=OUT,REMOVECC,NODETAIL,
SECTIONS=(1,7,
TRAILER3=(1,80))
WER268A REFORMAT STATEMENT: SYNTAX ERROR
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE
Back to top
enrico-sorichetti Superior Member Joined: 14 Mar 2007Posts: 10873 Location: italy
did You notice that the topic was moved to the jcl forum ?
the syn.... stuff and the wer.... messages indicate that You are using syncsort
Frank and Kolusu are DFSORT developers , seems unfair to ask them to answer questions dealing with <competitor> software
Back to top
Frank Yaeger DFSORT Developer Joined: 15 Feb 2005Posts: 7129 Location: San Jose, CA
Quote:
Forgot to add one more point I got the above return code as i removed the ? from Reformat Fields.
If i have the ? in Reformat fields I get the below error message:
REFORMAT FIELDS=(F1:1,80,F2:1,80,?)
DFSORT supports the ? indicator for JOINKEYS. AFAIK, Syncsort doesn't.
Back to top
Alissa Margulies SYNCSORT Support Joined: 25 Jul 2007Posts: 496 Location: USA
Here are the SyncSort for z/OS control statements that should give you the requested output:
Code:
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(1,3,A)
JOINKEYS FILES=F2,FIELDS=(1,3,A)
JOIN UNPAIRED
REFORMAT FIELDS=(F1:1,80,F2:1,80)
INREC IFTHEN=(WHEN=(1,80,CH,EQ,C' '),BUILD=(81,80)),
IFTHEN=(WHEN=NONE,BUILD=(1,80))
SORT FIELDS=(1,7,CH,A)
SUM FIELDS=NONE
/*
Please let us know if you continue to encounter difficulties or have any follow-up questions.
Regards,
Back to top
srajanbose New User Joined: 11 Oct 2004Posts: 69 Location: chennai
Hi Alissa,
Thanks for the above control statements.I gave you the sample Input and Output file and for that your control card works fine.For my case the :
Input file1:
05.08.2010AAC770010860926-AAC10.08.201014.04.1710
05.08.2010AAC770010861027-AAC10.08.201014.04.1710
05.08.2010AAC7700111331MJ-AAC10.08.201014.04.1710
05.08.2010AAC7700111332MK-AAC10.08.201014.04.1710
05.08.2010AAC7700115267D6-AAC10.08.201014.04.1710
05.08.2010AAC7701716736UJ-AAC10.08.201014.04.1710
05.08.2010AAC820001918193-AAC10.08.201014.04.1710
05.08.2010AAC820001918395-AAC10.08.201014.04.1710
05.08.2010AAC82000328539C-AAC10.08.201014.04.1710
05.08.2010AAC82000380427A-AAC10.08.201014.04.1710
Input file 2:
05.08.2010AAC770010860926-AAC06.08.201003.26.1206
05.08.2010AAC770010861027-AAC06.08.201003.26.1206
05.08.2010AAC7700111331MJ-AAC06.08.201003.26.1206
05.08.2010AAC7700111332MK-AAC06.08.201003.26.1206
05.08.2010AAC7700115267D6-AAC06.08.201003.26.1206
05.08.2010AAC7701716736UJ-AAC06.08.201003.26.1206
05.08.2010AAC820001918193-AAC06.08.201003.26.1206
05.08.2010AAC820001918395-AAC06.08.201003.26.1206
05.08.2010AAC82000328539C-AAC06.08.201003.26.1206
05.08.2010AAC82000380427A-AAC06.08.201003.26.1206
05.08.2010APB8200557429N4-A/P06.08.201003.26.1206
05.08.2010APB8200557436NB-A/P06.08.201003.26.1206
05.08.2010APB8200557444NK-A/P06.08.201003.26.1206
05.08.2010APB8200557450NS-A/P06.08.201003.26.1206
05.08.2010APB8200954286NJ-A/P06.08.201003.26.1206
05.08.2010APN8200453975CN-A/P06.08.201003.26.1206
05.08.2010APN8200460684S9-A/P06.08.201003.26.1206
05.08.2010APN8200460688SD-A/P06.08.201003.26.1206
05.08.2010APN8200460704SW-A/P06.08.201003.26.1206
05.08.2010APN8200913073FJ-A/P06.08.201003.26.1206
05.08.2010APP8200463405BS-A/P06.08.201003.26.1206
05.08.2010APP8200463408BV-A/P06.08.201003.26.1206
05.08.2010APP8200463410BX-A/P06.08.201003.26.1206
05.08.2010APP8200463411BY-A/P06.08.201003.26.1206
Expected Output is:
05.08.2010AAC770010860926-AAC10.08.201014.04.1710
05.08.2010AAC770010861027-AAC10.08.201014.04.1710
05.08.2010AAC7700111331MJ-AAC10.08.201014.04.1710
05.08.2010AAC7700111332MK-AAC10.08.201014.04.1710
05.08.2010AAC7700115267D6-AAC10.08.201014.04.1710
05.08.2010AAC7701716736UJ-AAC10.08.201014.04.1710
05.08.2010AAC820001918193-AAC10.08.201014.04.1710
05.08.2010AAC820001918395-AAC10.08.201014.04.1710
05.08.2010AAC82000328539C-AAC10.08.201014.04.1710
05.08.2010AAC82000380427A-AAC10.08.201014.04.1710
05.08.2010APB8200557429N4-A/P06.08.201003.26.1206
05.08.2010APB8200557436NB-A/P06.08.201003.26.1206
05.08.2010APB8200557444NK-A/P06.08.201003.26.1206
05.08.2010APB8200557450NS-A/P06.08.201003.26.1206
05.08.2010APB8200954286NJ-A/P06.08.201003.26.1206
05.08.2010APN8200453975CN-A/P06.08.201003.26.1206
05.08.2010APN8200460684S9-A/P06.08.201003.26.1206
05.08.2010APN8200460688SD-A/P06.08.201003.26.1206
05.08.2010APN8200460704SW-A/P06.08.201003.26.1206
05.08.2010APN8200913073FJ-A/P06.08.201003.26.1206
05.08.2010APP8200463405BS-A/P06.08.201003.26.1206
05.08.2010APP8200463408BV-A/P06.08.201003.26.1206
05.08.2010APP8200463410BX-A/P06.08.201003.26.1206
05.08.2010APP8200463411BY-A/P06.08.201003.26.1206
The above control card gives me the output as:
05.08.2010AAC770010860926-AAC10.08.201014.04.1710
05.08.2010APB8200557429N4-A/P06.08.201003.26.1206
05.08.2010APB8200557436NB-A/P06.08.201003.26.1206
05.08.2010APB8200557444NK-A/P06.08.201003.26.1206
05.08.2010APB8200557450NS-A/P06.08.201003.26.1206
05.08.2010APB8200954286NJ-A/P06.08.201003.26.1206
05.08.2010APN8200453975CN-A/P06.08.201003.26.1206
05.08.2010APN8200460684S9-A/P06.08.201003.26.1206
05.08.2010APN8200460688SD-A/P06.08.201003.26.1206
05.08.2010APN8200460704SW-A/P06.08.201003.26.1206
05.08.2010APN8200913073FJ-A/P06.08.201003.26.1206
05.08.2010APP8200463405BS-A/P06.08.201003.26.1206
05.08.2010APP8200463408BV-A/P06.08.201003.26.1206
05.08.2010APP8200463410BX-A/P06.08.201003.26.1206
05.08.2010APP8200463411BY-A/P06.08.201003.26.1206
Below is the control card that i gave for my requirement:
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(1,13,A)
JOINKEYS FILES=F2,FIELDS=(1,13,A)
JOIN UNPAIRED
REFORMAT FIELDS=(F1:1,106,F2:1,106)
INREC IFTHEN=(WHEN=(1,106,CH,EQ,C' '),BUILD=(107,106)),
IFTHEN=(WHEN=NONE,BUILD=(1,106))
SORT FIELDS=(1,13,CH,A)
SUM FIELDS=NONE
/*
I tried various options with your Control card but couldnt get the desired output.
Please help in this regard.
Back to top
Alissa Margulies SYNCSORT Support Joined: 25 Jul 2007Posts: 496 Location: USA
You should get the desired results if you change the SORT statement
from
Code:
SORT FIELDS=(1,13,CH,A)
to
Code:
SORT FIELDS=(1,106,CH,A)
Please let us know if this resolves the issue.
Back to top
srajanbose New User Joined: 11 Oct 2004Posts: 69 Location: chennai
Hi Alissa,
Thanks you so much.It works perfectly for me.
Regards,
Rajan BOSE.
Back to top
Please enable JavaScript!