NUMERIC data types

Informix^® supports several data types to store numbers:

Table 1. Informix numeric data types
Informix data type	Description
SMALLINT	16 bit signed integer
INT / INTEGER	32 bit signed integer
BIGINT	64 bit signed integer
INT8	64 bit signed integer (replaced by BIGINT)
DEC / DECIMAL	Equivalent to DECIMAL(16)
DEC / DECIMAL(p)	Floating-point exact decimal number, with p significant digits
DEC / DECIMAL(p,s)	Fixed-point exact decimal number, with p significant digits as s decimals
MONEY	Equivalent to DECIMAL(16,2)
MONEY(p)	Equivalent to DECIMAL(p,2)
MONEY(p,s)	Equivalent to DECIMAL(p,s)
REAL / SMALLFLOAT	32-bit floating point decimal (C float)
DOUBLE PRECISION / FLOAT[(n)]	64-bit floating point decimal (C double)

Solution

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
NUMERIC(p,s) / DECIMAL(p,s)	Exact decimal number with p significant digits and s decimals (1<=p<=38)
NUMERIC(p) / DECIMAL(p)	Integer with precision p (1<=p<=38)
NUMERIC / 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)

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.