large_aggregation - large aggregation configuration parameter

This parameter is used to control the data types returned by Db2® for aggregate functions, allowing for a smoother migration from one version of Db2 to another. The large_aggregation configuration parameter enables or disables the use of larger data types for COUNT, SUM and AVG aggregate functions.

Configuration type
Database
Applies to
Parameter
  • Configurable (requires a database connection)
Propagation class
  • Connection
    Note: The change in aggregation behavior will take effect after a database is deactivated and then reactivated.
Default [Range]
  • If on Db2 or Db2 on Cloud:
    • No [Yes; No]
  • If on BigSQL, Db2 Warehouse SaaS or IAS:
    • Yes [Yes; No]

Usage notes

Changing the value of the large_aggregation configuration parameter has the effect of changing the data type returned by the COUNT, SUM, and AVG functions as outlined below. Applications written to expect a specific data type returned from these functions should first be modified to handle the new data type. A change in the value of the configuration parameter takes effect once the Db2 instance is restarted.
COUNT
With the large_aggregation configuration parameter set to NO, the COUNT function returns a value of data type INTEGER. With the large_aggregation configuration parameter set to YES, the COUNT function returns a value of data type DECIMAL (31,0).
SUM
The SUM function returns the following data types based on the value of the large_aggregation configuration parameter.
If the value of large_aggregation is set to NO:
  • If the data type of the input expression is SMALL INTEGER or INTEGER, the data type of the result is INTEGER.
  • If the data type of the input expression is BIGINT, the data type of the result is the BIGINT.
  • If the data type of the input expression is DECIMAL, the data type of the result is DECIMAL.
  • If the data type of the input expression is FLOAT or DOUBLE, the data type of the result is DOUBLE.
  • If the data type of the input expression is DECFLOAT16 or DECFLOAT34, the data type of the result is DECFLOAT34.
If the value of large_aggregation is set to YES:
  • If the data type of the input expression is SMALL INTEGER, INTEGER or BIGINT, the data type of the result is BIGINT.
  • If the data type of the input expression is DECIMAL, the data type of the result is DECIMAL.
  • If the data type of the input expression is FLOAT or DOUBLE, the data type of the result is DOUBLE.
  • If the data type of the input expression is DECFLOAT16 or DECFLOAT34, the data type of the result is DECFLOAT34.
AVG
The AVG function returns the following data types based on the value of the large_aggregation configuration parameter.
If the value of large_aggregation is set to NO:
  • If the data type of the input expression is SMALL INTEGER or INTEGER, the data type of the result is INTEGER.
  • If the data type of the input expression is BIGINT, the data type of the result is BIGINT.
  • If the data type of the input expression is DECIMAL, the data type of the result is DECIMAL.
  • If the data type of the input expression is FLOAT or DOUBLE, the data type of the result is DOUBLE.
  • If the data type of the input expression is DECFLOAT16 or DECFLOAT34, the data type of the result is DECFLOAT34.
If the value of large_aggregation is set to YES:
  • If the data type of the input expression is SMALL INTEGER, INTEGER or BIGINT, the data type of the result is DECIMAL (31,6).
  • If the data type of the input expression is DECIMAL, the data type of the result is DECIMAL.
  • If the data type of the input expression is FLOAT or DOUBLE, the data type of the result is DOUBLE.
  • If the data type of the input expression is DECFLOAT16 or DECFLOAT34, the data type of the result is DECFLOAT34.