DB2 Version 10.1 for Linux, UNIX, and Windows

MOD scalar function

Returns the remainder of the first argument divided by the second argument.


>>-MOD--(--expression1--,--expression2--)----------------------><

The schema is SYSIBM. (The SYSFUN version of this function continues to be available.)

expression1: An expression that returns a value of any built-in numeric data type.
expression2: An expression that returns a value of any built-in numeric data type. This expression can only be zero when at least one of the function arguments is a decimal floating point.

The formula for calculating the remainder is:

MOD(x,y) = x-(x/y)*y

where x/y is the truncated integer result of the division.

The result only is negative when the first argument is negative.

The result can be null if either argument can be null or if neither argument is a decimal floating-point number and the dft_sqlmathwarn database configuration parameter is set to YES; the result is the null value when either argument is NULL.

The data type of the result depends on the data types of the arguments.

Small integer if both arguments are small integers.
Large integer if one argument is a large integer and the other argument is either a small integer or a large integer.
Big integer if both arguments are integers and at least one argument is a big integer.
Decimal if one argument is an integer and the other argument is a decimal. The result has the same precision as the decimal argument.
Decimal if both arguments are decimals. The precision of the result is MIN (p-s,p'-s')+(MAX(s,s') and the scale is (MAX(s,s'). Where p and s represent the precision and scale of the first argument and p' and s' represent the precision and scale of the second argument.
Double-precision floating point if one argument is a floating-point number and the other is not a DECFLOAT. The arguments are converted to double-precision floating point numbers before performing the MOD function. For example, if one argument is a floating-point number and the other is an integer or decimal, the function is performed with a temporary copy of the integer or decimal, which has been converted to double-precision floating-point. The result must be in the range of floating-point numbers.
Double-precision floating point if both arguments are floating-point numbers. The result must be in the range of floating-point numbers.
DECFLOAT(34) if either argument is a decimal floating-point. If expression2 evaluates to zero, the result is not a number (NaN) and an invalid operation warning with the associated SQLSTATE is issued.
If either argument is a special decimal floating-point value, the general arithmetic operation rules for decimal floating-point apply.
Results when arguments include infinity:
- MOD(x, +/-INFINITY) returns the value x.
- MOD(+/-INFINITY, +/-INFINITY) returns NaN and an invalid operation warning with the associated SQLSTATE.
- MOD(+/-INFINITY, x) returns NaN and an invalid operation warning with the associated SQLSTATE.

Examples

Example 1: Assume the host variable M1 is an INTEGER with a value of 5 and host variable M2 is an INTEGER with a value of 2
```
 SELECT MOD(:M1, :M2)
    FROM SYSIBM.SYSDUMMY1
```
The result is 1 with a data type of INTEGER.
Example 2: Assume the host variable M1 is an INTEGER with a value of 5 and host variable M2 is a DECIMAL(3,2) with a value of 2.20
```
 SELECT MOD(:M1, :M2)
    FROM SYSIBM.SYSDUMMY1
```
The result is 0.60 with a data type of DECIMAL(3,2).
Example 3: Assume the host variable M1 is a DECIMAL(4,2) with a value of 5.5 and host variable M2 is a DECIMAL(4,1) with a value of 2.0
```
 SELECT MOD(:M1, :M2)
    FROM SYSIBM.SYSDUMMY1
```
The result is 1.50 with a data type of DECIMAL(4,2).