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

Join 2 File using a Join Key


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
scorp_rahul23

New User


Joined: 06 May 2008
Posts: 96
Location: Delhi

PostPosted: Tue Aug 14, 2018 7:36 am
Reply with quote

Can someone help in explaining the logic on how to join 2 files as per the requirements below.

Code:

FILE1  [FB , 80 BYTES]
==================

A234567890.....................HELLO
B234567890.....................HELLO
C434567890.....................HELLO
D254567890.....................HELLO



Code:


FILE2  [FB , 80 BYTES]
==================

A234567890.....................BYE
C234567890.....................BYE
E234567890.....................BYE



I want output like this.

Code:


FILE 3    [FB,80 Bytes]
==================
A234567890.....................HELLO     A234567890.....................BYE
B234567890.....................HELLO     NOT PRESENT
C434567890.....................HELLO     C234567890.....................BYE
D254567890.....................HELLO     NOT PRESENT
NOT PRESENT                                 E234567890.....................BYE

Back to top
View user's profile Send private message
scorp_rahul23

New User


Joined: 06 May 2008
Posts: 96
Location: Delhi

PostPosted: Tue Aug 14, 2018 8:53 am
Reply with quote

I tried this below, but this also did not worked.

Code:

//F1ONLY    DD DSN=F9340T.RAMPUP.MERGE.F1ONLY,             
//             DISP=(NEW,CATLG),UNIT=SYSDA,               
//             SPACE=(TRK,(1,1),RLSE)                     
//F2ONLY    DD DSN=F9340T.RAMPUP.MERGE.F2ONLY,             
//             DISP=(NEW,CATLG),UNIT=SYSDA,               
//             SPACE=(TRK,(1,1),RLSE)                     
//MERGE     DD DSN=F9340T.RAMPUP.MERGE,                   
//             DISP=(NEW,CATLG),UNIT=SYSDA,               
//             SPACE=(TRK,(1,1),RLSE)                     
//SYSIN     DD    *                                       
 JOINKEYS FILE=F1,FIELDS=(1,15,A),TYPE=F                   
 JOINKEYS FILE=F2,FIELDS=(1,15,A),TYPE=F                   
  JOIN UNPAIRED,F1,F2                                     
  REFORMAT FIELDS=(?,F1:1,80,F2:1,80)                     
* CONTROL STATEMENTS FOR MAIN TASK (JOINED RECORDS)       
  OPTION COPY                                             
  OUTFIL FNAMES=F1ONLY,INCLUDE=(1,1,CH,EQ,C'1'),           
    BUILD=(1:2,80,81:C' ',82:C'NOT PRESENT IN TEK',93:61X)
  OUTFIL FNAMES=F2ONLY,INCLUDE=(1,1,CH,EQ,C'2'),           
    BUILD=(1:C'NOT PRESENT IN TEK',19:61X,82:82,80)       
  OUTFIL FNAMES=MERGE,INCLUDE=(1,1,CH,EQ,C'B'),           
    BUILD=(1:2,80,81:82,80)   
