MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Merge multiple records into single record
Goto page 1, 2  Next
 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
chandra
Beginner


Joined: 26 Sep 2003
Posts: 130
Topics: 36
PostPosted: Thu Oct 16, 2008 10:58 am    Post subject: Merge multiple records into single record Reply with quote
Hi All,

I have a file with LRECL=80, RECFM=FB and the input data as shown. The column 1 to 39 is key. column 40 will tell the sequence number for key.
Code:

----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
XZ41A1900110011979*****99999         0010000015000099999999999***   PRKY ENF 32A
XZ41A1900110011979*****99999         002C10000000000000000000 ** LANY8OU ** NOOA
XZ41A1900110011979*****99999         0119999999999999999999999*** FGW656IU78H FF
XZ41A1900110011979*****99999         012C01500000000000000000  &&& JAKJNN   **AS
XZ41A1900110011979*****99999         013D0163456700000000000000000 GWYJ4TH 4545
XZ41A1900210151999*****04499         0210000015000099999999999***SJHRTU6U323565H
XZ41A1900210151999*****04499         022C00000000000000000000 0110011979*****9 *
XZ41A1900210151999*****04499         0310000030000099999999999*** FGHRHFS465735
XZ41A1900210151999*****04499         032C00000000000000000000 01100ACFGGEF*****9
XZ41A1900210151999*****04499         0330000045000099999999999***
XZ41A1900210151999*****04499         034C00000000000000000000 01100SFDREHG*****9
XZ41A1900210151999*****04499         0510000075000099999999999***

I want to merge same key records into single record as follows (Exclude the 40 the column)

Output :-
Code:

XZ41A1900110011979*****99999         0010000015000099999999999***   PRKY ENF 32AC10000000000000000000 ** LANY8OU ** NOOA
XZ41A1900110011979*****99999         0119999999999999999999999*** FGW656IU78H FFC01500000000000000000  &&& JAKJNN   **ASD0163456700000000000000000 GWYJ4TH 4545
XZ41A1900210151999*****04499         0210000015000099999999999***SJHRTU6U323565HC00000000000000000000 0110011979*****9 *
XZ41A1900210151999*****04499         0310000030000099999999999*** FGHRHFS465735 C00000000000000000000 01100ACFGGEF*****90000045000099999999999***              C00000000000000000000 01100SFDREHG*****9
XZ41A1900210151999*****04499         0510000075000099999999999***

Can any one please let me know how to do it with SORT utility. I have SYNCSORT.
_________________
Regards,
Chandra
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12382
Topics: 75
Location: San Jose
PostPosted: Thu Oct 16, 2008 12:46 pm    Post subject: Reply with quote
chandra,

You can do this kind of thing quite easily and efficiently with the new WHEN=GROUP function of DFSORT available with z/OS DFSORT V1R5 PTF UK90013 (July, 2008) like this

I assumed that your can have a max of 9 duplicates per key as the sequence in the 40th byte is just 1 byte

Code:

 //STEP0100 EXEC PGM=ICEMAN                                           
 //SYSOUT   DD SYSOUT=*                                               
 //SORTIN   DD DSN=your input 80 byte file,DISP=SHR                         
 //SORTOUT  DD SYSOUT=*                                               
 //SYSIN    DD *                                                       
   SORT FIELDS=(1,39,CH,A)                                             
   OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(40,1,ZD,EQ,1),RECORDS=9,           
   PUSH=(081:1,39,41,40)),                                             
   IFTHEN=(WHEN=GROUP,BEGIN=(40,1,ZD,EQ,2),RECORDS=8,PUSH=(160:41,40)),
   IFTHEN=(WHEN=GROUP,BEGIN=(40,1,ZD,EQ,3),RECORDS=7,PUSH=(200:41,40)),
   IFTHEN=(WHEN=GROUP,BEGIN=(40,1,ZD,EQ,4),RECORDS=6,PUSH=(240:41,40)),
   IFTHEN=(WHEN=GROUP,BEGIN=(40,1,ZD,EQ,5),RECORDS=5,PUSH=(280:41,40)),
   IFTHEN=(WHEN=GROUP,BEGIN=(40,1,ZD,EQ,6),RECORDS=4,PUSH=(320:41,40)),
   IFTHEN=(WHEN=GROUP,BEGIN=(40,1,ZD,EQ,7),RECORDS=3,PUSH=(360:41,40)),
   IFTHEN=(WHEN=GROUP,BEGIN=(40,1,ZD,EQ,8),RECORDS=2,PUSH=(400:41,40)),
   IFTHEN=(WHEN=(40,1,ZD,EQ,9),OVERLAY=(440:41,40))                   
                                                                       
   OUTFIL REMOVECC,NODETAIL,BUILD=(400X),                             
   SECTIONS=(1,39,                                                     
   TRAILER3=(81,256,338,142))             
