Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Matching column from 2 files needs to be on 3rd file

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Matching column from 2 files needs to be on 3rd file
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Converting Variable block file to Fix... bhavana yalavarthi DFSORT/ICETOOL 10 Tue May 16, 2017 2:20 pm
No new posts Unable to create multiple files using... mbattu COBOL Programming 3 Fri May 05, 2017 5:35 pm
No new posts Getting error while opening a variabl... apandey1 COBOL Programming 5 Fri May 05, 2017 12:22 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts Issue with NDM process to transmit ES... chetanambi All Other Mainframe Topics 6 Wed May 03, 2017 10:52 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us