IBM Mainframe Forum

Mainframe Technical Support Forums

Skip to content


Help with merging 2 files together



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER
Post a reply

Help with merging 2 files together

Postby D9988 » Thu Mar 24, 2011 2:21 am

I have 2 files, each is 50 LRECL
Select all
=COLS> ----+----1----+----2----+----3----+----4----+----5
****** ***************************** Top of Data ********
000001 0000030137082920041              ñc *  Ã¥     `   
000002 0000030137082920042              á     Ã¢Ã‹`     Ã° 
000003 0000030137082920043              é çð  çÇÌ%   `   
000004 0000030137082920044              èã <  éÊ æ       

=COLS> ----+----1----+----2----+----3----+----4----+----5
****** ***************************** Top of Data ********
000001 0000030137082920041  ñc *  Ã¥                    
000002 0000030137082920042  á     Ã¢Ã‹`                   
000003 0000030137082920043  é çð  çÇÌ%                   
000004 0000030137082920044  èã <  éÊ æ               


I want to merge them both together based on matching sort key of 1,19,CH,A

I want the packed columns in file 2 to go where the blanks are in file 1.

Can someone help me with the syntax for this please?

I'm pasting the hex of the packed data below in case it's needed:
Select all
=COLS> ----+----1----+----2----+----3----+----4----+----5
****** ***************************** Top of Data ********
000001 0000030137082920041              ñc *  Ã¥     `   
       FFFFFFFFFFFFFFFFFFF4444444444440048150043600037100
       000003013708292004100000000000000931C00722C0049C00
---------------------------------------------------------
000002 0000030137082920042              á     Ã¢Ã‹`     Ã° 
       FFFFFFFFFFFFFFFFFFF4444444444440040000047710034800
       000003013708292004200000000000000500C00239C0011C00
---------------------------------------------------------

=COLS> ----+----1----+----2----+----3----+----4----+----5
****** ***************************** Top of Data ********
000001 0000030137082920041  ñc *  Ã¥                    
       FFFFFFFFFFFFFFFFFFF0048150043600000000000000000000
       000003013708292004100931C00722C0000000000000000000
---------------------------------------------------------
000002 0000030137082920042  á     Ã¢Ã‹`                   
       FFFFFFFFFFFFFFFFFFF0040000047710000000000000000000
       000003013708292004200500C00239C0000000000000000000
---------------------------------------------------------
David
D9988
 
Posts: 34
Joined: Fri Nov 09, 2007 10:55 pm
Has thanked: 0 time
Been thanked: 0 time
Top

Re: Help with merging 2 files together

Postby Frank Yaeger » Thu Mar 24, 2011 2:45 am

Here's a DFSORT job that will do what I think you asked for:

Select all
//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/50)
//IN2 DD DSN=...  input file2 (FB/50)
//SORTOUT DD DSN=... output file (FB/50)
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(1,19,A)
  JOINKEYS F2=IN2,FIELDS=(1,19,A)
  REFORMAT FIELDS=(F1:1,19,F2:20,12,F1:32,19)
  OPTION COPY
/*
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times
Top

Re: Help with merging 2 files together

Postby D9988 » Thu Mar 24, 2011 9:16 pm

This is exactly what I needed, and it works great! Thanks Frank!

The final issue I am having is related converting the PD to ZD in the next step. I am using the following code to change the PD to ZD, and to add the ',' delimeter between all relevent fields. It appears to be doing everything except adding the decimal place in the correct spot. Is there something I need to add, or do different, in my sort?

Sort statement:
Select all
SORT FIELDS=(1,19,CH,A)                   
OUTREC BUILD=(1,8,C',',9,6,C',',15,5,C',',
    20,6,PD,TO=ZD,C',',                   
    26,6,PD,TO=ZD,C',',                   
    32,6,PD,TO=ZD,C',',                   
    38,6,PD,TO=ZD,C',',                   
    44,5,PD,TO=ZD)                       
OPTION ZDPRINT       


Input:
Select all
=COLS> ----+----1----+----2----+----3----+----4----+----5
****** ***************************** Top of Data ********
000001 0000030137082920041  ñc *  Ã¥    Ã±c *  Ã¥     `   
000002 0000030137082920042  á     Ã¢Ã‹`   Ã¡     Ã¢Ã‹`     Ã° 