/*

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
chandra
Beginner


Joined: 26 Sep 2003
Posts: 130
Topics: 36
PostPosted: Thu Oct 16, 2008 2:00 pm    Post subject: Reply with quote
Hi Kolusu,

Thanks for your solution. I think the SYNCSORT don't support WHEN=GROUP function.

Thanks,
Chandra.
Back to top
View user's profile Send private message
Dibakar
Advanced


Joined: 02 Dec 2002
Posts: 700
Topics: 63
Location: USA
PostPosted: Sun Oct 19, 2008 11:47 am    Post subject: Reply with quote
Chandra,

Are you interested only in SYNCSORT solution or COBOL/REXX will do?

Diba.
Back to top
View user's profile Send private message Send e-mail
chandra
Beginner


Joined: 26 Sep 2003
Posts: 130
Topics: 36
PostPosted: Mon Oct 20, 2008 9:36 am    Post subject: Reply with quote
Hi Diba,

Can you please let me know how to do it in REXX ?
_________________
Regards,
Chandra
Back to top
View user's profile Send private message
Dibakar
Advanced


Joined: 02 Dec 2002
Posts: 700
Topics: 63
Location: USA
PostPosted: Mon Oct 20, 2008 10:47 am    Post subject: Reply with quote
Chandra,

Don't know if this can be done with SYNCSORT or not. I don't have mainframe access to try. With Kolusu moving to DFSORT this forum has lost valuable SYNCSORT expertise.

Anyway below is REXX solution (may need fine tuning), if performance is not good then I would suggest you to go for COBOL solution. This solution is to run in batch. If you don't know how run REXX in batch then search this forum.

Code:


/* Untested REXX  code to merge records.
    Input and output files are assumed to be allocated in the JCL
    Input file: PREMERGE
    Output file: MERGED
*/

"execio 0 diskr premerge"
"execio 0 disw merged"

