IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search Log in to check your private messages Log in
 

Weirdo results with JOINKEYS


 
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
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
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: 446
Location: USA

PostPosted: Wed Oct 18, 2017 9:36 pm
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
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 Member


Joined: 29 Apr 2008
Posts: 875
Location: Maryland

PostPosted: Wed Oct 18, 2017 10:43 pm
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
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 All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Forum Replies
No new posts JOINKEYS to match two input files on ... DFSORT/ICETOOL 2
No new posts COBOL NOADVANCING strange results in ... COBOL Programming 4
No new posts partitioning row-num and aggregation ... DB2 0
No new posts New JOINKEYS Head Scratcher DFSORT/ICETOOL 2
No new posts Joinkeys overlay and unmatched with O... DFSORT/ICETOOL 11

Back to Top