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 records

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

New User


Joined: 11 Nov 2008
Posts: 27
Location: Canada

PostPosted: Thu Sep 10, 2015 3:19 am    Post subject: Merging records
Reply with quote

I have a situation and can't find a solution since morning.
Can you please help me with below...

I have a file

ABC123DA11
ABC123DA12
ABC123DA13
ABC123DA14
ABC123DA15
DEF345DA21
DEF345DA22
DEF345DA23
DEF345DA24

where position 1,6 is a key and 7,10 are data.

I need to merge the records based on key as per below
ABC123DA11DA12DA13DA14DA15
DEF345DA21DA22DA23DA24

That means, first 6 bytes will be the key and the data will be appended from all matching records and becomes single record. Other dups will be removed.
There could be max 5 match records, so if there are less records.. remaining data positions will be replaced with spaces.

I hope, I was clear. Thanks for your help.
Back to top
View user's profile Send private message

RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Thu Sep 10, 2015 6:25 am    Post subject: Reply to: Merging records
Reply with quote

Per me, It certainly can be done but definitely require some thinking.

Here are the steps (can be done in a single SORT/ICETOOL step):

1. Mark the records with sequence numbers inside a group. So, a group having 5 records will have seq numbers 1,2,3,4,5. Use RESTART to restart the seq numbers for each group.
2. Now, use OVERLAY to copy the data part of the record with seq num 1 to a different location (say column 15-18). And then, the data part of record 2 to location 20-23 and repeat the process for all the records in a group.

Your records will look like this:
Code:
ABC123DA11    DA11
ABC123DA12         DA12
ABC123DA13              DA13
ABC123DA14                   DA14
ABC123DA15                        DA15

3. Now, use IFTHEN=(WHEN=GROUP for a group that begins with a non-blank character and with KEYBEGIN=(1,6) and PUSH the data parts. So now, your data will look like this:
Code:
ABC123 DA11
ABC123 DA11 DA12
ABC123 DA11 DA12 DA13
ABC123 DA11 DA12 DA13 DA14
ABC123 DA11 DA12 DA13 DA14 DA15

If you reach here then there should be no problem in filtering the record you want i.e. the last one with all the data parts.

Try and post if you face any difficulty.

.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Thu Sep 10, 2015 1:18 pm    Post subject: Reply to: Merging records
Reply with quote

Unless SORT's MERGE works for you (it does not) you are not Merging records.

COPY operation.

WHEN=INIT with SEQNUM and RESTART (for the key) to a temporary extension of the record.

Four WHEN=GROUPs, with BEGIN for the first four values of the SEQNUM, to PUSH the data to specific locations for each GROUP. Specify RECORDS=5 on the first, =4 on the second, =3 then =2.

The fifth record now has all the data you want. If there is a fifth record. If there is not, the last record will have what data there is.

Use OUTFIL reporting functions, REMOVECC, NODETAIL, SECTIONS (specifying the key) and TRAILER3 to do a final arrangement of the data. Which will get you the last record in each group.

Have a look at this one.
Back to top
View user's profile Send private message
hailashwin

New User


Joined: 16 Oct 2008
Posts: 73
Location: Boston

PostPosted: Thu Sep 10, 2015 4:47 pm    Post subject: Reply to: Merging records
Reply with quote

I tried Bill's steps and here is the working code icon_smile.gif
Code:

//SYSIN DD *                                                     
  OPTION COPY                                                     
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(20:SEQNUM,1,ZD,RESTART=(1,6))),
         IFTHEN=(WHEN=GROUP,BEGIN=(20,1,CH,EQ,C'1'),RECORDS=5,   
       PUSH=(21:7,4)),                                           
         IFTHEN=(WHEN=GROUP,BEGIN=(20,1,CH,EQ,C'2'),RECORDS=4,   
       PUSH=(31:7,4)),                                           
         IFTHEN=(WHEN=GROUP,BEGIN=(20,1,CH,EQ,C'3'),RECORDS=3,   
       PUSH=(41:7,4)),                                           
         IFTHEN=(WHEN=GROUP,BEGIN=(20,1,CH,EQ,C'4'),RECORDS=2,   
       PUSH=(51:7,4)),                                           
         IFTHEN=(WHEN=GROUP,BEGIN=(20,1,CH,EQ,C'5'),RECORDS=1,   
       PUSH=(61:7,4))                                             
  OUTFIL REMOVECC,                                               
           NODETAIL,                                             
           SECTIONS=(1,6,                                         
                     TRAILER3=(1,6,21,4,31,4,41,4,51,4,61,4))     
