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 

Report Printing
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
sakreg
Beginner


Joined: 28 Feb 2005
Posts: 80
Topics: 26
PostPosted: Wed Mar 02, 2005 7:24 am    Post subject: Report Printing Reply with quote
Hi,

Is it possible to produce formated reports with JCL using any of the utilities:

I need to do a read on DB2 and sum up some amounts and print that in the report.

Report Name 2 should occur in a New page and after decimal there should be 4 digits.

For Report Name 2 under Type Of Transaction there are about 24 different Transaction Types and I should print only those that have a dollar value of more than 0.

Code:

<Repor Name 1>
<Run date and Time>

Earned    : $ <value>
Reserved : $ <value>
Used       : $ <value>
Expired   : $ <value>

Total      : $ <value>

<Report Name 2>
<Run date and Time>

Type Of Transaction   Amount

Transaction 1      $100.0000
     .            .
     .            .
     .            .
     .            .
     .            .
     .            .
     .            .
     .            .
     .            .
     .            .
     .            .
Transaction 24      $50.000


Can any of you give me how I can proceed using JCL and what utilities I need to have to do this
Back to top
View user's profile Send private message
Brian
Beginner


Joined: 12 Aug 2003
Posts: 95
Topics: 6
PostPosted: Wed Mar 02, 2005 8:02 am    Post subject: Reply with quote
Sakreg,

Easytrieve is what you should be looking it. It has wonderful reporting capabilities.

Cheers
Brian
Back to top
View user's profile Send private message
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Wed Mar 02, 2005 11:57 am    Post subject: Reply with quote
DFSORT has extensive reporting capabilities. If you want more specific help with your reports, you need to show an example of your input records (with actual values for relevent fields), and exactly what you want the output report to look like with actual values.
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
sakreg
Beginner


Joined: 28 Feb 2005
Posts: 80
Topics: 26
PostPosted: Wed Mar 02, 2005 11:26 pm    Post subject: Reply with quote
Thanks guys for the response.

I choose DFSORT.

Frank,

I have the following values in a table1:
Code:

Code      Earned      Reserved     Expired    Used
F1        100         100          50         0
F2        95.1567     23.4567      1.9999     1.2345
C1        0           0            0          9.0000

Now my First Output report should look like:
Code:

<Repor Name 1>
<Run date and Time>

Earned    : $ 195.1567
Reserved  : $ 123.4567
Used      : $ 10.2345
Expired   : $ 51.9999
            ------------
Total       $ 380.8478
            ------------

I have the following values in Table 2:
Code:

Code Amount
C1    1.3451
C2    2.2222
C3    3.1234
C4    0

Second Output should look like and this should start in the Next page and if there are more than one page each page should have the header and the page number:
Code:

<Report Name 2>                                      Page n
<Run date and Time>

Type of Transaction  Amount
C1                   1.3451
C2                   2.2222
C3                   3.1234


First Report will always fit in a page. But the Second report will follow to the next page. So for the second report, I have to print page numbers also.

Frank, are the above details OK? If not please let me know.

Thank You.
Back to top
View user's profile Send private message
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Thu Mar 03, 2005 11:34 am    Post subject: Reply with quote
Some questions:

1) You refer to table1 and table2. Are these actually two input files with the records shown? What is the RECFM and LRECL of the input files?
2) You show amounts like 100 and 95.1567. Are these the actual character strings in the records? Or is 100 really 100.0000? Or are the actual fields in some numeric format (e.g. PD, BI)? If they are in a numeric format, what is the format? If you don't know, just show what the 100 and 95.1567 values look like in hex. Also, what is the starting position and length of each field?
3) In the second report, the input file has C4, but the output file doesn't. Do you want to omit records with an amount of 0?
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
NASCAR9
Intermediate


Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
PostPosted: Thu Mar 03, 2005 1:35 pm    Post subject: Reply with quote
Am I missing something or are you going to read DB2 Tables with a sort? Is so, I'm interested.
_________________
Thanks,
NASCAR9
Back to top
View user's profile Send private message
sakreg
Beginner


Joined: 28 Feb 2005
Posts: 80
Topics: 26
PostPosted: Fri Mar 04, 2005 12:41 am    Post subject: Reply with quote
1) Table1 and Table2 are DB2 tables.

2) SUM function of DB2 is used to arrive at the values for Earned, Reserved, Expired and Used amount. and the values for this are obtained by summing along all the column values for each of them (Earned, Reserved, Expired and Used) for Report1 and

