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.