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
- The ODBC driver must be installed and configured on the server that acts as the federated server.
- Federation must be installed on the server that acts as the federated server.
- Check the federated parameter to ensure that federation is enabled.
- Ensure that the following variables are specified in both your
system environment and in the db2dj.ini file:
- The DB2LIBPATH variable of the Db2® Profile Registry (db2set)
- 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.