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 records from 2 files with No Duplicates using DFSORT
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 17
Location: India

PostPosted: Sun Aug 27, 2017 10:35 pm    Post subject: Join records from 2 files with No Duplicates using DFSORT
Reply with quote

Hi!
This is my first post in the forum. I am working on a SORT card in which i am reading records from 2 input files and joining them on a 15 byte key. But the issue is, in both the files few records have duplicate key. So due to these duplicate keys it is creating cartesian product of records in output file. My requirement is to write one to one matching record. I am using DFSORT and for this i have searched in the forum that SORTED,NOSEQCK should be used to accomplish my requirement. But even after using this option in my SORT i am not getting the expected output. I can really use some help here as i need to deliver this tomorrow. Please let me know what i am missing here. Many Thanks !
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10373
Location: italy

PostPosted: Mon Aug 28, 2017 1:37 am    Post subject: Reply to: Join records from 2 files with No Duplicates using DFSORT
Reply with quote

do not post images, a plain TEXT cut and paste from Your emulator window is more than enough.

the images will be deleted shortly
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 211
Location: Chennai

PostPosted: Mon Aug 28, 2017 3:35 am    Post subject: Reply to: Join records from 2 files with No Duplicates using DFSORT
Reply with quote

Quote:

I am using DFSORT and for this i have searched in the forum that SORTED,NOSEQCK should be used to accomplish my requirement


SORTED,NOSEQCK indicates records are already in order by the specified binary key, Sequence check will not be performed by dfsort.
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 211
Location: Chennai

PostPosted: Mon Aug 28, 2017 3:54 am    Post subject: Reply to: Join records from 2 files with No Duplicates using DFSORT
Reply with quote

Assuming both the files are in sorted order.

change the joinkeys as follows

UNTESTED
Code:

JOINKEYS FILES=F1,FIELDS=(01,15,A,174,4,A),SORTED,NOSEQCK   
JOINKEYS FILES=F2,FIELDS=(01,15,A,068,4,A),SORTED,NOSEQCK


Try Adding following statements.
Code:

//JNF1CNTL DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(174:SEQ=4))         
//JNF2CNTL DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(068:SEQ=4))         


Let us know the results.
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 211
Location: Chennai

PostPosted: Mon Aug 28, 2017 4:10 am    Post subject:
Reply with quote

NOTE

In case you have more than one record for a key in file1 and only one matching key/record in file2, then only one record would be extracted from the input, the other records would be skipped.
Back to top
View user's profile Send private message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 17
Location: India

PostPosted: Mon Aug 28, 2017 3:04 pm    Post subject: Join records from 2 files with No Duplicates using DFSORT
Reply with quote

Hi Enrico,

I will keep this in mind going forward. Thanks !

Hi Magesh,

The sort card you provided has worked and i am getting the exact results what i was expecting. I will post the results shortly. Thank you very much for this icon_biggrin.gif

I want to understand the meaning of what you coded :
JOINKEYS FILES=F1,FIELDS=(01,15,A,174,4,A),SORTED,NOSEQCK
JOINKEYS FILES=F2,FIELDS=(01,15,A,068,4,A),SORTED,NOSEQCK

Que - In above card for File F1 and File F2, is it sorting on 4 bytes from 174 and 068 ? I am confused about this part. Please guide here.

//JNF1CNTL DD *
INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(174:SEQ=4))
//JNF2CNTL DD *
INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(068:SEQ=4))

Que : what does it means WHEN=GROUP in above card and does the PUSH keyword inserting a sequence no. of 4 bytes here ? If yes, then i will be having more than 6 lakhs records in my files so is it correct if i need to increase the length of SEQ from 4 to 6 ?
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1931
Location: UK

PostPosted: Mon Aug 28, 2017 3:23 pm    Post subject:
Reply with quote

"lakhs" is not understood - please use English terminology.
Back to top
View user's profile Send private message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 17
Location: India

PostPosted: Mon Aug 28, 2017 4:59 pm    Post subject: Join records from 2 files with No Duplicates using DFSORT
Reply with quote

Hi Nic,

I will be having 600,000 records in my file.


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