/*                                                               


Thanks,
Ashwin.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Thu Sep 10, 2015 6:45 pm    Post subject:
Reply with quote

FWIW, something else to think about if the number of records in a group are equal (not for this example).

Use RESIZE and make it easy.

Code:
//STEP0001 EXEC PGM=ICETOOL                               
//TOOLMSG  DD  SYSOUT=*                                   
//DFSMSG   DD  SYSOUT=*                                   
//IN       DD  DISP=SHR,DSN=INPUT/FB10                   
//OUT      DD  SYSOUT=*                                   
//TOOLIN   DD  *                                         
 RESIZE FROM(IN) TO(OUT) TOLEN(50) -                     
 USING(CTL1)                                             
/*                                                       
//CTL1CNTL DD *                                           
 OUTFIL FNAMES=OUT,BUILD=(1,10,17,04,27,04,37,04,47,04)   
/*                                                       
//SYSOUT   DD  SYSOUT=*                                   
//SYSPRINT DD  SYSOUT=*                                   
//*                                                       


Thanks,
Back to top
View user's profile Send private message
skgupta81

New User


Joined: 11 Nov 2008
Posts: 27
Location: Canada

PostPosted: Thu Sep 10, 2015 6:52 pm    Post subject: Reply to: Merging records
Reply with quote

Thanks.. I will try the above solutions quickly and let you know the outcome...
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Thu Sep 10, 2015 8:33 pm    Post subject: Reply to: Merging records
Reply with quote

Quote:
Use RESIZE and make it easy.

It's good knowledge. Smaller records can be combined into a larger record and larger records can be broken into smaller one. Although, as mentioned, not for this example but definitely something to keep in mind.

.
Back to top
View user's profile Send private message
skgupta81

New User


Joined: 11 Nov 2008
Posts: 27
Location: Canada

PostPosted: Fri Sep 11, 2015 3:17 am    Post subject: Re: Reply to: Merging records
Reply with quote

Below solution(Aswin's method) works only in case there are 5 records everytime for a key. but in my case, there could be 1 to 5 records for the key.

With below solution, when there are more than 1 but less than 5 match records for a key, it writes the data in all consecutive 5 records even if its not part of the key and creates invalid file in the end.


FOR EX.
input file:

XYZ345DA10
ABC123DA11
ABC123DA12
DEF678DA21
MNO345DA33
PQR345DA44

output file:

XYZ345DA10 DA10
ABC123DA11 DA11
ABC123DA12 DA11 DA12
DEF678DA21 DA21 DA12
MNO345DA33 DA33 DA12
PQR345DA44 DA44 DA12


Well, this is how my actual control card looks like for the INREC part, where 1,140 is the key and then the data is at 141,164 (24char)

OPTION COPY
INREC IFTHEN=(WHEN=INIT,OVERLAY=(300:SEQNUM,1,ZD,RESTART=(1,140))),
IFTHEN=(WHEN=GROUP,BEGIN=(300,1,CH,EQ,C'1'),RECORDS=5,
PUSH=(165:141,24)),
IFTHEN=(WHEN=GROUP,BEGIN=(300,1,CH,EQ,C'2'),RECORDS=4,
PUSH=(190:141,24)),
IFTHEN=(WHEN=GROUP,BEGIN=(300,1,CH,EQ,C'3'),RECORDS=3,
PUSH=(213:141,24)),
IFTHEN=(WHEN=GROUP,BEGIN=(300,1,CH,EQ,C'4'),RECORDS=2,
PUSH=(237:141,24)),
IFTHEN=(WHEN=GROUP,BEGIN=(300,1,CH,EQ,C'5'),RECORDS=1,
PUSH=(261:141,24))

Will try 'RahulG31' method now.





hailashwin wrote:
I tried Bill's steps and here is the working code icon_smile.gif
Code:

//SYSIN DD *                                                     
  OPTION COPY                                                     
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(20:SEQNUM,1,ZD,RESTART=(1,6))),
         IFTHEN=(WHEN=GROUP,BEGIN=(20,1,CH,EQ,C'1'),RECORDS=5,   
       PUSH=(21:7,4)),                                           
         IFTHEN=(WHEN=GROUP,BEGIN=(20,1,CH,EQ,C'2'),RECORDS=4,   
       PUSH=(31:7,4)),                                           
         IFTHEN=(WHEN=GROUP,BEGIN=(20,1,CH,EQ,C'3'),RECORDS=3,   
       PUSH=(41:7,4)),                                           
         IFTHEN=(WHEN=GROUP,BEGIN=(20,1,CH,EQ,C'4'),RECORDS=2,   
       PUSH=(51:7,4)),                                           
         IFTHEN=(WHEN=GROUP,BEGIN=(20,1,CH,EQ,C'5'),RECORDS=1,   
       PUSH=(61:7,4))                                             
  OUTFIL REMOVECC,                                               
           NODETAIL,                                             
           SECTIONS=(1,6,                                         
                     TRAILER3=(1,6,21,4,31,4,41,4,51,4,61,4))     
/*                                                               


Thanks,
Ashwin.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Fri Sep 11, 2015 5:17 am    Post subject: Reply to: Merging records
Reply with quote

Sorry, forgot: After the WHEN=GROUPs you need WHEN=(logicalexpression)s, again testing the SEQ.

If one, clobber the places for two, three and four.

If two, clobber for three and four.

If three, clobber for four.

Clobber means set it to space.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Fri Sep 11, 2015 5:24 am    Post subject: Reply to: Merging records
Reply with quote

This is what I had:
Code:
 SORT FIELDS=COPY
 INREC IFTHEN=(WHEN=INIT,OVERLAY=(15:SEQNUM,2,ZD,RESTART=(1,6))),
       IFTHEN=(WHEN=(15,2,ZD,EQ,01),OVERLAY=(20:7,4)),
       IFTHEN=(WHEN=(15,2,ZD,EQ,02),OVERLAY=(25:7,4)),
       IFTHEN=(WHEN=(15,2,ZD,EQ,03),OVERLAY=(30:7,4)),
       IFTHEN=(WHEN=(15,2,ZD,EQ,04),OVERLAY=(35:7,4)),
       IFTHEN=(WHEN=(15,2,ZD,EQ,05),OVERLAY=(40:7,4))
 OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(20,4,CH,NE,C'    '),KEYBEGIN=(1,6),
                                               PUSH=(20:20,4)),
        IFTHEN=(WHEN=GROUP,BEGIN=(25,4,CH,NE,C'    '),KEYBEGIN=(1,6),
                                               PUSH=(25:25,4)),
        IFTHEN=(WHEN=GROUP,BEGIN=(30,4,CH,NE,C'    '),KEYBEGIN=(1,6),
                                               PUSH=(30:30,4)),
        IFTHEN=(WHEN=GROUP,BEGIN=(35,4,CH,NE,C'    '),KEYBEGIN=(1,6),
                                               PUSH=(35:35,4)),
        IFTHEN=(WHEN=GROUP,BEGIN=(40,4,CH,NE,C'    '),KEYBEGIN=(1,6),
                                               PUSH=(40:40,4))
   OUTFIL REMOVECC,
            NODETAIL,
            SECTIONS=(1,6,
                      TRAILER3=(1,6,20,4,25,4,30,4,35,4,40,4))
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Fri Sep 11, 2015 7:00 pm    Post subject: Reply to: Merging records
Reply with quote

You also have an option to use old school SPLICE method. Output will be sorted on 1,6 position. Hopefully i have covered all possible situations icon_smile.gificon_smile.gif.

If we can request one feature to be added to existing set of functionalities, RESIZE using ON fields would top my list.

Code:
//STEP0001 EXEC PGM=ICETOOL                               
//TOOLMSG  DD  SYSOUT=*                                   
//DFSMSG   DD  SYSOUT=*                                   
//IN       DD  DISP=SHR,DSN=INPUT/FB10                   
//OUT      DD  SYSOUT=*                                   
//TOOLIN   DD  *                                         
 SPLICE FROM(IN) TO(OUT) ON(1,6,CH) WITHEACH -           
 WITH(11,4) WITH(15,4) WITH(19,4) WITH(23,4) -           
 USING(CTL1)                                             
/*                                                       
//CTL1CNTL DD *                                           
 INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,6),PUSH=(81:SEQ=1)),
       IFTHEN=(WHEN=(81,1,ZD,EQ,2),OVERLAY=(11:7,4)),     
       IFTHEN=(WHEN=(81,1,ZD,EQ,3),OVERLAY=(15:7,4)),     
       IFTHEN=(WHEN=(81,1,ZD,EQ,4),OVERLAY=(19:7,4)),     
       IFTHEN=(WHEN=(81,1,ZD,EQ,5),OVERLAY=(23:7,4))     
 OUTFIL FNAMES=OUT,BUILD=(1,26)                           
/*                                                       
//SYSOUT   DD  SYSOUT=*                                   
//SYSPRINT DD  SYSOUT=*                                   
//*                                                       




Thanks,
Back to top
View user's profile Send private message
skgupta81

New User


Joined: 11 Nov 2008
Posts: 27
Location: Canada

PostPosted: Sat Sep 12, 2015 9:13 pm    Post subject: Re: Reply to: Merging records
Reply with quote

Yes, it worked now. I will also try other guy's suggestions to learn more.

Thanks everyone.

Bill Woodger wrote:
Sorry, forgot: After the WHEN=GROUPs you need WHEN=(logicalexpression)s, again testing the SEQ.

If one, clobber the places for two, three and four.

If two, clobber for three and four.

If three, clobber for four.

Clobber means set it to space.
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 Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm
No new posts how to split records based on specifi... Venkata Ramayya DFSORT/ICETOOL 6 Wed Sep 28, 2016 3:20 am


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