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

Can i do this with dfsort?
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
kushkush

New User


Joined: 30 Jun 2005
Posts: 37
Location: Germany
PostPosted: Tue Sep 25, 2012 7:27 pm
 Reply with quote
I have a big file with Banknumber, branches and transactions.
The file is FB80. Position 1 Banknumber, position 19 Branchnumber. There is transaction data from 25 to 80. But i am only interested in counting.
Banks are from 001 to 260. Branches can be from 1 to 250. I need to get an output like:

Banknumber 001 : Total 6
Branch 1 : Total 3
Branch 2 : Total 1
Branch 3 : Total 1
Branch 151 : Total 1
Banknumber 002 : Total 3
Branch 1 : Total 1
Branch 2 : Total 1
Branch 151 : Total 1
Banknumber 003 : Total 2
Branch 1 : Total 1
Branch 15 : Total 1

Means count the banks, count trxs on branches and total, subtotal.
Quote:

001 1
002 2
002 1
001 1
001 2
005 1
001 1
005 2
001 3
005 4
005 4
003 15
001 151
002 151
003 1
260 4
260 1
260 1
Back to top
View user's profile Send private message
kushkush

New User


Joined: 30 Jun 2005
Posts: 37
Location: Germany
PostPosted: Tue Sep 25, 2012 7:31 pm
 Reply with quote
Code:

001               1   
002               2   
002               1   
001               1   
001               2   
005               1   
001               1   
005               2   
001               3   
005               4   
005               4   
003               15 
001               151
002               151
003               1   
260               4   
260               1   
260               1   
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Tue Sep 25, 2012 11:34 pm
 Reply with quote
kushkush,

Do you really need the total of ALL branches on the header record of each bank? or can you have it as a trailer?

Here is a DFSORT JCL which will give you the report of what you wanted and the total of all branches is at the bottom.
Code:

//STEP0100 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//SORTIN   DD *                                                     
001               1                                                 
002               2                                                 
002               1                                                 
001               1                                                 
001               2                                                 
005               1                                                 
001               1                                                 
005               2                                                 
001               3                                                 
005               4                                                 
005               4                                                 
003               15                                               
001               151                                               
002               151                                               
003               1                                                 
260               4                                                 
260               1                                                 
260               1                                                 
//SORTOUT  DD SYSOUT=*                                             
//SYSIN    DD *                                                     
  INREC BUILD=(1,24,C'00000001')                                   
  SORT FIELDS=(1,18,CH,A,19,6,UFF,A)                               
  SUM FIELDS=(25,8,ZD)                                             
  OUTREC BUILD=(C'BRANCH ',19,6,2X,                                 
                C'TOTAL : ',25,8,ZD,M10,LENGTH=8,81:1,18)           
  OUTFIL REMOVECC,BUILD=(1,80),                                     
  SECTIONS=(81,18,                                                 
  HEADER3=('BANK NUMBER : ',81,18,/),                               
  TRAILER3=(16:7'=',/,16:'TOTAL : ',TOT=(24,8,UFF,M10,LENGTH=8),/))
//*


The output from this job is
Code:

BANK NUMBER : 001                     
                                     
BRANCH 1       TOTAL :        3       
BRANCH 2       TOTAL :        1       
BRANCH 3       TOTAL :        1       
BRANCH 151     TOTAL :        1       
               =======               
               TOTAL :        6       
                                     
BANK NUMBER : 002                     
                                     
BRANCH 1       TOTAL :        1       
BRANCH 2       TOTAL :        1       
BRANCH 151     TOTAL :        1       
               =======               
               TOTAL :        3       
                                     
BANK NUMBER : 003                     
                                     
BRANCH 1       TOTAL :        1       
BRANCH 15      TOTAL :        1       
               =======               
               TOTAL :        2       
                                     
BANK NUMBER : 005                     
                                     
BRANCH 1       TOTAL :        1       
BRANCH 2       TOTAL :        1       
BRANCH 4       TOTAL :        2       
               =======               
               TOTAL :        4       
                                     
BANK NUMBER : 260                     
                                     
