How to query MariaDB using Db2 Federation through ODBC driver?

To configure federation to access MariaDB 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 MariaDB ODBC support begins since db2 v111m3fp3

1. Enable Federation server and restart Db2.

# db2 update dbm cfg using federated YES

# db2stop force

# db2start

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

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

# telnet 9.30.244.223 3306
Trying 9.30.244.223...
Connected to 9.30.244.223.
If the connection fails, you will receive an error, and please check the status of the MariaDB server.

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

# connect to testdb

# create server server1 type mariadb version 6.0 OPTIONS(host '9.30.244.223', 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.