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)

Microsoft™ SQL Server

Microsoft SQL Server supports the following numeric data types:

Table 2. Microsoft SQL Server numeric data types
Microsoft SQL Server data type	Description
`SMALLINT`	16 bit signed integer
`INTEGER`	32 bit signed integer
`BIGINT`	64 bit signed integer
`DECIMAL(p,s)`	Fixed point decimal.
`SMALLMONEY`	32-bit floating point decimal with currency
`MONEY`	64-bit floating point decimal with currency
`REAL`	32-bit floating point decimal (C float)
`FLOAT[(n)] (DOUBLE)`	64-bit floating point decimal (C double)

Notes about SQL Server DECIMAL type:

Without any decimal storage specification, the precision defaults to 18 and the scale defaults to zero:
- DECIMAL in SQL Server = DECIMAL(18,0) in Genero BDL.
- DECIMAL(p) in SQL Server = DECIMAL(p,0) in Genero BDL.
The maximum precision is 38.

Notes about the SQL Server MONEY and SMALLMONEY types:

SQL Server provides the MONEY and SMALLMONEY data types, but the currency symbol handling is quite different. Therefore, it is recommended to implement Informix MONEY columns as DECIMAL columns in SQL Server.

Solution

Use the following conversion rules to map Informix numeric types to SQL Server numeric types:

Table 3. Informix data types and Microsoft SQL Server equivalents
Informix	Microsoft SQL SERVER
`SMALLINT`	`SMALLINT`
`INTEGER`	`INT / INTEGER`
`BIGINT`	`BIGINT`
`INT8`	`BIGINT`
`DECIMAL(p,s)`	`DECIMAL(p,s)`
`DECIMAL(p<=19)`	`DECIMAL(2*p,p)`
`DECIMAL(p>19)`	N/A
`DECIMAL`	`DECIMAL(32,16)`
`MONEY(p,s)`	`DECIMAL(p,s)`
`MONEY(p)`	`DECIMAL(p,2)`
`MONEY`	`DECIMAL(16,2)`
`REAL / SMALLFLOAT`	`REAL`
`FLOAT[(n)] / DOUBLE PRECISION`	`FLOAT(n)` (Where n must be from 1 to 15)

When creating tables from BDL programs, the database interface automatically converts Informix numeric data types to corresponding Microsoft SQL Server data types. In database creation scripts, apply the conversion rules as described in the above table.

Important: There is no SQL Server 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 SQL Server compatible types. To workaround the SQL Server limitation, the SQL Server database drivers convert 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, since SQL Server 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 Server error 2750.

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.