BRANCH 1       TOTAL :        2       
BRANCH 4       TOTAL :        1       
               =======               
               TOTAL :        3       
Back to top
View user's profile Send private message
kushkush

New User


Joined: 30 Jun 2005
Posts: 37
Location: Germany
PostPosted: Wed Sep 26, 2012 11:31 am
 Reply with quote
Thanks a lot Skolusu!
It works beautifully.
Back to top
View user's profile Send private message
jose.jeyan

New User


Joined: 28 Jul 2006
Posts: 60
Location: Mumbai
PostPosted: Wed Mar 12, 2014 9:12 pm
 Reply with quote
Hi Kolusu,

I am working on a similar report based on ur example. I am just trying to explore how this works..

Can you please explain how this works...

C'TOTAL : ',25,8,ZD,M10,LENGTH=8,81:1,18)

SECTIONS=(81,18,
HEADER3=('BANK NUMBER : ',81,18,/),
TRAILER3=(16:7'=',/,16:'TOTAL : ',TOT=(24,8,UFF,M10,LENGTH=8),/))
Back to top
View user's profile Send private message
jose.jeyan

New User


Joined: 28 Jul 2006
Posts: 60
Location: Mumbai
PostPosted: Wed Mar 12, 2014 9:37 pm
 Reply with quote
I tried the same but getting error.

INREC BUILD=(1,4,C'0001')
SORT FIELDS=(194,3,CH,A,1,4,ZD,A)
SUM FIELDS=(241,8,ZD)
OUTREC BUILD=(C'BRANCH ',1,4,2X,
C'TOTAL : ',241,8,ZD,M10,LENGTH=8,81:194,3)
OUTFIL REMOVECC,BUILD=(1,80),
SECTIONS=(81,3,
HEADER3=('BANK NUMBER : ',81,3,/),
TRAILER3=(16:7'=',/,16:'TOTAL : ',TOT=(24,8,UFF,M10,LENGTH=8),/))

RECORD TYPE IS F - DATA STARTS IN POSITION 1
END OF FIELD BEYOND MAXIMUM RECORD LENGTH

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

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Wed Mar 12, 2014 10:24 pm
 Reply with quote
jose.jeyan,

Please do NOT send private messages seeking help. Please post in the forum itself. Thank you.

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/support/docview.wss?rs=114&uid=isg3T7000080

Coming to your issue, it is not clear as to what you are trying to do. Please answer the following questions.

1. What is the LRECL and RECFM of the input file?
2. What is the key field? 1, 4 ? or 194 ,3?
3. What do you have at position 241 for 8 bytes?
4. Show us a sample input (just show the contents of 1,4, 194,4 and 241,8) and desired output.
Back to top
View user's profile Send private message
jose.jeyan

New User


Joined: 28 Jul 2006
Posts: 60
Location: Mumbai
PostPosted: Wed Mar 12, 2014 11:03 pm
 Reply with quote
1. Lrecl = 80 & Recfm = FB

2. Input file is 300 lrecl & FB. Has records from 1-240.
Need to create reports based on 1,4 & 194,3 .
Say 194,3 is Branch Id
1,4 is Branch Num
3. 240 -300 is blank.

Sample File
1 - 4|5 - 193|194|195 - 230|240 - 300 --> File Cols
1234|000000123408AYN|AA1|00001MJ00|
2343|000000324408BYB|AA2|00001NY00|
3431|000000123408AYN|AA1|00001MJ00|
2333|000000324408BYB|AA2|00001NY00|

Output Same as your example above..

BANK ID : AA1

BRANCH 1234 TOTAL : 1
BRANCH 3431 TOTAL : 1
=======
TOTAL : 2

BANK NUMBER : AA2

BRANCH 2343 TOTAL : 1
BRANCH 2333 TOTAL : 1
=======
TOTAL : 2
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Thu Mar 13, 2014 12:47 am
 Reply with quote
Jose.Jeyan,

Since you had made an effort to search and pick the right topic , I am going to explain the job in detail for your understanding. Use the following DFSORT JCL which will give you the desired results.