/*                             
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Tue Aug 14, 2018 11:28 am
Reply with quote

Maybe if you show the log and the output we might be better equipped to help you.

Psychic day isn't until Friday this week
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Tue Aug 14, 2018 7:49 pm
Reply with quote

scorp_rahul23 wrote:
I tried this below, but this also did not worked.

Code:

//F1ONLY    DD DSN=F9340T.RAMPUP.MERGE.F1ONLY,             
//             DISP=(NEW,CATLG),UNIT=SYSDA,               
//             SPACE=(TRK,(1,1),RLSE)                     
//F2ONLY    DD DSN=F9340T.RAMPUP.MERGE.F2ONLY,             
//             DISP=(NEW,CATLG),UNIT=SYSDA,               
//             SPACE=(TRK,(1,1),RLSE)                     
//MERGE     DD DSN=F9340T.RAMPUP.MERGE,                   
//             DISP=(NEW,CATLG),UNIT=SYSDA,               
//             SPACE=(TRK,(1,1),RLSE)                     
//SYSIN     DD    *                                       
 JOINKEYS FILE=F1,FIELDS=(1,15,A),TYPE=F                   
 JOINKEYS FILE=F2,FIELDS=(1,15,A),TYPE=F                   
  JOIN UNPAIRED,F1,F2                                     
  REFORMAT FIELDS=(?,F1:1,80,F2:1,80)                     
* CONTROL STATEMENTS FOR MAIN TASK (JOINED RECORDS)       
  OPTION COPY                                             
  OUTFIL FNAMES=F1ONLY,INCLUDE=(1,1,CH,EQ,C'1'),           
    BUILD=(1:2,80,81:C' ',82:C'NOT PRESENT IN TEK',93:61X)
  OUTFIL FNAMES=F2ONLY,INCLUDE=(1,1,CH,EQ,C'2'),           
    BUILD=(1:C'NOT PRESENT IN TEK',19:61X,82:82,80)       
  OUTFIL FNAMES=MERGE,INCLUDE=(1,1,CH,EQ,C'B'),           
    BUILD=(1:2,80,81:82,80)   
/*                             

As per the example of your desired output you need one single output listing.
In your code example you try to create 3 separate listings.

1) What is the actual requirement?
2) What exactly "did not work(ed)" in your test?
3) For "listing only" purposes it would be much easier to use option FILL=C'-' in your REFORMAT statement, rather than '?' with selection to substitute dummy and senseless words instead of missing pairs.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Tue Aug 14, 2018 8:50 pm
Reply with quote

You were trying to create 3 datasets based on the conditions and probably thought of adding another step to merge them in one data set but you need just one out data set. So understand the below card and this is what you need to do to achieve what you posted.
Code:
//SORT      EXEC  PGM=SORT
//SYSOUT    DD SYSOUT=*
//SORTJNF1  DD *
111.................HELLO
222.................HELLO
//SORTJNF2  DD *
111...................BYE
333...................BYE
//SORTOUT   DD SYSOUT=*
//SYSIN     DD *
 JOINKEYS FILE=F1,FIELDS=(1,3,A),SORTED
 JOINKEYS FILE=F2,FIELDS=(1,3,A),SORTED
 JOIN UNPAIRED,F1,F2
 REFORMAT FIELDS=(F1:1,25,F2:1,25,?)
 OPTION COPY
 OUTFIL IFTHEN=(WHEN=(51,1,CH,EQ,C'B'),BUILD=(1,25,3X,26,25)),
     IFTHEN=(WHEN=(51,1,CH,EQ,C'1'),BUILD=(1,25,3X,C'NOT PRESENT')),
     IFTHEN=(WHEN=(51,1,CH,EQ,C'2'),BUILD=(C'NOT PRESENT',17X,26,25))

Note: Adjust the offsets per your record layout and rerun.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Tue Aug 14, 2018 10:39 pm
Reply with quote

Simplified version with the same final result.
Missing pairs just marked with '-------------------' which is not critical in real life.
Code:
//SORT      EXEC  PGM=SORT
//SYSOUT    DD SYSOUT=*
//SORTJNF1  DD *
111.................HELLO
222.................HELLO
//SORTJNF2  DD *
111...................BYE
333...................BYE
//SORTOUT   DD SYSOUT=*
//SYSIN     DD *
 JOINKEYS FILE=F1,FIELDS=(1,3,A),SORTED
 JOINKEYS FILE=F2,FIELDS=(1,3,A),SORTED
 JOIN UNPAIRED,F1,F2
 REFORMAT FIELDS=(F1:1,25,F2:1,25),FILL=C'-'
 OPTION COPY
 OUTREC BUILD=(1,25,3X,26,25)
 END
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Wed Aug 15, 2018 12:13 am
Reply with quote

Quote:
Missing pairs just marked with '-------------------' which is not critical in real life.
I agree. But some people require things to be told clearly and especially dealing with different group and vendors so it’s good to mention what’s missing in words than having some cryptic way.
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 -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Access to non cataloged VSAM file JCL & VSAM 18
No new posts Need help for File Aid JCL to extract... Compuware & Other Tools 23
No new posts Using Dynamic file handler in the Fil... COBOL Programming 2
Search our Forums:

Back to Top