Configuring ODBC access to PostgreSQL data sources

To configure the federated server to access PostgreSQL data sources, you must provide the federated server with information about the data sources and objects that you want to access. The ODBC wrapper is optimized to access PostgreSQL data sources. The ODBC wrapper detects the ODBC driver and automatically configures the performance options.

Before you begin

The PostgreSQL ODBC driver must be configured on your federated server.

For prerequisites that apply to all ODBC data sources, see Configuring access to ODBC data sources.

Restrictions

  • nnstat method 1 is not supported for PostgreSQL. There is no error when using method 1, but no data will be returned.
  • Transparent DDL is not supported for PostgreSQL.
  • IUD is not supported for PostgreSQL.

Additional restrictions apply to all ODBC data sources. See Configuring access to ODBC data sources.

Procedure

  1. Use one of the following methods to prepare the federated server and federated database:
  2. You might need to add ODBC library directory to the DB2LIBPATH environment variable so that the client libraries load properly. Run the db2set command to set the DB2LIBPATH environment variable and specify the installation directory of the ODBC library: 
    db2set DB2LIBPATH="odbc_library_directory"

    where odbc_library_directory is the directory of the ODBC library.

  3. For Linux, and UNIX operating systems, the following items must be configured on your federated server:
    • Add the data source client library directory to DB2LIBPATH in db2dj.ini:

      DB2LIBPATH=/<odbc_library_directory>

      You must restart the Db2® instance to make the changes effective.

    • Create a symbolic link for odbc.ini:

      ln -s $ODBCINI $HOME/.odbc.ini

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

CREATE SERVER statement - Example for PostgreSQL

The following example shows you how to register a server definition for PostgreSQL by using the CREATE SERVER statement:

CREATE SERVER psqlserver
  TYPE postgresql VERSION 9.2 OPTIONS (
    HOST 'pgsqldev.ibm.com', PORT '5432', DBNAME 'CLNTS390')
  • The name (required) that you assign to the PostgreSQL database server (in this case, "psqlserver") must be unique.
  • The TYPE clause specifies the type of data source server to which you are configuring access.
  • HOST (required) specifies the fully qualified domain name, host name alias, or IP address of the host for the PostgreSQL database that you want to access.
  • PORT specifies the port on which the PostgreSQL database that you want to access is listening.
  • DBNAME specifies an alias for the database that you want to access.