Data type conversions

IBM® InfoSphere® DataStage® supports a set of SQL data types that are different from Db2® SQL data types.

When you import metadata through the Db2 connector or read data by using the Db2 connector, the Db2 Connector stage converts the Db2 data types to InfoSphere DataStage data types. Conversely, when you write data to a target Db2 table through the Db2 connector, InfoSphere DataStage data types are converted to Db2 data types.

When the connector is configured to read Db2 data and provide the data on an output link, the connector tries to match the names of the result set columns with the output link columns. The order of the columns on the link and in the Oracle database is irrelevant.

If the Lookup type is set to Sparse, the connector matches by name the reference link columns with the parameters in the SQL block. The connector maps the columns that are marked as key columns to SQL input/output parameters and maps the remaining columns to the SQL output parameters. If the connector cannot match the names, the connector attempts to use the column order to associate link columns and parameters. Therefore, the connector associates the first column on the link with the first parameter, associates the second column on the link with the second parameter, and so on.

When the Write mode property is set to Insert, Update, Delete, the connector maps the columns on the input link to the input parameters that are specified in the SQL statement.

The following syntax describes how the connector maps the columns, based on the format that you use to specify the parameters:
InfoSphere DataStage syntax
The InfoSphere DataStage syntax is ORCHESTRATE.parameter_name. If you use InfoSphere DataStage syntax to specify parameters, the connector uses name matching. Therefore, every parameter in the statement must match a column on the link, and the parameter and the column must have the same name. If the connector cannot locate a matching column for a parameter, an error message is logged and the operation stops.