Active User


Joined: 29 Apr 2008
Posts: 226
Location: Maryland

PostPosted: Mon Aug 28, 2017 7:45 pm    Post subject: Re: Reply to: Join records from 2 files with No Duplicates using DFSORT
Reply with quote

magesh23586 wrote:
Assuming both the files are in sorted order.

change the joinkeys as follows

UNTESTED
Code:

JOINKEYS FILES=F1,FIELDS=(01,15,A,174,4,A),SORTED,NOSEQCK   
JOINKEYS FILES=F2,FIELDS=(01,15,A,068,4,A),SORTED,NOSEQCK


Try Adding following statements.
Code:

//JNF1CNTL DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(174:SEQ=4))         
//JNF2CNTL DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(068:SEQ=4))         

Let us know the results.

It is not obvious that this approach will work, because the topic starter did not provide enough information on his requirements.
What is the role of each of joined files in his example?
For instance, both datasets have two records each with the same key field
Code:
2307914558..023
(assuming non-printed bytes '..' are the same? Or not?)
In terms of defined JOIN operation that means that the output file should include (2 * 2) = 4 output records with the same key; every record of F1 is matching with every record of F2. The given example demonstrates exactly the result which is expected by JOIN definition.

In the suggested solution with sequentially numbered records, for the given example the first matching F1 record will match with the first of F2 record, etc., but not clear if this is what the TS wanted from the beginning?
Let's say there are 100 same key records in F1, and 2 same key records in F2; in that case the SEQ= solution will produce 2 of the first matching records, and the rest 98 records of F1 should be silently ignored.
When using the original TS solution, this case should produce (100 * 2) = 200 output records.

Again: from the TS description it is not clear what is the actual task, and/or what is the role of each F1/F2 in his job?
Back to top
View user's profile Send private message
sergeyken

Active User


Joined: 29 Apr 2008
Posts: 226
Location: Maryland

PostPosted: Mon Aug 28, 2017 8:39 pm    Post subject: Re: Join records from 2 files with No Duplicates using DFSORT
Reply with quote

Poha Eater wrote:
I am using DFSORT and for this i have searched in the forum that SORTED,NOSEQCK should be used to accomplish my requirement.

I would recommend, before you get some basic knowledge about SORT/JOIN/MERGE processing, and used methods, - not to use the NOSEQCK option. Please, postpone even use of the SORTED option - at least until you're able to create yourself (not via copy-paste!) at least primitive, but really working, jobs for DFSORT/SYNCSORT.
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 211
Location: Chennai

PostPosted: Mon Aug 28, 2017 8:45 pm    Post subject: Reply to: Join records from 2 files with No Duplicates using DFSORT
Reply with quote

Poha Eater wrote:

Que - In above card for File F1 and File F2, is it sorting on 4 bytes from 174 and 068 ? I am confused about this part. Please guide here.


Poha Eater wrote:

what does it means WHEN=GROUP in above card


Refer DFSORT Application programming guide.

Quote:

does the PUSH keyword inserting a sequence no. of 4 bytes here ? If yes, then i will be having more than 6 lakhs records in my files so is it correct if i need to increase the length of SEQ from 4 to 6 ?


No, Not required, unless if you have more than 10K records having same key value.

I belive even 2 bytes is sufficient, i dont think you will have more than 99 records having same key.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1826
Location: NY,USA

PostPosted: Mon Aug 28, 2017 9:04 pm    Post subject:
Reply with quote

Welcome!!
Quote:
I am using DFSORT and for this i have searched in the forum that SORTED,NOSEQCK should be used to accomplish my requirement.

You havn't done a proper research.

Why don't you eliminate duplicate before JOINKEYS by adding this piece to what you have posted?
Code:
//JNF1CNTL DD *     
  SUM FIELDS=NONE   
//*

Code:
//JNF2CNTL DD *     
  SUM FIELDS=NONE   
//*

or also try solution provide here or given above by magesh23586,
http://www.ibmmainframeforum.com/dfsort-icetool-icegener/topic5914.html
Back to top
View user's profile Send private message
sergeyken

Active User


