ODBC data types and how they correspond to Db2 for i database types
The IBM® i Access support for the ODBC driver maps data types between ODBC types and Db2® for i data types.
The following table shows the default mappings of the supported data types. Choose the related link below, to the Db2 for i database types, for more information on data types.
| Db2 for i Database Type | 3.x ODBC Data Type |
|---|---|
| BIGINT | SQL_BIGINT |
| BINARY | SQL_BINARY |
| BLOB | SQL_LONGVARBINARY |
| CHAR | SQL_CHAR |
| CHAR FOR BIT DATA | SQL_BINARY |
| CLOB | SQL_LONGVARCHAR |
| DATALINK | SQL_VARCHAR |
| DATE | SQL_DATE |
| DBCLOB | SQL_LONGVARCHAR |
| DBCLOB CCSID 1200 | SQL_WLONGVARCHAR |
| DBCLOB CCSID 13488 | SQL_WLONGVARCHAR |
| DECFLOAT | SQL_VARCHAR |
| DECIMAL | SQL_DECIMAL |
| DOUBLE | SQL_DOUBLE |
| FLOAT | SQL_FLOAT |
| GRAPHIC | SQL_CHAR |
| GRAPHIC CCSID 1200 | SQL_WCHAR |
| GRAPHIC CCSID 13488 | SQL_WCHAR |
| INTEGER | SQL_INTEGER |
| LONG VARCHAR | SQL_VARCHAR |
| LONG VARCHAR FOR BIT DATA | SQL_VARBINARY |
| LONG VARGRAPHIC | SQL_VARCHAR |
| LONG VARGRAPHIC CCSID 1200 | SQL_WVARCHAR |
| LONG VARGRAPHIC CCSID 13488 | SQL_WVARCHAR |
| NUMERIC | SQL_NUMERIC |
| REAL | SQL_REAL |
| ROWID | SQL_VARBINARY |
| SMALLINT | SQL_SMALLINT |
| TIME | SQL_TYPE_TIME |
| TIMESTAMP | SQL_TYPE_TIMESTAMP |
| VARBINARY | SQL_VARBINARY |
| VARCHAR | SQL_VARCHAR |
| VARCHAR FOR BIT DATA | SQL_VARBINARY |
| VARGRAPHIC | SQL_VARCHAR |
| VARGRAPHIC CCSID 1200 | SQL_WVARCHAR |
| VARGRAPHIC CCSID 13488 | SQL_WVARCHAR |
| XML | SQL_XML |
Implementation notes:
- All conversions in the Microsoft ODBC Software Development Kit Programmer's Reference Version 3.5 are supported for these ODBC SQL data types.
- Call the ODBC API SQLGetTypeInfo to learn more about each of these data types.
- The database type of VARCHAR will be changed to LONG VARCHAR by the database if the column size that is specified is larger than 255.
- The ODBC driver does not support any of the interval SQL data types.
- 2.x ODBC applications use the SQL_DATE, SQL_TIME, and SQL_TIMESTAMP defines in place of the SQL_TYPE_DATE, SQL_TYPE_TIME, and SQL_TYPE_TIMESTAMP defines.
- Unicode data which are the data types with a CCSID of 1200 (UTF-16), 1208 (UTF-8), or 13488 (UCS-2), report to ODBC 2.x applications as SQL_CHAR, SQL_VARCHAR, and SQL_LONGVARCHAR instead of SQL_WCHAR, SQL_WVARCHAR, and SQL_WLONGVARCHAR.
- LOBs (BLOB, CLOB, and DBCLOB) up to 2 GB in size are supported. For more information on LOBs and datalinks choose the related link below, to the Large Objects (LOBs) considerations topic collection.
- Note that to retrieve decimal fields with large precision successfully you must bind the column as SQL_C_CHAR. The structure that stores SQL_C_NUMERIC data can hold up to 38 digits.