| SQL adaptation guide For IBM DB2 UDB 8.x, 9x / Data dictionary | |
Informix® provides the following data types to store numbers:
| 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(p) / DECIMAL(p) | Floating-point decimal number |
| DEC(p,s) / DECIMAL(p,s) | Fixed-point decimal number |
| 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) |
Most data types supported by IBM® DB2® UDB are compatible to Informix data types. DB2 V 9.1 introduces the DECFLOAT(16) and DECFLOAT(34) floating point decimal types to store large decimals. The next table lists the Informix types and DB2 equivalents.
| Informix data type | IBM DB2 equivalent |
|---|---|
| INT8 | Use BIGINT instead |
| DECIMAL(p) | With DB2 V9.1, DECIMAL(p<=16) can be stored in DECFLOAT(16) and DECIMAL(p>16) can be stored in DECFLOAT(34). With older versions of DB2, we can use DECIMAL(p*2,p), but with a limitation of 15 for the original Informix DECIMAL precision. |
| DECIMAL(32,s) | DB2 decimals maximum precision is 31 digits! |
| MONEY | DECIMAL(16,2) |
| MONEY(p) | DECIMAL(p,2) |
| MONEY(p,s) | DECIMAL(p,s) |
| SMALLFLOAT | REAL |
| FLOAT[(n)] | 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 DB2.
The maximum precision for DB2 decimals is 31 digits, while Informix supports 32 digits.
When using DB2 V8 and prior:
There is no DB2 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 DB2 compatible types. To workaround the DB2 limitation, the DB2 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 15 ((2*15) = 30), since DB2 maximum DECIMAL precision is 31. If the original precision is bigger than 15, a CREATE TABLE statement executed from a Genero program will fail with a DB2 SQLSTATE 42611.
When using DB2 V9.1 and higher:
The DECIMAL(p) data type is converted to DECFLOAT(16) (for p<=16) or DECFLOAT(34) (for p>16) to store floating point decimals. If you create tables with DECFLOAT columns, you will lose the original DECIMAL precision when extracting the schema with fgldbsch, because IBM DB2 supports only two precision specifications (16 or 34). Note also the DECFLOAT(34) will be extracted as DECIMAL(32), since the Genero DECIMAL type has a maximum precision of 32 digits.