IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Syncsort -Need Clarification during elimination of 1st Occur
IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
srajanbose

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai
PostPosted: Tue Aug 10, 2010 6:49 pm
 Reply with quote
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
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
PostPosted: Tue Aug 10, 2010 6:57 pm
 Reply with quote
It will be mightly helpful if you actually explain the rules of getting the result you wish.
Back to top
View user's profile Send private message
smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune
PostPosted: Tue Aug 10, 2010 6:59 pm
 Reply with quote
your requirement is not clear .... will you put it into words ?

what abt the record "111 333"
Back to top
View user's profile Send private message
srajanbose

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai
PostPosted: Tue Aug 10, 2010 7:04 pm
 Reply with quote
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
View user's profile Send private message
smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune
PostPosted: Tue Aug 10, 2010 7:09 pm
 Reply with quote
does your DFSORT support JOINKEYS ?
Back to top
View user's profile Send private message
srajanbose

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai
PostPosted: Tue Aug 10, 2010 7:22 pm
 Reply with quote
Hi,

Yes my DFSORT supports JOINKEYS

Regards,
Rajan BOSE
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Tue Aug 10, 2010 9:14 pm
 Reply with quote
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
View user's profile Send private message
srajanbose

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai
PostPosted: Wed Aug 11, 2010 2:14 pm
 Reply with quote
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
View user's profile Send private message
smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune
PostPosted: Wed Aug 11, 2010 3:40 pm
 Reply with quote
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
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Wed Aug 11, 2010 10:18 pm
 Reply with quote
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
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Wed Aug 11, 2010 10:26 pm
 Reply with quote
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
View user's profile Send private message
smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune
PostPosted: Thu Aug 12, 2010 11:55 am
 Reply with quote
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
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Thu Aug 12, 2010 5:06 pm
 Reply with quote
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
View user's profile Send private message
srajanbose

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai
PostPosted: Thu Aug 12, 2010 5:53 pm
 Reply with quote
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
View user's profile Send private message
smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune
PostPosted: Thu Aug 12, 2010 6:02 pm
 Reply with quote
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
View user's profile Send private message
smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune
PostPosted: Thu Aug 12, 2010 6:05 pm
 Reply with quote
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
View user's profile Send private message
srajanbose

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai
PostPosted: Thu Aug 12, 2010 7:23 pm
 Reply with quote
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
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10903
Location: italy
PostPosted: Thu Aug 12, 2010 7:27 pm
 Reply with quote
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 icon_biggrin.gif
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Thu Aug 12, 2010 11:47 pm
 Reply with quote
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
View user's profile Send private message
Alissa Margulies

SYNCSORT Support


Joined: 25 Jul 2007
Posts: 496
Location: USA
PostPosted: Fri Aug 13, 2010 12:52 am
 Reply with quote
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
View user's profile Send private message
srajanbose

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai
PostPosted: Fri Aug 13, 2010 3:16 pm
 Reply with quote
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
View user's profile Send private message
Alissa Margulies

SYNCSORT Support


Joined: 25 Jul 2007
Posts: 496
Location: USA
PostPosted: Wed Aug 18, 2010 10:51 pm
 Reply with quote
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
View user's profile Send private message
srajanbose

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai
PostPosted: Mon Aug 23, 2010 2:21 pm
 Reply with quote
Hi Alissa,

Thanks you so much.It works perfectly for me.

Regards,
Rajan BOSE.
Back to top
View user's profile Send private message
View previous topic : : View next topic  
Post new topic   Reply to topic All times are GMT + 6 Hours
Forum Index -> JCL & VSAM

 

Similar Topics
Topic Forum Replies
No new posts Write record twice based on condition... SYNCSORT 7
No new posts Create a FTP record with help of SYNC... SYNCSORT 3
No new posts SYNCSORT-DB2 interface SYNCSORT 1
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Count Records with a crietaria in a f... DFSORT/ICETOOL 5
Search our Forums:


Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us