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
 

 

Merging of two files (log+delta)

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Andrew Shinkarev

New User


Joined: 10 Jan 2008
Posts: 22
Location: Belarus

PostPosted: Thu Apr 03, 2008 7:23 pm    Post subject: Merging of two files (log+delta)
Reply with quote

There are two files (FB/80) with the same layout:

country code(2 chars),';',old name(15),';',present name(15),';',comments(55)

Example of file1:

Code:

US;Name1          ;Name2          ;comment1...
SW;Name3          ;Name4          ;comment2...
DE;Name5          ;               ;comment3...
US;Name6          ;Name2          ;comment4...
UK;Name7          ;Name8          ;comment5...
FR;Name9          ;Name2          ;comment6...
IT;Name10         ;               ;comment7...
...........


File1 contains the log of name changing: who was in position originally and who is in position(role as contact) now. If the second name is empty it means that the role was cancelled (it's never changed in the future).

The second file (file2) contains the fresh log of changes:

Code:

US;Name2          ;Name11         ;comment8...
UK;Name8          ;               ;comment9...
NO;Name12         ;Name10         ;comment10...


The requirements is to get the next output (keeping the original order):

Code:

US;Name1          ;Name11         ;comment8...
SW;Name3          ;Name4          ;comment2...
DE;Name5          ;               ;comment3...
US;Name6          ;Name11         ;comment8...
UK;Name7          ;               ;comment9...
FR;Name9          ;Name2          ;comment6...
IT;Name10         ;               ;comment7...
NO;Name12         ;Name10         ;comment10...


In other words the "present name" from file1 should be compared with the "old name" from file2 (! country codes are compared as well). If they are equal - the "present name" in file1 and the comments should be replaced by the "present name" and the comments from file2. If they are not equal - the record stays in the log unchanged. The records from file2 which does not correspond to any one in file1 are simply added to the end of the log.

In the example above:
- for US Name2 was replaced by Name11 and the comments were updated;
- for UK Name8 was replaced by blanks (cancellation) and the comments were updated;
- for FR Name2 was not changed to Name11 as for US because of the different country code(!);
- new postion ('NO') from file2 was added to the change log;

!!! Some names at the "present name" position in file1 could be equal (the obvious example is blanks).
Back to top
View user's profile Send private message

Skolusu

Senior Member


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

PostPosted: Thu Apr 03, 2008 11:29 pm    Post subject: Reply to: Merging of two files (log+delta)
Reply with quote

The following DFSORT/ICETOOL Jcl will give you the desired results

Code:

//STEP0100 EXEC PGM=ICETOOL                   
//TOOLMSG  DD SYSOUT=*                         
//DFSMSG   DD SYSOUT=*                         
//IN1      DD DSN=your log changes file,
//            DISP=SHR
//IN2      DD DSN=your delta file,
//            DISP=SHR       
//T1       DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(1,1),RLSE)     
//T2       DD DSN=&&T2,DISP=(MOD,PASS),SPACE=(CYL,(1,1),RLSE)     
//OUT      DD SYSOUT=*                                             
//TOOLIN   DD *                                                   
  COPY FROM(IN1) USING(CTL1)                                       
  COPY FROM(IN2) USING(CTL2)                                       
  SPLICE FROM(T1) ON(1,2,CH) ON(20,15,CH) WITH(01,98) WITH(171,1)-
  WITHALL TO(T2) KEEPNODUPS USING(CTL3)                           
  SORT FROM(T2) USING(CTL4)                                       
//CTL1CNTL DD *                                                   
  OUTFIL FNAMES=T1,OVERLAY=(99:20,16,36,55,C'22',20:4,15)         
//CTL2CNTL DD *                                                   
  OUTFIL FNAMES=T1,OVERLAY=(91:SEQNUM,8,ZD,71X,C'11')             
//CTL3CNTL DD *                                                   
  OUTFIL FNAMES=T2,                                               
      IFTHEN=(WHEN=(99,71,CH,GT,C' ',AND,170,2,CH,NE,C'22'),       
      BUILD=(1,19,20:99,71,91,8)),                                 
      IFTHEN=(WHEN=(170,2,CH,EQ,C'22'),                           
      BUILD=(1,19,20:99,71,8C'9')),                               
      IFTHEN=(WHEN=NONE,                                           
      BUILD=(1,98))                                               
//CTL4CNTL DD *                                                   
  SORT FIELDS=(91,8,CH,A)                                         
  OUTFIL FNAMES=OUT,BUILD=(01,90)                                 
/*
Back to top
View user's profile Send private message
Andrew Shinkarev

New User


Joined: 10 Jan 2008
Posts: 22
Location: Belarus

PostPosted: Fri Apr 04, 2008 4:10 pm    Post subject:
Reply with quote

Looking at your job I understood that the length of comments is not correct. It should be 45 (file is FB/80) - my typing fault.

Anyway I'll try to correct the job, for the moment it simply concatenates both files.
Back to top
View user's profile Send private message
Andrew Shinkarev

New User


Joined: 10 Jan 2008
Posts: 22
Location: Belarus

PostPosted: Fri Apr 04, 2008 10:56 pm    Post subject:
Reply with quote

What I've got for the moment:

Code:

Orig.log:
US;Name1          ;Name2          ;comment1

          -- after 1st COPY --
                           |
                           V

US;Name1          ;Name1          ;comment1 …         Name2          ;comment1 … 22

                           |
                           V

Result after SPLICE:
US;Name1          ;Name2          ;comment1 … 99999999
US;Name2          ;Name3          ;comment2 … 00000001

                           ^
                           |

US;Name2          ;Name3          ;comment2 … 00000001                         … 11

                           ^
                           |
          -- after 2nd COPY --

Delta:
US;Name2          ;Name3          ;comment2

But the desired result is:
Code:

US;Name1          ;Name3          ;comment2 .....
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Sat Apr 05, 2008 1:42 am    Post subject: Reply to: Merging of two files (log+delta)
Reply with quote

Use the following Control cards

Code:

//TOOLIN   DD *                                                   
  COPY FROM(IN1) USING(CTL1)                                       
  COPY FROM(IN2) USING(CTL2)                                       
  SPLICE FROM(T1) ON(1,2,CH) ON(20,15,CH) WITH(01,88) WITH(151,1)-
  WITHALL TO(T2) KEEPNODUPS USING(CTL3)                           
  SORT FROM(T2) USING(CTL4)                                       
//CTL1CNTL DD *                                                   
  OUTFIL FNAMES=T1,OVERLAY=(89:20,16,36,45,C'22',20:4,15)         
//CTL2CNTL DD *                                                   
  OUTFIL FNAMES=T1,OVERLAY=(81:SEQNUM,8,ZD,61X,C'11')             
//CTL3CNTL DD *                                                   
  OUTFIL FNAMES=T2,                                               
      IFTHEN=(WHEN=(89,61,CH,GT,C' ',AND,150,2,CH,NE,C'22'),       
      BUILD=(1,19,20:89,61,81,8)),                                 
      IFTHEN=(WHEN=(150,2,CH,EQ,C'22'),                           
      BUILD=(1,19,20:89,61,8C'9')),                               
      IFTHEN=(WHEN=NONE,                                           
      BUILD=(1,88))                                               
//CTL4CNTL DD *                                                   
  SORT FIELDS=(81,8,CH,A)                                         
  OUTFIL FNAMES=OUT,BUILD=(01,80)                                 
/
Back to top
View user's profile Send private message
Andrew Shinkarev

New User


Joined: 10 Jan 2008
Posts: 22
Location: Belarus

PostPosted: Mon Apr 07, 2008 4:28 pm    Post subject:
Reply with quote

Thank you for correction, Skolusu, but it doesn't help, since the principle of the work is not correct. I have already corrected the job:

Code:

//STEP20 EXEC PGM=ICETOOL                                         
//TOOLMSG  DD SYSOUT=*                                           
//DFSMSG   DD SYSOUT=*                                           
//IN1      DD DISP=SHR,DSN=<original log>       
//IN2      DD DISP=SHR,DSN=<delta>       
//T1       DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(1,1),RLSE)     
//T2       DD DSN=&&T2,DISP=(MOD,PASS),SPACE=(CYL,(1,1),RLSE) 
//OUT      DD DSN=<output log>,           
//            DISP=(MOD,CATLG,DELETE),                           
//            UNIT=SYSDA,SPACE=(TRK,(1,1),RLSE)                   
//TOOLIN   DD *                                                   
  COPY FROM(IN1) USING(CTL1)                                     
  COPY FROM(IN2) USING(CTL2)                                     
  SPLICE FROM(T1) ON(1,2,CH) ON(20,15,CH) WITH(01,88) WITH(151,1)-
  WITHALL TO(T2) KEEPNODUPS USING(CTL3)                           
  SORT FROM(T2) USING(CTL4)                                       
//CTL1CNTL DD *                                                   
  OUTFIL FNAMES=T1,OVERLAY=(89:20,16,36,45,C'22',20:4,15)         
//CTL2CNTL DD *                                                   
  OUTFIL FNAMES=T1,OVERLAY=(81:SEQNUM,8,ZD,61X,C'11')             
//CTL3CNTL DD *                                                   
  OUTFIL FNAMES=T2,                                               
      IFTHEN=(WHEN=(89,61,CH,GT,C' ',AND,150,2,CH,NE,C'22'),     
      BUILD=(1,19,20:89,61,81,8)),                               
      IFTHEN=(WHEN=(150,2,CH,EQ,C'22'),                           
      BUILD=(1,19,20:89,61,8C'9')),                               
      IFTHEN=(WHEN=NONE,                                         
      BUILD=(1,88))                                               
//CTL4CNTL DD *                                                   
  SORT FIELDS=(81,8,CH,A)                                         
  OUTFIL FNAMES=OUT,BUILD=(01,80)                                 
//                                                               


And simple test does not show a needed result:
Code:

Original log:
US;AAAAAAAAAAAAAAA;BBBBBBBBBBBBBBB;1111111111111111111111111111111111111

Delta:
US;BBBBBBBBBBBBBBB;CCCCCCCCCCCCCCC;2222222222222222222222222222222222222

Result:
US;BBBBBBBBBBBBBBB;CCCCCCCCCCCCCCC;2222222222222222222222222222222222222
US;AAAAAAAAAAAAAAA;BBBBBBBBBBBBBBB;1111111111111111111111111111111111111

But the required result should be:
US;AAAAAAAAAAAAAAA;CCCCCCCCCCCCCCC;2222222222222222222222222222222222222
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Mon Apr 07, 2008 9:06 pm    Post subject:
Reply with quote

Andrew Shinkarev wrote:
Thank you for correction, Skolusu, but it doesn't help, since the principle of the work is not correct. I have already corrected the job:

And simple test does not show a needed result:
Code:

Original log:
US;AAAAAAAAAAAAAAA;BBBBBBBBBBBBBBB;1111111111111111111111111111111111111

Delta:
US;BBBBBBBBBBBBBBB;CCCCCCCCCCCCCCC;2222222222222222222222222222222222222

Result:
US;BBBBBBBBBBBBBBB;CCCCCCCCCCCCCCC;2222222222222222222222222222222222222
US;AAAAAAAAAAAAAAA;BBBBBBBBBBBBBBB;1111111111111111111111111111111111111

But the required result should be:
US;AAAAAAAAAAAAAAA;CCCCCCCCCCCCCCC;2222222222222222222222222222222222222


Andrew Shinkarev,

I am not sure what you corrected but the solution posted by me will NOT give you 2 records. I am guessing that you associated the wrong input files to the IN1 and IN2 DD's

IN1 should have the latest changes from which you want to overlay the contents and In2 is the original file with old values.

Just for my sanity I ran with your sample data of just 1 record and i get the correct results

Code:

//STEP0200 EXEC PGM=ICETOOL                                             
//TOOLMSG  DD SYSOUT=*                                                 
//DFSMSG   DD SYSOUT=*                                                 
//IN1      DD *                                                         
US;BBBBBBBBBBBBBBB;CCCCCCCCCCCCCCC;2222222222222222222222222222222222222
//IN2      DD *                                                         
US;AAAAAAAAAAAAAAA;BBBBBBBBBBBBBBB;1111111111111111111111111111111111111
//T1       DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(1,1),RLSE)           
//T2       DD DSN=&&T2,DISP=(MOD,PASS),SPACE=(CYL,(1,1),RLSE)           
//OUT      DD SYSOUT=*                                                 
//TOOLIN   DD *                                                         
  COPY FROM(IN1) USING(CTL1)                                           
  COPY FROM(IN2) USING(CTL2)                                           
  SPLICE FROM(T1) ON(1,2,CH) ON(20,15,CH) WITH(01,88) WITH(151,1)-     
  WITHALL TO(T2) KEEPNODUPS USING(CTL3)                                 
  SORT FROM(T2) USING(CTL4)                                             
//CTL1CNTL DD *                                                         
  OUTFIL FNAMES=T1,OVERLAY=(89:20,16,36,45,C'22',20:4,15)               
//CTL2CNTL DD *                                                         
  OUTFIL FNAMES=T1,OVERLAY=(81:SEQNUM,8,ZD,61X,C'11')                   
//CTL3CNTL DD *                                                         
  OUTFIL FNAMES=T2,                                                     
      IFTHEN=(WHEN=(89,61,CH,GT,C' ',AND,150,2,CH,NE,C'22'),           
      BUILD=(1,19,20:89,61,81,8)),                                     
      IFTHEN=(WHEN=(150,2,CH,EQ,C'22'),                                 
      BUILD=(1,19,20:89,61,8C'9')),                                     
      IFTHEN=(WHEN=NONE,                                               
      BUILD=(1,88))                                                     
//CTL4CNTL DD *                                                         
  SORT FIELDS=(81,8,CH,A)                                               
  OUTFIL FNAMES=OUT,BUILD=(01,80)                                       
/*                                                                     


Try running the same job as is and if you dont get the desired results let me know. Please post your DFSMSG sysout
Back to top
View user's profile Send private message
Andrew Shinkarev

New User


Joined: 10 Jan 2008
Posts: 22
Location: Belarus

PostPosted: Mon Apr 07, 2008 11:33 pm    Post subject:
Reply with quote

Quote:

I am not sure what you corrected but the solution posted by me will NOT give you 2 records. I am guessing that you associated the wrong input files to the IN1 and IN2 DD's


1. I meant that I corrected position and length according to the real length of fields (LEN(comments) = 45) and got a code absolutely identical to your one after //TOOLIN

2. Looks like we really did not understand one another. Here we call "log file" - a bigger file to be changed (or original) and "delta" - a smaller file containing the changes to be implemented.

Anyway even before your post I tried to replace IN1 and IN2. The result contains less records than the original file - it shouldn't be. I've mentioned in my first post that this file (file1 or original) contains some duplicates in position (20,15) and it complicates the issue. You may see below:

Original file:
Code:

US;AAAAAAAAAAAAAAA;BBBBBBBBBBBBBBB;1111111111111111111111111111111111111
US;DDDDDDDDDDDDDDD;EEEEEEEEEEEEEEE;3333333333333333333333333333333333333
US;FFFFFFFFFFFFFFF;EEEEEEEEEEEEEEE;4444444444444444444444444444444444444


Delta file:
Code:

US;BBBBBBBBBBBBBBB;CCCCCCCCCCCCCCC;2222222222222222222222222222222222222


Result after job work:
Code:

US;AAAAAAAAAAAAAAA;CCCCCCCCCCCCCCC;2222222222222222222222222222222222222
US;FFFFFFFFFFFFFFF;EEEEEEEEEEEEEEE;4444444444444444444444444444444444444


But there should be:
Code:

EM;AAAAAAAAAAAAAAA;CCCCCCCCCCCCCCC;2222222222222222222222222222222222222
US;DDDDDDDDDDDDDDD;EEEEEEEEEEEEEEE;3333333333333333333333333333333333333
EM;FFFFFFFFFFFFFFF;EEEEEEEEEEEEEEE;4444444444444444444444444444444444444
Back to top
View user's profile Send private message
Andrew Shinkarev

New User


Joined: 10 Jan 2008
Posts: 22
Location: Belarus

PostPosted: Tue Apr 08, 2008 5:12 pm    Post subject:
Reply with quote

Sorry, should be:

"But there should be:
Code:

US;AAAAAAAAAAAAAAA;CCCCCCCCCCCCCCC;2222222222222222222222222222222222222
US;DDDDDDDDDDDDDDD;EEEEEEEEEEEEEEE;3333333333333333333333333333333333333
US;FFFFFFFFFFFFFFF;EEEEEEEEEEEEEEE;4444444444444444444444444444444444444
"
Back to top
View user's profile Send private message
Andrew Shinkarev

New User


Joined: 10 Jan 2008
Posts: 22
Location: Belarus

PostPosted: Fri Apr 11, 2008 7:06 pm    Post subject:
Reply with quote

Finally I've written a code by myself. I'm putting it here for someone else, who could get the same or similar task. For sure it is not perfect and can be improved. BUT it is checked as the program written in PL/I gave the identical result. Thanks Skolusu and thanks to the author of "Create files with matching and non-matching records" trick in "Smart DFSORT Tricks" doc (I suppose it's for you, Frank :-) )

Code:

//STEP20 EXEC PGM=ICETOOL                                           
//TOOLMSG  DD SYSOUT=*                                             
//DFSMSG   DD SYSOUT=*                                             
//SOURCE   DD DISP=SHR,DSN=<original change log>
//DELTA    DD DISP=SHR,DSN=<delta file to be implemented>
//TMP1     DD DSN=&&T1,DISP=(MOD,DELETE),SPACE=(CYL,(100,50),RLSE) 
//TMP2     DD DSN=&&T2,DISP=(MOD,DELETE),SPACE=(CYL,(100,50),RLSE) 
//TMP3     DD DSN=&&T3,DISP=(MOD,CATLG),SPACE=(CYL,(100,50),RLSE)           
//OUT      DD DSN=<output file>,             
//            DISP=(MOD,CATLG,DELETE),                             
//            UNIT=SYSDA,SPACE=(TRK,(100,50),RLSE)                 
//TOOLIN   DD *                                                     
  SELECT FROM(SOURCE) TO(TMP1) ON(20,15,CH) FIRSTDUP USING(CTL1)   
  SELECT FROM(DELTA)  TO(TMP1) ON(04,15,CH) FIRST    USING(CTL2)   
  COPY FROM(DELTA) USING(CTL3)                                     
  SPLICE FROM(TMP1) ON(1,2,CH) ON(20,15,CH) WITH(151,1)-           
    WITHALL TO(TMP2) KEEPNODUPS USING(CTL4)                         
  COPY FROM(SOURCE) USING(CTL5)                                     
  SPLICE FROM(TMP2) ON(1,2,CH) ON(20,15,CH) WITH(01,88) WITH(151,1)-
    WITHALL TO(TMP3) KEEPNODUPS USING(CTL6)                           
  SORT FROM(TMP3) USING(CTL7)                                       
/*                                                                 
//CTL1CNTL DD *                                                     
  INREC OVERLAY=(81:SEQNUM,8,ZD,61X,C'LL')                         
/*                                                                 
//CTL2CNTL DD *                                                     
  OUTFIL FNAMES=TMP1,                                               
  OVERLAY=(89:20,16,36,45,C'DD',20:4,15)                           
/*                                                                 
//CTL3CNTL DD *                                                     
  OUTFIL FNAMES=TMP2,                                               
  OVERLAY=(89:20,16,36,45,C'22',20:4,15)                           
/*                                                                 
//CTL4CNTL DD *                                             
  OUTFIL FNAMES=TMP2,                                       
  INCLUDE=(150,2,CH,EQ,C'LL'),                             
  OVERLAY=(150:C'11')                                       
/*                                                         
//CTL5CNTL DD *                                             
  OUTFIL FNAMES=TMP2,                                       
  OVERLAY=(81:SEQNUM,8,ZD,61X,C'11')                       
/*
//CTL6CNTL DD *                                             
  OUTFIL FNAMES=TMP3,                                       
      IFTHEN=(WHEN=(89,61,CH,GT,C' ',AND,150,2,CH,NE,C'22'),
      BUILD=(1,19,20:89,61,81,8)),                         
      IFTHEN=(WHEN=(150,2,CH,EQ,C'22'),                     
      BUILD=(1,19,20:89,61,8C'9')),                         
      IFTHEN=(WHEN=NONE,                                   
      BUILD=(1,88))                                         
/*
//CTL7CNTL DD *                                             
  SORT FIELDS=(81,8,CH,A)                                   
  OUTFIL FNAMES=OUT,BUILD=(01,80)                           
/*
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 getting three output files out of JOI... migusd SYNCSORT 7 Mon Jun 05, 2017 11:30 pm
No new posts Unable to create multiple files using... mbattu COBOL Programming 3 Fri May 05, 2017 5:35 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 Dynamic split of files under groups sril.krishy DFSORT/ICETOOL 4 Mon Apr 17, 2017 1:09 pm
No new posts Compare two files and subtract values ameetmund DFSORT/ICETOOL 7 Fri Mar 31, 2017 3:35 pm


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