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

Merge records from two file


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

New User


Joined: 07 Sep 2006
Posts: 15
Location: india

PostPosted: Fri Dec 14, 2007 5:15 pm
Reply with quote

here are two flat files. 1st file contains policy-number and some start date. 2nd file contains policy-number and end-date. The policy numbers in both files are same and no of records in both files are also same. Both the files are sorted on policy numbers in ascending order. Now I want a single file which will contain policy-number, start-date and end-date.

For ex.

1st file data 2nd file data

Pol-num Start-date Pol-num End-date

100000 2050101 100000 2061201

200000 2040203 200000 2071231


10:40:30 AM: Ravi: Expected results

Output file data

Pol-num start-date end-date

100000 2050101 2061201

200000 2040203 2071231
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri Dec 14, 2007 9:59 pm
Reply with quote

shyam_shagam,

Concatenate both file1 and file2 to Sortin and use the following DFSORT JCL to give you the desired results. I assumed that your Policy # starts in pos 1 for 6 bytes and the dates start in pos 8 for 7 bytes.

Code:

//STEP0100 EXEC PGM=ICEMAN                       
//SYSOUT   DD SYSOUT=*                         
//SORTIN   DD DSN=Your input file1,
//            DISP=SHR
//         DD DSN=Your input file2,
//            DISP=SHR
//SORTOUT  DD SYSOUT=*                         
//SYSIN    DD *                                 
  SORT FIELDS=(01,06,CH,A)                     
  OUTREC IFTHEN=(WHEN=INIT,                     
        OVERLAY=(81:SEQNUM,1,ZD,RESTART=(1,6))),
         IFTHEN=(WHEN=(81,1,ZD,EQ,1),           
        OVERLAY=(16:7C'0')),                   
         IFTHEN=(WHEN=(81,1,ZD,EQ,2),           
        OVERLAY=(16:8,8,8:7C'0'))               
                                               
  OUTFIL REMOVECC,NODETAIL,                     
  OUTREC=(1,23),                               
  SECTIONS=(1,6,                               
  TRAILER3=(1,7,                               
            TOT=(08,7,ZD,EDIT=(TTTTTTT)),X,     
            TOT=(16,7,ZD,EDIT=(TTTTTTT))))     
/*


Hope this helps...
Back to top
View user's profile Send private message
shyam_shagam

New User


Joined: 07 Sep 2006
Posts: 15
Location: india

PostPosted: Mon Dec 17, 2007 9:11 am
Reply with quote

SKolusu,

Super thank you, Its working fine. icon_lol.gif

If possible, could you please explain line by how its working.

- Thanks
Shyam.
Back to top
View user's profile Send private message
Deepa.m

New User


Joined: 28 Apr 2005
Posts: 99

PostPosted: Mon Dec 17, 2007 4:42 pm
Reply with quote

also can be done using SPLICE operator

Code:

//JSTY0000 EXEC PGM=ICETOOL                                             
//IN1   DD *                                                           
100000 2050101                                                         
200000 2040203                                                                   
/*                                                                     
//IN2   DD *                                                           
100000 2061201                                                         
200000 2071231                                                                             
/*                                                                     
//TEMP1  DD DSN=&&TEMP,UNIT=SYSDA,SPACE=(TRK,(5,5)),DISP=(MOD,PASS)     
//OUT   DD  SYSOUT=*
//DFSMSG DD SYSOUT=*                                                   
//TOOLMSG DD SYSOUT=*                                                   
//TOOLIN  DD *                                       
 COPY FROM(IN1) TO(TEMP1)                             
 COPY FROM(IN2) TO(TEMP1) USING(CTL1)                 
 SPLICE FROM(TEMP1) TO(OUT) ON(1,6,CH) WITH(16,7)     
/*                                                   
//CTL1CNTL DD *                                       
    OUTREC FIELDS=(1,6,9X,16:8,7,80:X)                     
/*
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Mon Dec 17, 2007 10:01 pm
Reply with quote

shyam_shagam wrote:
SKolusu,

Super thank you, Its working fine. icon_lol.gif

If possible, could you please explain line by how its working.

- Thanks
Shyam.


file 1 :
Code:

100000 2050101
200000 2040203


file 2 :
Code:

100000 2061201
200000 2071231


when you concatenate and sort them on the they will be as follows

Code:

100000 2050101
100000 2061201
200000 2040203
200000 2071231


Now we add a seqnum at pos 81 to number the dups


Code:

100000 2050101             1
100000 2061201             2
200000 2040203             1
200000 2071231             2


The IFTHEN statements will change the data as follows

Code:

100000 2050101 0000000
100000 0000000 2061201
200000 2071231 0000000
200000 0000000 2040203


Using DFSORT's reporting features we write out the records summing up the values in column 18 and 16 which gives the desired result.

Hope this helps...
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 Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Access to non cataloged VSAM file JCL & VSAM 18
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Need help for File Aid JCL to extract... Compuware & Other Tools 23
Search our Forums:

Back to Top