Can we do Join Pair and Unpair on different key flds



Support for NetApp SyncSort for z/OS, Visual SyncSort, SYNCINIT, SYNCLIST and SYNCTOOL

Can we do Join Pair and Unpair on different key flds

Postby Mann_B » Tue Jul 17, 2012 7:43 pm

Hi All

Can we do Join Pair and Unpair on different key flds in single Sort step ?
I have 2 files ,file1 & file2 .I need to Pair 2 files for the based on 2 different key fields .The O/p should have record from 2nd file which is matched on first field (fld1) and not matched on second field(fld2).

File 1:
fld 1 fld2
---------------
AAAA ZZZZ
BBBBB YYYY
CCCC XXXXX

File 2:
fld 1 fld2
---------------
AAAA KKKK
BBBBB YYYY
DDDD XXXXX


If I have to match on first and second field,My output should be AAAA KKKK (from FIle 2).For the first record in file2,the 2 files match on first field but not matched on second field.

I have searched and I could see we have different syntax for Pair and Unpair on same key field.But I could n ot find it on different key field.
Can you please let me know if this is possible using joins?
Mann_B
 
Posts: 79
Joined: Wed Mar 31, 2010 11:48 am
Has thanked: 0 time
Been thanked: 0 time

Re: Can we do Join Pair and Unpair on different key flds

 

Re: Can we do Join Pair and Unpair on different key flds

Postby BillyBoyo » Tue Jul 17, 2012 8:32 pm

The key for JOINKEYS does not have to be in the same position on the two files.

I'm not sure if this answers your question, so if it doesn't please do a better job of explaining, with a sample expected output for your given inputs.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Can we do Join Pair and Unpair on different key flds

Postby NicC » Tue Jul 17, 2012 9:48 pm

Try posting in the correct section. This is JCL. If you use DFSORT you should have posted there , SYNCSORT then the SYNCSORT section. Tell us which and the post will be moved to the correct section.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Can we do Join Pair and Unpair on different key flds

Postby Mann_B » Wed Jul 18, 2012 11:32 am

HI NicC

Thank you.We use DFSORT..
Mann_B
 
Posts: 79
Joined: Wed Mar 31, 2010 11:48 am
Has thanked: 0 time
Been thanked: 0 time

Re: Can we do Join Pair and Unpair on different key flds

Postby Mann_B » Wed Jul 18, 2012 12:34 pm

HI BillyBoyo

Thank you for the reply.
"The key for JOINKEYS does not have to be in the same position on the two files".

I have tested JOINKEYS with different positions on 2 files.My Query here is different.I need to Pair on 2 key fields.
Consider below are 2 files.

File 1:
fld 1 fld2
(Pos1-4) (Pos9-4)
--------------
AAAA ZZZZ
BBBBB YYYY
CCCC XXXXX

File 2:
fld 1 fld2
(Pos1-4) (Pos9-4)
--------------
AAAA KKKK
BBBBB YYYY
DDDD XXXXX


The 2 files should match on fld1 and should not match on fld2.
My o/p after pairing should be AAAA KKKK (from file 2).fld1 from file1 and file 2 is AAAA which is same in both files,where in fld 2 is different.
I have tested Pairing based on more than one field and from different positions.But I am not sure if we can do such pair where Match based on one field and if the record is matched on fld 1 ,chk if it matches on fld2.If it matched on fld1 and unmatches on fld2 only then write to out file.

We can perform this in 2 steps.
1)Pair based on fld1:
JOINKEYS F1=INA,FIELDS=(1,4,A)                             
JOINKEYS F2=INB,FIELDS=(1,4,A)
REFORMAT FIELDS=(F1:1,80)


The paired records from File1 need to be matched again on fld2.

2)Unpair based on fld2:
F1 here is matched out file from Previous step which will have matched records on fld1 from File1.

JOINKEYS F1=INA,FIELDS=(9,4,A)                             
JOINKEYS F2=INB,FIELDS=(9,4,A)
JOIN UNPAIRED,F2,ONLY


