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 DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts Copy RECFM =VB TO FB file with RECL =... sahil41352 DFSORT/ICETOOL 3 Wed Dec 28, 2016 11:29 pm
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 15 Thu Dec 01, 2016 8:28 pm
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm


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