ODBC data type support
When a job runs, the stage maps InfoSphere® DataStage® data types to target database types.
Multiple data types can be listed in the table because the actual target data type depends on the ODBC driver in question. In some instances, the ODBC driver will not accept the target data type specified by the stage when creating the table. For example, the Dynamic RDBMS stage will map theBinary source data type to the CHAR(n) FOR BIT DATA target data type, which will be accepted by Db2® driver but not by Oracle.
The database can accept the data type specified by the stage, but will then internally convert it to a more suitable native data type.
For example, the Dynamic RDBMS stage will map Integer source data type to INTEGER target data type, which Oracle will accept but will then convert it to NUMBER(38) native data type.
The generic data type is the type that the stage
specifies in the auto-generated CREATE TABLE statement.
The mapping from this data type to the actual database type in the
backend database is performed by the ODBC driver. If the mapping is
not supported, the CREATE TABLE statement fails.
| SQL type | Length | Scale | Extended | Generic Column Definition |
|---|---|---|---|---|
| BigInt | n/a | n/a | any | BIGINT, NUMBER |
| BigInt | n/a | n/a | any | BIGINT |
| Binary | n | n/a | n/a | Not supported |
| Binary | n | n/a | n/a | CHAR(n) FOR BIT DATA |
| BinaryDouble | n/a | n/a | n/a | BINARY_DOUBLE |
| BinaryFloat | n/a | n/a | n/a | BINARY_FLOAT |
| Bit | n/a | n/a | n/a | SMALLINT |
| Bit | n/a | n/a | n/a | Not supported |
| Char | n | n/a | blank | CHAR(n) |
| Char | n | n/a | Unicode | CHAR(n) |
| Date | n/a | n/a | n/a | DATE, TIMESTAMP, DATETIME |
| Date | n/a | n/a | n/a | DATE, DATETIME |
| Datetime | n/a | n/a | n/a | DATE |
| Decimal | p | s | n/a | DECIMAL(p,s) |
| Double | p | n/a | n/a | DECFLOAT, NUMBER, FLOAT |
| Double | p | n/a | n/a | FLOAT(p) |
| Float | p | n/a | n/a | REAL, NUMBER |
| Float | p | n/a | n/a | FLOAT(p) |
| Integer | n/a | n/a | n/a | INTEGER, INT, NUMBER |
| Integer | n/a | n/a | n/a | INTEGER |
| LongNVarChar | n | n/a | n/a | VARCHAR(n), VARCHAR2(n)
NTEXT, LONG |
| LongVarBinary | n | n/a | n/a | Not supported |
| LongVarBinary | n | n/a | n/a | IMAGE, LONG |
| LongVarChar | n | n/a | blank | VARCHAR, VARCHAR2 |
| LongVarChar | n | n/a | blank | TEXT, LONG |
| LongVarChar | n | n/a | Unicode | VARCHAR, VARCHAR2 |
| LongVarChar | n | n/a | Unicode | TEXT, LONG |
| NChar | n | n/a | n/a | CHAR(n) |
| NChar | n | n/a | n/a | NCHAR(n) |
| NVarChar | n | n/a | n/a | VARCHAR(n) |
| NVarChar | n | n/a | n/a | NVARCHAR(n) |
| Numeric | p | s | n/a | DECIMAL(p,s) |
| Real | n/a | n/a | n/a | REAL, NUMBER |
| Real | n/a | n/a | n/a | REAL |
| SmallInt | n/a | n/a | any | SMALLINT, NUMBER |
| SmallInt | n/a | n/a | any | SMALLINT |
| Time | n/a | any | any | TIME, TIMESTAMP, DATETIME |
| Time | n/a | any | any | TIME, DATETIME, DATE |
| Timestamp | n/a | any | any | TIMESTAMP, DATETIME |
| Timestamp | n/a | any | any | TIMESTAMP, DATETIME, DATE |
| TinyInt | n/a | n/a | any | TINYINT, SMALLINT,NUMBER |
| TinyInt | n/a | n/a | any | SMALLINT |
| VarBinary | n | n/a | n/a | Not supported |
| VarBinary | n | n/a | n/a | VARCHAR(n) FOR BIT DATA |
| VarChar | n | n/a | blank | VARCHAR(n), VARCHAR2(n) |
| VarChar | n | n/a | blank | VARCHAR(n) |
| VarChar | n | n/a | Unicode | VARCHAR(n), VARCHAR2(n) |
| VarChar | n | n/a | Unicode | VARCHAR(n) |