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

Combine Multiple Rows into Single Row


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
padhumainframe

New User


Joined: 21 Sep 2010
Posts: 17
Location: India

PostPosted: Tue Sep 28, 2010 2:54 pm
Reply with quote

I have a Input file (RECFM = VB, LRECL = 400). It contains data as below

Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0----+----1----+----2
***************************** Top of Data ******************************************************************************
${1:F01NWBKGB20CXXX1672607646}{2:O2020632100713}   
${1:F01NWBKGB20CXXX1672607646}                   
$                                                 
 {1:F01NWBKGB20CXXX1672607646}                   
 {2:O2000632100713BBRUBEB0A0106227632601100}       
 {3:{103:EBA}                                     
 {4:  :20:71F:USD95249751F}                       
${1:F01NWBKGB20CXXX1672607646}{2:O2050632100713BBR}
${2:O2000632100713BBRUBEB0A0106227632601100}       
 {5:{CHK:B58DE89F073C}                           
**************************** Bottom of Data ****************************************************************************


I need the output file (RECFM = VB, LRECL = 400)as below

Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0----+----1----+----2
***************************** Top of Data ******************************************************************************
${1:F01NWBKGB20CXXX1672607646}{2:O2020632100713}   
${1:F01NWBKGB20CXXX1672607646}                   
${1:F01NWBKGB20CXXX1672607646}{2:O2000632100713BBRUBEB0A0106227632601100}{3:{103:EBA}{4:  :20:71F:USD95249751F}         
${1:F01NWBKGB20CXXX1672607646}{2:O2050632100713BBR}
${2:O2000632100713BBRUBEB0A0106227632601100}{5:{CHK:B58DE89F073C}       
**************************** Bottom of Data ****************************************************************************


Rule:
1.Each '$' symbol indicates the begining of a record.
2.Any number of rows between two '$' symbols must me joined to make a single row.
3. There should not be any spaces present between data when two rows are joined
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA

PostPosted: Tue Sep 28, 2010 5:20 pm
Reply with quote

padhumainframe,
Is there a limit to max. number of records between 2 $ symbols?

Since your input is vb 400 and you are combining multiple records into single, your output is likely go beyond 400 bytes.

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

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune

PostPosted: Tue Sep 28, 2010 5:24 pm
Reply with quote

although it might be possible in DFSORT, i am sure the more efficient way would be to write a cobol code and use the STRING function.

Simple AND effective in this case
Back to top
View user's profile Send private message
padhumainframe

New User


Joined: 21 Sep 2010
Posts: 17
Location: India

PostPosted: Wed Sep 29, 2010 8:27 am
Reply with quote

sqlcode1,

Quote:
Is there a limit to max. number of records between 2 $ symbols?


There is no limit to the max. number of records b/w two $ symbols. The max. number of records b/w two $ symbols i could see is 7. In some cases it is 5/3/2/4.

Quote:
Since your input is vb 400 and you are combining multiple records into single, your output is likely go beyond 400 bytes.


True. But, the max length i could see is only 55. Also the max. number of records b/w two $ symbols i could see is 7.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Sep 29, 2010 8:51 am
Reply with quote

Hello,

Why does someone believe these records need to be combined?

Hopefully, this "requirement" does not exist simply because there are developers who cannot process the multi-record data. The posted input data is completely usable to anyone who can write even rather simple code.
Back to top
View user's profile Send private message
padhumainframe

New User


Joined: 21 Sep 2010
Posts: 17
Location: India

PostPosted: Wed Sep 29, 2010 2:11 pm
Reply with quote

Dick,

The requirement actually exists and it does.

The actual input file i have contains more than 10 million records. I dont want my program to search for a string in that many rows. Thats the reason i need to combine the records that fall under a $ symbol. By doing so, i hope the run time will reduce a lot.
Back to top
View user's profile Send private message
smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune

PostPosted: Wed Sep 29, 2010 3:17 pm
Reply with quote

Quote:
The actual input file i have contains more than 10 million records. I dont want my program to search for a string in that many rows. Thats the reason i need to combine the records that fall under a $ symbol. By doing so, i hope the run time will reduce a lot.


and you expect adding a sort step will reduce the time of your JOB ?
DFSORT too will have to process your 10 million records icon_smile.gif

Instead of double / multiple processing your file, why not finish it in one go in one program
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Wed Sep 29, 2010 3:18 pm
Reply with quote

odd but the topic looks a lot like
ibmmainframes.com/viewtopic.php?t=51485&highlight=

it would be nice if people working in the same organization would coordinate their forum posting
in order to avoid duplicate posts on the same issue and wasting people time in replying to many posts when the issue is really ONE
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Sep 29, 2010 7:45 pm
Reply with quote

Hello,

Quote:
The requirement actually exists and it does.
Because you don't want to write the code to use the file does not constitute a requirement.. . .

Why would you believe that copying 10 million records to save a bit of thought would reduce any run time. . . icon_confused.gif If this is to be an ongoing process, the wasted machine resources will be wasted many times - over and over. If this is propeerly implemented, there is no need to waste the time. . .

If there is any kind of proper review, this would not be accepted for promotion.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA

PostPosted: Wed Sep 29, 2010 11:05 pm
Reply with quote

padhumainframe,
See if below jcl works for you...
I assumed that you can have maximum of 7 records between two $ symbols. Also I assumed max. record length of 60. You may adjust if they are different than my assumption.

Code:
//STEP01   EXEC  PGM=ICETOOL                                           
//TOOLMSG  DD SYSOUT=*                                                 
//DFSMSG   DD SYSOUT=*                                                 
//TOOLIN   DD *                                                         
 SPLICE FROM(IN) TO(OUT) ON(504,8,CH) WITHANY KEEPNODUPS -             
     WITH(076,61) WITH(137,61) WITH(198,61) WITH(259,61) -             
     WITH(320,61) WITH(381,61) WITH(442,61) USING(CTL1)                 
/*                                                                     
//IN       DD DSN=INPUT VB400                                 
//OUT      DD DSN=FORMATTED VB400                                     
//CTL1CNTL DD *                                                         
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(503:X)),                             
        IFTHEN=(WHEN=GROUP,BEGIN=(5,1,CH,EQ,C'$'),                     
                PUSH=(504:ID=8,SEQ=2)),                                 
        IFTHEN=(WHEN=(512,2,ZD,EQ,2),OVERLAY=(076:C'$',077:06,60)),     
        IFTHEN=(WHEN=(512,2,ZD,EQ,3),OVERLAY=(137:C'$',138:06,60)),     
        IFTHEN=(WHEN=(512,2,ZD,EQ,4),OVERLAY=(198:C'$',199:06,60)),     
        IFTHEN=(WHEN=(512,2,ZD,EQ,5),OVERLAY=(259:C'$',260:06,60)),     
        IFTHEN=(WHEN=(512,2,ZD,EQ,6),OVERLAY=(320:C'$',321:06,60)),     
        IFTHEN=(WHEN=(512,2,ZD,EQ,7),OVERLAY=(381:C'$',382:06,60)),     
        IFTHEN=(WHEN=(512,2,ZD,EQ,8),OVERLAY=(442:C'$',443:06,60))     
 OUTFIL IFTHEN=(WHEN=INIT,PARSE=(%01=(ENDAT=C'$',FIXLEN=01),           
                                 %02=(ENDBEFR=C'$',FIXLEN=60),         
                                 %03=(ENDBEFR=C'$',FIXLEN=60),         
                                 %04=(ENDBEFR=C'$',FIXLEN=60),         
                                 %05=(ENDBEFR=C'$',FIXLEN=60),         
                                 %06=(ENDBEFR=C'$',FIXLEN=60),         
                                 %07=(ENDBEFR=C'$',FIXLEN=60),         
                                 %08=(ENDBEFR=C'$',FIXLEN=60),         
                                 %09=(ENDBEFR=C'$',FIXLEN=60)),         
                                 BUILD=(1,4,%01,                       
                                            %02,                       
                                            %03,                       
                                            %04,                       
                                            %05,                       
                                            %06,                       
                                            %07,                       
                                            %08,                       
                                            %09)),                     
        IFTHEN=(WHEN=INIT,FINDREP=(OUT=C'',IN=C' ')),FNAMES=OUT         
/*                                                                     
//STEP02   EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD DSN=VB FORMATTED                                         
//SORTOUT  DD SYSOUT=*                                                 
//SYSIN    DD *                                                         
  SORT FIELDS=COPY                                                     
  OUTFIL VTOF,BUILD=(1,2,BI,M10,LENGTH=6)                               
//*                                                                     

Thanks,
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