Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
JOIN paired, Unpaired

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

New User


Joined: 18 Jun 2008
Posts: 9
Location: chennai

PostPosted: Wed Nov 26, 2008 3:10 pm    Post subject: JOIN paired, Unpaired
Reply with quote

I was trying to compare file F1 and F2 to get all the records in F1 which is not there in F2.

PLease see the code below.

Code:
JOINKEYS FILES=F1,FIELDS=(1,6,A)   
JOINKEYS FILES=F2,FIELDS=(1,6,A)   
JOIN UNPAIRED F1,ONLY             
REFORMAT FIELDS=(F1:1,6)           
SORT FIELDS=COPY             


but I am getting records which are matching and some other junk values along with the correct values.
I am using sorted files.

Could someone help me out.

Thanks
sree
Back to top
View user's profile Send private message

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Wed Nov 26, 2008 7:25 pm    Post subject:
Reply with quote

sreerocks,

I dont see anything wrong with the control cards you have used. Make sure your file postions are correct. Can you post some sample records with key values and also the output you are getting, using the "Code" tag.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Wed Nov 26, 2008 7:26 pm    Post subject:
Reply with quote

Also the input/output file attributes... RECFM/LRECL etc..
Back to top
View user's profile Send private message
sreerocks

New User


Joined: 18 Jun 2008
Posts: 9
Location: chennai

PostPosted: Thu Nov 27, 2008 2:09 pm    Post subject: Reply to: JOIN paired, Unpaired
Reply with quote

Hi,

I got it..

I missed one comma after the command UNPAIRED.
Now it is coming correct.

It should be
JOIN UNPAIRED,F1,ONLY

Thanks for the help..
Back to top
View user's profile Send private message
sasikumar1984

Active User


Joined: 02 Jul 2007
Posts: 109
Location: Chennai - India

PostPosted: Fri Nov 28, 2008 10:05 am    Post subject: I need similar output
Reply with quote

Hi,

I too have almost similar type of requirement.

I have two files F1 and F2... Both have different data and LRECL is 80 and FB mode. I want compare two fields from file F1 say 3rd variable and 5th variable in File F1 with 6th variable and 8 th variable in F2. If match then i want file f1 record to one file and unmatching record to another file. So my output should be two file one with match and another with unmatch. I want file F1 records only in the bothe files. I don't want any data from file 2.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Fri Nov 28, 2008 10:16 am    Post subject:
Reply with quote

Sasikumar,

If I understand correctly,you are trying to write matched records from file-1 into OUT1 and unmatched records from file-1 into OUT2.
What are the positions and lengths of the key fields in both the files?
Back to top
View user's profile Send private message
sasikumar1984

Active User


Joined: 02 Jul 2007
Posts: 109
Location: Chennai - India

PostPosted: Fri Nov 28, 2008 12:32 pm    Post subject:
Reply with quote

Yes I have a variable in File 1 at 7th byte and length is 9 and another variable at 20th byte and length is 12, I want these two files to be matched with file 2. In file 2 the first variable is at 1st byte and length is 9 and second variable is at 15 position and lenght is 12 bytes. If file 1 variables matches with File 2 variables then i want file 1 full record (LRecl is 80) to one file and unmatched records to another file.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Fri Nov 28, 2008 1:20 pm    Post subject:
Reply with quote

Sasikumar,

I dont have access to mainframe now. But I think the below Syncsort JOIN UNPAIRED application would do the job for you. Try this and post back if you face any issues.
Code:
//STEP1    EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//MATCH    DD SYSOUT=*
//NOMATCH  DD SYSOUT=*
//SORTJNF1 DD DSN=file-1
//SORTJNF2 DD DSN=file-2
//SYSIN    DD *
 JOINKEYS FILES=F1,FIELDS=(7,9,A,20,12,A)
 JOINKEYS FILES=F2,FIELDS=(1,9,A,15,12,A)
 JOIN UNPAIRED,F1
 REFORMAT FIELDS=(F1:1,80,F2:1,1)
 SORT FIELDS=COPY
 OUTFIL FNAMES=MATCH,INCLUDE=(81,1,CH,NE,C' '),BUILD=(1,80)
 OUTFIL FNAMES=NOMATCH,SAVE,BUILD=(1,80)
/*
Back to top
View user's profile Send private message
sasikumar1984

Active User


Joined: 02 Jul 2007
Posts: 109
Location: Chennai - India

PostPosted: Fri Nov 28, 2008 5:34 pm    Post subject:
Reply with quote

It works as expected. But I have few questions:

1) INCLUDE=(81,1,CH,NE,C' '),BUILD=(1,80), why we arr using this when writing match records??

2) SAVE,BUILD=(1,80) , why we are using this when writing unmatch file.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Fri Nov 28, 2008 6:00 pm    Post subject:
Reply with quote

Sasikumar,

You're welcome. Please dont do double-posting.
Code:
 JOIN UNPAIRED,F1
This will write all the records from file-1 into output (matching + non-matching).
Code:
 REFORMAT FIELDS=(F1:1,80,F2:1,1)
Here I have taken the first byte of the file-2 key as an indicator for further processing. This byte will have a value not = ' ' for every match. For non-matching records, the default fill-byte is ' ' which gets routed to OUT2 via SAVE which captures the remaining records.

You can refer the Syncsort manual to get to know more about this. If you don't have one, contact Syncsort support team or send a PM to Alissa.
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Mon Dec 01, 2008 4:13 pm    Post subject: Re: JOIN paired, Unpaired
Reply with quote

sreerocks wrote:

I am using sorted files.


in that case you can add SORTED to avoid sorting:
Code:
JOINKEYS FILES=F1,FIELDS=(1,6,A),SORTED
JOINKEYS FILES=F2,FIELDS=(1,6,A),SORTED


hth
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 JOINKEYS Paired and Unpaired Sort MFwhiz DFSORT/ICETOOL 4 Fri Sep 29, 2017 1:48 am
No new posts Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 22 Sun Aug 27, 2017 10:35 pm
No new posts Reduce CPU Times for Join Sort santoshn SYNCSORT 12 Sat Jun 10, 2017 1:40 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us