MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Merge Data from 3 files into a Single file

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
Srishti Rawat
Beginner


Joined: 03 Nov 2021
Posts: 20
Topics: 6
PostPosted: Thu Dec 09, 2021 11:27 am    Post subject: Merge Data from 3 files into a Single file Reply with quote
Hi Team,

I have one query related to DFSORT/ICETOOL.

We have three input files.
File 1. Having date
File 2. Having header, detail records and trailer. Trailer is having two counts of 5 bytes each. First starting from position 2 and second from 7th.
File 3. Having header, detail records and trailer. Trailer is having two counts of 5 bytes each. First starting from position 2 and second from 7th.

I want to have one output file where I will have date(10bytes) from first file, followed by sum of counts present in the trailer(5 bytes) of second file and sum of counts(5 bytes) present in the trailer of third file.

Input files:
File1:
Code:

2021-12-09

File2:
Code:

120211209999
51122222233333
519919989289
5878090909-0-0
900001000020000000

File3:
Code:

120211209999
51122222233333
5878090909-0-0
90000100001000000

Output file:

Code:
2021-12-090000300002


Can we do it in one step?

Please suggest.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12370
Topics: 75
Location: San Jose
PostPosted: Thu Dec 09, 2021 1:16 pm    Post subject: Reply with quote
Srishti Rawat,

