How to query MySQL Enterprise Edition data source using Federation Server through JDBC driver

To configure a federation to access MySQL Enterprise Edition data source through JDBC wrapper, you need to provide the information about the data sources and objects that you want to access. Then create wrapper, server, user mapping and nickname about the tables on remote MySQL Enterprise Edition data source.

Before you begin

Check the driver FOmysql.jar in the path $/INSTANCE_HOME/sqllib/federation/jdbc/lib.

1. Enable Federation server and restart Db2.

# db2 update dbm cfg using federated YES

# db2stop force

# db2start

2. Test the connection to the MySQL Enterprise Edition data source and verify the service is started correctly.

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

# telnet test1.fyre.ibm.com 3306
Trying 9.111.139.11...
Connected to test1.fyre.ibm.com.

If the connection fails, you will receive an error, please check the MySQL service status.

3. Create wrapper, server, user mapping, nickname, and query the nickname. 

(1) For Db2 on Linux or Unix platform
 

# connect to testdb

# create wrapper "wrapper1" library 'libdb2rcjdbc.so' options(db2_fenced 'y')

# CREATE SERVER server1 TYPE MYSQL WRAPPER "wrapper1" OPTIONS (DRIVER_CLASS  'com.ibm.fluidquery.jdbc.mysql.MySQLDriver' ,DRIVER_PACKAGE  '/home/hotellnx116/haijs/sqllib/federation/jdbc/lib/FOmysql.jar' ,JDBC_LOG  'Y' ,URL  'jdbc:ibm:mysql://test1.fyre.ibm.com:3306;DatabaseName=testdb' ); 

# create user mapping for user server server1 options(REMOTE_AUTHID 'user',REMOTE_PASSWORD 'password')
# 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.

(2) For Db2 on windows platform

db2 => connect to sample

   Database Connection Information

 Database server        = DB2/NT64 11.5.6.0
 SQL authorization ID   = ADMINIST...
 Local database alias   = SAMPLE

db2 => create server server1 type jdbc version 8 OPTIONS(DRIVER_PACKAGE 'C:\Program Files\IBM\SQLLIB_04\federation\jdbc\lib\FOmysql.jar', DRIVER_CLASS 'com.ibm.fluidquery.jdbc.mysql.MySQLDriver', URL 'jdbc:ibm:mysql://test1.fyre.ibm.com:3306;databaseName=testdb')

db2 => CREATE USER MAPPING FOR user SERVER server1 OPTIONS (REMOTE_AUTHID 'root' ,REMOTE_PASSWORD 'password')

db2 => set passthru server1

db2 => create table test1(col1 int, col2 varchar(20))

db2 => insert into test1 values(1, 'haijunshen')

db2 => set passthru reset

db2 => create nickname nk_test1 for server1."test1"

db2 => select * from nk_test1

col1                 col2
-------------------- --------------------
                   1 haijunshen

  1 record(s) selected.