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
 
Weirdo results with JOINKEYS

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Balaryan
Warnings : 2

New User


Joined: 20 Nov 2009
Posts: 27
Location: chennai

PostPosted: Wed Oct 18, 2017 9:04 pm    Post subject: Weirdo results with JOINKEYS
Reply with quote

Hi There,

I am trying to compare two files using a key and write to build one output file based on the conditions like, when a MATCH found, write the output from file-1 (col 1,59) and file-2 (29,09) content. When no match found, then write output from File-01 (col 1,59) along with '0000.0000'.

Code:

OPTION COPY
  JOINKEYS FILE=F1,FIELDS=(01,10,A,12,04,A)               
  JOINKEYS FILE=F2,FIELDS=(01,10,A,12,04,A)               
  REFORMAT FIELDS=(F1:1,59,?,F2:29,09)                   
  OUTREC IFTHEN=(WHEN=(60,01,CH,EQ,C'1'),                 
    BUILD=(01:01,39,X,41:40,20,61:C'0000.0000')),         
    IFTHEN=(WHEN=(60,01,CH,EQ,C'B'),                     
    BUILD=(01:01,39,40:51,09,50:49,11,61:61,09)),
  IFOUTLEN=69


When I execute the aforementioned code, it has more millions of records in output even if I have input file records dint exceed few hundred thousand records. Looks like it has a lot of duplicate records build in the output file. When I include SUM FIELDS=NONE and eliminate spaces ' ' in column 1 to 59 to ignore duplicate and invalid records, I am getting the desired output.

I really don't understand what was an issue behind this much millions of records in output file. Any expertise advise would help. Thanks in advance.
Back to top
View user's profile Send private message

RahulG31

Active User


Joined: 20 Dec 2014
Posts: 408
Location: USA

PostPosted: Wed Oct 18, 2017 9:36 pm    Post subject: Reply to: Weirdo results with JOINKEYS
Reply with quote

As a simple test, if you have 10 records in each file all having same key values then you'll get 100 records in the output i.e. 10 times more than what is in the input file(s). The more the number of identical key records, the more it gets multiplied (Cartesian product).

So, if you have thousands of records (where duplicates can happen) then, it's no surprise to expect millions in the output.

.
Back to top
View user's profile Send private message
Balaryan
Warnings : 2

New User


Joined: 20 Nov 2009
Posts: 27
Location: chennai

PostPosted: Wed Oct 18, 2017 10:00 pm    Post subject:
Reply with quote

Hi Rahul,

Just curious, is there any way to have only the actual unique records without using 'SUM FIELDS=NONE' in this process?
Back to top
View user's profile Send private message
sergeyken

Active User


Joined: 29 Apr 2008
Posts: 212
Location: Maryland

PostPosted: Wed Oct 18, 2017 10:43 pm    Post subject:
Reply with quote

As discussed many times in this forum, the typical approach is:

1) assign unique ID (sequence number) to each input record with equal key of both input datasets

AAA -> AAA+001
AAA -> AAA+002
AAA -> AAA+003
BBB -> BBB+001
BBB -> BBB+002
CCC -> CCC+001
CCC -> CCC+002
CCC -> CCC+003
CCC -> CCC+004
. . . etc. . . . . . . (for both input files)

2) JOIN the records on combined keys, including just assigned unique ID as key part. If the number of records with the same original key in one file is greater than in another file, they will not match because of non-matching assigned unique ID.

The details of implementation must be obvious. If not, then:
1) RTFM
2) GOTO beginners forum
Back to top
View user's profile Send private message
Balaryan
Warnings : 2

New User


Joined: 20 Nov 2009
Posts: 27
Location: chennai

PostPosted: Wed Oct 18, 2017 11:38 pm    Post subject:
Reply with quote

Thanks Sergeyken. I got it.
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
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts JOINKEYS Paired and Unpaired Sort on ... MFwhiz DFSORT/ICETOOL 7 Tue Nov 07, 2017 2:46 am
No new posts JOINKEYS Paired and Unpaired Sort MFwhiz DFSORT/ICETOOL 4 Fri Sep 29, 2017 1:48 am
No new posts getting three output files out of JOI... migusd SYNCSORT 7 Mon Jun 05, 2017 11:30 pm
No new posts Joinkeys with condition scdinesh DFSORT/ICETOOL 10 Tue Feb 14, 2017 12:20 am
No new posts Joinkeys with decimal scdinesh DFSORT/ICETOOL 3 Fri Feb 10, 2017 4:46 am

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