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® MySQL and MariaDB

MySQL and MariadDB support the following data types to store numbers:

Table 2. MySQL numeric data types
MySQL data type	Description
`SMALLINT`	16 bit signed integer
`INTEGER`	32 bit signed integer
`BIGINT`	64 bit signed integer
`DECIMAL(p,s)`	Fixed point decimal. Maximum precision depends on MySQL Version, see documentation
`DECIMAL(p)`	Stores whole numeric numbers up to p digits
`FLOAT[(M,D)]`	32 bit floating point number
`DOUBLE[(M,D)]`	64 bit floating point number

Note: Before MySQL 5.0.3, the maximum range of DECIMAL values is the same as for DOUBLE. Since MySQL 5.0.3, DECIMAL can store real precision numbers as in Informix. However, the maximum number of digits depends on the version of MySQL, see documentation for more details. We strongly recommend that you make tests (INSERT + SELECT) to check whether large decimals are properly inserted and fetched back.

Solution

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

Table 3. Informix numeric data types and MySQL equivalents
Informix data type	MySQL equivalent
`SMALLINT`	`SMALLINT`
`INTEGER`	`INTEGER`
`INT8 / BIGINT`	`BIGINT`
`DECIMAL(p<=16)`	`DECFLOAT(16)`
`DECIMAL(p>16)`	`DECFLOAT(34)`
`DECIMAL(p<=31,s)`	`DECIMAL(p,s)`
`DECIMAL(32,s)`	No equivalent
`MONEY`	`DECIMAL(16,2)`
`MONEY(p)`	`DECIMAL(p,2)`
`MONEY(p,s)`	`DECIMAL(p,s)`
`SMALLFLOAT`	`REAL`
`FLOAT[(n)]`	`FLOAT[(n)] (DOUBLE)`

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.