Joined: 29 Apr 2008
Posts: 226
Location: Maryland

PostPosted: Mon Aug 28, 2017 9:26 pm    Post subject:
Reply with quote

Rohit Umarjikar wrote:
Welcome!!
Quote:
I am using DFSORT and for this i have searched in the forum that SORTED,NOSEQCK should be used to accomplish my requirement.

You havn't done a proper research.

Why don't you eliminate duplicate before JOINKEYS by adding this piece to what you have posted?
Code:
//JNF1CNTL DD *     
  SUM FIELDS=NONE   
//*

Code:
//JNF2CNTL DD *     
  SUM FIELDS=NONE   
//*

or also try solution provide here or given above by magesh23586,
http://www.ibmmainframeforum.com/dfsort-icetool-icegener/topic5914.html

A have to repeat again: it is not clear what is the role of each F1/F2 in the original task? Why multiple duplicate keys can appear in both F1+F2? What to do with duplicated key in each of them?

Eliminating duplicate keys via SUM FIELDS=NONE will create a single matching record produced, but (in general case) created from an unpredicted pair of matching keys.

Matching the pairs of records via use of re-numbered (via SEQ=) groups of records will create matching pairs from sequential duplicate key records, up to the minimum in each of F1, or F2; while the rest of same key records will be ignored.

The TS must clearly explain what his real requirements are, before trying to use (unknown to him) options of SORT control statements. Before clear understanding of the task, no advice from the Forum, and no control statement option would help. Period.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1826
Location: NY,USA

PostPosted: Tue Aug 29, 2017 10:12 pm    Post subject:
Reply with quote

Quote:
Eliminating duplicate keys via SUM FIELDS=NONE will create a single matching record produced, but (in general case) created from an unpredicted pair of matching keys.

He could use SUM FIELDS on one ds to get what he wants.
Poha Eater, please respond to to clear the air of doubts in the requirements to get it further moving else it can be locked.
Back to top
View user's profile Send private message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 17
Location: India

PostPosted: Wed Aug 30, 2017 12:00 am    Post subject: Join records from 2 files with No Duplicates using DFSORT
Reply with quote

Hi All,

Thanks for all your responses. It was really helpful and informative. Let me reiterate my requirement and before that i accept that i didnt mention in my first post that in 2nd file when the key is same for 2 records but in these 2 records data is not same after the key. I did attached the screenshot of my File 2 which is showing different data for each row when there is duplicate key.

My requirement is - I have 2 files. Each files having a key of first 15 byte (starting from column 1 to 15). First file is having record length of 173 and second file has the record length of 251.

Both the files have exact same number of records. Suppose if file 1 has 1000 records then file 2 will also have 1000 records. In both the files, data only on first 15 bytes will be same, which is also key to my JOIN condition here.

In the output file, i have write all the 173 bytes of data from the File 1 and from File 2 i have to write the data of length 39 and starting from 28th byte.

In the File 1, when there is any record which is having duplicate key, the rest of the data in each record is also same whereas in File 2 whenever there is any duplicate key, the data in each record after the key (after the 15th byte) is not same and as per my requirement both files have exact same number of records so i have to join each row of both files and that is why i could not use the SUM FIELDS=NONE here because in File 2 it will remove the second record with same key but the second record have different data after the first 15 bytes from the first record with same key. I have attached the pics of File 2 data earlier. Pasting the data of File 2 below as well to show that it has different data in each row because i dont know how paste the image of file here. I tried using Img button, nothing is happening, i am sorry.

2307914558,,,,,,,,,,,,MD,1,2013,-62,,FVT,21117,LNL,20100
2307914558,,,,,,,,,,,,MD,1,2013,-93,,FVT,21117,LNL,8500G

Thank you magesh23586 for providing the resolution. The results are coming exactly as per the attached image from my first post, named as expected output.

Thanks all for your responses !!
Back to top
View user's profile Send private message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 17
Location: India

PostPosted: Wed Sep 06, 2017 2:03 am    Post subject:
Reply with quote

magesh23586 wrote:
NOTE

In case you have more than one record for a key in file1 and only one matching key/record in file2, then only one record would be extracted from the input, the other records would be skipped.


