How to query MySQL Community Edition server using Db2 Federation through ODBC driver?

To configure federation to access MySQL CE server through ODBC wrapper, you need to provide the information of the data source and objects that you want to access to for creating server, user mapping and nickname at the Federation side.

Before you begin

You need to check Db2 version by using db2level command,  as Federation MySQL CE ODBC support begins since db2 v115m5fp0.

1. Enable Federation server and restart Db2.

# db2 update dbm cfg using federated YES

# db2stop force

# db2start

2. Test the connection to the MySQL CE server and verify the service is started correctly.

telnet <MySQL_community_server_ip> <port>
If the connection is successful, you will  receive the following similar output from the command.

# telnet bye1.fyre.ibm.com 3306
Trying 9.30.230.97...
Connected to bye1.fyre.ibm.com.
If the connection fails, you will receive an error, and please check the status of the MySQL CE server.

3. Create server, user mapping, nickname, and query the nickname. For MySQL CE server you need to specify the server type “MYSQL_CE” while you are creating the server.

# connect to testdb

# create server server1 type mysql_ce version 8.0 OPTIONS(host 'bye1.fyre.ibm.com', port '3306', dbname 'mysql')

# create user mapping for user server server1 options (remote_authid 'root', remote_password 'Passw0rd!')

# create nickname nk1 for server1."tb1"

# select * from nk1

col_1

--------------------------

1970-11-23-12.12.12.000000

1999-12-31-12.12.12.000000

  2 record(s) selected.