Limited supportBMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see BMC AMI Ops Monitor for Db2 13.1 image-2024-5-19_8-5-1.png .

SUMMARIZE

The SUMMARIZE control statement requests that the input values be summarized according to the included specifications.

The following table describes the operands for the SUMMARIZE control statement.

Operand

Description

TYPE=tableType

Specifies the type of the receiving table

Possible values are:

  • SUMACCT
  • SUMDDFACCT
  • SUMPKGACCT
  • SUMBUFACCT
  • SUMMSTAT
  • SUMDDFSTAT
  • SUMBUFSTAT

This required field has no default.

TYPE4=acceleratorSimBPTableType

Specifies the type of accelerator table or simulated bufferpool table

Possible values are:

  • SUMACCA
  • SUMACCS
  • SUMSYMB

This required field has no default.

TABLE=tableName

Name of the receiving table

This table is read to define the columns defined within the table. It can be loaded by a specifically requested LOAD request. This field is optional. If it is not specified, no source of default summarization columns exists, and SUMMKEY is the only other possible source.

FROMTBL=tableName

Name of the table from which records are to be read and summarized

This operand is mutually exclusive with FROMDD.

FROMDD=ddName

ddname assigned to the file that is to be loaded.

This operand is mutually exclusive with FROMTBL

TODD=ddName

ddname receiving the file of summarized records

This operand is used as a work file for the specified LOAD request. This required field has no default.

COMBINE=(fld1,fld2,fld3,...)

Defines a list of column names from the input table that are to be combined in the output table

The values from all fields are added together and passed on in the first field. There is no default. The default is to ignore combine processing.

It is possible to type multiple COMBINE statements in a single summarization process. Only columns with like attributes can be combined. It is possible to combine integer and short integer columns. Character columns cannot be combined, nor can datetime columns. If overflow occurs while combining fields, the largest possible positive value is placed in the field.

Note

All COMBINE statements for a summarization process have a limit of 200 field names.

Note

This keyword is deprecated and will be removed in a future release of the product.

SUMMSTART=date-time

Defines the first record which participates in summarization

  • Format for date:
    • yyyy-mm-dd (For a specific date)
    • * (For the current date)
    • *_nn (For a previous date nn days before the current date)
    • *_l (For the beginning of the previous day)
  • Format for time is hh:mm:ss for a specific time.

If this operand is omitted, the default is to start with the oldest record from the specified input source.

Note

For ease in reading the resulting reports, it is preferable to specify a start time on an even time period, such as an hour.

SUMMEND=date-time

Defines the last record which participates in summarization

Format for date:

  • yyyy-mm-dd (Up to (but not including) the specified date)
  • * (Up to (but not including) the current date)
  • *_nn (Up to (but not including) a previous date nn days before the current date)

Format for time is hh:mm:ss to specify up to (but not including) the specified time. If omitted, the default is to process all records after the first one selected.

SUMMINT=nnnX

Indicates the number of minutes (M), hours (H), days (D), weeks (W), or months (N or MON) to be included in each summary interval (row)

The X can be entered as M, H, D, W, N, or MON. The default value is 30M.

Each interval generated starts at microsecond 0 and ends at microsecond 999999; for example, if SUMMINT=15M, a generated interval will be 14:45:00.000000 to 14:59:59.999999; then the next interval will be 15:00:00.000000 to 15:14:59.999999. The end interval time is reflected in the DATETIME, TIME and HOUR columns of the Performance Reporter tables.

SUMMKEY=(key,key,...)

Defines which columns are to be used as keys, so that their values remain unique in the summarized records

Valid value

Associated varchar

AUTHID

AUTHID_L

BPNAME


COLLECTIONID

COLLECTIONID_L

CONNECTION


CONSISTOKEN


CORRID


CORRNAME


DDFLOCATION

DDFLOCATION_L

ENDUSERID

ENDUSERID_L

ENDUSERTX

ENDUSERTX_L1

ENDUSERWN

ENDUSERWN_L1

EXECLOCATION

EXECLOCATION_L

LOCATION

LOCATION_L

LUWIDLUNM


LUWIDNID


ORIGPRIMID

ORIGPRIMID_L

PLANNAME


PROGRAMNAME

PROGRAMNAME_L