Please use meaningful topics (don't use the language/forum name/PGM name for the topic). Use a descriptive Title to explain your problem.

I edited the title for you now.

Coming to your requirement, it can be done quite easily. Please answer the following questions.

1. What is the LRECL and RECFM of the all the 3 input files and desired output file?
2. How do you identify the trailer record? '9' in position 1?
3. Is there a chance the summation of the 5 byte counts may exceed ? what do you in that case?
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Srishti Rawat
Beginner


Joined: 03 Nov 2021
Posts: 20
Topics: 6
PostPosted: Fri Dec 10, 2021 1:07 am    Post subject: Reply with quote
Sure Kolusu.

Please find my response below:

1. What is the LRECL and RECFM of the all the 3 input files and desired output file?
SR: LRECL: 200 RECFM: FB for 2nd and 3rd input files and LRECL:80, RECFM: FB for First input file and output file.
2. How do you identify the trailer record? '9' in position 1?
SR: Yes thats right.
3. Is there a chance the summation of the 5 byte counts may exceed ? what do you in that case?
SR: As per the requirement, output file layout is:
DATE 10 BYTES CHAR
FIRST COUNT 05 BYTES NUM
SECOND COUNT 05 BYTES NUM
FILLER SPACES 60 BYTES CHAR
So we are assuming that we will never encounter the scenario of counts exceeding 5 bytes.

Thanks!
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12370
Topics: 75
Location: San Jose
PostPosted: Fri Dec 10, 2021 9:19 am    Post subject: Reply with quote
Srishti Rawat,

Try this untested JCL which will give you the desired results

Code:

//STEP0100 EXEC PGM=SORT     
//SYSOUT   DD SYSOUT=*       
//INA      DD DISP=SHR,DSN=Your.Input.fb.200.file2
//         DD DISP=SHR,DSN=Your.Input.fb.200.file3
/*
//INB      DD DISP=SHR,DSN=your.input.fb80.date.file1
//SORTOUT  DD SYSOUT=*                             
//SYSIN    DD *                                     
  OPTION COPY                                       
  JOINKEYS F1=INA,FIELDS=(12,1,A)                   
  JOINKEYS F2=INB,FIELDS=(81,1,A),SORTED,NOSEQCK   
  REFORMAT FIELDS=(F2:1,10,F1:2,10,F2:11,60)       
/*                                                 
//JNF1CNTL DD *                                     
  INCLUDE COND=(1,1,CH,EQ,C'9')                     
  OPTION STOPAFT=2                                 
  INREC BUILD=(1,11,C'A')                           
  SUM FIELDS=(2,5,ZD,                               
              7,5,ZD)                               
/*                                                 
//JNF2CNTL DD *                                     
  OPTION STOPAFT=1                                 
  INREC BUILD=(1,80,C'A')                           
/*                           

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Srishti Rawat
Beginner


Joined: 03 Nov 2021
Posts: 20
Topics: 6
PostPosted: Fri Dec 10, 2021 12:48 pm    Post subject: Reply with quote
Hi Kolusu,

I tried the JCL but trailer sum is not coming fine in the output file.

In the second file I am having the below trailer:
Code:

9000020000100000000000003000

In the third file I am having the below trailer:
Code:

9000020000400000000000003000

Expected Output file:
Code:

10-12-20210000300006

Output file we are getting:
Code:

10-12-20210000400005

Could you please suggest. Also if you can explain a little.

Thanks.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12370
Topics: 75
Location: San Jose
PostPosted: Fri Dec 10, 2021 5:42 pm    Post subject: Reply with quote
Srishti Rawat,

I guess I misunderstood your requirement incorrectly. I was under the impression that you wanted a vertical summation of 2 records, but from the looks of it you needed a horizontal summation.

My initial assumption was

Code:

FILE   ID   COUNT-1   COUNT-2
----   --   -------   -------
2       9     00002     00001
3       9     00002     00004
----   --   -------   -------
TOTAL   9     00004     00005


But your requirement is
Code:

FILE   ID   COUNT-1   COUNT-2    TOTAL     
----   --   -------   -------   ------     
2       9     00002 +   00001 =  00003     
3       9     00002 +   00004 =  00006     


So you need to use the following JCL
Code:

//STEP0100 EXEC PGM=SORT     
//SYSOUT   DD SYSOUT=*       
//INA      DD DISP=SHR,DSN=Your.Input.fb.200.file2
//         DD DISP=SHR,DSN=Your.Input.fb.200.file3
/*
//INB      DD DISP=SHR,DSN=your.input.fb80.date.file1
//SORTOUT  DD SYSOUT=*     
//SYSIN    DD *                                                                 
  OPTION COPY                                                                   
  JOINKEYS F1=INA,FIELDS=(11,1,A)                                               
  JOINKEYS F2=INB,FIELDS=(81,1,A),SORTED,NOSEQCK                               
  REFORMAT FIELDS=(F2:1,10,F1:1,10,F2:11,60)                                   
/*                                                                             
//JNF1CNTL DD *                                                                 
** Only include the trailer records from file-2 and file-3                     
  INCLUDE COND=(1,1,CH,EQ,C'9')                                                 
** Since we are only expecting 2 trailer records we stop after                 
** 2 RECORDS. File-2 trailer record will record number 1 and                   
** file-3 record will be record number 2                                       
  OPTION STOPAFT=2                                                             
** Now build the record initially with zero counts                             
  INREC IFTHEN=(WHEN=INIT,                                                     
         BUILD=(01:5C'0',                 $ Init-count-1 with zeros             
                06:5C'0',                 $ Init-count-2 with zeros             
                11:C'A',                  $ Matching key                       
                12:SEQNUM,1,ZD,           $ sequence each record               
                13:02,05,                 $ original count-1                   
                18:07,05)),               $ original count-2                   
                                                                               
** add counts for the file-2 trailer record                                     
        IFTHEN=(WHEN=(12,1,ZD,EQ,1),      $ If Record-number = 1               
       OVERLAY=(01:13,05,ZD,ADD,          $ Count-1 from file-2                 
                   18,05,ZD,M11,            Count-2 from file-2                 
                   LENGTH=5)),              = 5 BYTE TOTAL                     
                                                                               
** add counts for the file-3 trailer record                                     
        IFTHEN=(WHEN=(12,1,ZD,EQ,2),      $ If Record-number = 2               
       OVERLAY=(06:13,05,ZD,ADD,          $ Count-1 from file-3                 
                   18,05,ZD,M11,            Count-2 from file-3                 
                   LENGTH=5))               = 5 BYTE TOTAL                     
                                                                               
** Now sum the counts so the counts are on one line                             
                                                                               
  SUM FIELDS=(01,05,ZD,                   $ Sum count-1                         
              06,05,ZD)                   $ Sum count-2                         
/*                                                                           
//JNF2CNTL DD *                                                     
** Since date is on the first record we stop after reading 1 rec   
  OPTION STOPAFT=1                                                 
** Build the record                                                 
  INREC BUILD=(01:1,10,                   $ Date                   
               81:C'A')                   $ Matching key           
/*                                                                     


To explain how the code works JNF1CNTL will process the data as follows

After the first WHEN=INIT the record will look like this


Code:

   POS    POS       POS     POS     POS        POS       
  01-05  06-10       11      12    13-17     18-22       
 ------  ------   -----     ---   ------    ------       
 00000   00000       A       1     00002     00001       
 00000   00000       A       2     00002     00004       


For the record number 1 the record will look like this notice how count is updated in position 1

Code:

                                                   
   POS    POS       POS     POS     POS        POS       
  01-05  06-10       11      12    13-17     18-22       
 ------  ------   -----     ---   ------    ------       
 00003   00000       A       1     00002     00001       
 00000   00000       A       2     00002     00004       


For the record number 2 the record will look like this notice how count is updated in position 6

Code:
         
   POS    POS       POS     POS     POS        POS       
  01-05  06-10       11      12    13-17     18-22       
 ------  ------   -----     ---   ------    ------       
 00003   00000       A       1     00002     00001       
 00000   00006       A       2     00002     00004       


After the SUM statement. Notice the summed counts at position 1 and 6
Code:
                                                         
   POS    POS       POS     POS     POS        POS       
  01-05  06-10       11      12    13-17     18-22       
 ------  ------   -----     ---   ------    ------       
 00003   00006       A       1     00002     00001       


Now we will use matching key (position 11 from file-2/3 and position 81 from file1

Once matched we just the contents that we want using REFORMAT statement
Code:

01-10 date from file-2
11-15 count from file-2
16-20 count from file-3
60 spaces

which will look like below

Code:

2021-12-090000300006 
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Srishti Rawat
Beginner


Joined: 03 Nov 2021
Posts: 20
Topics: 6
PostPosted: Sun Dec 12, 2021 2:25 am    Post subject: Reply with quote
Thanks a lot Kolusu.

I was earlier doing it in three steps using DFSORT. Great learning I must say.

Regards,
Srishti Rawat.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group