The following table shows how OLE DB data types map to the DB2 data types, as described in Microsoft® OLE DB 2.0 Programmer's Reference and Data Access SDK, Microsoft Press, 1998. The IBM OLE DB Table Function Designer defines the appropriate RETURNS TABLE columns in your OLE DB table function, based on the values in this table.
In the three cases where an OLE DB data type maps to multiple DB2 data types, the default DB2 data type that the designer assigns to an OLE DB data column might not be the data type that you want. You can change the DB2 data type of a column to another valid DB2 data type in the Column Mapping section of the designer.
For example, the OLE DB data type DBTYPE_WSTR maps to four DB2 data types. The designer assigns the default DB2 data type of GRAPHIC(N) to an OLE DB data column of type DBTYPE_WSTR. You can change the Type property of a DB2 data column from type GRAPHIC to VARGRAPHIC, LONG GRAHIC, or DBCLOB.
Note: The XML data type cannot be used as either a parameter type or a return type for functions that are written in SQL.
| OLE DB Data Type | DB2 Data Type |
|---|---|
| DBTYPE_I2 | SMALLINT |
| DBTYPE_I4 | INTEGER |
| DBTYPE_I8 | BIGINT |
| DBTYPE_R4 | REAL |
| DBTYPE_R8 | FLOAT/DOUBLE |
| DBTYPE_NUMERIC (p, s) | DEC (p, s) |
| DBTYPE_DBDATE | DATE |
| DBTYPE_DBTIME | TIME |
| DBTYPE_DBTIMESTAMP | TIMESTAMP |
| DBTYPE_STR | CHAR(N) |
| DBTYPE_STR | VARCHAR(N) |
| DBTYPE_STR | LONG VARCHAR |
| DBTYPE_STR | CLOB(N) |
| DBTYPE_BYTES | CHAR(N) FOR BIT DATA |
| DBTYPE_BYTES | VARCHAR(N) FOR BIT DATA |
| DBTYPE_BYTES | LONG VARCHAR FOR BIT DATA |
| DBTYPE_BYTES | BLOB(N) |
| DBTYPE_WSTR | GRAPHIC(N) |
| DBTYPE_WSTR | VARGRAPHIC(N) |
| DBTYPE_WSTR | LONG GRAPHIC |
| DBTYPE_WSTR | DBCLOB(N) |
Developing OLE DB Table Functions | Creating OLE DB Table Functions | OLE DB Table Functions | OLE DB Table Function Designer