Arithmetic operators in expressions

Arithmetic with two integer operands

If both operands of an arithmetic operator are integers, the operation is performed in binary. The result is a large integer unless either (or both) operand is a big integer, in which case the result is a big integer.

The result of an integer arithmetic operation (including unary minus) must be within the range of the result type.

Arithmetic with an integer and a decimal operand

If one operand is an integer and the other operand is decimal, the operation is performed in decimal. The arithmetic operation uses a temporary copy of the integer that has been converted to a decimal number.

The temporary copy of the integer that has been converted to a decimal number has a precision p and scale 0. p is 19 for a big integer, 11 for a large integer, and 5 for a small integer. In the case of an integer constant, p depends on the number of digits in the integer constant. p is 5 for an integer constant consisting of 5 digits or fewer. Otherwise, p is the same as the number of digits in the integer constant.

Arithmetic with an integer and a decimal floating-point operand

If one operand is a small integer, large integer, or big integer and the other is a decimal floating-point number, the operation is performed in decimal floating point. The arithmetic operation uses a temporary copy of the integer that has been converted to a decimal floating-point number.

For small integer or large integer, the temporary copy of the integer is converted to DECFLOAT(16). For big integer, the temporary copy of the big integer is converted to DECFLOAT(34). The rules for two decimal floating point operands are then applied.

Arithmetic with two decimal operands

If both operands are decimal, the operation is performed in decimal.

For static SQL statements, the value of the field DECIMAL ARITHMETIC on installation panel DSNTIP4 or the SQL processing option DEC determines whether DEC15 or DEC31 is used.

For dynamic SQL statements, the value of the field DECIMAL ARITHMETIC on installation panel DSNTIP4, the SQL processing option DEC, or the special register CURRENT PRECISION determines whether DEC15 or DEC31 is used according to these rules:

• Field DECIMAL ARITHMETIC applies if either of these conditions is true:
  • DYNAMICRULES run behavior applies and the application has not set CURRENT PRECISION.

    For a list of the DYNAMICRULES option values that specify run, bind, define, or invoke behavior, see Table 1.

  • DYNAMICRULES bind, define, or invoke behavior applies; the value of installation panel field USE FOR DYNAMICRULES is YES; and the application has not set CURRENT PRECISION.
• SQL processing option DEC applies if DYNAMICRULES bind, define, or invoke behavior is in effect, the value of installation panel field USE FOR DYNAMICRULES is NO, and the application has not set CURRENT PRECISION.
• Special register CURRENT PRECISION applies if the application sets the register.

The value of DECIMAL ARITHMETIC is the default value for the SQL processing option and the special register. SQL statements executed using SPUFI use the value in DECIMAL ARITHMETIC.

Decimal addition and subtraction

For decimal operations, the precision and scale of the result depends on the precision and scale of the operands.

If the operation is addition or subtraction and the operands do not have the same scale, the operation is performed with a temporary copy of one of the operands that has been extended with trailing zeros so that its fractional part has the same number of digits as the other operand.

The precision of the result is the minimum of n and the quantity MAX(p-s,p'-s')+MAX(s,s')+1. The scale is MAX(s,s'). n is 31 if DEC31 is in effect or if the precision of at least one operand is greater than 15. Otherwise, n is 15.

In COBOL, blanks must precede and follow a minus sign to avoid any ambiguity with COBOL host variable names (which allow the use of a dash).

Decimal multiplication

For decimal multiplication, the precision and scale of the result depends on the precision and scale of the operands.

For multiplication, the precision of the result is MIN(n,p+p'), and the scale is MIN(n,s+s'). n is 31 if DEC31 is in effect or if the precision of at least one operand is greater than 15. Otherwise, n is 15.

If both operands have a precision greater than 15, the operation is performed using a temporary copy of the operand with the smaller precision. If the operands have the same precision, the second operand is selected. If more than 15 significant digits are needed for the integral part of the copy, the statement's execution is ended and an error occurs. Otherwise, the copy is converted to a number with precision 15, by truncating the copy on the right. The truncated copy has a scale of MAX(0,S-(P-15)), where P and S are the original precision and scale. If, in the process of truncation, one or more nonzero digits are removed, SQLWARN7 in SQLCA is set to W, indicating loss of precision.

When both operands have a precision greater than 15, the foregoing formulas for the precision and scale of the result still apply, with one change: for the operand selected as the copy, use the precision and scale of the truncated copy; that is, use 15 as the precision and MAX(0,S-(P-15)) for the scale.

   10000000000000000000000000. * 1

Arithmetic with an integer and a decimal operand

If one operand is an integer and the other operand is decimal, the operation is performed in decimal. The arithmetic operation uses a temporary copy of the integer that has been converted to a decimal number.

