Configuring ODBC access to MySQL data sources

To configure the federated server to access MySQL 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 MySQL data sources. The ODBC wrapper detects the ODBC driver and automatically configures the performance options.

Before you begin

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

Restrictions

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

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.

CREATE SERVER statement - Example for MySQL

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

CREATE SERVER mysqlserver
  TYPE mysql VERSION 5.6 OPTIONS (
    HOST 'mysqldev.ibm.com', PORT '5624', DBNAME 'CLNTS390')
  • The name (required) that you assign to the MySQL database server (in this case, "mysqlserver") 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 MySQL database that you want to access.
  • PORT specifies the port on which the MySQL database that you want to access is listening.
  • DBNAME specifies an alias for the database that you want to access.