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 ASE

SAP® ASE supports the following numeric data types:

Table 2. SAP ASE numeric data types
SAP ASE 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 SAP ASE DECIMAL type:

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

Notes about the SAP ASE MONEY and SMALLMONEY types:

SAP ASE 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 SAP ASE.

Solution

Use the following conversion rules to map Informix numeric types to SAP ASE numeric types:

Table 3. Informix data types and SAP ASE equivalents
Informix	SAP ASE
`SMALLINT`	`SMALLINT`
`INTEGER`	`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 SAP ASE data types. In database creation scripts, apply the conversion rules as described in the above table.

Important: There is no SAP ASE 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 SAP ASE limitation, the SAP ASE 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, since SAP ASE 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 SAP ASE error 2756.

SAP ASE does not support implicit character string to numeric conversions. For example, if you compare an integer column to '123' in a WHERE clause, SAP ASE will raise a conversion error. The problem exists also when using CHAR or VARCHAR SQL parameters. Check that your programs do not use string literals or CHAR/VARCHAR SQL parameters in integer expressions, as in this example:

DEFINE pv CHAR(1)
CREATE TABLE mytable ( v1 INT, v2 INT )
LET pv = '1'
SELECT * FROM mytable WHERE v1 = '1' AND v2 = pv

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.