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)

IBM® DB2®

IBM DB2 supports the following numeric data types:

Table 2. IBM DB2 numeric data types
IBM DB2 data type	Description
`SMALLINT`	16 bit signed integer
`INTEGER`	32 bit signed integer
`BIGINT`	64 bit signed integer
`DECFLOAT(16)`	16 bit signed floating point decimal
`DECFLOAT(34)`	32 bit signed floating point decimal
`DECIMAL(p,s)`	Fixed point decimal (max is 31 digits)
`REAL`	32-bit floating point decimal (C float)
`FLOAT[(n)] (DOUBLE)`	64-bit floating point decimal (C double)

Note:

DB2 V 9.1 introduced the DECFLOAT(16) and DECFLOAT(34) floating point decimal types to store large decimals.

Solution

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

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

SQL scripts to create databases must be converted manually. Tables created from BDL programs do not have to be converted; the database interface detects the MONEY data type and uses the DECIMAL type for DB2.

Important:

The maximum precision for DB2 decimals is 31 digits, while Informix supports 32 digits.

The DECIMAL(p) data type is converted to DECFLOAT(16) (for DECIMAL(p<=16)) or DECFLOAT(34) (for DECIMAL(p>16)) to store floating point decimals. If you create tables with DECFLOAT columns, you will lose the original DECIMAL precision when extracting the schema with fgldbsch, because IBM DB2 supports only two precision specifications (16 or 34). Note also the DECFLOAT(34) will be extracted as DECIMAL(32), since the Genero DECIMAL type has a maximum precision of 32 digits.

Note:

Avoid using DECIMAL[(p)] type in FGL or SQL: Due to the implementation differences in Informix SQL / Genero BDL and the native SQL type, such data type is not recommended. Always specify a precision and scale with DECIMAL(p,s).

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.