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
 

 

Merge records from two file

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Merge records from two file
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    Post subject: Reply to: Merge records from two file
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    Post subject: Reply to: Merge records from two file
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
Warnings : 1

New User


Joined: 28 Apr 2005
Posts: 99

PostPosted: Mon Dec 17, 2007 4:42 pm    Post subject:
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    Post subject: Re: Reply to: Merge records from two file
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    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 Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 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