"execio 1 diskr premerge (stem pline."
if (rc = 0) then
do
  mline.1 = pline.1
  "execio 1 diskr premerge (stem pline."
  do while (rc = 0)
    if ((substr(pline.1,40,1) = '1') then
    do
      "execio 1 diskw merged (stem mline."
      mline.1 = pline.1
    end
    else
      mline.1 = mline.1||right(pline.1,40)
  end
  "execio 1 diskw merged (stem mline."
end

"execio 0 diskr premerge (finis"
"execio 0 disw merged (finis"

exit 0


Diba
Back to top
View user's profile Send private message Send e-mail
amargulies
Beginner


Joined: 10 Jan 2007
Posts: 123
Topics: 0
PostPosted: Mon Oct 20, 2008 1:19 pm    Post subject: Reply with quote
Try this SyncSort application:
Code:
//STEP1 EXEC PGM=SORT
//SORTIN  DD DSN=input.file
//SORTOUT DD DSN=output.file
//SYSOUT  DD SYSOUT=*
//SYSIN   DD *
   SORT FIELDS=(1,39,CH,A),EQUALS
   OUTREC IFTHEN=(WHEN=INIT, 
     OVERLAY=(40:SEQNUM,1,ZD,RESTART=(1,39))), 
     IFTHEN=(WHEN=(40,1,CH,EQ,C'1'),
     BUILD=(1:1,80,320Z)), 
     IFTHEN=(WHEN=(40,1,CH,EQ,C'2'), 
     BUILD=(1:1,39,41Z,81:41,40,280Z)),   
     IFTHEN=(WHEN=(40,1,CH,EQ,C'3'), 
     BUILD=(1:1,39,81Z,121:41,40,240Z)), 
     IFTHEN=(WHEN=(40,1,CH,EQ,C'4'), 
     BUILD=(1:1,39,121Z,161:41,40,200Z)), 
     IFTHEN=(WHEN=(40,1,CH,EQ,C'5'),   
     BUILD=(1:1,39,161Z,201:41,40,160Z)),
     IFTHEN=(WHEN=(40,1,CH,EQ,C'6'),
     BUILD=(1:1,39,201Z,241:41,40,120Z)),   
     IFTHEN=(WHEN=(40,1,CH,EQ,C'7'),
     BUILD=(1:1,39,241Z,281:41,40,80Z)),   
     IFTHEN=(WHEN=(40,1,CH,EQ,C'8'),   
     BUILD=(1:1,39,281Z,321:41,40,40Z)), 
     IFTHEN=(WHEN=(40,1,CH,EQ,C'9'), 
     BUILD=(1:1,39,321Z,361:41,40))
   OUTFIL NODETAIL,REMOVECC,SECTIONS=(1,39,
     TRAILER3=(1,80,
     TOT=(81,40,BI,BI,LENGTH=40),
     TOT=(121,40,BI,BI,LENGTH=40),
     TOT=(161,40,BI,BI,LENGTH=40),
     TOT=(201,40,BI,BI,LENGTH=40),
     TOT=(241,40,BI,BI,LENGTH=40),
     TOT=(281,40,BI,BI,LENGTH=40),
     TOT=(321,40,BI,BI,LENGTH=40),
     TOT=(361,40,BI,BI,LENGTH=40)))
/* 

_________________
Alissa Margulies
SyncSort Mainframe Product Services
201-930-8260
zos_tech@syncsort.com
Back to top
View user's profile Send private message Send e-mail
chandra
Beginner


Joined: 26 Sep 2003
Posts: 130
Topics: 36
PostPosted: Tue Oct 21, 2008 4:39 pm    Post subject: Reply with quote
Hi Diba and Alissa,

Thanks for your solution.

When I ran the Syncsort solution I got the following error

Code:

      TRAILER3=(1,80,
           TOT=(81,40,BI,BI,LENGTH=40),
                   *
           TOT=(121,40,BI,BI,LENGTH=40),
           TOT=(161,40,BI,BI,LENGTH=40),
           TOT=(201,40,BI,BI,LENGTH=40),
           TOT=(241,40,BI,BI,LENGTH=40),
           TOT=(281,40,BI,BI,LENGTH=40),
           TOT=(321,40,BI,BI,LENGTH=40),
           TOT=(361,40,BI,BI,LENGTH=40)))
 WER271A  OUTFIL STATEMENT  : NUMERIC FIELD ERROR
 WER275A  NO KEYWORDS FOUND ON CONTROL STATEMENT
 WER211B  SYNCSMF  CALLED BY SYNCSORT; RC=0000

_________________
Regards,
Chandra
Back to top
View user's profile Send private message
amargulies
Beginner


Joined: 10 Jan 2007
Posts: 123
Topics: 0
PostPosted: Wed Oct 22, 2008 3:37 pm    Post subject: Reply with quote
Ack. I should know better than to code in haste without testing. Try breaking down the TOT fields into chunks of 4.
Code:
TRAILER3=(1,80,
    TOT=(81,4,BI,BI,LENGTH=4),
    TOT=(85,4,BI,BI,LENGTH=4),
    TOT=(89,4,BI,BI,LENGTH=4),
    TOT=(93,4,BI,BI,LENGTH=4),
    TOT=(97,4,BI,BI,LENGTH=4),
    TOT=(101,4,BI,BI,LENGTH=4),
    TOT=(105,4,BI,BI,LENGTH=4),
    .
    .
    .
    .
    TOT=(377,4,BI,BI,LENGTH=4),
    TOT=(381,4,BI,BI,LENGTH=4),
    TOT=(385,4,BI,BI,LENGTH=4),
    TOT=(389,4,BI,BI,LENGTH=4).
    TOT=(393,4,BI,BI,LENGTH=4)))

Then let me know if you still have any problems.
_________________
Alissa Margulies
SyncSort Mainframe Product Services
201-930-8260
zos_tech@syncsort.com
Back to top
View user's profile Send private message Send e-mail
chandra
Beginner


Joined: 26 Sep 2003
Posts: 130
Topics: 36
PostPosted: Thu Oct 23, 2008 9:25 am    Post subject: Reply with quote
Hi Alissa,

Thanks for your reply

Still I am getting following error.

WER275A NO KEYWORDS FOUND ON CONTROL STATEMENT
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000

Could you please let me know what is the problem ?

Thank you,

Chandra
Back to top
View user's profile Send private message
amargulies
Beginner


Joined: 10 Jan 2007
Posts: 123
Topics: 0
PostPosted: Thu Oct 23, 2008 1:27 pm    Post subject: Reply with quote
chandra wrote:
Still I am getting following error.

WER275A NO KEYWORDS FOUND ON CONTROL STATEMENT
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000

What are you specifying as your SORT statement? Show me your actual SYSIN.
_________________
Alissa Margulies
SyncSort Mainframe Product Services
201-930-8260
zos_tech@syncsort.com
Back to top
View user's profile Send private message Send e-mail
chandra
Beginner


Joined: 26 Sep 2003
Posts: 130
Topics: 36
PostPosted: Thu Oct 23, 2008 7:28 pm    Post subject: Reply with quote
Hi Alissa,

Here is my SYSIN
Code:

//SYSIN   DD *
   SORT FIELDS=(1,39,CH,A),EQUALS
   OUTREC IFTHEN=(WHEN=INIT,
     OVERLAY=(40:SEQNUM,1,ZD,RESTART=(1,39))),
     IFTHEN=(WHEN=(40,1,CH,EQ,C'1'),
     BUILD=(1:1,80,320Z)),
     IFTHEN=(WHEN=(40,1,CH,EQ,C'2'),
     BUILD=(1:1,39,41Z,81:41,40,280Z)),
     IFTHEN=(WHEN=(40,1,CH,EQ,C'3'),
     BUILD=(1:1,39,81Z,121:41,40,240Z)),
     IFTHEN=(WHEN=(40,1,CH,EQ,C'4'),
     BUILD=(1:1,39,121Z,161:41,40,200Z)),
     IFTHEN=(WHEN=(40,1,CH,EQ,C'5'),
     BUILD=(1:1,39,161Z,201:41,40,160Z)),
     IFTHEN=(WHEN=(40,1,CH,EQ,C'6'),
     BUILD=(1:1,39,201Z,241:41,40,120Z)),
     IFTHEN=(WHEN=(40,1,CH,EQ,C'7'),
     BUILD=(1:1,39,241Z,281:41,40,80Z)),
     IFTHEN=(WHEN=(40,1,CH,EQ,C'8'),
     BUILD=(1:1,39,281Z,321:41,40,40Z)),
     IFTHEN=(WHEN=(40,1,CH,EQ,C'9'),
     BUILD=(1:1,39,321Z,361:41,40))
     OUTFIL NODETAIL,REMOVECC,SECTIONS=(1,39,
     TRAILER3=(1,80,
     TOT=(081,4,BI,BI,LENGTH=4),
     TOT=(085,4,BI,BI,LENGTH=4),
     TOT=(089,4,BI,BI,LENGTH=4),
     TOT=(093,4,BI,BI,LENGTH=4),
     TOT=(097,4,BI,BI,LENGTH=4),
     TOT=(101,4,BI,BI,LENGTH=4),
     TOT=(105,4,BI,BI,LENGTH=4),
     TOT=(109,4,BI,BI,LENGTH=4),
     TOT=(113,4,BI,BI,LENGTH=4),
     TOT=(117,4,BI,BI,LENGTH=4),
     TOT=(121,4,BI,BI,LENGTH=4),
     TOT=(125,4,BI,BI,LENGTH=4),
     TOT=(129,4,BI,BI,LENGTH=4),
     TOT=(133,4,BI,BI,LENGTH=4),
     TOT=(137,4,BI,BI,LENGTH=4),
     TOT=(141,4,BI,BI,LENGTH=4),
     TOT=(145,4,BI,BI,LENGTH=4),
     TOT=(149,4,BI,BI,LENGTH=4),
     TOT=(153,4,BI,BI,LENGTH=4),
     TOT=(157,4,BI,BI,LENGTH=4),
     TOT=(161,4,BI,BI,LENGTH=4),
     TOT=(165,4,BI,BI,LENGTH=4),
     TOT=(169,4,BI,BI,LENGTH=4),
     TOT=(173,4,BI,BI,LENGTH=4),
     TOT=(177,4,BI,BI,LENGTH=4),
     TOT=(181,4,BI,BI,LENGTH=4),
     TOT=(185,4,BI,BI,LENGTH=4),
     TOT=(189,4,BI,BI,LENGTH=4),
     TOT=(193,4,BI,BI,LENGTH=4),
     TOT=(197,4,BI,BI,LENGTH=4),
     TOT=(201,4,BI,BI,LENGTH=4),
     TOT=(205,4,BI,BI,LENGTH=4),
     TOT=(209,4,BI,BI,LENGTH=4),
     TOT=(213,4,BI,BI,LENGTH=4),
     TOT=(217,4,BI,BI,LENGTH=4),
     TOT=(221,4,BI,BI,LENGTH=4),
     TOT=(225,4,BI,BI,LENGTH=4),
     TOT=(229,4,BI,BI,LENGTH=4),
     TOT=(233,4,BI,BI,LENGTH=4),
     TOT=(237,4,BI,BI,LENGTH=4),
    TOT=(241,4,BI,BI,LENGTH=4),
    TOT=(245,4,BI,BI,LENGTH=4),
    TOT=(249,4,BI,BI,LENGTH=4),
    TOT=(253,4,BI,BI,LENGTH=4),
    TOT=(257,4,BI,BI,LENGTH=4),
    TOT=(261,4,BI,BI,LENGTH=4),
    TOT=(265,4,BI,BI,LENGTH=4),
    TOT=(269,4,BI,BI,LENGTH=4),
    TOT=(273,4,BI,BI,LENGTH=4),
    TOT=(277,4,BI,BI,LENGTH=4),
    TOT=(281,4,BI,BI,LENGTH=4),
    TOT=(285,4,BI,BI,LENGTH=4),
    TOT=(289,4,BI,BI,LENGTH=4),
    TOT=(293,4,BI,BI,LENGTH=4),
    TOT=(297,4,BI,BI,LENGTH=4),
    TOT=(301,4,BI,BI,LENGTH=4),
    TOT=(357,4,BI,BI,LENGTH=4),
    TOT=(361,4,BI,BI,LENGTH=4),
    TOT=(365,4,BI,BI,LENGTH=4),
    TOT=(369,4,BI,BI,LENGTH=4),
    TOT=(373,4,BI,BI,LENGTH=4),
    TOT=(377,4,BI,BI,LENGTH=4),
    TOT=(381,4,BI,BI,LENGTH=4),
    TOT=(385,4,BI,BI,LENGTH=4),
    TOT=(389,4,BI,BI,LENGTH=4),
    TOT=(393,4,BI,BI,LENGTH=4)))
/*

_________________
Regards,
Chandra
Back to top
View user's profile Send private message
amargulies
Beginner


Joined: 10 Jan 2007
Posts: 123
Topics: 0
PostPosted: Mon Oct 27, 2008 1:01 pm    Post subject: Reply with quote
Chandra,

By simply eye-balling it, I do not see anything in your coding that should cause the error. Please send me the complete and unedited job listing offline and I will review it in greater detail. You can email it to me as a text attachment to alissa.margulies@syncsort.com.
_________________
Alissa Margulies
SyncSort Mainframe Product Services
201-930-8260
zos_tech@syncsort.com
Back to top
View user's profile Send private message Send e-mail
chandra
Beginner


Joined: 26 Sep 2003
Posts: 130
Topics: 36
PostPosted: Mon Oct 27, 2008 3:14 pm    Post subject: Reply with quote
Hi Alissa,

I sent you an e-mail with full job
_________________
Regards,
Chandra
Back to top
View user's profile Send private message
amargulies
Beginner


Joined: 10 Jan 2007
Posts: 123
Topics: 0
PostPosted: Mon Oct 27, 2008 3:26 pm    Post subject: Reply with quote
Hi Chandra.

You resent only the JCL. I need the complete SYSOUT, including the SyncSort message output. Thanks.
_________________
Alissa Margulies
SyncSort Mainframe Product Services
201-930-8260
zos_tech@syncsort.com
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group