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)

Netezza®

Netezza supports the following data types to store numbers:

Table 2. Netezza numeric data types
Netezza data type	Description
`BYTEINT`	8-bit value with the range -128 to 127
`SMALLINT`	16 bit signed integer
`INTEGER`	32 bit signed integer
`BIGINT`	64 bit signed integer
`DECIMAL(p,s)`	Exact decimal number with p significant digits and s decimals (1<=p<=38)
`DECIMAL(p)`	Integer with precision p (1<=p<=38)
`DECIMAL`	Integer, same as NUMERIC(18,0)
`FLOAT(p)` with 1 <= p <= 6	16 bit approx floating point (C float)
`FLOAT(p)` with 7 <= p <= 15	32 bit approx floating point (C double)
`REAL`	Same as `FLOAT(6)`
`DOUBLE PRECISION`	Same as `FLOAT(15)`

Solution

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

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

Important:

There is no Netezza equivalent for the Informix DECIMAL(p) floating point decimal (i.e. without a scale). If your application uses such data types, you must review the database schema in order to use Netezza compatible types.

To workaround the Netezza limitation, the NTZ database drivers converts DECIMAL(p) types to a DECIMAL( 2*p, p ), to store all possible numbers that an Informix DECIMAL(p) can store. However, the original Informix precision cannot exceed 16 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.