Question & Answer
Question
How to query MySQL Community Edition by using Db2 Federation through ODBC driver?
Answer
Before You Begin
NOTE:
1. Check db2level, this feature is supported from Db2 11.5.5, only supports linuxamd64 platform.
2. Enable Federation feature and restart Db2 to make it effective.
$ db2 update dbm cfg using federated YESPre-Configuration on linux, unix
1. Make sure the required ODBC drivers are installed correctly. MySQL Community Edition server uses the same ODBC connector as MariaDB.
$ ls ~/sqllib/federation/odbc/lib/libmaodbc.so2. Make sure odbcinst.ini has [MARRIADB] node in ~/sqllib/cfg/odbcinst.ini.
example:
[db2inst1@snore1 cfg]$ cat /home/db2inst1/sqllib/cfg/odbcinst.ini
[ODBC]
InstallDir=/home/db2inst1/sqllib/federation/odbc
[MARIADB]
Driver=/home/db2inst1/sqllib/federation/odbc/lib/libmaodbc.so
DriverUnicodeType=1
DLOpenMode=103. Make sure the data source is accessible to Federation server.
$ telnet bye1.fyre.ibm.com 3306
Trying 9.30.230.97...
Connected to bye1.fyre.ibm.com.
Escape character is '^]'.4. Create server, user mapping, nickname, and query the nickname.
NOTE:
4.1 the server type must be mysql_ce.
4.2 You need to specify the corresponding codepage server options of character set of remote MySQL DB
create server SERVER1 type mysql_ce version 8.0 OPTIONS(host 'bye1.fyre.ibm.com', port '3306', dbname 'mysql', codepage '1208')
create user mapping for user server SERVER1 options (REMOTE_AUTHID 'root', REMOTE_PASSWORD 'password!')
create nickname n1 for SERVER1."REMOTE_TABLE1"
select count(*) from n1
1
-----------
1
1 record(s) selected.For Windows Db2 server configuration, refer to How to connect to MySQL community edition server by using Windows Db2 Federation server
For more information about codepage, refer to What is Db2 federation CODEPAGE server option? And Supported territory codes and code pages
[{"Type":"SW","Line of Business":{"code":"","label":""},"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSCAVPX","label":"Federated Server"},"ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)"}]
Was this topic helpful?
Document Information
More support for:
Federated Server
Software version:
All Version(s)
Document number:
6450735
Modified date:
28 February 2022
UID
ibm16450735
Manage My Notification Subscriptions