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

Compare 2 files and retrive records from one file with key


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

New User


Joined: 20 Apr 2024
Posts: 6
Location: Hyderabad

PostPosted: Wed Apr 24, 2024 11:37 pm
Reply with quote

Hi Team, I am new to DFSORT/ICETOOL.

I have 2 files, both of LRECL = 133 & RECFM = FB.
File 1 has key from position 22 to position 29.
File 2 has key from position 1 to position 8.

I need a ICETOOL condition when the key(1-8) from file 2 matches key(22-29) on file 1, write the complete record of the key match from file 1 to Out record data set(Output).

NOTE: File 2 has unique key records and File 1 has multiple keys and for each key they have multiple records/rows(I need the key record and the associated records/rows for that key.

Example:
File 1 Record Layout: Please find attachment for clear File 1 layout
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
12345678XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
23456789XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
34567891XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX



File 2 Record Layout: Please find attachment for clear File 2 layout
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
XXXX-XXXX-XXXXXXXX 12345678 XXXXX XXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXX
XXXXXXXXXXXXX
XXXXXXXXXXXXX
XXXXXXXXXXXXX
XXXXXXXXX XXXXXXXXXXXXX
XXXXXX XXXXXXXX XXXXXXXXXXXXX

XXXX-XXXX-XXXXXXXX 12345678 XXXXXXXX

XXXXXXXXXXXXX
XXXX-XXXX-XXXXXXXX 23456789 XXXXXXXX

XXXX-XXXX-XXXXXXXX 23456789 XXXXXXXX XXXXXXXX XXXXXXXXXXXXX
XXXXXXXXXXXXX
XXXXXXXXXXXXX
XXXXXXXXXXXXX
XXXXXXXXXXXXX



Expected output: Example if Key: 12345678 matches in file 1
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
XXXX-XXXX-XXXXXXXX 12345678 XXXXX XXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXX
XXXXXXXXXXXXX
XXXXXXXXXXXXX
XXXXXXXXXXXXX
XXXXXXXXX XXXXXXXXXXXXX
XXXXXX XXXXXXXX XXXXXXXXXXXXX


My Code:
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
//TOOLIN    DD *                                                       
  COPY FROM(INB) TO(TMP) USING(CTL1)                                   
                                                                       
  SPLICE FROM(INA) TO(TMP) ON(267,8,CH) KEEPNODUPS KEEPBASE -           
  WITHALL WITH(01,133) USING(CTL2)                                     
                                                                       
  SPLICE FROM(TMP) TO(OUT) ON(1,133,CH) WITHALL  -                     
    WITH(134,134) KEEPNODUPS KEEPBASE USING(CTL3)                       
                                                                       
                                                                       
//CTL1CNTL DD *                                                         
  INREC OVERLAY=(267:C'00')                                             
//*                                                                     
//CTL2CNTL DD *                                                         
  OPTION COPY                                                           
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(267:SEQNUM,8,ZD)),                   
  IFTHEN=(WHEN=(1,8,CH,EQ,C'12345678'),                                 
          OVERLAY=(134:1,133,SEQNUM,8,ZD)),                             
  IFTHEN=(WHEN=NONE,OVERLAY=(275:SEQNUM,8,ZD,                           
          267:267,8,ZD,SUB,275,8,ZD,M11,LENGTH=8))                     
                                                                       
  OUTFIL FNAMES=TMP,BUILD=(134,133,1,133,C'11')                         
//*                                                                     
//CTL3CNTL DD *                                                         
  OUTFIL FNAMES=OUT,INCLUDE=(267,2,ZD,EQ,10),BUILD=(134,133)           
//*     



When Key from File 2 matches key on File 1(even in multiple cases) write the complete record of file 1 into output
Example from above layout:
Key 12345678 from file 2 matches 2 times in file 1 with different record count like first occurrence in file 1 with key 12345678 has 8 records(including the Key record), I want all the 8 records into output (Matched records). Second occurrence with same key: 12345678 has 3 records which i want to put them in output 1(matched records) as well.

