The NUMBER data type supports applications that use the Oracle NUMBER data type.
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.
The effects of setting the number_compat database configuration parameter to ON are as follows.
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.
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 |
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 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.