Precision for operations with decimal numbers

DB2® accepts two sets of rules for determining the precision and scale of the result of an operation with decimal numbers.

  • DEC15 rules allow a maximum precision of 15 digits in the result of an operation. DEC15 rules are in effect when both operands have a precision of 15 or less, or unless the DEC31 rules apply.
  • DEC31 rules allow a maximum precision of 31 digits in the result. DEC31 rules are in effect if any of the following conditions is true:
    • Either operand of the operation has a precision greater than 15 digits.
    • The operation is in a dynamic SQL statement, and any of the following conditions is true:
      • The current value of special register CURRENT PRECISION is DEC31 or D31.s, where s is a number between 1 and 9 and represents the minimum scale to be used for division operations.
      • The installation option for DECIMAL ARITHMETIC on panel DSNTIP4 is DEC31, 31, or D31.s, where s is a number between 1 and 9; the installation option for USE FOR DYNAMICRULES on panel DSNTIP4 is YES; and the value of CURRENT PRECISION has not been set by the application.
      • The SQL statement has bind, define, or invoke behavior; the statement is in an application that is precompiled with option DEC(31); the installation option for USE FOR DYNAMICRULES on panel DSNTIP4 is NO; and the value of CURRENT PRECISION has not been set by the application. See DYNAMICRULES bind option for an explanation of bind, define, and invoke behavior.
    • The operation is in an embedded (static) SQL statement that you precompiled with the DEC(31), DEC31, or D31.s option, or with the default for that option when the installation option DECIMAL ARITHMETIC is DEC31 or 31. s is a number between 1 and 9 and represents the minimum scale to be used for division operations. See Processing SQL statements for information about precompiling and for a list of all precompiler options.

Recommendation: To reduce the chance of overflow, or when dealing with a precision greater than 15 digits, choose DEC31 or D31.s , wheres is a number between 1 and 9 and represents the minimum scale to be used for division operations.