Configuring access to data sources of type 1

For this type of ODBC data source, you need not download the ODBC driver because it will be installed by default. Also, configuration of data source is not required for using it (for DSN-less mode), and data source support is optimized. Currently, this type includes these data sources: Apache Hive, Spark, Cloudera Impala, Microsoft SQL Server, Microsoft Azure, MySQL, Oracle, Pivotal Greenplum, PostgreSQL, IBM PureData System for Analytics (formerly Netezza), MariaDB (only supported on Linux platform).

About this task

Restrictions

  • The ODBC wrapper does not support the following functions and statements:
    • Two-phase commit transactions
    • LOCK TABLE statements on nicknames
    • Features deprecated in ODBC 3.x
    • X/Open or SQL/CLI drivers
    • Stored procedure nicknames
    • Statement-level atomicity enforcement using remote savepoint statements
    • WITH HOLD cursors
  • For data sources that do not support positioned update and delete operations, positioned UPDATE and DELETE statements and certain searched UPDATE and DELETE statements on a nickname will fail if a unique index on non-nullable columns does not exist on the nickname or its corresponding remote table. The error SQL30090 with the reason code 21 is returned when these statements fail.
  • The ODBC wrapper does not support INSERT, UPDATE, or DELETE statements against data sources that restrict the number of active statements for each connection. Consult the documentation for your data source to determine if the data source restricts the number of active statements for each connection. One of the ODBC data sources that this restriction applies to is IBM® Red Brick® Warehouse.
  • The ODBC wrapper does not support operations on tables that contain columns with data types that use driver-specific SQL data type indicators. The type of operations that are not supported included the CREATE NICKNAME and SELECT statements in the pass-through mode. The ODBC wrapper supports only the SQL data type indicators that are defined by the ODBC standard in the Microsoft ODBC Programmer's Reference.

The data sources that are accessed through the ODBC API are referred to in this text as ODBC data sources.

Recommendation: For the following data sources, use a specialized wrapper instead of the ODBC wrapper: This will result in better performance and, in some cases, more functionality.

Procedure

  1. Register the ODBC wrapper.
  2. Register the server definitions for an ODBC data source.
  3. Create a user mapping for an ODBC data source.
  4. Test the connection to the ODBC data source server.
  5. Register nicknames for ODBC data source tables and views.