How to query MariaDB data source using Federation Server through JDBC driver

Created by Hai Jun Shen on Mon, 12/09/2019 - 02:51
Published URL:
https://www.ibm.com/support/pages/node/1126623
1126623

Question & Answer


Question

How to query MariaDB data source using Federation Server through JDBC driver?

Cause

Customers want to use Federation server connect to MariaDB through JDBC driver?

Answer

To configure a federation to access MariaDB data source through JDBC wrapper, you need to provide 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 MariaDB data source.

Before you begin

Download the jdbc connector mariadb-java-client-x.x.x.jar file from MariaDB official site, and put it in your local path.

1. Enable Federation server and restart Db2.

# db2 update dbm cfg using federated YES

# db2stop force

# db2start

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

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

# telnet 9.30.140.105 3306
Trying 9.30.140.105...
Connected to 9.30.140.105.

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

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

# connect to testdb

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

# create server server1 type mariadb wrapper "wrapper1" options (driver_class  'org.mariadb.jdbc.driver' ,driver_package  '/home/hotellnx116/haijs/fed_mysqljdbc0228/mariadb-java-client-2.4.0.jar', url  'jdbc:mariadb://9.30.140.105:3306/mysql')

# create user mapping for user server server1 options(remote_authid 'mysql',remote_password 'passw0rd')

# create nickname nk1 for server1."test1"

# select * from nk1

col1                 col4
-------------------- ----------
                   1 hai
                   3 hai
                   4 hai
                   5 hai
  4 record(s) selected.

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PkvAAE","label":"Federation"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
06 August 2020

UID

ibm11126623

Page Feedback