DB2 Version 9.7 for Linux, UNIX, and Windows

NUMBER data type

The NUMBER data type is introduced to support applications that use the Oracle NUMBER data type.

Support for NUMBER is at the database level, and must be enabled before creating the database where support is required. This is achieved by setting the DB2_COMPATIBILITY_VECTOR registry variable to the appropriate value. When a database is created with NUMBER enabled, the database configuration parameter number_compat is set to ON. After a database is created with NUMBER support enabled, it cannot be disabled for that database, even if the DB2_COMPATIBILITY_VECTOR registry variable is reset. Similarly, all databases created without NUMBER support enabled cannot have NUMBER support enabled, even by setting the DB2_COMPATIBILITY_VECTOR registry variable.

Enablement

NUMBER data type support is enabled by setting bit position number 5 (0x10) of the DB2_COMPATIBILITY_VECTOR registry variable before creating a database. To take full advantage of these DB2® compatibility features, set the value to ORA. This is the recommended setting. A new setting for the registry variable does not take effect until after the instance has been stopped and then restarted.

Usage

The following support is enabled for a DB2 database that has the number_compat database configuration parameter set to ON.

When the NUMBER data type is explicitly encountered in SQL statements, it is implicitly mapped as follows:

If NUMBER is specified without precision and scale attributes, it is mapped to DECFLOAT(16).
If NUMBER(p) is specified, it is mapped to DECIMAL(p)
If NUMBER(p,s) is specified, it is mapped to DECIMAL(p,s)

The maximum supported precision is 31, and the scale must be a positive value no greater than the precision. As a result of this implicit mapping, messages will refer to data types DECFLOAT and DECIMAL instead of NUMBER, and any operations that describe data types for columns or routines will return either DECIMAL or DECFLOAT instead of NUMBER. Note that DECFLOAT(16) provides a lower maximum precision than the Oracle NUMBER data type. If more than 16 digits of precision are needed for storing numbers in tables, the columns with this requirement should be defined explicitly as DECFLOAT(34).

Numeric literal support is unchanged in a DB2 database that has the number_compat configuration parameter set to ON. The rules for integer, decimal, and floating-point constants continue to apply. This limits decimal literals to 31 digits and floating-point literals to the range of binary double-precision floating-point values. A string to DECFLOAT(34) cast (using the CAST specification or the DECFLOAT function) can be used for values beyond the ranges of DECIMAL or DOUBLE up to the range of DECFLOAT(34).

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

There is currently no support for a numeric literal that ends in either D or F, representing 64-bit binary floating-point and 32-bit binary floating-point values, respectively. A numeric literal that includes an E has the data type of DOUBLE and can be cast to REAL using the CAST specification or the cast function REAL.

In a DB2 database that has the number_compat configuration parameter set to ON, the default data type that is used for a sequence value in the CREATE SEQUENCE statement is DECIMAL(27) instead of INTEGER.

In a DB2 database that has the number_compat configuration parameter set to ON, 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 also applies to arithmetic operations where both operands have DECIMAL or DECFLOAT(16) data types, which differs from the description of decimal arithmetic in the DB2 SQL Reference. (See "Expressions with arithmetic operators" in "Expressions".) Additionally, all division operations involving only integer data types (SMALLINT, INTEGER, or BIGINT) are effectively performed using decimal floating-point arithmetic and 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).

Function resolution is also changed, such that an argument of data type DECIMAL is considered to be a DECFLOAT value during the resolution process. For purposes of function resolution, this effectively treats functions with arguments that correspond to the NUMBER(p[,s]) data type as if the argument data type were NUMBER.

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 type on the set of data types of the arguments. The functions included in this set are:

COALESCE
DECODE
GREATEST
LEAST
MAX (scalar)
MIN (scalar)
NVL
VALUE

When the number_compat configuration parameter is set to ON, 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 31. These rules also apply to corresponding columns in set operations (UNION, EXCEPT(MINUS), and INTERSECT), expression values in the IN list of an IN predicate, and corresponding expressions of a multiple row VALUES clause.

The rounding mode used for assignments and casts that happen on the database server 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. This parameter defaults to round-half-even, but can be set 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
Source 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.

Client-server compatibility considerations

Client applications working with a DB2 database server that is enabled for NUMBER data type support never see a NUMBER data type from the server. Any column or expression that might have reported NUMBER from an Oracle server will report either DECIMAL or DECFLOAT from a DB2 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 setting should match the value of the decflt_rounding database configuration parameter. ROUND_HALF_UP should be specified to most closely match the Oracle rounding mode.

Restrictions

NUMBER data type support has the following restrictions:

There is no support for the NUMBER data type with a precision attribute greater than 31, a precision attribute of asterisk (*), a scale attribute that exceeds the precision attribute, or a negative scale attribute. There is no corresponding DECIMAL precision and scale support for such data type specifications.
The trigonometric functions and the DIGITS scalar function cannot be invoked with arguments of data type NUMBER without a precision (DECFLOAT).
A distinct type cannot be created with the name NUMBER.