This gives record from File 2 which is having match on fld1 from both files(from step1) and unmatch on fld 2.

But this is like reading same set of files twice.So can we perform the matchign from 2 steps in a Single step?

Please let me know if more details are required.
Mann_B
 
Posts: 79
Joined: Wed Mar 31, 2010 11:48 am
Has thanked: 0 time
Been thanked: 0 time

Re: Can we do Join Pair and Unpair on different key flds

Postby BillyBoyo » Wed Jul 18, 2012 5:49 pm

I think you have a potential for "false hits" with your two-step approach, if the second key is not unique across the file.

If you do the JOINKEYS on the first key, you can use IFTHEN=(WHEN=(logicalexpression) to test the values in the second key position, and then arrange, via a "flag" and OUTFIL OMIT/INCLUDE to only write the records where you have a match, and the second key is unmatched.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Can we do Join Pair and Unpair on different key flds

Postby skolusu » Wed Jul 18, 2012 10:47 pm

Mann_B,

Use the following DFSORT JCL which will give you the desired results.

//STEP0100 EXEC PGM=SORT                               
//SYSOUT   DD SYSOUT=*                                 
//INA      DD *                                         
----+----1----+----2----+----3----+----4----+----5----+-
AAAA    ZZZZ                                           
BBBB    YYYY                                           
CCCC    XXXX                                           
//INB      DD *                                         
AAAA    KKKK                                           
BBBBB   YYYY                                           
DDDD    XXXX                                           
//SORTOUT  DD SYSOUT=*                                 
//SYSIN    DD *                                         
  OPTION COPY                                           
  JOINKEYS F1=INA,FIELDS=(1,4,A)                       
  JOINKEYS F2=INB,FIELDS=(1,4,A)                       
  JOIN UNPAIRED,F2                                     
  REFORMAT FIELDS=(F2:1,80,?,F1:9,4)                   
  OUTFIL BUILD=(1,80),                                 
  INCLUDE=(81,1,CH,EQ,C'B',AND,(9,4,CH,NE,82,4,CH),OR, 
          (81,1,CH,EQ,C'2'))                           
//*



The output from this job is
AAAA    KKKK 
DDDD    XXXX 
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Can we do Join Pair and Unpair on different key flds

Postby Mann_B » Fri Jul 20, 2012 12:07 pm

HI BillyBoyo

Can you please tell me syntax for IFTHEN=(WHEN=(logicalexpression) synatx for unmatching on second key. I have searched but could not get how exactly we can use it on a record after Pairing on first key.
Mann_B
 
Posts: 79
Joined: Wed Mar 31, 2010 11:48 am
Has thanked: 0 time
Been thanked: 0 time

Re: Can we do Join Pair and Unpair on different key flds

Postby BillyBoyo » Fri Jul 20, 2012 12:20 pm

Kolusu's solution is better. Did you try it? Puts the "IF" together with the INCLUDE, just by using INCLUDED instead of what I suggested.

If you search/browse the forum you'll find many examples of IFTHEN. Look at the DFSORT Getting Started as a good way to... get started.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Can we do Join Pair and Unpair on different key flds

Postby Mann_B » Fri Jul 20, 2012 12:56 pm

Thanks BillyBoyo .
Hi skolusu

As mentioned the oupt should be only AAAA KKKK (from file 2 - match on fld 2 and unmatch on fld2).I tried to sub the JCL you have given .But I am getting errors.

Can you please let me what exaclty is
REFORMAT FIELDS=(F2:1,80,?,F1:9,4)

INCLUDE=(81,1,CH,EQ,C'B',AND,(9,4,CH,NE,82,4,CH),OR,
(81,1,CH,EQ,C'2'))

My jobs is saying "SORTOUT INCLUDE/OMIT FIELD BEYOND RECORD ".

Thank you.
Mann_B
 
Posts: 79
Joined: Wed Mar 31, 2010 11:48 am
Has thanked: 0 time
Been thanked: 0 time

Next

Return to Syncsort/Synctool

 


  • Related topics
    Replies
    Views
    Last post