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

Matching and non matching records using a Key


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

New User


Joined: 14 Dec 2020
Posts: 4
Location: Germany

PostPosted: Tue Jan 12, 2021 1:20 am
Reply with quote

Hi All,

I have a requirement to match a value from F1 with F2 and if that value is found in F2 then write the complete record into F3 and if not found it in F2 then write into F4.
- The match value can be repeated in the file F2; So all the matched records should written into F3.
- match value start from POS 06 in F2
- match value start from POS 01 till 15 in F1

F1 file: FB with Record length . . . : 15
the record 1 - 4 is always have values
5 - is a separator '/'
6 - 15 may have values

eg:
Code:

=COLS> ----+----1----+
****** ***************
000001 0000/123
000002 0001/1234
000003 0010/12345
000004 0100/123456
000005 1000/1234567
000006 1100/12345678
000007 1110/123456789
000008 1111/1234567890


F2 file: VB with Record length . . . : 32000

Code:

=COLS> ----+----1----+----2----+----3----+
****** ***************************** Top o
000001 @(#)@1000/1234567  566  :16R:GENL 
000002 @(#)@1100/12345678  566  :16R:GENL
000003 @(#)@1100/12345678  566  :16R:GENL
000004 @(#)@0110/12345  566  :16R:GENL
000005 @(#)@0000/12345678  566  :16R:GENL
000006 @(#)@1010/1234567890  566  :16R:GENL



expected output:
F3 VB with Record length . . . : 32000
Code:

=COLS> ----+----1----+----2----+----3----+
****** ***************************** Top o
000001 @(#)@1000/1234567  566  :16R:GENL 
000002 @(#)@1100/12345678  566  :16R:GENL
000003 @(#)@1100/12345678  566  :16R:GENL


F4 VB with Record length . . . : 32000
Code:

=COLS> ----+----1----+----2----+----3----+
****** ***************************** Top o
000001 @(#)@0110/12345  566  :16R:GENL
000002 @(#)@0000/12345678  566  :16R:GENL
000003 @(#)@1010/1234567890  566  :16R:GENL
Back to top
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 962
Location: Maryland

PostPosted: Tue Jan 12, 2021 1:35 am
Reply with quote

1. Read any manual on SORT utility regarding JOIN feature
2. Try to test something using examples from the manual
3. Post your results at this forum.
4. In case of a problem people would help you, but not do your job instead of yourself
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Tue Jan 12, 2021 3:27 am
Reply with quote

Welcome!!
You also need to Parse the keys into fixed format using INREC in //JNF2CNTL DD * and then refer that in the join task.
Example 5 - Paired and unpaired F1/F2 records
You can also search this forum and you will find many similar topics and solution posted which will help you. Try it and let us know what's not working.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Wed Jan 13, 2021 3:50 pm
Reply with quote

Sebin89, Please don’t create multiple topics for the same problem, I deleted the other one and reply to this topic with what you tried , the JCL.
Back to top
View user's profile Send private message
Sebin89

New User


Joined: 14 Dec 2020
Posts: 4
Location: Germany

PostPosted: Wed Jan 13, 2021 4:23 pm
Reply with quote

As the above solution will be bit complex. I have changed the question in this way.

---------------------------------------------------------
I am using below SORT step to join records from 2 files. But it is not giving me the expected results. Can you please help with the correct SYSIN statement?

- Input file1 has RECFM=VB and LRECL=32000. It contains the following records:
Code:

----+----1----+----2----+----3----+----4----+----5
***************************** Top of Data ********
1000/1234567   @(#)@1000/1234567  566  :16R:GENL
1100/12345678  @(#)@1100/12345678  566  :16R:GENL
1100/12345678  @(#)@1100/12345678  566  :16R:GENL
0110/12345     @(#)@0110/12345  566  :16R:GENL
0000/12345678  @(#)@0000/12345678  566  :16R:GENL
1010/1234567890@(#)@1010/1234567890  566  :16R:GENL

- Input file2 has RECFM=FB and LRECL=15. It contains the following records:
Code:

----+----1----+
***************
0000/123
0001/1234
0010/12345
0100/123456
1000/1234567
1100/12345678
1110/123456789
1111/1234567890

- Expected output in first file (RECFM=VB and LRECL=32000):
Code:

----+----1----+----2----+----3----+----4----+----5
***************************** Top of Data ********
@(#)@1000/1234567  566  :16R:GENL
@(#)@1100/12345678  566  :16R:GENL
@(#)@1100/12345678  566  :16R:GENL

- Expected output in second file (RECFM=VB and LRECL=32000):
Code:

----+----1----+----2----+----3----+----4----+----5
***************************** Top of Data ********
@(#)@0110/12345  566  :16R:GENL
@(#)@0000/12345678  566  :16R:GENL
@(#)@1010/1234567890  566  :16R:GENL

JCL used:
Code:

//STEP1 EXEC PGM=SORT                                         
//SYSOUT   DD SYSOUT=*                                       
//VBIN DD DSN=P104219.MT566.FULL.RECORDS.ADD.SORTOUT,DISP=SHR
//FBIN DD DSN=P104219.FOMT566.TLWL.T3011.FINAL,DISP=SHR       
//F1MT DD DSN=P104219.MT566.FULL.RECORDS.MATCH,               
//          DISP=(,CATLG,DELETE),SPACE=(CYL,(5,5),RLSE),     
//          LRECL=32000,RECFM=VB                             
//F2NM DD DSN=P104219.MT566.FULL.RECORDS.NONMATCH,           
//          DISP=(,CATLG,DELETE),SPACE=(CYL,(5,5),RLSE),     
//          LRECL=32000,RECFM=VB                             
//SYSIN    DD *                                               
  JOINKEYS FILE=F1,FIELDS=(5,20,A),SORTED                     
  JOINKEYS FILE=F2,FIELDS=(1,15,A),SORTED                     
  JOIN UNPAIRED,F1,F2                                         
  REFORMAT FIELDS=(F1:19)                                     
  OPTION COPY                                                 
  OUTFIL FNAMES=F1MT,BUILD=(19,32000)                         
  OUTFIL FNAMES=F2NM,BUILD=(19,32000)                         
/*
Back to top
View user's profile Send private message
Joerg.Findeisen

Active User


Joined: 15 Aug 2015
Posts: 396
Location: Bamberg, Germany

PostPosted: Wed Jan 13, 2021 5:06 pm
Reply with quote

The first solution is NOT complex. The Job is about 30 lines.
Back to top
View user's profile Send private message
Sebin89

New User


Joined: 14 Dec 2020
Posts: 4
Location: Germany

PostPosted: Wed Jan 13, 2021 5:24 pm
Reply with quote

could you please share the JCL?
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Wed Jan 13, 2021 5:42 pm
Reply with quote

You want the JCL but not the sort control statements (which are NOT JCL)???
Back to top
View user's profile Send private message
Sebin89

New User


Joined: 14 Dec 2020
Posts: 4
Location: Germany

PostPosted: Wed Jan 13, 2021 6:02 pm
Reply with quote

I am fine with anything. sort control or JCL
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Wed Jan 13, 2021 8:24 pm
Reply with quote

Ok - here is some JCL
Code:

//STEP1 EXEC PGM=SORT                                         
//SYSOUT   DD SYSOUT=*                                       
//VBIN DD DSN=P104219.MT566.FULL.RECORDS.ADD.SORTOUT,DISP=SHR
//FBIN DD DSN=P104219.FOMT566.TLWL.T3011.FINAL,DISP=SHR       
//F1MT DD DSN=P104219.MT566.FULL.RECORDS.MATCH,               
//          DISP=(,CATLG,DELETE),SPACE=(CYL,(5,5),RLSE),     
//          LRECL=32000,RECFM=VB                             
//F2NM DD DSN=P104219.MT566.FULL.RECORDS.NONMATCH,           
//          DISP=(,CATLG,DELETE),SPACE=(CYL,(5,5),RLSE),     
//          LRECL=32000,RECFM=VB                             
//SYSIN    DD *
 your sort control cards go here                                               
/*
Back to top
View user's profile Send private message
Joerg.Findeisen

Active User


Joined: 15 Aug 2015
Posts: 396
Location: Bamberg, Germany

PostPosted: Wed Jan 13, 2021 9:47 pm
Reply with quote

Use PARM='MSG=AP' after EXEC PGM=SORT to get all the Messages. Should be known.

Your code uses different key lengths, why? Also the records in F1 are NOT sorted
Code:
JOINKEYS FILE=F1,FIELDS=(5,20,A),SORTED                     
  JOINKEYS FILE=F2,FIELDS=(1,15,A),SORTED

You only want missing entries from F1, F2 is not needed here.
Code:
JOIN UNPAIRED,F1,F2

Read when the other things are fixed what messages you will get. F1:19 will likely not going to work. Also when wanting to separate the data you need an Id to do so. Read about the '?' operator.
Code:
REFORMAT FIELDS=(F1:19)

Consider a SORT statement to keep the original sequence of F1 after the Join.
Code:
OPTION COPY

For both OUTFIL the ident is missing (look up the '?' operator). Also start positions seem to be off. That will not be the last hurdle I guess.
Code:
OUTFIL FNAMES=F1MT,BUILD=(19,32000)                         
  OUTFIL FNAMES=F2NM,BUILD=(19,32000)


As Rohit has said when your F1 (in the first sample provided) has some floating keys, you must parse F1 before the JOINKEYS and provide them as fixed length at the same position.
Back to top
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 962
Location: Maryland

PostPosted: Wed Jan 13, 2021 10:13 pm
Reply with quote

The suggested
JOINKEYS FILE=F1/F2
does not correspond to the JCL code presented in all examples here.

Definitely will not work, causing the TS to ask for help again (and again, and again, and again...)

The right parameter FNAMES= is shown at the very end; need to attract the TS attention to this “extremely complicated issue”.
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 Selecting multiple constant records DB2 3
This topic is locked: you cannot edit posts or make replies. Writng matched records into output fi... DFSORT/ICETOOL 11
No new posts Merging more than two records DFSORT/ICETOOL 15
No new posts REMOVE DUPLICATE RECORDS BASED ON A S... DFSORT/ICETOOL 4
No new posts Using sort on VB records based on off... DFSORT/ICETOOL 6
Search our Forums:

Back to Top