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.

Table 1. Data Type Mapping for Db2 for i database types
Db2 for i Database Type 3.x ODBC Data Type
BIGINT SQL_BIGINT
BINARY SQL_BINARY
BLOB SQL_LONGVARBINARY
Start of changeBOOLEANEnd of change Start of changeSQL_BITEnd of change
CHAR SQL_CHAR
Start of changeCHAR CCSID 65535End of change Start of changeSQL_BINARYEnd of change
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
Start of changeNCHAREnd of change Start of changeSQL_WCHAREnd of change
Start of changeNCLOBEnd of change Start of changeSQL_WLONGVARCHAREnd of change
NUMERIC SQL_NUMERIC
Start of changeNVARCHAREnd of change Start of changeSQL_WVARCHAREnd of change
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
Start of changeVARCHAR CCSID 65535End of change Start of changeSQL_VARBINARYEnd of change
VARGRAPHIC SQL_VARCHAR
VARGRAPHIC CCSID 1200 SQL_WVARCHAR
VARGRAPHIC CCSID 13488 SQL_WVARCHAR
XML Start of changeSQL_LONGVARCHAREnd of change

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.
  • Start of changeUTF-16 data which are the data types with a CCSID of 1200 or 13488 report to ODBC 2.x applications as SQL_CHAR, SQL_VARCHAR, and SQL_LONGVARCHAR instead of SQL_WCHAR, SQL_WVARCHAR, and SQL_WLONGVARCHAR.End of change
  • 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.