3)For Report2 we just print all the values that have NON-ZERO dollar amount in the respective Reports with the required header.
Back to top
View user's profile Send private message
NASCAR9
Intermediate


Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
PostPosted: Fri Mar 04, 2005 10:56 am    Post subject: Reply with quote
sakreg, I'm not sure but I don't think you can read a DB2 table with Sort of any flavor. You could run a SPUFI Query and output the results to a file. Then use Sort and create your report. If this is a one shot request, this may be the best approach. If it is a frequently run job, you will need to write a program.
BTW - I do many requests this way, I run a SPUFI query to get my desired rows then use sort to create an Excel friendly file and FTP it to our LAN.
_________________
Thanks,
NASCAR9
Back to top
View user's profile Send private message
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Fri Mar 04, 2005 11:18 am    Post subject: Reply with quote
sakreq,

DFSORT can only read input files - it can't read DB2 tables directly. So unless you can produce files from the DB2 tables, I can't help you. If you can produce files, then you should format the records so that each field is in a "normalized" form, for example:

Code:

F1        100.0000    100.0000      50.0000      0.0000
F2         95.1567     23.4567       1.9999      1.2345
C1          0.0000      0.0000       0.0000      9.0000


Can you create a file like that? If so, I can show you how to use DFSORT to get the reports you want.
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Dummy
Beginner


Joined: 03 Jan 2003
Posts: 8
Topics: 1
PostPosted: Fri Mar 04, 2005 1:00 pm    Post subject: Reply with quote
If you have Synsort you can access DB2 Tables.
Try this, if it does not work try talking talking to your system programmers in your shop.

Code:

//SQLINSRT   EXEC  PGM=SORT,
//  PARM='DB2=SUBS'                                       
//SORTDBIN  DD *                                         
SELECT MYCOLUMN     FROM   USER.MYTABLE   WHERE ID = 1122
//SORTOUT   DD SYSOUT=*                                   
//SYSOUT    DD SYSOUT=*                                   
//SYSPRINT  DD SYSOUT=*                                   
//SYSIN     DD *                                         
 SORT FIELDS=COPY                                         