REQLOCATION

REQLOCATION_L

SUBSYSTEM


SYSTEMID


1 Valid for Db2 Version 11 and later.

SUMMKEY=(key,key,...)

(continued)

Any combination of these values can be entered separated by commas. All values entered must be defined in the receiving table, if the receiving table is named. The SUMMKEY or TABLE keyword (or both) must be defined in a SUMMARIZE statement. The limitations are described in the SUMMKEY and TABLE keyword values table.

SUMMKEY columns that are not specified will be set to blanks in the summarized record to avoid storing erroneous or misleading information in the record. The blanks indicate that the column's value in the record is immaterial, because the summarized record might be produced from multiple records that have different values in that field.

Notes

Consider the following information:

  • When you specify SUMMKEYs, BMC AMI Ops Monitor for Db2 Performance Reporter now summarizes records in associated VARCHAR fields (in addition to CHAR fields) in Performance Reporter summary tables. That is, the performance data table includes columns for both CHAR fields and VARCHAR fields. For example, if you specify the LOCATION SUMMKEY, columns for LOCATION and LOCATION_L are loaded into the summary record.The CHAR field contains the first 18 bytes of the value. The VARCHAR field contains the full value.


  • If a SUMMKEY is not supported in the version of Db2 in which you are running, the VARCHAR column in not populated.
  • Although CONNTYPE is not a summkey, it is added to the final summarization output. When different CONNTYPEs are encountered during a summarization interval, the word MULTIPLE will be recorded in the resulting summarized output. Otherwise the actual value for CONNTYPE is used.

The following table describes the valid key values for the SUMMKEY and the TABLE keywords for each type of receiving table.

Table

Keyword

SUMACCT

  • AUTHID
  • CONNECTION
  • CORRID
  • CORRNAME
  • ENDUSERID
  • ENDUSERTX
  • ENDUSERWN
  • LOCATION
  • LUWIDLUNM
  • LUWIDNID
  • ORIGPRIMID
  • PLANNAME
  • SUBSYSTEM
  • SYSTEMID

SUMDDFACCT

  • AUTHID
  • CONNECTION
  • CORRID
  • CORRNAME
  • DDFLOCATION
  • ENDUSERID
  • ENDUSERTX
  • ENDUSERWN
  • LOCATION
  • LUWIDLUNM
  • LUWIDNID
  • ORIGPRIMID
  • PLANNAME
  • REQLOCATION
  • SUBSYSTEM
  • SYSTEMID

SUMPKGACCT

  • AUTHID
  • COLLECTIONID
  • CONNECTION
  • CONSISTOKEN
  • CORRID
  • CORRNAME
  • ENDUSERID
  • ENDUSERTX
  • ENDUSERWN
  • EXECLOCATION
  • LOCATION
  • LUWIDLUNM
  • LUWIDNID
  • ORIGPRIMID
  • PLANNAME
  • PROGRAMNAME
  • SUBSYSTEM
  • SYSTEMID

SUMBUFACCT

  • AUTHID
  • BPNAME
  • CONNECTION
  • CORRID
  • CORRNAME
  • ENDUSERID
  • ENDUSERTX
  • ENDUSERWN
  • LOCATION
  • LUWIDLUNM
  • LUWIDNID
  • ORIGPRIMID
  • PLANNAME
  • SUBSYSTEM
  • SYSTEMID

SUMSTAT

  • LOCATION
  • SUBSYSTEM
  • SYSTEMID

SUMDDFSTAT

  • DDFLOCATION
  • LOCATION
  • SUBSYSTEM
  • SYSTEMID

SUMBUFSTAT

  • AUTHID
  • LOCATION
  • SUBSYSTEM
  • SYSTEMID

SUMACCA

  • SUBSYSTEM
  • PLANNAME
  • AUTHID
  • CONNECTION
  • CORRID
  • ORIGPRIMID
  • LUWIDNID
  • LUWIDLUNM
  • LOCATION

SUMACCS

  • LOCATION
  • SYSTEMID
  • SUBSYSTEM

SUMSYMB

  • BPNAME
  • SYSTEMID
  • SUBSYSTEM

The default is to use all key columns that are defined and valid in the receiving table.

Related topic

Control-statement-detail-definitions


 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*