Summarizing data
Use the PRL statement SUMMARY to report all of the records on just two lines whenever there is a control-break in the variables used with the ORDER BY statement (starting from right to left as shown in the reports below). A control-break is the point in a report when the value of the variables specified in ORDER BY and USING statement changes. For this reason, it is best to always make the first USING variable the same as the ORDER BY variable if one variable is use. For multiple variables with the ORDER BY statement, the same variables must be listed as the first group of variables in the USING statement in the same order.
SUMMARY PRL statement using 1 ORDER BY field
SET REPORTID = 'REPORT1'
SET TITLE = 'TRANSACTION ANALYSIS FOR ALL TRANSACTIONS'
SELECT TYPE 6E RECORDS FROM CMRDETL
USING T6ETRID -
T6ERESP -
T6ECPUR -
T6EFCWT -
T6ESHWM -
T6EPAGCT
ORDER BY T6ETRID
SUMMARY
REPORT
END
The PRL for this report is in the CMRPRL3 member of the sample library (BBSAMP). As shown in the following figure, the first of the two summary lines contains totals for all numeric fields. The second line contains the computed averages. The totals and averages are shown whenever there is a change in the first reported variable. These two lines of totals and averages also are shown when the PRL statement DETAIL is used. One selected record is reported per line, and when the first variable changes, the totals and averages are reported as SUMMARY.
Transaction Analysis for All Transactions summary report
TIME: 17:03:11
DATE: 09/17/1999
PAGE: 1
TRAN RESPONSE CPU REAL FILE_CONTROL STORAGE TOTAL
NAME TOTAL TIME TIME I/O TIME HI_WATER_MARK PAGE_COUNT
--------------- ------------- ------------- ------------- --------------- ---------------
CSGM 52 29.530 0.264 0.000 354984 58
AVG ***** 0.568 0.005 0.000 6827 1
CSMT 6 4.380 0.062 0.000 72952 13
AVG ***** 0.730 0.010 0.000 12159 2
CSPG 13 0.418 0.022 0.000 102048 18
AVG ***** 0.032 0.002 0.000 7850 1
CSSF 33 24.234 0.496 0.000 321368 137
AVG ***** 0.734 0.015 0.000 9738 4
CSSN 4 2.339 0.021 0.000 31776 7
AVG ***** 0.585 0.005 0.000 7944 2
CSST 5 0.206 0.017 0.000 39432 0
AVG ***** 0.041 0.003 0.000 7886 0
DB2 16 9.879 0.224 0.000 99040 1
AVG ***** 0.617 0.014 0.000 6190 0
DISP 3 7.617 0.756 0.000 39328 37
AVG ***** 2.539 0.252 0.000 13109 12
FCD2 6 5.147 0.088 0.000 88624 8
AVG ***** 0.858 0.015 0.000 14771 1
FIC2 28922 6788.137 132.645 2275.220 305450768 14799
AVG ***** 0.235 0.005 0.079 10561 1
FST2 4 2.539 0.014 0.000 54400 28
AVG ***** 0.635 0.003 0.000 13600 7
HST2 72 15.729 1.789 8.092 1259048 23
AVG ***** 0.218 0.025 0.112 17487 0
JNL2 56 156338.884 13.032 0.000 409264 397
AVG ***** 2791.766 0.233 0.000 7308 7
JPH1 929 10020.464 4.830 0.000 599785928 5
AVG ***** 10.786 0.005 0.000 645625 0
SMN2 62 19.769 0.308 0.000 436824 33
AVG ***** 0.319 0.005 0.000 7046 1
STAR 5360 308.284 10.726 0.000 32212464 2
AVG ***** 0.058 0.002 0.000 6010 0
TSM2 30 964.988 0.327 0.000 911808 17
AVG ***** 32.166 0.011 0.000 30394 1
TSM4 16 72.636 0.235 0.000 483792 14
AVG ***** 4.540 0.015 0.000 30237 1
TSM5 15 160.559 0.262 0.000 455016 3
AVG ***** 10.704 0.017 0.000 30334 0
SUMMARY PRL statement using 2 ORDER BY fields
SET REPORTID = 'REPORT02'
SET TITLE = ‘SUMMARY REPORT USING 2 ORDER BY FIELDS '
SELECT TYPE 6E RECORDS FROM CMRDETL
USING T6ESMFID -
CMR$SYS -
T6EUSER -
T6ETRID -
T6ERESP -
T6ECPUR
ORDER BY T6ESMFID CMR$SYS
SUMMARY
REPORT
END
The totals and averages are shown whenever there is a change in the ORDER BY variable, starting from the last variable in the ORDER BY statement, and proceeding from right to left. See the following reports for examples.
Summary report using 2 ORDER BY fields
TIME: 04:51:35
DATE: 06/20/2023
PAGE: 1
SMF SYSTEM RESPONSE CPU REAL
ID APPLID TOTAL TIME TIME
---- ------------------- ------------- -------------
SJSC BCVDH740 41 45.144373 0.132047
AVG ***** 1.101082 0.003221
BCVDN740 28 2.344305 0.139384
AVG ***** 0.083725 0.004978
SJSC * 69 47.488678 0.271431
AVG ***** 0.688242 0.003934
SJSD BCVDN740 25 0.696523 0.009816
AVG ***** 0.027861 0.000393
SJSD * 25 0.696523 0.009816
AVG ***** 0.027861 0.000393
SUMMARY PRL statement using 3 ORDER BY fields
SET REPORTID = 'REPORT03'
SET TITLE = ‘SUMMARY REPORT USING 3 ORDER BY FIELDS’
SELECT TYPE 6E RECORDS FROM CMRDETL
USING T6ESMFID -
CMR$SYS -
T6EUSER -
T6ETRID -
T6ERESP -
T6ECPUR
ORDER BY T6ESMFID CMR$SYS T6EUSER
SUMMARY
REPORT
END
Summary report using 3 ORDER BY fields
TIME: 04:51:27
DATE: 06/20/2023
PAGE: 1
SMF SYSTEM EXTENDED RESPONSE CPU REAL
ID APPLID USER-ID. TOTAL TIME TIME
---- -------- ------------------- ------------- -------------
SJSC BCVDH740 BCVCSTC 2 0.008684 0.001366
AVG ***** 0.004342 0.000683
MVSNYS1 39 45.135689 0.130681
AVG ***** 1.157325 0.003351
SJSC BCVDH740 * 41 45.144373 0.132047
AVG ***** 1.101082 0.003221
BCVDN740 MVSAMS1 21 2.213784 0.095820
AVG ***** 0.105418 0.004563
MVSAMS2 7 1.231343 0.046579
AVG ***** 0.175906 0.006654
SJSC BCVDN740 * 28 3.445127 0.142399
AVG ***** 0.123040 0.005086
SJSC * * 69 48.589500 0.274446
AVG ***** 0.704196 0.003977
SJSD BCVDN740 MVSAMS1 25 0.712595 0.009911
AVG ***** 0.028504 0.000396
SJSD BCVDN740 * 25 0.712595 0.009911
AVG ***** 0.028504 0.000396
SJSD * * 25 0.712595 0.009911
AVG ***** 0.028504 0.000396
SUMMARY PRL statement using 4 ORDER BY fields
SET REPORTID = 'REPORT04'
SET TITLE = ‘SUMMARY REPORT USING 4 ORDER BY FIELDS’
SELECT TYPE 6E RECORDS FROM CMRDETL
USING T6ESMFID -
CMR$SYS -
T6EUSER -
T6ETRID -
T6ERESP -
T6ECPUR
ORDER BY T6ESMFID CMR$SYS T6EUSER T6ETRID
SUMMARY
REPORT
END
Summary report using 4 ORDER BY fields
TIME: 04:46:14
DATE: 06/20/2023
PAGE: 1
SMF SYSTEM EXTENDED TRAN RESPONSE CPU REAL
ID APPLID USER-ID. NAME TOTAL TIME TIME
---- -------- -------- --------------- ------------- -------------
SJSC BCVDH740 BCVCSTC CLS2 2 0.008684 0.001366
AVG ***** 0.004342 0.000683
SJSC BCVDH740 BCVCSTC * 2 0.008684 0.001366
AVG ***** 0.004342 0.000683
MVSNYS1 CLS2 4 0.007544 0.001381
AVG ***** 0.001886 0.000345
CSAC 26 0.010281 0.006174
AVG ***** 0.000395 0.000237
PFI2 9 45.122013 0.123752
AVG ***** 5.013557 0.013750
SJSC BCVDH740 MVSNYS1 * 39 45.139838 0.131307
AVG ***** 1.157432 0.003367
SJSC BCVDH740 * * 41 45.148522 0.132673
AVG ***** 1.101183 0.003236
Use the PRL statement SUMMARY to report all of the records on just two lines whenever there is a control-break in the report, as shown in the following figures. A control-break is the point in a report when the first variable specified with USING changes. For this reason, it is best to always make the first USING variable the same as the ORDER BY variable.
SUMMARY PRL statement
SET REPORTID = 'REPORT1'
SET TITLE = 'TRANSACTION ANALYSIS FOR ALL TRANSACTIONS'
SELECT TYPE 6E RECORDS FROM CMRDETL
USING T6ETRID -
T6ERESP -
T6ECPUR -
T6EFCWT -
T6ESHWM -
T6EPAGCT
ORDER BY T6ETRID
SUMMARY
REPORT
END
The PRL for this report is in the CMRPRL3 member of the sample library (BBSAMP). As shown in the following figure, the first of the two summary lines contains totals for all numeric fields. The second line contains the computed averages. The totals and averages are shown whenever there is a change in the first reported variable. These two lines of totals and averages also are shown when the PRL statement DETAIL is used. One selected record is reported per line, and when the first variable changes, the totals and averages are reported as SUMMARY.
Transaction Analysis for All Transactions summary report
TIME: 17:03:11
DATE: 09/17/1999
PAGE: 1
TRAN RESPONSE CPU REAL FILE_CONTROL STORAGE TOTAL
NAME TOTAL TIME TIME I/O TIME HI_WATER_MARK PAGE_COUNT
--------------- ------------- ------------- ------------- --------------- ---------------
CSGM 52 29.530 0.264 0.000 354984 58
AVG ***** 0.568 0.005 0.000 6827 1
CSMT 6 4.380 0.062 0.000 72952 13
AVG ***** 0.730 0.010 0.000 12159 2
CSPG 13 0.418 0.022 0.000 102048 18
AVG ***** 0.032 0.002 0.000 7850 1
CSSF 33 24.234 0.496 0.000 321368 137
AVG ***** 0.734 0.015 0.000 9738 4
CSSN 4 2.339 0.021 0.000 31776 7
AVG ***** 0.585 0.005 0.000 7944 2
CSST 5 0.206 0.017 0.000 39432 0
AVG ***** 0.041 0.003 0.000 7886 0
DB2 16 9.879 0.224 0.000 99040 1
AVG ***** 0.617 0.014 0.000 6190 0
DISP 3 7.617 0.756 0.000 39328 37
AVG ***** 2.539 0.252 0.000 13109 12
FCD2 6 5.147 0.088 0.000 88624 8
AVG ***** 0.858 0.015 0.000 14771 1
FIC2 28922 6788.137 132.645 2275.220 305450768 14799
AVG ***** 0.235 0.005 0.079 10561 1
FST2 4 2.539 0.014 0.000 54400 28
AVG ***** 0.635 0.003 0.000 13600 7
HST2 72 15.729 1.789 8.092 1259048 23
AVG ***** 0.218 0.025 0.112 17487 0
JNL2 56 156338.884 13.032 0.000 409264 397
AVG ***** 2791.766 0.233 0.000 7308 7
JPH1 929 10020.464 4.830 0.000 599785928 5
AVG ***** 10.786 0.005 0.000 645625 0
SMN2 62 19.769 0.308 0.000 436824 33
AVG ***** 0.319 0.005 0.000 7046 1
STAR 5360 308.284 10.726 0.000 32212464 2
AVG ***** 0.058 0.002 0.000 6010 0
TSM2 30 964.988 0.327 0.000 911808 17
AVG ***** 32.166 0.011 0.000 30394 1
TSM4 16 72.636 0.235 0.000 483792 14
AVG ***** 4.540 0.015 0.000 30237 1
TSM5 15 160.559 0.262 0.000 455016 3
AVG ***** 10.704 0.017 0.000 30334 0