=COLS> ----+----1----+----2----+----3----+----4----+----5
****** ***************************** Top of Data ********
000001 0000030137082920041  ñc *  Ã¥    Ã±c *  Ã¥     `   
       FFFFFFFFFFFFFFFFFFF0048150043600048150043600037100
       000003013708292004100931C00722C00931C00722C0049C00
---------------------------------------------------------
000002 0000030137082920042  á     Ã¢Ã‹`   Ã¡     Ã¢Ã‹`     Ã° 
       FFFFFFFFFFFFFFFFFFF0040000047710040000047710034800
       000003013708292004200500C00239C00500C00239C0011C00
---------------------------------------------------------

Output I am getting is:
Select all
=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
****** ***************************** Top of Data ******************************
000001 00000301,370829,20041,00004983115,00004732620,00004983115,00004732620,000034791
000002 00000301,370829,20042,00004500000,00004273791,00004500000,00004273791,000031418
David
D9988
 
Posts: 34
Joined: Fri Nov 09, 2007 10:55 pm
Has thanked: 0 time
Been thanked: 0 time
Top

Re: Help with merging 2 files together

Postby Frank Yaeger » Thu Mar 24, 2011 10:23 pm

It appears to be doing everything except adding the decimal place in the correct spot.


Did I miss the part where you said you wanted a decimal point and where exactly the decimal point should go? My crystal ball is a little foggy today. :lol:

TO=ZD will not give you a decimal point. You would need to use an appropriate edit mask like EDIT=(IIIIIT.TT) to get a decimal point.

Since I don't know what you want your output records to look like, I can't tell you exactly how to change your control statements.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times
Top

Re: Help with merging 2 files together

Postby Frank Yaeger » Thu Mar 24, 2011 10:28 pm

BTW, you can do all of this in one step and you don't need to do the SORT since JOINKEYS already does it. You can use these control statements:

Select all
  JOINKEYS F1=IN1,FIELDS=(1,19,A)
  JOINKEYS F2=IN2,FIELDS=(1,19,A)
  REFORMAT FIELDS=(F1:1,19,F2:20,12,F1:32,19)
  OPTION COPY
  OUTREC ...
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times
Top

Re: Help with merging 2 files together

Postby D9988 » Thu Mar 24, 2011 10:35 pm

Oh! Sorry I didn't realize I left that piece out. In my head, it was crystal clear :lol:

I wanted the decimal place to be before the last two digits. So for the output, I would like it to be in the format

Select all
=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
****** ***************************** Top of Data ******************************
000001 00000301,370829,20041,000049831.15,000047326.20,000049831.15,000047326.20,0034791
000002 00000301,370829,20042,000045000.00,000042737.91,000045000.00,000042737.91,0031418


In my sort statement this applies to 20,6 26,6 32,6 38,6. The last number doesn't have a decimal place.

Select all
SORT FIELDS=(1,19,CH,A)                       
OUTREC BUILD=(1,8,C',',9,6,C',',15,5,C',',   
    20,6,PD,TO=ZD,C',',                       
    26,6,PD,TO=ZD,C',',                       
    32,6,PD,TO=ZD,C',',                       
    38,6,PD,TO=ZD,C',',                       
    44,5,PD,TO=ZD)                           
OPTION ZDPRINT     


I tried adding an edit to the above, like 20,6,PD,EDIT=(IIIIIT.TT), TO=ZD and 20,6,PD,TO=ZD,EDIT=(IIIIIT.TT) but I get U007 syntax error.
Last edited by D9988 on Thu Mar 24, 2011 10:45 pm, edited 1 time in total.
David
D9988
 
Posts: 34
Joined: Fri Nov 09, 2007 10:55 pm
Has thanked: 0 time
Been thanked: 0 time
Top

Re: Help with merging 2 files together

Postby D9988 » Thu Mar 24, 2011 10:38 pm

Nevermind! I got it to work with your advice. I replaced the TO=ZD with the actual edit mask. Now, to combine these into one step :D Thanks again!
David
D9988
 
Posts: 34
Joined: Fri Nov 09, 2007 10:55 pm
Has thanked: 0 time
Been thanked: 0 time
Top

Re: Help with merging 2 files together

Postby D9988 » Thu Apr 07, 2011 3:49 am

Let me know if this should be a new topic. Is it possible to write a trailer line that displays the total number of records, and sums up each of the fields in green below? Or should this be done in a separate step? This is the code from my sort:

JOINKEYS F1=IN1,FIELDS=(1,19,A)
JOINKEYS F2=IN2,FIELDS=(1,19,A)
REFORMAT FIELDS=(F1:1,19,F2:20,12,F1:32,19)
OPTION COPY
OUTREC BUILD=(1,8,C',',9,6,C',',15,5,C',',
20,6,PD,EDIT=(IIIIIIIIIT.TT),C',',
26,6,PD,EDIT=(IIIIIIIIIT.TT),C',',
32,6,PD,EDIT=(IIIIIIIIIT.TT),C',',
38,6,PD,EDIT=(IIIIIIIIIT.TT),C',',
44,5,PD,EDIT=(IIIIIIIIT))
OPTION ZDPRINT

Here's an example of what I am expecting:
End of file - Total Records: 42000 Sum1: 24353 Sum2: 53235 Sum3: 6394 Sum4: 36236

Using the Getting Started guide (page 95), I tried diong this as a separate step using OUTFIL but I am getting various syntax errors.
David
D9988
 
Posts: 34
Joined: Fri Nov 09, 2007 10:55 pm
Has thanked: 0 time
Been thanked: 0 time
Top

Re: Help with merging 2 files together

Postby Frank Yaeger » Thu Apr 07, 2011 4:30 am

It's not clear exactly what you want the output to look like, but you can modify the following DFSORT statements as needed for what you actually need:

Select all
  JOINKEYS F1=IN1,FIELDS=(1,19,A)                           
  JOINKEYS F2=IN2,FIELDS=(1,19,A)                           
  REFORMAT FIELDS=(F1:1,19,F2:20,12,F1:32,19)               
  OPTION COPY                                               
  OUTREC BUILD=(1,8,C',',9,6,C',',15,5,C',',                 
   20,6,PD,EDIT=(IIIIIIIIIT.TT),C',',                       
   26,6,PD,EDIT=(IIIIIIIIIT.TT),C',',                       
   32,6,PD,EDIT=(IIIIIIIIIT.TT),C',',                       
   38,6,PD,EDIT=(IIIIIIIIIT.TT),C',',                       
   44,5,PD,EDIT=(IIIIIIIIT),120:X)                           
  OUTFIL REMOVECC,                                           
    TRAILER1=(/,'End of file - Total Records: ',             
     COUNT=(M10,LENGTH=5),X,                                 
     TOT=(23,13,UFF,EDIT=(IIIIIIIIIT.TT)),X,                 
     TOT=(37,13,UFF,EDIT=(IIIIIIIIIT.TT)),X,                 
     TOT=(51,13,UFF,EDIT=(IIIIIIIIIT.TT)),X,                 
     TOT=(65,13,UFF,EDIT=(IIIIIIIIIT.TT)),X,                 
     TOT=(79,9,UFF,EDIT=(IIIIIIIIT)))                       
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times
Top

Re: Help with merging 2 files together

Postby D9988 » Thu Apr 07, 2011 5:02 am

It's working great, thanks Frank!!
David
D9988
 
Posts: 34
Joined: Fri Nov 09, 2007 10:55 pm
Has thanked: 0 time
Been thanked: 0 time
Top
Post a reply

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post
Forum Rules|FAQ|Subscriptions|Bookmarks
cron