/*
Back to top
View user's profile Send private message
sakreg
Beginner


Joined: 28 Feb 2005
Posts: 80
Topics: 26
PostPosted: Mon Mar 07, 2005 12:37 am    Post subject: Reply with quote
Thanks Dummy,

Frank,

I can create a file and write the DB2 data to the 2 files. File1 and File2
Code:

File1 Contents will look like below:
111111111222222222333333333444444444

For Report 1, all the 1's, 2's, 3's and 4's (of length 9 integer) will go under Earned, Reserved, Used and Expired respectively.
Code:

<Repor Name 1>
<Run date and Time>

Earned    : $ 111111111
Reserved  : $ 222222222
Used      : $ 333333333
Expired   : $ 444444444
            ------------
Total       $ 1111111110
            ------------

Code:

File2 contents will look like:

ABCD1111
EFGH2222
IJKL3333
MNOP4444
ABCD1000
EFGH2000
IJKL3000
MNOP4000


Code:

<Report Name 2>                                      Page n
<Run date and Time>

Type of Transaction  Amount
ABCD                  2111
EFGH                  4222
IJKL                  6333
MNOP                  8444


For Report 2, if in the Input file, the first 4 characters are equal, then while writing to the file, they are summed up and produced as a single row for that particular transaction type. Hope this is clear.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
PostPosted: Mon Mar 07, 2005 9:42 am    Post subject: Reply with quote
Sakreg,

Try the following JCL. I assumed that your file1 is of 16 bytes in length and is of FB recfm.

An S9(09) comp field occupies 4 bytes and you have 4 such fields, so the total would be 16 bytes.

Report 1 JCL:

Code:

//STEP0100 EXEC PGM=SORT                                         
//SYSOUT   DD SYSOUT=*                                           
//SORTIN   DD DSN=YOUR INPUT FILE1,
//            DISP=SHR               
//SORTOUT  DD SYSOUT=*                                           
//SYSIN    DD *                                                 
  SORT FIELDS=COPY                                               
  OUTREC FIELDS=(01,16,                                           
                 01,4,BI,ADD,                                     
                 05,4,BI,ADD,                                     
                 09,4,BI,ADD,                                     
                 13,4,BI,BI,LENGTH=4,                             
                 80:X)                                           
  OUTFIL REMOVECC,NODETAIL,                                       
  HEADER1=(C'<REPORT NAME 1>',/,                                 
           C'RUN DATE: ',&DATE(4MD-),C' TIME : ',&TIME,2/,80:X), 
  TRAILER1=(C'EARNED   :',C' $',TOT=(01,4,BI,M10,LENGTH=8),/,     
            C'RESERVED :',C' $',TOT=(05,4,BI,M10,LENGTH=8),/,     
            C'USED     :',C' $',TOT=(09,4,BI,M10,LENGTH=8),/,     
            C'EXPIRED  :',C' $',TOT=(13,4,BI,M10,LENGTH=8),/,     
            C'           ---------',/,                           
            C'TOTAL    :',C' $',TOT=(17,4,BI,M10,LENGTH=8),/,     
            C'           ---------',80:X)                         
/*                                                               


Report2 JCL:

Code:

//STEP0200 EXEC PGM=SORT                                     
//SYSOUT   DD SYSOUT=*                                       
//SORTIN   DD *                                             
ABCD1111                                                     
EFGH2222                                                     
IJKL3333                                                     
MNOP4444                                                     
ABCD1000                                                     
EFGH2000                                                     
IJKL3000                                                     
MNOP4000                                                     
//SORTOUT  DD SYSOUT=*                                       
//SYSIN    DD *                                             
  SORT FIELDS=(1,4,CH,A)                                     
  OUTREC FIELDS=(1,10,80:X)                                   
  OUTFIL REMOVECC,NODETAIL,                                   
  HEADER2=(C'<REPORT NAME 2>',/,                             
           C'RUN DATE: ',&DATE(4MD-),C' TIME : ',&TIME,       
           50:'PAGE: ',&PAGE,2/,                             
           2:'TYPE OF TRANSACTION ',C'   AMOUNT  ',/,         
           2:'====================',C' ==========',80:X),     
  SECTIONS=(1,4,                                             
  TRAILER3=(3:1,4,25:TOT=(5,4,ZD,M10,LENGTH=8),80:X))         
/*                                                           


Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Mon Mar 07, 2005 11:44 am    Post subject: Reply with quote
Sakreg,

Here's a DFSORT/ICETOOL job that will give you the reports you asked for. I assumed that your input files are RECFM=FB, that the values in input file1 are 9-byte ZD values and that you have more than one record in input file1 (if not, the job could be simplified).

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=...  input file1
//IN2 DD DSN=...  input file2
//RPT1 DD SYSOUT=*
//RPT2 DD SYSOUT=*
//TOOLIN DD *
  COPY FROM(IN1) TO(RPT1) USING(CTL1)
  SORT FROM(IN2) TO(RPT2) USING(CTL2)
/*
//CTL1CNTL DD *
  OUTREC FIELDS=(1,36,
   37:1,9,ZD,ADD,10,9,ZD,ADD,19,9,ZD,ADD,28,9,ZD,TO=ZD,LENGTH=10)
  OUTFIL FNAMES=RPT1,NODETAIL,OUTREC=(80X),
    TRAILER1=('Report Name 1',/,DATE,X,TIME,/,X,/,
              'Earned',11:':',13:'$ ',TOT=(1,9,ZD,M10,LENGTH=9),/,
              'Reserved',11:':',13:'$ ',TOT=(10,9,ZD,M10,LENGTH=9),/,
              'Used',11:':',13:'$ ',TOT=(19,9,ZD,M10,LENGTH=9),/,
              'Expired',11:':',13:'$ ',TOT=(28,9,ZD,M10,LENGTH=9),/,
              13:12'-',/,
              'Total',11:':',13:'$ ',TOT=(37,10,ZD,M10,LENGTH=10),/,
              13:12'-')
/*
//CTL2CNTL DD *
  SORT FIELDS=(1,4,CH,A)
  OUTFIL FNAMES=RPT2,NODETAIL,OUTREC=(80X),
    HEADER2=('Report Name 2',54:'Page',PAGE,/,X,/,
     'Type of Transaction',22:'Amount'),
    SECTIONS=(1,4,
     TRAILER3=(1,4,22:TOT=(5,4,ZD,M10,LENGTH=5)))
/*

_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
sakreg
Beginner


Joined: 28 Feb 2005
Posts: 80
Topics: 26
PostPosted: Tue Mar 08, 2005 12:05 am    Post subject: Reply with quote
Thanks Frank Smile .

It is working in the way I expected. But I am in the process of understanding. If I need help in that I will get back to you.
Back to top
View user's profile Send private message
sakreg
Beginner


Joined: 28 Feb 2005
Posts: 80
Topics: 26
PostPosted: Tue Mar 08, 2005 12:30 am    Post subject: Reply with quote
Kolusu,

Thanks for your suggestion.

The job abended with

Quote:

WER215A OUTREC ARITHMETIC OVERFLOW


If using SORT, Can I do a direct read on DB2 and then print the report based on the values from DB2?
Back to top
View user's profile Send private message
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