The temporary copy of the integer that has been converted to a decimal number has a precision p and scale 0. p is 19 for a big integer, 11 for a large integer, and 5 for a small integer. In the case of an integer constant, p depends on the number of digits in the integer constant. p is 5 for an integer constant consisting of 5 digits or fewer. Otherwise, p is the same as the number of digits in the integer constant.

Decimal division

The rules for a specific decimal division depend on whether the DEC31 option is in effect for the operation, whether p is greater than 15, and whether p' is greater than 15.

The following table shows how the precision and scale of the result depend on these factors. In that table, the occurrence of N/A in a row implies that the indicated factor is not relevant to the case represented by the row.

If p' is greater than 15, the division is performed using a temporary copy of the divisor. If more than 15 significant digits are needed for the integral part of the divisor, the statement's execution is ended, and an error occurs. Otherwise, the copy is converted to a number with precision 15, by truncating the copy on the right. The truncated copy has a scale of MAX(0,s'-(p'-15)), which is the formula for x. If, in the process of truncation, one or more nonzero digits are removed, SQLWARN7 in SQLCA is set to W, indicating loss of precision.

Minimum divide result scale

If the calculated value of 's' is negative, an error occurs. If a minimum divide result scale is specified, this error does not occur.

The minimum scale is determined according to the following precedence:

Static SQL

1. The precompiler DEC option, if it is set with a non-zero scale.
2. The DECARTH subsystem parameter, if set with a non-zero scale.
3. The MINDVSCL subsystem parameter, if set to value other than NONE.
4. The DECDIV3 subsystem parameter, if set to YES.

Dynamic SQL

1. The CURRENT PRECISION special register, if it is set with a non-zero scale.
2. Either of the following cases:
   • For a package that was bound with DYNAMICRULES RUN or if the DYNRULS DECP value is set to YES: The DECARTH subsystem parameter, if it is set with a non-zero scale.
   • For all other cases: The precompiler DEC option, if it is set with a non-zero scale.
3. The MINDVSCL subsystem parameter, if set to value other than NONE.
4. The DECDIV3 subsystem parameter, if set to YES.

SQL statements that are executed using SPUFI

The DECARTH subsystem parameter value.

The default value for both the precompiler DEC option and the CURRENT PRECISION special register is DECIMAL ARITHMETIC.

