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
 

 

Add Columns in Row and Sum them up on a Key

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

New User


Joined: 17 Feb 2007
Posts: 47
Location: USA

PostPosted: Tue Jun 09, 2009 8:12 pm    Post subject: Add Columns in Row and Sum them up on a Key
Reply with quote

I got 2 requirements to be done on a file with Length 106 FB.

1. Add 4 columns in a row and write it in fifth column. All these 5 fields are PIC S9(9)V99 (Zoned Decimal of length 11).

Column 1 is at position 52 (length 11), Column 2 is at position 63 (length 11), Column 3 is at position 74 (length 11), Column 4 is at position 85 (length 11). The fifth column which is sum of column 1 + column 2 + column 3 + column 4 is at position 96 (length 11)

So, how do I add these 4 columns and write it in fifth column?

2. The second requirement is, inside the same file there is a Key at postion 43 with PIC X(5). Example Input file Keys at position 43 are as below.


00010
00010
00010
00033
00033
00053
00053
00053


I need to sum up "same columns" as in Step 1 above "based on Key" and write additional "New Line" for each Key Total. The output should come as

00010
00010
00010
00010 ---> This line contains sum of all above 00010 Key (Columns 1 through 5 see step1). The other remaining fields (1 thru 42 etc) can be spaces/zeros.
00033
00033
00033 ---> This line contains sum of all above 00033 Key (Columns 1 through 5 see step1). The other remaining fields (1 thru 42 etc) can be spaces/zeros.
00053
00053
00053
00053 ---> This line contains sum of all above 00053 Key (Columns 1 through 5 see step1). The other remaining fields (1 thru 42 etc) can be spaces/zeros.


Please let me know if we can accomplish this using DFSORT? I do not care if I need to do this in multiple steps inside same JCL.
Back to top
View user's profile Send private message

Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Tue Jun 09, 2009 8:52 pm    Post subject: Reply to: Add Columns in Row and Sum them up on a Key
Reply with quote

Ajayreddy,

Use the following DFSORT control cards which will give you the desired results

Code:

//SYSIN    DD *
  SORT FIELDS=(43,5,CH,A)                                 
  OUTREC OVERLAY=(96:52,11,ZD,ADD,63,11,ZD,ADD,           
                     74,11,ZD,ADD,85,11,ZD,ZD,LENGTH=11)   
                                                           
  OUTFIL REMOVECC,                                         
  SECTIONS=(43,5,                                         
  TRAILER3=(43:43,5,                                       
            52:TOT=(52,11,ZD,ZD,LENGTH=11),               
               TOT=(63,11,ZD,ZD,LENGTH=11),               
               TOT=(74,11,ZD,ZD,LENGTH=11),               
               TOT=(85,11,ZD,ZD,LENGTH=11),               
               TOT=(96,11,ZD,ZD,LENGTH=11)))               
/*
Back to top
View user's profile Send private message
AJAYREDDY

New User


Joined: 17 Feb 2007
Posts: 47
Location: USA

PostPosted: Thu Jun 11, 2009 7:37 pm    Post subject: Add Columns in Row and Sum them up on a Key
Reply with quote

Skolusu,

Thank you very much. The code worked well. icon_biggrin.gif I am trying to understand the Sort parameters below. Can you please let me know the answers.

1. What does TRAILER3 means? Do we have something like TRAILER1 and TRAILER2? Or is this just a SORT keyword which does not matter with the suffix number?

2. You did not mentioned something like 63:TOT=(63,11,ZD,ZD,LENGTH=11) as you mentioned with 52:TOT=(52,11,ZD,ZD,LENGTH=11) but still the code worked. How does it know to put the SUM under position 63 , under 74 etc and so on ? Does the LENGTH=11 will automatically takes care of?

TRAILER3=(43:43,5,
52:TOT=(52,11,ZD,ZD,LENGTH=11),
TOT=(63,11,ZD,ZD,LENGTH=11),
TOT=(74,11,ZD,ZD,LENGTH=11),
TOT=(85,11,ZD,ZD,LENGTH=11),
TOT=(96,11,ZD,ZD,LENGTH=11)))

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

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Thu Jun 11, 2009 9:31 pm    Post subject: Reply to: Add Columns in Row and Sum them up on a Key
Reply with quote

Ajayreddy,

1. What does TRAILER3 means? Do we have something like TRAILER1 and TRAILER2? Or is this just a SORT keyword which does not matter with the suffix number?

Trailer3 Specifies the section trailer to be used with the associated section break field for the reports produced for this OUTFIL group. The section trailer appears after the last data record of each section. We do have trailer1 and trailer2 too.

Trailer1 = You can use this to print reports at the end

Trailer2 = page wise report

Trailer3 = key wise report

2. You did not mentioned something like 63:TOT=(63,11,ZD,ZD,LENGTH=11) as you mentioned with 52:TOT=(52,11,ZD,ZD,LENGTH=11) but still the code worked. How does it know to put the SUM under position 63 , under 74 etc and so on ? Does the LENGTH=11 will automatically takes care of?

YES. Length will automatically take care.

If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:

www.ibm.com/servers/storage/support/software/sort/mvs/srtmpub.html
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 unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
No new posts Combine columns without breaking words Indrajit_57 SYNCSORT 1 Sat Mar 19, 2016 9:33 am
No new posts 3 columns update Seppe DB2 2 Mon Jun 22, 2015 6:43 pm


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