NUMBER data type

The NUMBER data type supports applications that use the Oracle NUMBER data type.

Enablement

You enable NUMBER support at the database level, before creating the database where you require the support. To enable the support, set the DB2_COMPATIBILITY_VECTOR registry variable to hexadecimal value 0x10 (bit position 5), and then stop and restart the instance to have the new setting take effect.
db2set DB2_COMPATIBILITY_VECTOR=10
db2stop
db2start
To take full advantage of the Db2® compatibility features for Oracle applications, the recommended setting for the DB2_COMPATIBILITY_VECTOR is ORA, which sets all of the compatibility bits.

When you create a database with NUMBER support enabled, the number_compat database configuration parameter is set to ON.

If you create a database with NUMBER support enabled, you cannot disable NUMBER support for that database, even if you reset the DB2_COMPATIBILITY_VECTOR registry variable. Similarly, if you create a database with NUMBER support disabled, you cannot enable NUMBER support for that database later, even by setting the DB2_COMPATIBILITY_VECTOR registry variable.

Effects

The effects of setting the number_compat database configuration parameter to ON are as follows.

When the NUMBER data type is explicitly encountered in SQL statements, the data type is implicitly mapped as follows:
  • If you specify NUMBER without precision and scale attributes, it is mapped to DECFLOAT(16).
  • If you specify NUMBER(p), it is mapped to DECIMAL(p).
  • If you specify NUMBER(p,s), it is mapped to DECIMAL(p,s).
The maximum supported precision is 31, and the scale must be a positive value that is no greater than the precision. Also, a result of the implicit mapping, messages refer to data types DECFLOAT and DECIMAL instead of NUMBER. In addition, any operations that describe data types for columns or routines return either DECIMAL or DECFLOAT instead of NUMBER.
Tip: The DECFLOAT(16) data type provides a lower maximum precision than that of the Oracle NUMBER data type. If you need more than 16 digits of precision for storing numbers in columns, then explicitly define those columns as DECFLOAT(34).

Numeric literal support is unchanged: the rules for integer, decimal, and floating-point constants continue to apply. These rules limit decimal literals to 31 digits and floating-point literals to the range of binary double-precision floating-point values. If necessary, you can use a string-to-DECFLOAT(34) cast, using the CAST specification or the DECFLOAT function, for values beyond the range of DECIMAL or DOUBLE up to the range of DECFLOAT(34). There is currently no support for a numeric literal that ends in either D, representing 64-bit binary floating-point values, or F, representing 32-bit binary floating-point values. A numeric literal that includes an E has the data type of DOUBLE, which you can cast to REAL using the CAST specification or the cast function REAL

If you cast NUMBER data values to character strings, using either the CAST specification or the VARCHAR or CHAR scalar function, all leading zeros are stripped from the result.

The default data type that is used for a sequence value in the CREATE SEQUENCE statement is DECIMAL(27) instead of INTEGER.

All arithmetic operations and arithmetic or mathematical functions involving DECIMAL or DECFLOAT data types are effectively performed using decimal floating-point arithmetic and return a value with a data type of DECFLOAT(34). This type of performance also applies to arithmetic operations where both operands have a DECIMAL or DECFLOAT(16) data type, which differs from the description of decimal arithmetic in the Expressions with arithmetic operators section of Expressions. Additionally, all division operations involving only integer data types (SMALLINT, INTEGER, or BIGINT) are effectively performed using decimal floating-point arithmetic. These operations return a value with a data type of DECFLOAT(34) instead of an integer data type. Division by zero with integer operands returns infinity and a warning instead of an error.

In some cases function resolution is also changed, such that an argument of data type DECIMAL is considered to be a DECFLOAT value during the resolution process. Also functions with arguments that correspond to the NUMBER(p[,s]) data type are effectively treated as if the argument data types were NUMBER. However, this change in function resolution does not apply to the set of functions that have a variable number of arguments and base their result data types on the set of data types of the arguments. The functions included in this set are as follows:
  • COALESCE
  • DECODE
  • GREATEST
  • LEAST
  • MAX (scalar)
  • MIN (scalar)
  • NVL
  • VALUE
In addition to the general changes to numeric functions, a special consideration also applies to the MOD function; if the second argument in the MOD function is zero, then the function returns the value of the first argument.
For more information on how the rules for result data types are extended to make DECFLOAT(34) the result data type if the precision of a DECIMAL result data type would have exceeded, see Rules for result data types. These rules also apply to the following items:
  • Corresponding columns in set operations: UNION, EXCEPT(MINUS), and INTERSECT
  • Expression values in the IN list of an IN predicate
  • Corresponding expressions of a multiple row VALUES clause
The rounding mode that is used for assignments and casts depends on the data types that are involved. In some cases, truncation is used. In cases where the target is a binary floating-point (REAL or DOUBLE) value, round-half-even is used, as usual. In other cases, usually involving a DECIMAL or DECFLOAT value, the rounding is based on the value of the decflt_rounding database configuration parameter. The value of this parameter defaults to round-half-even, but you can set it to round-half-up to match the Oracle rounding mode. The following table summarizes the rounding that is used for various numeric assignments and casts.
Table 1. Rounding for numeric assignments and casts
Source data type Target data type
Integer types DECIMAL DECFLOAT REAL/DOUBLE
Integer types not applicable not applicable decflt_rounding round_half_even
DECIMAL decflt_rounding decflt_rounding decflt_rounding round_half_even
DECFLOAT decflt_rounding decflt_rounding decflt_rounding round_half_even
REAL/DOUBLE truncate decflt_rounding decflt_rounding round_half_even
String (cast only) not applicable decflt_rounding decflt_rounding round_half_even
The Db2 decimal floating-point values are based on the IEEE 754R standard. Retrieval of DECFLOAT data and casting of DECFLOAT data to character strings removes any trailing zeros after the decimal point.

Starting in Db2 Version 10.5 Fix Pack 4, in a database with NUMBER support enabled, the built-in functions STDDEV, VAR and VARIANCE with integer input returns DECFLOAT instead of DOUBLE. Any view column or materialized query table (MQT) column with a result type that depends on this function continues to return the old result type until the view or MQT is regenerated or recreated.  In the case of an MQT, any queries that previously routed to the MQT will not longer do so until the MQT is recreated.

Client-server compatibility

Client applications working with a Db2 database server that you enable for NUMBER data type support never receive a NUMBER data type from the server. Any column or expression that would report NUMBER from an Oracle server report either DECIMAL or DECFLOAT from a Db2 database server.

Because an Oracle environment expects the rounding mode to be round-half-up, it is important that the client rounding mode match the server rounding mode. This means that the db2cli.ini file setting must match the value of the decflt_rounding database configuration parameter. To most closely match the Oracle rounding mode, you should specify ROUND_HALF_UP for the database configuration parameter.

Restrictions

NUMBER data type support has the following restrictions:
  • There is no support for the following items:
    • A precision attribute greater than 31
    • A precision attribute of asterisk (*)
    • A scale attribute that exceeds the precision attribute
    • A negative scale attribute
    There is no corresponding DECIMAL precision and scale support for NUMBER data type specifications.
  • You cannot invoke the trigonometric functions or the DIGITS scalar function with arguments of data type NUMBER without a precision (DECFLOAT).
  • You cannot create a distinct type with the name NUMBER.