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

Matching column from 2 files needs to be on 3rd file
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
erhema

New User


Joined: 07 Oct 2008
Posts: 23
Location: Chennai
PostPosted: Wed May 27, 2009 4:20 pm
 Reply with quote
Hi,

One column needs to be compared from two different files and needs to be written to the 3rd file with 2nd file format. Could you please help me to produce the 3rd file. Can SPLICE be used?

1st file - 80 bytes

First 17bytes is 1st column

11111111111111111
33333333333333333

2nd File - record lenght 15000

ABCDEF 11111111111111111 .... KIH
FJGJHGJ 22222222222222222 .... YHB
GTCDEF 33333333333333333 .... UHJ

I want 7th to 23rd position (17 bytes) of second File needs to be compared to first column 17 bytes of the first file.

Output File - 3rd File
ABCDEF 11111111111111111 .... KIH
GTCDEF 33333333333333333 .... UHJ

Thanks
Back to top
View user's profile Send private message
erhema

New User


Joined: 07 Oct 2008
Posts: 23
Location: Chennai
PostPosted: Wed May 27, 2009 4:48 pm
 Reply with quote
Hi All,

I used JOINKEYS, but since we have only DFSORT, it doesnt help. JOINKEYS goes only with SYNCSORT.

Thanks.
Back to top
View user's profile Send private message
vvmanyam

New User


Joined: 16 Apr 2008
Posts: 86
Location: Bangalore
PostPosted: Wed May 27, 2009 5:00 pm
 Reply with quote
Hi erhema,
Please find the below jcl for your requirement:
This will work even if any duplicates in the second file.
If there are no duplicates, jcl can be simplified.
Code:

//STEP0    EXEC PGM=ICETOOL                                             
//IN1      DD *                                                         
11111111111111111                                                       
33333333333333333                                                       
//IN2      DD DSN=TEST.SORT.IN,DISP=SHR             <-- length 15,000
//TOOLIN   DD *                                                         
  SORT FROM(IN1) TO(OUT1) USING(CTL1)                                   
  SORT FROM(IN2) TO(OUT1) USING(CTL2)                                   
  SPLICE FROM(OUT1) TO(OUT2) ON(7,17,CH) -                             
   WITHALL WITH(1,15001)                                               
  SORT FROM(OUT2) TO(OUTFILE) USING(CTL3)                               
//CTL1CNTL DD *                                                         
  INREC FIELDS=(7:1,17,15001:C'AA')                                     
  SORT FIELDS=COPY                                                     
//CTL2CNTL DD *                                                         
  INREC OVERLAY=(15001:C'BB')                                           
  SORT FIELDS=COPY                                                     
//CTL3CNTL DD *                                                         
  SORT FIELDS=COPY                 
  INCLUDE COND=(15001,2,CH,EQ,C'BA')                                 
  OUTREC BUILD=(1,15000)                                               
//OUT1     DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(1,1)),DISP=(MOD,PASS)     
//OUT2     DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(1,1)),DISP=(MOD,PASS)     
//OUTFILE  DD  DSN=TEST.SORT.OUT,DISP=(NEW,CATLG,DELETE),     
//             SPACE=(1500,(300,5),RLSE),AVGREC=K,                     
 //             RECFM=FB,LRECL=15000                   
 //TOOLMSG  DD SYSOUT=*                                 
 //DFSMSG   DD SYSOUT=*                                 


Regards,
Balu
Back to top
View user's profile Send private message
erhema

New User


Joined: 07 Oct 2008
Posts: 23
Location: Chennai
PostPosted: Wed May 27, 2009 7:03 pm
 Reply with quote
This worked fine, Thanks a lot Balu.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Wed May 27, 2009 9:40 pm
 Reply with quote
Erhema,

You really don't need that many passes of data , use the following DFSORT JCL
Code:

//STEP0100 EXEC PGM=ICETOOL                                         
//TOOLMSG  DD SYSOUT=*                                             
//DFSMSG   DD SYSOUT=*                                             
//I1       DD DSN=Your 80 byte file,DISP=SHR                       
//I2       DD DSN=your 1500 byte file,DISP=SHR
//T1       DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(X,Y),RLSE)       
//OUT      DD SYSOUT=*                                             
//TOOLIN   DD *                                                     
  COPY FROM(I1) USING(CTL1)                                         
  COPY FROM(I2) USING(CTL2)                                         
  SORT FROM(T1) USING(CTL3)                                         
//CTL1CNTL DD *                                                     
  OUTFIL FNAMES=T1,BUILD=(7:1,17,1501:C'1')                         
//CTL2CNTL DD *                                                     
  OUTFIL FNAMES=T1,OVERLAY=(1501:X)                                 
//CTL3CNTL DD *                                                     
  SORT FIELDS=(7,17,CH,A),EQUALS                                   
  OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(1501,1,ZD,EQ,1),PUSH=(1502:7,17))
  OUTFIL FNAMES=OUT,BUILD=(1,1500),                                 
  INCLUDE=(1501,1,CH,EQ,C' ',AND,7,17,CH,EQ,1502,17,CH)             
/*


If you don't have the latest PTF which supports WHEN=GROUP , use the following JCL
Code:

//STEP0100 EXEC PGM=ICETOOL                                         
//TOOLMSG  DD SYSOUT=*                                             
//DFSMSG   DD SYSOUT=*                                             
//I1       DD DSN=Your 80 byte file,DISP=SHR                       
//I2       DD DSN=your 1500 byte file,DISP=SHR
//T1       DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(X,Y),RLSE)       
//OUT      DD SYSOUT=*                                       
//TOOLIN   DD *                                             
  COPY FROM(I1) USING(CTL1)                                 
  COPY FROM(I2) USING(CTL2)                                 
  SPLICE FROM(T1) TO(OUT) ON(7,17,CH) WITH(1,1500) -         
  WITHALL USING(CTL3)                                       
//CTL1CNTL DD *                                             
  OUTFIL FNAMES=T1,BUILD=(7:1,17,1501:C'1')                 
//CTL2CNTL DD *                                             
  OUTFIL FNAMES=T1,OVERLAY=(1501:X)                         
//CTL3CNTL DD *                                             
  OUTFIL FNAMES=OUT,BUILD=(1,1500),INCLUDE=(1501,1,ZD,EQ,1) 
/*


VVmanyam,

Please try to keep things simple and do NOT post solutions with unnecessary passes of data
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 FTP VB File from Mainframe retaining ... JCL & VSAM 4
No new posts Replacing 'YYMMDD' with date, varying... SYNCSORT 3
No new posts Extract the file name from another fi... DFSORT/ICETOOL 6
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us