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.