Code:

//STEP0100 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//SORTIN   DD DISP=SHR,DSN=Your Input FB file
//SORTOUT  DD SYSOUT=*                                             
//SYSIN    DD *                                                   
  INREC BUILD=(001,4,            $ BRANCH NUM                     
               194,3,            $ BRANCH ID                       
               C'00000001')      $ CONSTANT '1'                   
                                                                   
                                                                   
  SORT FIELDS=(5,3,CH,A,         $ BRANCH ID                       
               1,4,CH,A)         $ BRANCH NUM                     
                                                                   
  SUM FIELDS=(8,8,ZD)            $ SUM ON CONTANT                 
                                                                   
  OUTREC BUILD=(C'BRANCH ',      $ CONSTANT BRANCH                 
                1,4,             $ BRANCH NUM                     
                5X,              $ 5 SPACES                       
                C'TOTAL :   ',   $ CONSTANT TOTAL                 
                8,8,ZD,          $ TOTAL OF CONSTANT               
                M10,LENGTH=8,    $ LEADING ZERO SUPRESS EDIT MASK 
                81:5,3)          $ BRANCH ID AT POSITION 81       
                                                                   
  OUTFIL REMOVECC,               $ SUPRESS CARRIAGE CONTROL CHAR   
  SECTIONS=(81,3,                $ CONTROL BREAK ON BRANCH ID     
  HEADER3=('BANK ID : ',         $ CONSTANT BANK ID               
           81,3,                 $ BRANCH ID                       
           /),                   $ WRITE ANOTHER EMPTY LINE       
  TRAILER3=(17:18'=',            $ CONSTANT '=' AT POS 17         
            /,                   $ WRITE ANOTHER EMPTY LINE       
            17:'TOTAL :   ',     $ CONSTANT TOTAL AT POS 17       
            TOT=(27,8,UFF,       $ TOTAL BY BRANCH ID             
                 M10,LENGTH=8),  $ EDIT MASK FOR TOTAL             
            /)),                 $ WRITE ANOTHER EMPTY LINE       
  TRAILER1=('TOTAL OF ',         $ CONSTANT TOTAL OF               
            'ALL BRANCHES : ',   $ CONSTANT ALL BRANCHES           
            TOT=(27,8,UFF,       $ TOTAL BY ALL BRANCH ID'S       
                 M10,LENGTH=10)) $ EDIT MASK FOR TOTAL             
//*


will produce

Code:

BANK ID : AA1                     
                                   
BRANCH 1234     TOTAL :          1
BRANCH 3431     TOTAL :          1
                ==================
                TOTAL :          2
                                   
BANK ID : AA2                     
                                   
BRANCH 2333     TOTAL :          1
BRANCH 2343     TOTAL :          1
                ==================
                TOTAL :          2
                                   
TOTAL OF ALL BRANCHES :          4


I had comments explaining every step, and to understand it fully you need this diagram

publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA60/1.5.4?

See how my sysin control cards align with the flow shown in the diagram.

Once you have a theortical understanding of the job, you need to run the job multiple times to understand what each statement does.

First use these sysin cards

Code:

//SYSIN    DD *                                 
  INREC BUILD=(001,4,            $ BRANCH NUM   
               194,3,            $ BRANCH ID     
               C'00000001')      $ CONSTANT '1' 
                                                 
  OPTION COPY                                   
//*


Now go look at the output in SORTOUT DD and see how INREC has picked the values and created a 15 byte record with only values we need.

Next run the job with these control cards

Code:

//SYSIN    DD *                                   
  INREC BUILD=(001,4,            $ BRANCH NUM     
               194,3,            $ BRANCH ID     
               C'00000001')      $ CONSTANT '1'   
                                                 
  SORT FIELDS=(5,3,CH,A,         $ BRANCH ID     
               1,4,CH,A)         $ BRANCH NUM     
                                                 


Now go look at the output in SORTOUT DD and see how we SORTED the data.

So repeat this process by adding 1 statement at a time and viewing the output so that you understand how the final report is generated.
Back to top
View user's profile Send private message
jose.jeyan

