# 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)` `DECIMAL(p*2,p)`
`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 19, since the Netezza maximum `DECIMAL` precision is `38(2*19)`. If the original precision is bigger than 19, a `CREATE TABLE` statement executed from a Genero program will fail with an SQL error.

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.