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 Profile Log in to check your private messages Log in
 
Merge records of one file based on a key

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

New User


Joined: 08 Oct 2006
Posts: 19
Location: Bangalore

PostPosted: Sat Oct 06, 2007 9:03 pm    Post subject: Merge records of one file based on a key
Reply with quote

Wanted to know if it were possible to merge records from one file based on a key using SORT or may be other utility.

In a file I would have multiple records of same key as different records. I would like to bring the records matching the key on the same line in the output.

For example, the input file would look like, the first three bytes being the key.

001ABC
002XYZ
001DEF
003PPP
003BBB
003CCC

The ouput that is desired is :

001ABCDEF
002XYX
003PPPBBBCCC

Is it also possible to add , a constant like '9999' at the end of the record while writing to the output file. For example the output file after adding the constant would look like :

001ABCDEF9999
002XYX9999
003PPPBBBCCC9999

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

krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Sun Oct 07, 2007 3:45 pm    Post subject:
Reply with quote

sudhirk63
Here is an DFSORT/ICETOOL JCL that merges the records based on the key from the first 3 characters, assuming the maximum number of occurance of each key is considered as 3 here, which can be changed as per your requirement.
But this JCL doesn't add the constant 9999 to our records.
Code:
//*******************************************************             
//STEP001  EXEC PGM=ICETOOL                                           
//TOOLMSG  DD SYSOUT=*                                                 
//DFSMSG   DD SYSOUT=*                                                 
//IN1      DD *                                                       
001ABC                                                                 
002XYZ                                                                 
001DEF                                                                 
003PPP                                                                 
003BBB                                                                 
003CCC                                                                 
/*                                                                     
//TMP1     DD DSN=&&TEMP1,DISP=(MOD,PASS),SPACE=(TRK,(5,5)),UNIT=SYSDA
//TMP2     DD DSN=&&TEMP2,DISP=(MOD,PASS),SPACE=(TRK,(5,5)),UNIT=SYSDA
//OUT      DD SYSOUT=*                                                 
//TOOLIN   DD *                                                       
 SORT FROM(IN1)  TO(TMP1) USING(CP01)                                 
 COPY FROM(TMP1)  TO(TMP2) USING(CP02)                                 
 SPLICE FROM(TMP2) TO(OUT) ON(1,3,ZD) KEEPNODUPS -                     
  WITHEACH WITH(7,3) WITH(10,3) USING(CP03)                             
/*                                                                     
//CP01CNTL DD   *                                                       
  SORT FIELDS=(1,3,CH,A)                                               
  OUTREC BUILD=(1,80,81:SEQNUM,4,ZD,RESTART=(1,3))                     
/*                                                                     
//CP02CNTL DD   *                                                       
  OUTREC IFTHEN=(WHEN=(81,4,ZD,EQ,2),BUILD=(1,3,7:4,3)),               
         IFTHEN=(WHEN=(81,4,ZD,EQ,3),BUILD=(1,3,10:4,3))               
/*                                                                     
//CP03CNTL DD   *                                                       
  OUTREC BUILD=(1,80)                                                   
/*                                                                     
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Sun Oct 07, 2007 3:51 pm    Post subject:
Reply with quote

sudhirk63
You can try using this untested CP03 in my previous DFSORT/ICETOOL JCL.
Code:
//CP03CNTL DD   *                                                       
  OUTREC BUILD=(1,76:C'9999')                                                   
  OUTFIL FNAMES=OUT,BUILD=(1,80,SQZ=(SHIFT=LEFT))
/*
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Mon Oct 08, 2007 9:00 pm    Post subject:
Reply with quote

Sudhir,

As often seems to be the case, Krisprem's solution uses more passes over the data than are needed. Here's a DFSORT/ICETOOL job that does what you asked for in less passes. I assumed your input and output files have RECFM=FB and LRECL=80 and the maximum number of records with each key is 3, but you can change the job appropriately for other situations.

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN DD *
001ABC
002XYZ
001DEF
003PPP
003BBB
003CCC
/*
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=...  output file (FB/80)
//TOOLIN   DD    *
SORT FROM(IN) TO(T1) USING(CTL1)
SPLICE FROM(T1) TO(OUT) ON(1,3,CH) KEEPNODUPS -
  WITHEACH WITH(7,3) WITH(10,3) USING(CTL2)
/*
//CTL1CNTL DD *
  SORT FIELDS=(1,3,CH,A)
  OUTREC IFOUTLEN=84,
   IFTHEN=(WHEN=INIT,
    OVERLAY=(81:C'9999',85:SEQNUM,8,ZD,RESTART=(1,3))),
   IFTHEN=(WHEN=(85,8,ZD,EQ,+2),
     BUILD=(1,3,7:4,3,81:81,4)),
   IFTHEN=(WHEN=(85,8,ZD,EQ,+3),
     BUILD=(1,3,10:4,3,81:81,4))
/*
//CTL2CNTL DD *
  OUTFIL FNAMES=OUT,BUILD=(1,84,SQZ=(SHIFT=LEFT,LENGTH=80))
/*
Back to top
View user's profile Send private message
sudhirk63

New User


Joined: 08 Oct 2006
Posts: 19
Location: Bangalore

PostPosted: Mon Oct 08, 2007 9:17 pm    Post subject:
Reply with quote

Thanks a bunch Frank and krisprems.

I think I may not be able to grasp all the features of ICETOOL till I retire.

Sudhir
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Mon Oct 08, 2007 9:47 pm    Post subject:
Reply with quote

Quote:
I think I may not be able to grasp all the features of ICETOOL till I retire.


Probably not, since I'll keep adding features to it. icon_cool.gif
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Mon Oct 08, 2007 9:49 pm    Post subject:
Reply with quote

sudhirk63
Welcome, Glad that i could help you in time. icon_biggrin.gif
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 Split 1 file into 10 output Files - S... Prasanth Kumar SYNCSORT 5 Sat Sep 16, 2017 12:02 am
No new posts BUILD OUTFIL based on condition other... balaji81_k DFSORT/ICETOOL 13 Fri Sep 08, 2017 11:06 pm
No new posts Skip file from delete pema_yozer All Other Mainframe Topics 3 Tue Aug 29, 2017 3:19 pm
No new posts Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 21 Sun Aug 27, 2017 10:35 pm
No new posts editting the file by inserting zeros ... pramodrsingh DFSORT/ICETOOL 9 Mon Aug 21, 2017 5:58 pm

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