Please help me on this if you can.
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


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

PostPosted: Thu Apr 25, 2024 7:53 am
Reply with quote

Your layout does not match the descriptions for it.

File 1 has key from position 22 to position 29.
File 2 has key from position 1 to position 8.
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


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

PostPosted: Thu Apr 25, 2024 8:04 am
Reply with quote

Demo how to do it:

Code:
//WHATEVER EXEC PGM=ICEMAN                                             
//F1       DD *                                                         
12345678XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
23456789XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
34567891XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
/*                                                                     
//F2       DD *                                                         
XXXX-XXXX-XXXXXXXX 12345678 XXXXX XXXXXXXXXXXXXXXXXXXXX                 
XXXXXXXXXXXXX                                                           
XXXXXXXXXXXXX                                                           
XXXXXXXXXXXXX                                                           
XXXXXXXXXXXXX                                                           
XXXXXXXXX XXXXXXXXXXXXX                                                 
XXXXXX XXXXXXXX XXXXXXXXXXXXX                                           
                                                                       
XXXX-XXXX-XXXXXXXX 12345678 XXXXXXXX                                   
                                                                       
XXXXXXXXXXXXX                                                           
XXXX-XXXX-XXXXXXXX 23456789 XXXXXXXX                                   
                                                                       
XXXX-XXXX-XXXXXXXX 23456789 XXXXXXXX XXXXXXXX XXXXXXXXXXXXX             
XXXXXXXXXXXXX                                                           
XXXXXXXXXXXXX                                                           
XXXXXXXXXXXXX                                                           
XXXXXXXXXXXXX                                                           
                                                                       
XXXX-XXXX-XXXXXXXX 99999999 XXXXXXXX XXXXXXXX XXXXXXXXXXXXX             
XXXXXXXXXXXXX                                                           
XXXXXXXXXXXXX                                                           
/*                                                                     
//SYSOUT   DD SYSOUT=*                                                 
//SORTOUT  DD DUMMY                                         
//OUTPUT1  DD SYSOUT=* <* All Data records for matching keys           
//OUTPUT2  DD SYSOUT=* <* Data records that do not match               
//SYSIN    DD *                                                         
  OPTION COPY                                                           
  JOINKEYS F1=F1,FIELDS=(1,8,A)                                         
  JOINKEYS F2=F2,FIELDS=(81,8,A)                                       
  JOIN UNPAIRED,F2                                                 
  REFORMAT FIELDS=(?,F2:1,80)                                       
  OUTFIL FNAMES=(OUTPUT1),                                         
    INCLUDE=(1,1,CH,EQ,C'B'),                                       
    REMOVECC,                                                       
    BUILD=(2,80)                                                   
  OUTFIL FNAMES=(OUTPUT2),                                         
    REMOVECC,SAVE,                                                 
    BUILD=(2,80)                                                   
  END                                                               
/*                                                                 
//JNF2CNTL DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,                                         
    BEGIN=(5,1,CH,EQ,C'-',AND,10,1,CH,EQ,C'-',AND,20,8,FS,EQ,NUM), 
    PUSH=(81:20,8))                                                 
  END                                                               
/*
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2051
Location: USA

PostPosted: Thu Apr 25, 2024 5:16 pm
Reply with quote

Giving a ready-to-use solutions instead of an abstract demo version, - it is an eternal loop. These people will continue asking the same "questions" (aka requests to do their own jobs) again, and again, and again.

When the answer can be copied-and-pasted, there is no need to apply their own mind... 824.gif icon_kidra.gif
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 Format Binary file to EBCDIC JCL & VSAM 4
No new posts Binary File format getting change whi... All Other Mainframe Topics 7
No new posts To fetch records that has Ttamp value... DFSORT/ICETOOL 4
No new posts ICETOOL returns no records JCL & VSAM 1
No new posts Compare 2 files and write Matched/Unm... JCL & VSAM 8
Search our Forums:

Back to Top