A minimum divide result scale of 3 can be specified using the DECDIV3 subsystem parameter setting. A minimum divide scale result in the range 1–9 can be specified using the DECARTH subsystem parameter value of the form 'Dpp.s' where 'pp' is 15 or 31 and represents the precision and 's' represents the minimum divide scale, as a number in the range 1–9. Such a specification overrides the DECDIV3 subsystem parameter. When a minimum divide result scale is specified, the formula MAX(s,s'), where s represents the scale derived from the above table and s' represents the value specified by the minimum divide result scale, is applied and a new scale is derived. The newly derived scale is the scale of the result and overrides any scale derived using the table above.

For more information, see:

• DECIMAL ARITHMETIC field (DECARTH DECP value)
• MINIMUM DIVIDE SCALE field (DECDIV3 subsystem parameter)
• MINDVSCL subsystem parameter in DSN6SPRM
• USE FOR DYNAMICRULES field (DYNRULS DECP value)
• SQL processing options (see "DEC(15|31)")

Arithmetic with floating-point operands

If either operand of an arithmetic operator is floating-point, the operation is performed in floating-point. If necessary, the operands are first converted to double-precision floating-point numbers. Thus, if any element of an expression is a floating-point number, the result of the expression is a double-precision floating-point number.

An operation involving a floating-point number and an integer is performed with a temporary copy of the integer that has been converted to double-precision floating-point. An operation involving a floating-point number and a decimal number is performed with a temporary copy of the decimal number that has been converted to double-precision floating-point. The result of a floating-point operation must be within the range of floating-point numbers.

The order in which floating-point operands (or arguments to functions) are processed can affect the results slightly because floating-point operands are approximate representations of real numbers. Because the order in which operands are processed might be implicitly modified by Db2 (for example, Db2 might decide what degree of parallelism to use and what access plan to use), an application that uses floating-point operands should not depend on the results being precisely the same each time an SQL statement is executed.

Arithmetic with a floating-point and a decimal floating-point operand

If one operand is a floating-point number (real or double) and the other is a decimal floating-point number, the operation is performed in decimal floating-point. The arithmetic operation uses a temporary copy of the floating-point number that has been converted to a decimal floating-point number.

Arithmetic with two decimal floating-point operands

If both operands are decimal floating point, the operation is performed in decimal floating point. If one operand is DECFLOAT(n) and the other is DECFLOAT(m), the operation is performed in DECFLOAT(max(n,m)).

General arithmetic operation rules for DECFLOAT

The following general rules apply to all arithmetic operations on the DECFLOAT data type:

• Every operation on finite numbers is carried out as though an exact mathematical result is computed, using integer arithmetic on the coefficient where possible.

  If the coefficient of the theoretical exact result has no more than the number of digits that reflect its precision (16 or 34), it is used for the result without change (unless there is an underflow or overflow condition). If the coefficient has more than the number of digits that reflect its precision, it is rounded to exactly the number of digits that reflect its precision (16 or 34), and the exponent is increased by the number of digits that are removed.

  For static SQL statements other than CREATE VIEW, the ROUNDING bind option or the native SQL procedure option determines the rounding mode.

  For dynamic SQL statements (and static CREATE VIEW statements), the special register CURRENT DECFLOAT ROUNDING MODE determines the rounding mode.

  If the value of the adjusted exponent of the result is less than E_min, an exception condition is returned. In this case, the calculated coefficient and exponent form the result, unless the value of the exponent is less than E_tiny, in which case the exponent is set to E_tiny, the coefficient is rounded (possibly to zero) to match the adjustment of the exponent, and the sign is unchanged. If this rounding gives an inexact result, an underflow exception condition is returned.

  If the value of the adjusted exponent of the result is larger than E_max, an overflow exception condition is returned. In this case, the result is as defined as an overflow exception condition and might be infinite. It will have the same sign as the theoretical result.

• Arithmetic that uses the special value infinity follows the usual rules, where negative infinity is less than every finite number and positive infinity is greater than every finite number.

  Under these rules, an infinite result is always exact. Certain uses of infinity return an invalid operation condition. The following list is a list of operations that can cause an invalid operation condition and the result of the operation is NaN when one of the operands is infinity but the other operand is not NaN nor sNaN.

  • Add +infinity to -infinity during an addition or subtraction operation
  • Multiply 0 by +infinity or -infinity
  • Divide either +infinity or -infinity by either +infinity or -infinity
  • The first argument to the MOD function is either +infinity or -infinity
  • Either argument of the QUANTIZE function is +infinity or -infinity
  • The second argument of the POWER® function is +infinity or -infinity
  • A NaN when used as an operand to an arithmetic operation

  The following arithmetic rules apply to arithmetic operations and the NaN value:

  • The result of any arithmetic operation that has an operand that is a NaN (a quiet NaN or signaling NaN) is NaN. The sign of the result is copied from the first operand that is a signaling NaN, or if neither operand is signaling, the sign is copied from the first operand that is a NaN. Whenever a result is a NaN, the sign of the result depends only on the copied operand.
  • The sign of the result of a multiplication or division will be negative only if the operands have different signs and neither is a NaN.
  • The sign of the result of an addition or subtraction will be negative only if the result is less than zero and neither operand is a NaN, except for the following cases where the result is a negative 0:
    • A result is rounded to zero, and the value, before rounding, had a negative sign
    • Subtract 0 from -0
    • Addition of operands with opposite signs (or subtraction of operands with the same sign), the result has a coefficient of 0, and the rounding mode is ROUND_FLOOR
    • Multiplication or division and the result has a coefficient of 0 and the signs of the operands are different
    • The first argument of the POWER function is -0, and the second argument is a positive odd number
    • The argument of the CEIL, FLOOR, or SQRT function is -0
    • The first argument of the ROUND or TRUNCATE function is -0

Examples involving special DECFLOAT values:

   INFINITY + 1             = INFINITY
   INFINITY + INFINITY      = INFINITY
   INFINITY + -INFINITY     = NAN        -- exception
   NAN + 1                  = NAN
   NAN + INFINITY           = NAN
   1 - INFINITY             = -INFINITY
   INFINITY - INFINITY      = NAN        -- exception
   -INFINITY - -INFINITY    = NAN        -- exception
   -0.0 - 0.0E1             = -0.0
   -1.0 * 0.0E1             = -0.0
   1.0E1 / 0                = INFINITY
   -1.0E5 / 0.0             = -INFINITY
   1.0E5 / -0               = -INFINITY
   INFINITY / -INFINITY     = NAN        -- exception
   INFINITY / 0             = INFINITY
   -INFINITY / 0            = -INFINITY
   -INFINITY / -0           = INFINITY

Arithmetic with distinct type operands

A distinct type cannot be used with arithmetic operators even if its source data type is numeric. To perform an arithmetic operation, create a function with the arithmetic operator as its source. For example, if there were distinct types INCOME and EXPENSES, both of which had DECIMAL(8,2) data types, the following user-defined function, REVENUE, could be used to subtract one from the other.

  CREATE FUNCTION REVENUE ( INCOME, EXPENSES )
    RETURNS DECIMAL(8,2) SOURCE "-" ( DECIMAL, DECIMAL)

Alternately, the - (minus) operator could be overloaded using a function to subtract the new data types.

   CREATE FUNCTION "-" ( INCOME, EXPENSES )   
     RETURNS DECIMAL(8,2) SOURCE "-" ( DECIMAL, DECIMAL)

Alternatively, the distinct type can be cast to a built-in data type and the result used as an operand of an arithmetic operator.