NUMERIC data types

Informix®

Informix supports several data types to store numbers:

Table 1. Informix numeric data types
Informix data type	Description
`SMALLINT`	16 bit signed integer
`INTEGER`	32 bit signed integer
`BIGINT`	64 bit signed integer
`INT8`	64 bit signed integer (replaced by `BIGINT`)
`DECIMAL`	Equivalent to `DECIMAL(16)`
`DECIMAL(p)`	Floating-point decimal number (max precision is 32)
`DECIMAL(p,s)`	Fixed-point decimal number (max precision is 32)
`MONEY`	Equivalent to `DECIMAL(16,2)`
`MONEY(p)`	Equivalent to `DECIMAL(p,2)` (max precision is 32)
`MONEY(p,s)`	Equivalent to `DECIMAL(p,s)` (max precision is 32)
`REAL / SMALLFLOAT`	32-bit floating point decimal (C float)
`DOUBLE PRECISION / FLOAT[(n)]`	64-bit floating point decimal (C double)

ORACLE

Oracle® supports following data types to store numbers:

Table 2. Oracle numeric data types
Oracle data type	Description
`NUMBER(p,s) (1<=p<= 38, -84<=s<=127)`	Fixed point decimal numbers.
`NUMBER(p) (1<=p<= 38)`	Integer numbers with a precision of p.
`NUMBER(*,s)`	Fixed point decimal numbers with a precision of 38 digits.
`NUMBER`	Floating point decimals with a precision of 38 digits.
`FLOAT(b) (1<=b<= 126)`	Floating point numbers with a binary precision b. This is a sub-type of NUMBER.
`BINARY_FLOAT` (since Oracle 10g)	32-bit floating point number.
`BINARY_DOUBLE` (since Oracle 10g)	64-bit floating point number.

ANSI types like SMALLINT, INTEGER are supported by Oracle but will be converted to the native NUMBER type.

When dividing INTEGER or SMALLINT types, Informix rounds the result ( 7 / 2 = 3 ), while Oracle doesn't, because it does not have a native integer data type ( 7 / 2 = 3.5 )

Solution

Use the following conversion rules to map Informix numeric types to Oracle numeric types:

Table 3. Informix numeric types and Oracle equivalents
Informix data type	Oracle data type
`SMALLINT`	`NUMBER(5,0)`
`INTEGER`	`NUMBER(10,0)`
`BIGINT`	`NUMBER(20,0)`
`INT8`	`NUMBER(20,0)`
`DECIMAL(p,s)`	`NUMBER(p,s)`
`DECIMAL(p)`	`FLOAT(p * 3.32193)`
`DECIMAL` (not recommended)	`FLOAT`
`MONEY(p,s)`	`NUMBER(p,s)`
`MONEY(p)`	`NUMBER(p,2)`
`MONEY`	`NUMBER(16,2)`
`SMALLFLOAT`	`BINARY_FLOAT`
`FLOAT[(p)]`	`BINARY_DOUBLE`

Avoid dividing integers in SQL statements. If you do divide an integer, use the TRUNC() function with Oracle.

When creating a table directly in Oracle's sqlplus, using ANSI data types INTEGER, SMALLINT, you do actually create columns with the NUMBER type, which has a precision of 38 digits. As result, it is not possible to distinguish the original types used in CREATE TABLE, nor can it be possible to distinguish the columns created explicitly with the native NUMBER type, in the next example, all columns will be of type NUMBER:

$ sqlplus ...
sql> CREATE TABLE mytab (
   col1 INTEGER,
   col2 SMALLINT,
   col3 NUMBER,
   ...

When extracting the database schema with fgldbsch, NUMBER, NUMBER(p>32) and NUMBER(p>32,s) types will by default give an extraction error. However, these types can be converted to DECIMAL(32) and DECIMAL(32,s) with the -cv option, by using the "B" character at positions 22 (for NUMBER) and 23 (for NUMBER(p>32[,s])).

Note: When fetching a NUMBER[(p>32,s)] into a BDL DECIMAL(32[,s]) type, if the value stored in the NUMBER column has more than 32 digits, it will be rounded to fit into a DECIMAL(32), or the overflow error -1226 will occur when fetching into a DECIMAL(32,s). Note that it must be allowed to fetch numeric expressions such as 1/3 (=0.333333333333....) into a DECIMAL(p,s), even if such expression will produce more than 32 digits with Oracle.

When creating a table in a BDL program with DECIMAL(p), this type is converted to native Oracle FLOAT(p*3.32193). When creating a table in a BDL program with DECIMAL (without precision) this type is converted to native Oracle FLOAT. The native Oracle FLOAT[(p)] type can be extracted by fgldbsch, but Oracle's FLOAT has a higher precision than the BDL DECIMAL type, which can lead to value rounding when fetching rows.

With Oracle versions older than 10g, when creating tables in a BDL program with SMALLFLOAT or FLOAT types, these types are mapped to NUMBER (The native Oracle FLOAT(b) type could have been used, but this type is reserved to map DECIMAL(p) types). Starting with Oracle 10g, SMALLFLOAT or FLOAT types will respectively be converted to BINARY_FLOAT and BINARY_DOUBLE native Oracle types, which can be extracted by fgldbsch and mapped back to BDL SMALLFLOAT and FLOAT respectively in the .sch file.

Note: As a general recommendation, do not use DECIMAL[(p)] or SMALLFLOAT/FLOAT floating point types in business applications. These types should only be used for scientific data storage.

The numeric types translation can be controlled with the following FGLPROFILE entries:

dbi.database.dsname.ifxemul.datatype.smallint = { true | false }
dbi.database.dsname.ifxemul.datatype.integer = { true | false }
dbi.database.dsname.ifxemul.datatype.bigint = { true | false }
dbi.database.dsname.ifxemul.datatype.int8 = { true | false }
dbi.database.dsname.ifxemul.datatype.decimal = { true | false }
dbi.database.dsname.ifxemul.datatype.money = { true | false }
dbi.database.dsname.ifxemul.datatype.float = { true | false }
dbi.database.dsname.ifxemul.datatype.smallfloat = { true | false }

For more details see IBM Informix emulation parameters in FGLPROFILE.