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
 

 

how to get unpaired records from both the files into 1 file

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

New User


Joined: 09 Aug 2010
Posts: 18
Location: bangalore

PostPosted: Wed Feb 06, 2013 12:52 pm    Post subject: how to get unpaired records from both the files into 1 file
Reply with quote

hi all

my requirement is to compare 2 files and get the unmatched/unpaired records from both the files into a single output file


i have file 1 with below data

Code:
100
200
300
400


file 2 with data

Code:
100
200
400
600


i need to get in output file as

Code:
300
600


i have tried with the below joinkeys sort card and jcl as

Code:
SORTJNF1  DD  DISP=SHR,DSN=MY.RECORDS.FILE1
SORTJNF2  DD  DISP=SHR,DSN=MY.RECORDS.FILE2
SORTOUT   DD  DSN=MY.UNMTCH.RECORDS,
            DISP=(,CATLG,DELETE),
            SPACE=(CYL,(1,1),RLSE),
            DCB=(RECFM=FB,LRECL=80,BLKSIZE=27920)
SYSIN DD *
SORT FIELDS=COPY
JOINKEYS FILE=F1,FIELDS=(1,3,A)
JOINKEYS FILE=F2,FIELDS=(1,3,A)
JOIN UNPAIRED,ONLY
REFORMAT FIELDS=(F1:1,80,F2:1,80)
/*


i am getting output as
Code:

300

(spaces in second line instead of 600)

Could anyone please help me out in this

thanks in advance

Code'd
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Wed Feb 06, 2013 1:18 pm    Post subject: Reply to: how to get unpaired records from both the files in
Reply with quote

Please use the Code tags to preserve formatting.

Bear in mind that your "600" will being in column 81. Is that the problem?
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1714
Location: UK

PostPosted: Wed Feb 06, 2013 1:30 pm    Post subject:
Reply with quote

Are you using Syncsort?
Back to top
View user's profile Send private message
rajkumarmf1985

New User


Joined: 09 Aug 2010
Posts: 18
Location: bangalore

PostPosted: Wed Feb 06, 2013 2:07 pm    Post subject: Reply to: how to get unpaired records from both the files in
Reply with quote

No Bill 600 is not coming in the output file
Back to top
View user's profile Send private message
bodatrinadh

Active User


Joined: 05 Jan 2007
Posts: 101
Location: chennai (India)

PostPosted: Wed Feb 06, 2013 2:12 pm    Post subject: Reply to: how to get unpaired records from both the files in
Reply with quote

Raj,

Remove 'REFORMAT FIELDS=' and try.....

Thanks
-3nadh
Back to top
View user's profile Send private message
rajkumarmf1985

New User


Joined: 09 Aug 2010
Posts: 18
Location: bangalore

PostPosted: Wed Feb 06, 2013 2:19 pm    Post subject: Reply to: how to get unpaired records from both the files in
Reply with quote

Hi Nic ,

i am using

STEP01 EXEC PGM=SORT
Back to top
View user's profile Send private message
rajkumarmf1985

New User


Joined: 09 Aug 2010
Posts: 18
Location: bangalore

PostPosted: Wed Feb 06, 2013 2:22 pm    Post subject: Reply to: how to get unpaired records from both the files in
Reply with quote

Hi 3nadh

i tried deleting reformat field i job abended with below message

JOINKEYS REFORMAT RECORD LENGTH= 84, TYPE = V
SORTOUT RECFM INCOMPATIBLE
SORTOUT : RECFM=FB ; LRECL= ; BLKSIZE=
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Wed Feb 06, 2013 3:02 pm    Post subject: Reply to: how to get unpaired records from both the files in
Reply with quote

What are the RECFMs of your inputs?

Run this, please. If you see anything needing to be in hex, display it in hex. Either way, paste it here.

Code:
JOINKEYS FILE=F1,FIELDS=(1,3,A)
JOINKEYS FILE=F2,FIELDS=(1,3,A)
JOIN UNPAIRED,ONLY
REFORMAT FIELDS=(F1:1,7,F2:1,7)
Back to top
View user's profile Send private message
rajkumarmf1985

New User


Joined: 09 Aug 2010
Posts: 18
Location: bangalore

PostPosted: Wed Feb 06, 2013 6:01 pm    Post subject: Reply to: how to get unpaired records from both the files in
Reply with quote

Hi Bill

RECFM of input files are FB with LRECL of 80

i have tried with

Code:
 REFORMAT FIELDS=(F1:1,7,F2:1,7)


i am getting the following output


Code:
----+----1----+----2--
**********************
300           ........
       600    ........


600 is populated at column 8

Code'd, again
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Wed Feb 06, 2013 6:11 pm    Post subject: Reply to: how to get unpaired records from both the files in
Reply with quote

Quote:
STEP01 EXEC PGM=SORT


it tells absolutely nothing about the product You are using

to provide the proper answer we NEED TO KNOW THE BRAND of Your Sort product

SYNCSORT issues WER... messages
DFSORT issues ICE... messages

while providing the same overall functionality each of them has its own quirks

so ... what is the sort product You are using ???
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Wed Feb 06, 2013 6:13 pm    Post subject: Reply to: how to get unpaired records from both the files in
Reply with quote

If you had no 600 in your output before, then I'd guess you have a "pre-defined" or "defined in the JCL" output file, which is not taking account of the size of your REFORMAT record.

Since you got a failure with -3nadh's suggestion, you may even have other problems with your files.

Check that your inputs are both FB.

Check that your output is not pre-defined nor defined (LRECL/RECFM) in the JCL to let Sort set those values, or ensure instead that they are 80 and FB.

If you leave the REFORMAT off, I expect you'd then get the output you expect, even though you may have to review it for "usefulness".
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Wed Feb 06, 2013 6:22 pm    Post subject: Reply to: how to get unpaired records from both the files in
Reply with quote

if the requirement is simply to keep non matching records

an ICETOOL keep nodups migth give a better performance

search the forums and You will find quite a few examples of it
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1714
Location: UK

PostPosted: Wed Feb 06, 2013 6:26 pm    Post subject:
Reply with quote

I think you have been around long enough to know that PGM=SORT is not specifying the actual sort product but an alias. Look at the messages. They will tell you which sort product you are using. There are differences between Syncsort and DFSort - and DFSort questions are posted to the DFSort part of the forum whilst, for some reason, Syncsort questions are in the JCL section.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Wed Feb 06, 2013 6:57 pm    Post subject:
Reply with quote

Hi,

Try this

Code:
JOINKEYS FILE=F1,FIELDS=(1,3,A)                   
JOINKEYS FILE=F2,FIELDS=(1,3,A)                   
JOIN UNPAIRED,ONLY                                 
REFORMAT FIELDS=(F1:1,7,F2:1,7),FILL=X'00'         
SORT FIELDS=COPY                                   
OUTREC IFTHEN=(WHEN=(8,7,CH,EQ,X'00'),BUILD=(1,7)),
       IFTHEN=(WHEN=(1,7,CH,EQ,X'00'),BUILD=(8,7))


It will be great help to forum members if you post all required info

Regards,
Chandan
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed Feb 06, 2013 9:43 pm    Post subject:
Reply with quote

Chandan,

Checking 8,1 & 1,1 in Outrec should suffice
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Thu Feb 07, 2013 4:34 am    Post subject: Reply to: how to get unpaired records from both the files in
Reply with quote

rajkumarmf1985,

Looking back, you show your sample data in sorted order. If it is actually like this, specify SORTED on the JOINKEYS for each file that is in order.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Thu Feb 07, 2013 9:26 am    Post subject:
Reply with quote

Yes Pandora, Agree with you.. I missed it in the flow of posting resolution..
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Feb 08, 2013 11:23 am    Post subject:
Reply with quote

You could have gotten your answer from the very first post of Bill, if you could read that carefully enough.

You've coded
Code:
DCB=(RECFM=FB,LRECL=80,BLKSIZE=27920)
in your JCL, so you won't see "600" in output, which is at position 81.

Again the second hint from Bill, could have told you why "600" did not appear in output with your first Job in this thread but, you said,"600 is populated at column 8".

If you've inputs strictly as what you've posted, try this:

Code:
//JS001   EXEC PGM=SORT         
//*                             
//SYSOUT   DD SYSOUT=*           
//*                             
//SORTJNF1 DD *                 
100                             
200                             
300                             
400                             
//SORTJNF2 DD *                 
100                             
200                             
400                             
600                             
//*                             
//SORTOUT  DD SYSOUT=*           
//*                             
//SYSIN    DD *                 
  SORT FIELDS=COPY               
  JOINKEYS FILE=F1,FIELDS=(1,3,A)
  JOINKEYS FILE=F2,FIELDS=(1,3,A)
  JOIN UNPAIRED,ONLY                   
  REFORMAT FIELDS=(F1:1,3,F2:1,3)       
  OUTREC OVERLAY=(1,80,SQZ=(SHIFT=LEFT))
/*                                     
//*                                     
You'll get, what are you looking for. You could use BUILD as well, though I leave that as an exercise.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Feb 08, 2013 1:26 pm    Post subject: Re: Reply to: how to get unpaired records from both the file
Reply with quote

rajkumarmf1985 wrote:
Hi 3nadh

i tried deleting reformat field i job abended with below message

JOINKEYS REFORMAT RECORD LENGTH= 84, TYPE = V
SORTOUT RECFM INCOMPATIBLE
SORTOUT : RECFM=FB ; LRECL= ; BLKSIZE=
From the manual,
Quote:
if a REFORMAT control statement is not provided and ONLY the unpaired records from both join inputs are requested, the resultant records will be variable-length, regardless of the record formats of the join input data sets, and the record length will be the maximum of any fixed-length input file record length plus four (for an RDW) and any variable-length input file record length.

So if you get rid of DCB on SORTOUT and submit your Job again, you won't get that error.

errr..rr but the "600" will be at 81st posistion and it won't appear the way you want.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Fri Feb 08, 2013 1:55 pm    Post subject: Reply to: how to get unpaired records from both the files in
Reply with quote

Without the REFORMAT and for UNPAIRED,ONLY, both F1 and F2 records will appear in the same position.

After all that, TS will have no clue as to which record comes from where, so I'm reluctant to expend much effort on this :-)
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 High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm


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