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
 

 

Concatenating multiple rows (at max 3) into single row

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Concatenating multiple rows (at max 3) into single row
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    Post subject: Reply to: Concatenating multiple rows (at max 3) into single
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    Post subject:
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    Post subject:
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    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 Data replication from multiple Db2 ta... kishpra DB2 5 Mon Mar 27, 2017 9:58 pm
No new posts Splitting of single report to seperat... abdulrafi TSO/ISPF 4 Mon Mar 27, 2017 3:59 pm
No new posts Unable to catalog a gdg dataset resid... Shovan JCL & VSAM 7 Fri Mar 24, 2017 2:24 pm
No new posts Append data from two files into a sin... Praveen04 DFSORT/ICETOOL 5 Thu Mar 16, 2017 7:29 pm
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm


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