Configuring access to ODBC data sources

To configure the federated server to access ODBC data sources, you must provide the federated server with information about the data sources and objects that you want to access.

Before you begin

  • Ensure that the following variables are specified in the db2dj.ini file:
    • System environment variables that are required by your data source client. To determine which variables are required, refer to the documentation provided by your data source client. For example, if you need to specify run-time libraries (this is the case for aNetezza® client), you will need to specify the following environment variables:
      • For a client running on an AIX® operating system, LIBPATH
      • For a client running on a Windows operating system, PATH
      • For a client running on Linux® or Solaris operating system, LD_LIBRARY_PATH
    Note: If you configure system environment variables or modify the db2dj.ini file, to make them become effective you must stop and restart the Federation component.

About this task

Restrictions

  • The ODBC wrapper cannot be used to access any Db2® family data sources. Use the DRDA wrapper to access Db2 family data sources.
  • 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.