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
 

 

Multiple Header & Trailer - Summing up trailer

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
yuvrajdutta

New User


Joined: 13 Jul 2009
Posts: 40
Location: India

PostPosted: Fri May 24, 2013 10:41 pm    Post subject: Multiple Header & Trailer - Summing up trailer
Reply with quote

Hi there,

I am working on a SORT card and have come to a point where I would need some advise to proceed further.

Also I did some searching within the forum but could not find a topic addressing my query. Althouhg threre were quite a few with very close requirement.

Requirement:
- Eliminate records/header/trailer under header not equal to '01'
- Retain just the first header, if there are multiple header = '01' sets.
- Sum up position 15,5 and 20,6 in the trailer record for header = '01' and create a new trailer record.
- Final output should be sorted on (1,1,CH,A,2,10,CH,A)

Input file attributes: LRECL = 40 and FB


Jcl (with input file and attempted sort card is below):

Code:
//STEP1    EXEC PGM=SORT                                 
//SORTIN   DD *                                           
01(HEADEY)                   
1C(DETAILS OF 1ST FILE - REC1)
1D(DETAILS OF 1ST FILE - REC2)
1A(DETAILS OF 1ST FILE - REC3)
99XTRAILER    00003000410     
02(HEADEO)                   
11(DETAILS OF 2ND FILE - REC1)
11(DETAILS OF 2ND FILE - REC2)
99XTRAILER    00002000345     
02(HEADEO)                   
11(DETAILS OF 3RD FILE - REC1)
11(DETAILS OF 3RD FILE - REC2)
11(DETAILS OF 3RD FILE - REC3)
99XTRAILER    00002000345     
01(HEADEY)                   
1B(DETAILS OF 4TH FILE - REC1)
1E(DETAILS OF 4TH FILE - REC2)
1G(DETAILS OF 4TH FILE - REC3)
99XTRAILER    00003000510     
01(HEADEY)                   
1F(DETAILS OF 5TH FILE - REC1)
1H(DETAILS OF 5TH FILE - REC2)
1I(DETAILS OF 5TH FILE - REC3)
99XTRAILER    00003000710                           
/*                                                       
//SYSOUT   DD SYSOUT=*                                   
//SORTOUT  DD SYSOUT=*                                   
//SYSIN    DD *                                           
   SORT FIELDS=(1,1,CH,A,                                             
                2,10,CH,A)                                             
   INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,2,CH,EQ,C'01'),                   
                              END=(1,2,CH,EQ,C'99'),                   
                             PUSH=(41:ID=1)),                         
         IFTHEN=(WHEN=GROUP,BEGIN=(1,2,CH,EQ,C'99',AND,41,1,ZD,NE,1), 
                 PUSH=(42:15,11))                                     
                                                                       
   OUTREC IFTHEN=(WHEN=(1,2,CH,EQ,C'99',AND,41,1,CH,NE,C' '),         
   OVERLAY=(15:15,5,ZD,ADD,42,5,ZD,ZD,LENGTH=5,                       
            20:20,6,ZD,ADD,47,6,ZD,ZD,LENGTH=6))                     
                                                                       
   OUTFIL OMIT=((1,2,CH,EQ,C'01',AND,41,1,ZD,NE,1),OR,                 
                (1,2,CH,EQ,C'99',AND,41,1,ZD,NE,1),OR,                 
                (41,1,CH,EQ,C' ')),BUILD=(1,60)                       



Output:

Code:
01(HEADEY)                       
1A(DETAILS OF 1ST FILE - REC3)   
1B(DETAILS OF 4TH FILE - REC1)   
1C(DETAILS OF 1ST FILE - REC1)   
1D(DETAILS OF 1ST FILE - REC2)   
1E(DETAILS OF 4TH FILE - REC2)   
1F(DETAILS OF 5TH FILE - REC1)   
1G(DETAILS OF 4TH FILE - REC3)   
1H(DETAILS OF 5TH FILE - REC2)   
1I(DETAILS OF 5TH FILE - REC3)   
99XTRAILER    00003000410       


My Desired Output is :

Code:
01(HEADEY)                   
1A(DETAILS OF 1ST FILE - REC3)
1B(DETAILS OF 4TH FILE - REC1)
1C(DETAILS OF 1ST FILE - REC1)
1D(DETAILS OF 1ST FILE - REC2)
1E(DETAILS OF 4TH FILE - REC2)
1F(DETAILS OF 5TH FILE - REC1)
1G(DETAILS OF 4TH FILE - REC3)
1H(DETAILS OF 5TH FILE - REC2)
1I(DETAILS OF 5TH FILE - REC3)
99XTRAILER    00009001630


I tried SUM FEILDS within OUTREC in place of OVERLAY , but it seems it is not a valid statement.

Looking for some advise on this.
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7225

PostPosted: Sat May 25, 2013 12:21 am    Post subject: Reply to: Multiple Header & Trailer - Summing up traile
Reply with quote

Can you run this, and post the full sysout from the step, so we can determine your SORT level.

Code:
//S1 EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//SORTIN DD *
RECORD
/*
//SORTOUT DD DUMMY
//SYSIN   DD   *
  OPTION COPY
/*
Back to top
View user's profile Send private message
yuvrajdutta

New User


Joined: 13 Jul 2009
Posts: 40
Location: India

PostPosted: Sat May 25, 2013 12:37 am    Post subject:
Reply with quote

Hello Bill,

Thanks for the reply!

I should have included it in my intial post.

My site uses SYNCSORT FOR Z/OS 1.4.0.1R .

Let me know if you want to move my query to JCL section of this forum.

I posted it here as I found quite a few similar post have been handled here (with DFSORT) and might be good ot have it here.
Back to top
View user's profile Send private message
dick scherrer

Site Director


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

PostPosted: Sat May 25, 2013 1:07 am    Post subject:
Reply with quote

Hello,

Yup, your topic has been moved to the "JCL" part of the forum - where Syncsort topics are supported icon_cool.gif
Back to top
View user's profile Send private message
yuvrajdutta

New User


Joined: 13 Jul 2009
Posts: 40
Location: India

PostPosted: Mon May 27, 2013 6:31 pm    Post subject:
Reply with quote

Thanks Dick!

Looking forward to some opinions/suggestion on this!

icon_smile.gif
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2158
Location: @my desk

PostPosted: Mon May 27, 2013 10:27 pm    Post subject: Reply to: Multiple Header & Trailer - Summing up traile
Reply with quote

yuvrajdutta,

I have n't tested it. May be you could give this a try.

Code:
//SYSIN   DD *                                           
   INREC IFTHEN=(WHEN=INIT,
                 OVERLAY=(45:SEQNUM,8,ZD,11C'0')),                   
         IFTHEN=(WHEN=GROUP,BEGIN=(1,2,CH,EQ,C'01'),                   
                              END=(1,2,CH,EQ,C'99'),                   
                 PUSH=(41:ID=4)),                         
         IFTHEN=(WHEN=(1,2,CH,EQ,C'99',AND,41,4,CH,NE,C' '),
                 OVERLAY=(45:8C'9',15,11)) 
   SORT FIELDS=(1,11,CH,A,45,8,CH,A)
   SUM FIELDS=(53,5,ZD,58,6,ZD)
                                                                       
   OUTFIL IFOUTLEN=40,
          OMIT=((1,2,CH,EQ,C'01',AND,41,4,ZD,NE,1),OR,                 
                (41,1,CH,EQ,C' ')),
          IFTHEN=(WHEN=(1,2,CH,EQ,C'99'),
                 OVERLAY=(15:53,11))

I am away from work, else I could have run this once

EDIT: Corrected the typo.
Back to top
View user's profile Send private message
yuvrajdutta

New User


Joined: 13 Jul 2009
Posts: 40
Location: India

PostPosted: Mon May 27, 2013 11:27 pm    Post subject:
Reply with quote

Hello Arun,

The sort card was perfect and works for my query. Thanks !!

I just had to change the first OMIT cond a bit.
It would be "AND,41,<4>,ZD,NE,1 " as your id was of 4 byte long. I am sure that was just a typo.

In parallel I was working working on improving my approach as well and had come up with a better version what I posted initially, but it ain't perfect yet.

Code:
SORT FIELDS=(1,1,CH,A,                                     
             2,10,CH,A)                                   
INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,2,CH,EQ,C'01'),         
                           END=(1,2,CH,EQ,C'99'),         
                          PUSH=(41:ID=1)),                 
      IFTHEN=(WHEN=(1,2,CH,EQ,C'99',AND,41,1,CH,NE,C' '), 
       OVERLAY=(43:15,11))                                 
                                                           
OUTFIL OMIT=((1,2,CH,EQ,C'01',AND,41,1,ZD,NE,1),OR,       
             (1,2,CH,EQ,C'99'),OR,                         
            (41,1,CH,EQ,C' ')),                           
       TRAILER1=(1:'99XTRAILER    ',                       
       TOT=(43,11,UFF,EDIT=(TTTTTTTTTTT))),REMOVECC,       
       BUILD=(1,40)                                       



In the outfil statement I am trying to construct a new trailer using the data from the other "qualifying" trailers and then omit the individual "qualifying" trailer to retain just the new one created.

But, the problem is when OMIT is used along with TRAILER1, it seems OMIT happens first and as a result the new TOT function in the trailer is returning zero as the sum.

Output:

Code:
01(HEADEY)                     
1A(DETAILS OF 1ST FILE - REC3)
1B(DETAILS OF 4TH FILE - REC1)
1C(DETAILS OF 1ST FILE - REC1)
1D(DETAILS OF 1ST FILE - REC2)
1E(DETAILS OF 4TH FILE - REC2)
1F(DETAILS OF 5TH FILE - REC1)
1G(DETAILS OF 4TH FILE - REC3)
1H(DETAILS OF 5TH FILE - REC2)
1I(DETAILS OF 5TH FILE - REC3)
99XTRAILER    00000000000     


Any suggestion how to make this approach work. Just want to know for my learning.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2158
Location: @my desk

PostPosted: Tue May 28, 2013 1:17 am    Post subject:
Reply with quote

yuvrajdutta wrote:
Hello Arun,

The sort card was perfect and works for my query. Thanks !!
You're welcome. icon_smile.gif Good to hear it worked for you. I assumed that there may be duplicates on 1,11 for at least some detail records and had a separate counter to handle it.

I would like to appreciate your effort that you have tried something yourself. As you have already figured out, the OMIT happens first and you would not have those "qualifying" trailers to build a final TRAILER1.
Back to top
View user's profile Send private message
dick scherrer

Site Director


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

PostPosted: Tue May 28, 2013 7:25 pm    Post subject:
Reply with quote

Hi Arun,

Good to "see" you! Hopefully, you'll be seen often again . . . icon_cool.gif

d
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2158
Location: @my desk

PostPosted: Wed May 29, 2013 12:55 pm    Post subject:
Reply with quote

Hi dick,

Good to see you as well icon_smile.gif. Missed you all!..
Back to top
View user's profile Send private message
yuvrajdutta

New User


Joined: 13 Jul 2009
Posts: 40
Location: India

PostPosted: Thu Jun 13, 2013 5:46 am    Post subject:
Reply with quote

Hello Arun,

I encountered another scenario while working on creating this sort card and thought would discuss it here.

I had a slight change in requirement, where the count and hash total values of the trailer falls in line with the file key (1,11).

New Input file:

Code:
01(HEADEY)                   
1C(DETAILS OF 1ST FILE - REC1)
1D(DETAILS OF 1ST FILE - REC2)
1A(DETAILS OF 1ST FILE - REC3)
1K(DETAILS OF 1ST FILE - REC4)
99X00004000410               
02(HEADEO)                   
11(DETAILS OF 2ND FILE - REC1)
11(DETAILS OF 2ND FILE - REC2)
99X00002000345               
02(HEADEO)                   
11(DETAILS OF 3RD FILE - REC1)
11(DETAILS OF 3RD FILE - REC2)
11(DETAILS OF 3RD FILE - REC3)
99X00002000345               
01(HEADEY)                   
1B(DETAILS OF 4TH FILE - REC1)
1E(DETAILS OF 4TH FILE - REC2)
1G(DETAILS OF 4TH FILE - REC3)
99X00003000510               
01(HEADEY)                   
1F(DETAILS OF 5TH FILE - REC1)
1H(DETAILS OF 5TH FILE - REC2)
1I(DETAILS OF 5TH FILE - REC3)
99X00003000710               


In this case, if the the trailer record count (column 4 through 8) of the files
are different, the present sort card is unable to 'SUM' the fields as the key fields become different. Below is the output.

Output:

Code:
01(HEADEY)                       
1A(DETAILS OF 1ST FILE - REC3)   
1B(DETAILS OF 4TH FILE - REC1)   
1C(DETAILS OF 1ST FILE - REC1)   
1D(DETAILS OF 1ST FILE - REC2)   
1E(DETAILS OF 4TH FILE - REC2)   
1F(DETAILS OF 5TH FILE - REC1)   
1G(DETAILS OF 4TH FILE - REC3)   
1H(DETAILS OF 5TH FILE - REC2)   
1I(DETAILS OF 5TH FILE - REC3)   
1K(DETAILS OF 1ST FILE - REC4)   
99X00006001220                   
99X00004000410                   


To fix the issues I changed the sort card as below.

Code:
    INREC IFTHEN=(WHEN=INIT,                                   
                  OVERLAY=(45:SEQNUM,8,ZD,11C'0',64:1,11)),   
          IFTHEN=(WHEN=GROUP,BEGIN=(1,2,CH,EQ,C'01'),         
                               END=(1,2,CH,EQ,C'99'),         
                  PUSH=(41:ID=4)),                             
          IFTHEN=(WHEN=(1,2,CH,EQ,C'99',AND,41,4,CH,NE,C' '), 
                  OVERLAY=(45:8C'9',4,11,64:11C'9'))           
    SORT FIELDS=(64,11,CH,A,45,8,CH,A)                         
    SUM FIELDS=(53,5,ZD,58,6,ZD)                               
                                                               
    OUTFIL IFOUTLEN=40,                                       
           OMIT=((1,2,CH,EQ,C'01',AND,41,4,ZD,NE,1),OR,       
                 (41,1,CH,EQ,C' ')),                           
           IFTHEN=(WHEN=(1,2,CH,EQ,C'99'),                     
                  OVERLAY=(4:53,11))                           


I am copying col 1 - 11 at the end of the file at position 64, then making this column identical (11C'9') for the trailer records to SUM up on the file key, which sitting at the end of the file after modification through INREC statement.

Output comes out as desired:

Code:
01(HEADEY)                       
1A(DETAILS OF 1ST FILE - REC3)   
1B(DETAILS OF 4TH FILE - REC1)   
1C(DETAILS OF 1ST FILE - REC1)   
1D(DETAILS OF 1ST FILE - REC2)   
1E(DETAILS OF 4TH FILE - REC2)   
1F(DETAILS OF 5TH FILE - REC1)   
1G(DETAILS OF 4TH FILE - REC3)   
1H(DETAILS OF 5TH FILE - REC2)   
1I(DETAILS OF 5TH FILE - REC3)   
1K(DETAILS OF 1ST FILE - REC4)   
99X00010001630



But, in doing so I end up coding file key(s) which are beyond the file LRECL of 40. From the perspective of achieving the end result the solution is fine, however I was just wondering if there is a workaround to still include the file key 1,11 for sorting in the changed scenario.

Looking forward for your suggestion!
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7225

PostPosted: Thu Jun 13, 2013 12:56 pm    Post subject: Reply to: Multiple Header & Trailer - Summing up traile
Reply with quote

My choice would be to have a copy of he field which is to be SUMmed at the end of the record, and SORT with your natural key.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2158
Location: @my desk

PostPosted: Thu Jun 13, 2013 5:03 pm    Post subject:
Reply with quote

Yuvraj,

You can OVERLAY spaces to the amount after copying it as shown below to 'force' the trailer keys to be same.
Code:
  INREC IFTHEN=(WHEN=INIT,                                 
                OVERLAY=(45:SEQNUM,8,ZD,11C'0')),         
        IFTHEN=(WHEN=GROUP,BEGIN=(1,2,CH,EQ,C'01'),       
                             END=(1,2,CH,EQ,C'99'),       
                PUSH=(41:ID=4)),                           
        IFTHEN=(WHEN=(1,2,CH,EQ,C'99',AND,41,4,CH,NE,C' '),
                OVERLAY=(45:8C'9',4,11,4:11X))             
  SORT FIELDS=(1,11,CH,A,45,8,CH,A)                       
  SUM FIELDS=(53,5,ZD,58,6,ZD)                             
  OUTFIL IFOUTLEN=40,                                     
         OMIT=((1,2,CH,EQ,C'01',AND,41,4,ZD,NE,1),OR,     
                  (41,1,CH,EQ,C' ')),                     
            IFTHEN=(WHEN=(1,2,CH,EQ,C'99'),               
                   OVERLAY=(04:53,11))                     
Back to top
View user's profile Send private message
yuvrajdutta

New User


Joined: 13 Jul 2009
Posts: 40
Location: India

PostPosted: Thu Jun 13, 2013 6:18 pm    Post subject:
Reply with quote

Ah! yes that is a neat way.

Thanks Arun and Bill !
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2158
Location: @my desk

PostPosted: Thu Jun 13, 2013 6:20 pm    Post subject:
Reply with quote

You're welcome icon_smile.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 -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Converting multiple VB files to FB fi... Viswanath Reddy JCL & VSAM 6 Mon Aug 08, 2016 11:49 pm
No new posts Execute multiple DB2 Load commands in... faizm DB2 4 Wed Aug 03, 2016 12:53 pm
This topic is locked: you cannot edit posts or make replies. JCL MULTIPLE STEP EXECUTION QUERY Susanta JCL & VSAM 18 Sat Jul 30, 2016 1:17 pm
No new posts Multiple parameters Csongor All Other Mainframe Topics 16 Thu Jun 09, 2016 7:54 pm


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