Hi Magesh,
Due to enhancement in my requirement, now i have more than 1 record for a key in File 1 but only 1 matching record in File 2. So is it possible by any means to still join the one to one record based on same key and also get all the records form file 1 for the same key in output file ?

Now i don't have equal number of records in File 1 and File 2.

Example :

File 1 File2
********************************* *************************
0000L01455 ,xxx,yyy,ABCDEF, 0000L01455 ,2013,-2200.22,
4814375 ,xxx,yyy,GHIJKLMNO, 4814375 ,2013,-5122,
4814375 , , ,PQRSTUV, 0000L01762 ,2013,-100,
0000L01762 ,xxx,yyy,WXYZAB, 0000L02594 ,2013,-1056,
0000L02594 ,xxx,yyy,CDEFGHIJKL, 4828156 ,2013,-3369,
4828156 ,xxx,yyy,MNOPQRST, 0000L02908 ,2013,-2440,
4828156 , , ,UVWXY, 2307914558 ,2013,-62,
0000L02908 ,xxx,yyy,ZABCDEFGH, 2307914558 ,2013,-93,
2307914558 ,xxx,yyy,IOSIF W,
2307914558 ,xxx,yyy,IOSIF W,


Output should look like below : (Key is first 15 byte in both the files)

**********************************************************
0000L01455,xxx,yyy,ABCDEF,2013,-2200.22,
4814375,xxx,yyy,GHIJKLMNO,2013,-5122,
4814375, , ,PQRSTUV,
0000L01762,xxx,yyy,WXYZAB,2013,-100,
0000L02594,xxx,yyy,CDEFGHIJKL,2013,-1056,
4828156,xxx,yyy,MNOPQRST,2013,-3369,
4828156, , ,UVWXY,
0000L02908,xxx,yyy,ZABCDEFGH,2013,-2440,
2307914558,xxx,yyy,IOSIF W,2013,-62,
2307914558,xxx,yyy,IOSIF W,2013,-93,
Back to top
View user's profile Send private message
sergeyken

Active User


Joined: 29 Apr 2008
Posts: 226
Location: Maryland

PostPosted: Wed Sep 06, 2017 2:24 am    Post subject:
Reply with quote

Poha Eater wrote:
Due to enhancement in my requirement, now i have more than 1 record for a key in File 1 but only 1 matching record in File 2. So is it possible by any means to still join the one to one record based on same key and also get all the records form file 1 for the same key in output file ?

Now i don't have equal number of records in File 1 and File 2.

Example :

Code:
File 1                                  File2
*********************************    *************************   
0000L01455    ,xxx,yyy,ABCDEF,         0000L01455     ,2013,-2200.22,
   4814375       ,xxx,yyy,GHIJKLMNO,      4814375        ,2013,-5122,
   4814375       ,   ,    ,PQRSTUV,    0000L01762     ,2013,-100,
0000L01762    ,xxx,yyy,WXYZAB,         0000L02594    ,2013,-1056,
0000L02594    ,xxx,yyy,CDEFGHIJKL,        4828156        ,2013,-3369,
   4828156       ,xxx,yyy,MNOPQRST,    0000L02908    ,2013,-2440,
   4828156       ,   ,    ,UVWXY,      2307914558    ,2013,-62,
0000L02908    ,xxx,yyy,ZABCDEFGH,      2307914558    ,2013,-93,
2307914558    ,xxx,yyy,IOSIF W,
2307914558    ,xxx,yyy,IOSIF W,


Output should look like below : (Key is first 15 byte in both the files)

Code:
**********************************************************
     0000L01455,xxx,yyy,ABCDEF,2013,-2200.22,
        4814375,xxx,yyy,GHIJKLMNO,2013,-5122,
        4814375,   ,    ,PQRSTUV,
     0000L01762,xxx,yyy,WXYZAB,2013,-100,
     0000L02594,xxx,yyy,CDEFGHIJKL,2013,-1056,
        4828156,xxx,yyy,MNOPQRST,2013,-3369,
        4828156,   ,    ,UVWXY,
     0000L02908,xxx,yyy,ZABCDEFGH,2013,-2440,
     2307914558,xxx,yyy,IOSIF W,2013,-62,
     2307914558,xxx,yyy,IOSIF W,2013,-93,



