Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Joinkeys in SORT

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
gkanswa

New User


Joined: 09 Feb 2009
Posts: 5
Location: Pune

PostPosted: Tue Mar 16, 2010 8:17 pm    Post subject: Joinkeys in SORT
Reply with quote

I have 2 input (input1 and input2) files and I need 3 output(output1,output2,output3) files.

1) The output1 will have the matched records,
2) The Output2 will have the unmatched records which is present in the input2 but not in input1.
3) The Output3 will have the unmatched records which is present in input1 but not in input2.

Input1 - File length - 1717, Key - (1,30)
Input2 - File Length - 93, key -(7,30)

Output1 - File Length - 93 (Format same as input2)
Output2 - File Length - 93 (Format same as input2)
Output3 - File Length - 1717 (Format same as input1)

I'm able to get the 2 output files (Output1 and Output2) with the below sort:

Code:
JOINKEYS FILE=F1,FIELDS=(1,30,A)
JOINKEYS FILE=F2,FIELDS=(7,30,A)
JOIN UNPAIRED,F2
REFORMAT FIELDS=(F2:1,93,F1:1,13)
SORT FIELDS=COPY
OUTFIL FILES=01,INCLUDE=(94,13,CH,NE,C' '),BUILD=(1,93)
OUTFIL FILES=02,SAVE,BUILD=(1,93)
Back to top
View user's profile Send private message

Alissa Margulies

SYNCSORT Support


Joined: 25 Jul 2007
Posts: 500
Location: USA

PostPosted: Wed Mar 17, 2010 9:39 pm    Post subject:
Reply with quote

Here are the control statements for a SyncSort for z/OS JOIN application that should produce the requested output:
Code:

//SYSIN DD *
  JOINKEYS FILE=F1,FIELDS=(1,30,A)
  JOINKEYS FILE=F2,FIELDS=(7,30,A)   
  JOIN UNPAIRED
  REFORMAT FIELDS=(F2:1,93,F1:1,1717)
  SORT FIELDS=COPY
  OUTFIL FILES=03,INCLUDE=(1,1,CH,EQ,C' '),BUILD=(94,1717)
  OUTFIL FILES=02,INCLUDE=(94,1,CH,EQ,C' '),BUILD=(1,93)
  OUTFIL FILES=01,SAVE,BUILD=(1,93)
/*
Back to top
View user's profile Send private message
gkanswa

New User


Joined: 09 Feb 2009
Posts: 5
Location: Pune

PostPosted: Thu Mar 18, 2010 5:24 pm    Post subject: Reply to: Matched/Unmatced records from 2 files - Joinkey SY
Reply with quote

Thanks a lot Alissa ..!!

It worked fine for me ...
Back to top
View user's profile Send private message
jctgf
Currently Banned

Active User


Joined: 04 Nov 2006
Posts: 109

PostPosted: Tue Jun 01, 2010 5:00 pm    Post subject:
Reply with quote

Hi,

I have a similar requirement, but in my case both file records have exactly the same fields and lrecl.
I don't have blank fields in the input files records so that I can distinguish which record belongs to input file 1 or 2.

Code:

  OUTFIL FILES=03,INCLUDE=(1,1,CH,EQ,C' '),BUILD=(94,1717)
  OUTFIL FILES=02,INCLUDE=(94,1,CH,EQ,C' '),BUILD=(1,93)
  OUTFIL FILES=01,SAVE,BUILD=(1,93)


The only way to know which file the unpaired record belogs to is testing a value on the input record?

Thanks.
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Tue Jun 01, 2010 6:59 pm    Post subject:
Reply with quote

Exploiting SyncSort JOIN wrote:
Code:
REFORMAT FIELDS=(F1:1,40,
                 F2:1,40),FILL=X'FF'
REFORMAT defines the record layout to be produced by the join processing. If a record has been added to the master file, it will appear only in file 1 (the second half of the reformatted record is X’FF’, and the first half contains data). If the record is modified, it will appear in both file 1 and file 2 (both halves of the record contain data). If the record was deleted, it will appear only in file 2 (the first half of the reformatted record will contain X’FF’, while the second half contains data).

In this example, the record might or might not exist on the master file or the backup depending upon if the record is a new addition or a recent deletion.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SORT to unpack a feild shr_amar DFSORT/ICETOOL 7 Fri Jul 07, 2017 4:32 am
No new posts How to write Specific Fields from Mul... Padhu SYNCSORT 6 Thu Jul 06, 2017 10:26 am
No new posts Optimize sort inrec parse vishwakotin DFSORT/ICETOOL 6 Mon Jun 26, 2017 11:15 pm
No new posts Reduce CPU Times for Join Sort santoshn SYNCSORT 12 Sat Jun 10, 2017 1:40 pm
No new posts getting three output files out of JOI... migusd SYNCSORT 7 Mon Jun 05, 2017 11:30 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us