Default forward data type mappings for ODBC data sources

The following table lists the default forward data type mappings for ODBC data sources.

Table 1. ODBC default forward data type mappings
Remote Typename Remote Lower Len Remote Upper Len Remote Lower Scale Remote Upper Scale Remote Bit Data Remote Data Operators Federated Typename Federated Length Federated Scale Federated Bit Data
SQL_BIGINT - - - - - - BIGINT 8 - -
SQL_BINARY 1 254 - - - - BINARY - - Y
255 32672 - - - - VARBINARY - - Y
SQL_BIT1 - - - - - - SMALLINT1 2 - -
- - - - - - BOOLEAN1 1 - -
SQL_CHAR 1 254 - - - - CHAR - - N
255 32672 - - - - VARCHAR - - N
SQL_DATE - - - - - - DATE 4 - -
SQL_DECIMAL 1 31 0 31 - - DECIMAL - - -
32 38 0 38 - - DOUBLE 8 - -
SQL_DOUBLE - - - - - - DOUBLE 8 - -
SQL_FLOAT - 8 - - - - FLOAT 8 - -
- 4 - - - - FLOAT 4 - -
SQL_GUID 16 16 - - - - VARBINARY(16)
SQL_INTEGER2 - - - - - - INTEGER2 4 - -
- - - - - - BIGINT2 8 - -
SQL_LONGVARCHAR - - - - - - CLOB 2147483647 - N
SQL_LONGVARBINARY - - - - - - BLOB 2147483647 - Y
SQL_NUMERIC 1 31 0 31 - - DECIMAL - - -
32 32 0 31 - - DOUBLE 8 - -
SQL_REAL - - - - - - REAL 4 - -
SQL_SMALLINT3 - - - - - - SMALLINT3 2 - -
- - - - - - INTEGER3 4 - -
SQL_TIMESTAMP - - - - - - TIMESTAMP(6) 10 6 -
SQL_TIMESTAMP(p) - - - - - - TIMESTAMP(6) 10 6 -
SQL_TYPE_DATE - - - - - - DATE 4 - -
SQL_TYPE_TIME - - - - - - TIME 3 - -
SQL_TYPE_TIMESTAMP - - - - - - TIMESTAMP 10 - -
SQL_TINYINT - - - - - - SMALLINT 2 - -
SQL_VARBINARY 1 32672 - - - - VARBINARY - - Y
SQL_VARCHAR 1 32672 - - - - VARCHAR - - N
SQL_WCHAR 1 127 - - - - CHAR - - N
128 16336 - - - - VARCHAR - - N
SQL_WVARCHAR 1 16336 - - - - VARCHAR - - N
SQL_WLONGVARCHAR - 1073741823 - - - - CLOB 2147483647 - N
Note:
  1. SQL_BIT: If the federated data source is Netezza, PostgreSQL, Impala, Spark, Hive, Microsoft SQL Server, or Microsoft Azure, SQL_BIT maps to BOOLEAN; otherwise, it maps to SMALLINT.
  2. SQL_VARBINARY: If the federated data source is Microsoft SQL Server or Microsoft Azure, when the data source data type is sql_variant, you will need to alter the server with the following command: 
    alter server server_name OPTIONS(add CUSTOMIZED_ODBC_ATTRIBUTES ';DataTypeCompatibility=0');

    Once you have run the command, sql_variant will map to varbinary; otherwise, sql_variant can not be supported.

  3. SQL_INTEGER: If the federated data source is MySQL, SQL_INTEGER maps to BIGINT; otherwise, it maps to INTEGER.
  4. SQL_SMALLINT: If the federated data source is MySQL, SQL_SMALLINT maps to INTEGER; otherwise, it maps to SMALLINT.