1. RTFM:

1.1 JOINKEYS statement
1.2 JOIN statement
1.3 REFORMAT statement

2. This must be enough to start your task

2.1 create your own code to perform the task
2.2 perform test run
2.3 verify the results, and/or error codes
2.4 fix obvious errors
2.5 present to the forum what is really not clear to yourself

Please, do not try to do the above said in opposite order!
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 211
Location: Chennai

PostPosted: Wed Sep 06, 2017 7:57 am    Post subject: Reply to: Join records from 2 files with No Duplicates using DFSORT
Reply with quote

Try adding Join statement after joinkeys.
Code:


JOINKEYS FILES=F1,FIELDS=(01,15,A,174,4,A),SORTED,NOSEQCK   
JOINKEYS FILES=F2,FIELDS=(01,15,A,068,4,A),SORTED,NOSEQCK
JOIN UNPAIRED,F1


But how do you determine which record to join and which record not to join ?

Example

Your joined records
Code:
   
4814375,xxx,yyy,GHIJKLMNO,2013,-5122,
4814375,   ,    ,PQRSTUV,

why not like below ?
Code:

4814375,xxx,yyy,GHIJKLMNO,
4814375,   ,    ,PQRSTUV,2013,-5122,



There should be some relationship/logic behind it, you need to understand the requirement correctly else you may have incorrect output.
Back to top
View user's profile Send private message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 17
Location: India

PostPosted: Wed Sep 06, 2017 12:20 pm    Post subject:
Reply with quote

Hi Magesh,

Thanks for answering. The requirement is to Join the first record for any key from File1 to the first record of that key from File 2 (2nd File will have only 1 matching key/record) and 1st File can have more than 1 records per key, then those records should also be merged in the output file with the Joined records in the same serial as it was giving in 1st File.

I believe i also need to add the below code as well in SORT as i still have multiple records for 1 key in both files for which i need to do 1 to 1 matching (first record for a key from File 1 should be joined with the first record of File 2 and 2nd record for the same key should only join with the 2nd record of File 2 , there should be no cartesian product of records while joining (as i posted in my first requirement)).

Code:
//JNF1CNTL DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(174:SEQ=4))         
//JNF2CNTL DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(068:SEQ=4))
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 211
Location: Chennai

PostPosted: Wed Sep 06, 2017 6:43 pm    Post subject:
Reply with quote

Poha Eater wrote:
Hi Magesh,

Thanks for answering. The requirement is to Join the first record for any key from File1 to the first record of that key from File 2 (2nd File will have only 1 matching key/record) and 1st File can have more than 1 records per key, then those records should also be merged in the output file with the Joined records in the same serial as it was giving in 1st File.

I believe i also need to add the below code as well in SORT as i still have multiple records for 1 key in both files for which i need to do 1 to 1 matching (first record for a key from File 1 should be joined with the first record of File 2 and 2nd record for the same key should only join with the 2nd record of File 2 , there should be no cartesian product of records while joining (as i posted in my first requirement)).

Code:
//JNF1CNTL DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(174:SEQ=4))         
//JNF2CNTL DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(068:SEQ=4))


Still ur answer is not convincing..Good luck.

Yes, you need to add those statements.
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 -> DFSORT/ICETOOL All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Merging 2 records at multiple rows wi... Bijesh DFSORT/ICETOOL 1 Wed Dec 06, 2017 1:50 am
No new posts Formatting using DFsort Guru Nandu DFSORT/ICETOOL 5 Thu Nov 30, 2017 7:33 am
No new posts Compare VSAM files using DFSORT pshongal DFSORT/ICETOOL 5 Thu Nov 23, 2017 10:24 am
No new posts Compare and COPY using DFSORT pshongal DFSORT/ICETOOL 3 Fri Nov 17, 2017 9:49 am
No new posts Split files upto certain limit based ... bubbu75 DFSORT/ICETOOL 5 Tue Nov 14, 2017 11:11 pm

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