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

JOIN Paired & Unpaired in SORT


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
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
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: 2481
Location: @my desk

PostPosted: Wed Nov 26, 2008 7:25 pm
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: 2481
Location: @my desk

PostPosted: Wed Nov 26, 2008 7:26 pm
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
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
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: 2481
Location: @my desk

PostPosted: Fri Nov 28, 2008 10:16 am
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
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: 2481
Location: @my desk

PostPosted: Fri Nov 28, 2008 1:20 pm
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
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: 2481
Location: @my desk

PostPosted: Fri Nov 28, 2008 6:00 pm
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> JCL & VSAM

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts JCL sort card - get first day and las... JCL & VSAM 9
No new posts Sort First/last record of a subset th... DFSORT/ICETOOL 7
No new posts how to calculate SUM value for VB fil... DFSORT/ICETOOL 1
No new posts how to calculate SUM for VB file usin... JCL & VSAM 1
Search our Forums:

Back to Top