Using ACCOUNTING REDUCE subcommand options to balance performance and data set space

OMEGAMON for DB2® Performance Expert provides several commands for which the REDUCE subcommand and its options can be used to reduce the volume of data before the data is passed to subsequent other subcommands like REPORT and SAVE.

When OMEGAMON for Db2 Performance Expert processes a REDUCE subcommand, it holds its temporary data in a REDUCE data set to relieve virtual storage. This temporary data set is either automatically allocated or, if its placement and size needs to be controlled, explicitly specified as a ccWORK DD statement in your JCL. (cc stands for the report set, respectively the command to which the temporary REDUCE data set is associated, for example, AC for the ACCOUNTING command).

When using REDUCE, it is difficult to determine in advance an appropriate size of the temporary data set. The required size depends mostly on the amount and complexity of input data to be reduced and on REDUCE subcommand options like FROM/TO, INCLUDE/EXCLUDE, INTERVAL, and BOUNDARY, which influence the reduction factor.

The REDUCE subcommand option CALCULATE helps to reduce a high volume of Accounting data results in unacceptable performance, difficulties to determine adequate ACWORK space, or even abnormally terminated jobs (abend B37).

It provides information about the ACWORK space actually used during a REDUCE step. It can determine an adequate size for the ACWORK data set with regard to the amount and complexity of the data to be reduced. If insufficient ACWORK space is specified, and the job consequently terminates. In this case the CALCULATE option provides information about the size required to successfully complete the job in a second run.

This option is purely performance- and processing-related and does not manipulate data in any way. For the latter purpose use REDUCE subcommand options like FROM/TO, INCLUDE/EXCLUDE, INTERVAL, and BOUNDARY.

For the complete command syntax of ACCOUNTING REDUCE and for more information about the ACWORK DD statement, see the Report Command Reference.

For example, OMEGAMON for Db2 Performance Expert, by default, automatically allocates a temporary ACWORK data set to hold output data from the REDUCE subcommand. To control the placement and size of this data set, you can also specify a ddname of ACWORK. However, if the allocated size is too small for the temporary data, REDUCE processing terminates because of insufficient space (abend B37). You have to increase the size of the data set and run the job again, still without knowing an appropriate size.

In this case, you can use the CALCULATE subcommand option to calculate the appropriate size of ACWORK.
      Other REDUCE subcommand options
With CALCULATE in the command stream, OMEGAMON for Db2 Performance Expert counts the number and lengths of records written to ACWORK during ACCOUNTING REDUCE processing.
  • If ACWORK is sufficiently sized, REDUCE processing finishes normally and the information message FPEA0800I is shown in DPMLOG, which provides details about the calculations.

    Subsequent subcommands of ACCOUNTING (here, REPORT and SAVE) will be executed.

    You can compare the specified size of ACWORK to the value shown in the message. You might want to adjust your specification to the same dimension, considering a bonus for variations of the input data.

  • If the specified size of ACWORK is too small, REDUCE processing stops writing to the data set. However, OMEGAMON for Db2 Performance Expert continues counting and calculating until all input data to REDUCE is processed. The error message FPEA0801S in DPMLOG provides details about the minimum required size of ACWORK. Finally, the job terminates with abend B37.

    Subsequent subcommands of ACCOUNTING (here, REPORT and SAVE) will not be executed.

    It is recommended that you specify the size of ACWORK to at least the value shown in the message plus 20% . Then, you must rerun the job again.

In both messages the other values describe the amount and quality of processed records. The error message FPEA0801S is accompanied by other error messages indicating a failure to write to ACWORK. For more information, see Messages.