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

Concatenating multiple rows (at max 3) into single row
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
rahulcherekar

New User


Joined: 20 Feb 2007
Posts: 16
Location: New York
PostPosted: Tue Nov 16, 2010 5:36 pm
 Reply with quote
Hi,

My input file is of 2000 bytes.

I/p file looks like:

11abcsomedata
2defsomedata
3ghisomedata
11uvwsomedata
2xyzsomedata
11pqrsomedata
11abcsomedata
2defsomedata
3ghisomedata

O/p is expected to be of 6000 bytes

and desired o/p should look like

11abcsomedata2defsomedata3ghisomedata
11uvwsomedata2xyzsomedata
11pqrsomedata
11abcsomedata2defsomedata3ghisomedata

My key is at 2nd cloumn (1,2 &3). Records with keys 1,2 & 3 are required to in one row. If 3 is missing then 1 & 2 are to be copied in first 4000 bytes and next 2000 bytes needs to be filled with spaces, similarly if only 1 is present, then output should populate first 2000 bytes and next 4000 bytes needs to have spaces.

I am not able to use resize and splice is not accepting more than 1500 in ON command.

Thanks,
Rahul.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Tue Nov 16, 2010 11:01 pm
 Reply with quote
rahulcherekar,

The following DFSORT/ICETOOL JCL will give you the desired results

Code:

//STEP0100 EXEC PGM=ICETOOL                                         
//TOOLMSG  DD SYSOUT=*                                             
//DFSMSG   DD SYSOUT=*                                             
//IN       DD DSN=Your input FB lrecl 2000 byte file,DISP=SHR
//OUT      DD SYSOUT=*
//TOOLIN   DD *                                                     
  SPLICE FROM(IN) TO(OUT) ON(8001,8,CH) KEEPNODUPS -               
         WITH(2001,2000) WITH(4001,2000) USING(CTL1)               
//CTL1CNTL DD *                                                     
  SORT FIELDS=COPY                                                 
  INREC IFTHEN=(WHEN=INIT,BUILD=(6001:1,2000)),                     
  IFTHEN=(WHEN=GROUP,BEGIN=(6002,1,ZD,EQ,1),PUSH=(8001:ID=8)),     
  IFTHEN=(WHEN=GROUP,BEGIN=(6002,1,ZD,EQ,1),PUSH=(0001:6001,2000), 
  RECORDS=3),                                                       
  IFTHEN=(WHEN=GROUP,BEGIN=(6002,1,ZD,EQ,2),PUSH=(2001:6001,2000), 
  RECORDS=2),                                                       
  IFTHEN=(WHEN=(6002,1,ZD,EQ,1),OVERLAY=(2001:4001,2000)),         
  IFTHEN=(WHEN=(6002,1,ZD,EQ,3),OVERLAY=(4001:6001,2000))           
  OUTFIL BUILD=(1,6000)                                             
//*


OR You can use the following DFSORT JCL
Code:

//STEP0100 EXEC PGM=SORT 
//SYSOUT   DD SYSOUT=*
//SORTIN   DD DSN=Your input FB lrecl 2000 byte file,DISP=SHR
//SORTOUT  DD SYSOUT=*
//SYSIN    DD *                                                     
  SORT FIELDS=COPY                                                   
  INREC IFTHEN=(WHEN=INIT,BUILD=(6001:1,2000)),                     
  IFTHEN=(WHEN=GROUP,BEGIN=(6002,1,ZD,EQ,1),PUSH=(8001:ID=8)),       
  IFTHEN=(WHEN=GROUP,BEGIN=(6002,1,ZD,EQ,1),PUSH=(0001:6001,2000),   
  RECORDS=3),                                                       
  IFTHEN=(WHEN=GROUP,BEGIN=(6002,1,ZD,EQ,2),PUSH=(2001:6001,2000),   
  RECORDS=2),                                                       
  IFTHEN=(WHEN=(6002,1,ZD,EQ,1),OVERLAY=(2001:4001,2000)),           
  IFTHEN=(WHEN=(6002,1,ZD,EQ,3),OVERLAY=(4001:6001,2000))           
  OUTFIL REMOVECC,NODETAIL,BUILD=(1,6000),                           
  SECTIONS=(8001,8,TRAILER3=(1,6000))                               
//*
Back to top
View user's profile Send private message
rahulcherekar

New User


Joined: 20 Feb 2007
Posts: 16
Location: New York
PostPosted: Wed Nov 17, 2010 4:35 pm
 Reply with quote
Thanks Skolusu for your solution.

I used the below sort card and it also worked fine.

//TOOLIN DD *
SPLICE FROM(IN) TO(OUT) ON(6001,8,ZD) KEEPNODUPS WITHANY -
WITH(2001,2000) WITH(4001,2000) USING(CTL1)
/*
//CTL1CNTL DD *
OPTION COPY
INREC IFOUTLEN=6008,
IFTHEN=(WHEN=GROUP,BEGIN=(1,1,CH,EQ,C'1'),
PUSH=(6001:ID=8,6009:SEQ=5)),
IFTHEN=(WHEN=(6009,5,ZD,EQ,2),BUILD=(2001:1,2000,6001:6001,8)),
IFTHEN=(WHEN=(6009,5,ZD,EQ,3),BUILD=(4001:1,2000,6001:6001,8))
OUTFIL FNAMES=OUT,BUILD=(1,6000)
/*

Thanks again for your help,
Rahul.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Wed Nov 17, 2010 10:19 pm
 Reply with quote
rahulcherekar wrote:
Thanks Skolusu for your solution.

I used the below sort card and it also worked fine.



Good that it worked for you. what happens if you have data like this ?

Code:

11XXXXOMEDATA   
3XXXXXMEDATA    - missing type 2 record should be in 4001 byte
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 INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts Multiple table unload using INZUTILB DB2 2
No new posts Grouping by multiple headers DFSORT/ICETOOL 7
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