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)

SAP HANA®

SAP HANA supports the following numeric data types:

Table 2. SAP HANA numeric data types
SAP HANA data type	Description
`TINYINT`	8 bit unsigned integer (0 to 255)
`SMALLINT`	16 bit signed integer (-32,768 to 32,767)
`INTEGER`	32 bit signed integer (-2,147,483,648 to 2,147,483,647)
`BIGINT`	64 bit signed integer (-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807)
`DECIMAL(p,s)`	Fixed point decimal (max p is 38)
`DECIMAL(p)`	Floating point decimal (approximative)
`DECIMAL`	Floating point decimal (approximative)
`SMALLDECIMAL`	Floating point decimal (approximative)
`REAL`	32-bit floating point decimal (C float)
`FLOAT[(n)] (DOUBLE)`	64-bit floating point decimal (C double)

Solution

Table 3. Informix numeric data types and SAP HANA equivalents
Informix data type	SAP HANA equivalent
`SMALLINT`	`SMALLINT`
`INTEGER`	`INTEGER`
`INT8 / BIGINT`	`BIGINT`
`DECIMAL(p,s)`	`DECIMAL(p,s)`
`DECIMAL(p<=19)`	`DECIMAL(p*2,p)`
`DECIMAL(p>19)`	N/A: Max `p` is 38 for SAP HANA
`DECIMAL`	`DECIMAL(32,16)`
`MONEY`	`DECIMAL(16,2)`
`MONEY(p)`	`DECIMAL(p,2)`
`MONEY(p,s)`	`DECIMAL(p,s)`
`SMALLFLOAT`	`REAL`
`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 SAP HANA.

Important:

There is no SAP HANA equivalent for the Informix DECIMAL(p) floating point decimal (i.e. without a scale). If your application is using such data types, you must review the database schema in order to use SAP HANA compatible types. To workaround the SAP HANA limitation, the SAP HANA database driver converts DECIMAL(p) types to a DECIMAL( 2*p, p ), to store all possible numbers an Informix DECIMAL(p) can store. However, the original Informix precision cannot exceed 19, because SAP HANA supports a maximum DECIMAL precision of 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.