New User


Joined: 28 Jul 2006
Posts: 60
Location: Mumbai
PostPosted: Thu Mar 13, 2014 1:15 am
 Reply with quote
Thanks a lot kolusu for the detail information.

I shall try this.

Regards,
Jose
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Thu Mar 13, 2014 1:52 am
 Reply with quote
jose.jeyan wrote:
Thanks a lot kolusu for the detail information.

I shall try this.

Regards,
Jose


Jose.Jeyan,

Just so you know, I deliberately left out a parm in my control cards and once you understand the job you can add it.
Back to top
View user's profile Send private message
jose.jeyan

New User


Joined: 28 Jul 2006
Posts: 60
Location: Mumbai
PostPosted: Thu Mar 13, 2014 8:18 pm
 Reply with quote
Hi Kolusu,

Is that " OUTFIL REMOVECC,BUILD=(1,80)," you delibratly missed.

Whats the significance of having Header1,Header3 .Checked DFsort guide but coudlnt get much details on the difference between Header1,Header2,Header3. I am not sure i missing something.

Thanks again on your guidance and support.

Regards,
Tony J.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Thu Mar 13, 2014 10:06 pm
 Reply with quote
jose.jeyan wrote:
Hi Kolusu,

Is that " OUTFIL REMOVECC,BUILD=(1,80)," you delibratly missed.


Yes. I did not have the BUILD parm in there and if you looked at the report it would have shown you the residual of the key in position 81.

jose.jeyan wrote:

Whats the significance of having Header1,Header3 .Checked DFsort guide but coudlnt get much details on the difference between Header1,Header2,Header3. I am not sure i missing something.

Thanks again on your guidance and support.

Regards,
Tony J.


Header1 = Appears only on first page of the report
Header2 = Appears on Every page of the report
Header3 = Appears before the first data record of each section(aka key break) in your case it is 81,3

The same applies for Trailer1,Trailer2, Trailer3 but it is at the bottom of the page.

Run this jcl and see how the headers are written. The default for LINES for a page is 60 but we can override that. run the following JCL with different control cards shown below and verify the output after every run and you will understand how Header1/2/3 and trailer1/2/3 works

Code:

//STEP0100 EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*   
//SORTIN   DD *         
REC 01    000001         
REC 02    000001         
REC 03    000001         
REC 04    000001         
REC 05    000001         
REC 06    000002         
REC 07    000002         
REC 08    000002         
REC 09    000002         
REC 10    000002         
REC 11    000003         
REC 12    000003         
REC 13    000004         
REC 14    000004         
REC 15    000004         
REC 16    000004         
//SORTOUT  DD SYSOUT=*   



For header1 use the following control cards

Code:

//SYSIN    DD *                               
  OPTION COPY                                 
  OUTFIL REMOVECC,LINES=4,                     
  HEADER1=(15:'EXAMPLE OF HOW HEADER1 WORKS') 
//*


For header2 use the following control cards

Code:

//SYSIN    DD *                                 
  OPTION COPY                                   
  OUTFIL REMOVECC,LINES=4,                       
  HEADER2=(15:'EXAMPLE OF HOW HEADER2 WORKS')   
//*   


For header3 use the following control cards

Code:

  OUTFIL REMOVECC,                               
  SECTIONS=(11,6,                               
  HEADER3=(15:'EXAMPLE OF HOW HEADER3 WORKS'))   
//*
Back to top
View user's profile Send private message
jose.jeyan

New User


Joined: 28 Jul 2006
Posts: 60
Location: Mumbai
PostPosted: Fri Mar 14, 2014 1:20 am
 Reply with quote
Thanks Kolusu..

Thanks for your time 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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts Modifying Date Format Using DFSORT DFSORT/ICETOOL 9
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts Calling DFSORT from Cobol, using OUTF... DFSORT/ICETOOL 5
No new posts DFsort help with SUM() DFSORT/ICETOOL 12
No new posts DFSORT - VB file RDW getting overridden DFSORT/ICETOOL 3
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us