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

Merging of two files (log+delta)
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
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
 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
 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
 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
 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
 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
 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
 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
 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
 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
 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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts Compare 2 files and retrive records f... DFSORT/ICETOOL 3
No new posts Compare 2 files(F1 & F2) and writ... JCL & VSAM 8
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Merge two VSAM KSDS files into third ... JCL & VSAM 6
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us