Create IBM DB2 Federated Table in MySql

Question

I'm working on a project where I need to join data from a MySQL database table with data from an IBM DB2 database table. Since these are two different data sources in my Java project, there is no real easy way to join this data.

Searching, I read about federated tables. So I'm trying to create a table in my MySQL database that connects back to the IBM DB2 table.

So far I've tried doing this via a connection string

create table UGFPEV00
(
    EID        CHAR(4)      default ' '               not null,
    EDESCR     CHAR(25)     default ' '               not null,
    EGROUP     CHAR(1)      default ' '               not null,
    ESTATUS    CHAR(1)      default ' '               not null,
    EUSERID    VARCHAR(10)  default ''                not null,
    ETSTAMP    TIMESTAMP(6) default CURRENT_TIMESTAMP not null,
    EMODID     VARCHAR(10)  default ''                not null,
    EMODTSTAMP TIMESTAMP(6) DEFAULT 0                   not null
)
ENGINE=FEDERATED
CONNECTION='mysql://tomcat:*****@10.*****.21';

But this responds with

SQL Error (1432): Can't create federated table. The data source connection string 'mysql://tomcat:*****@10.*****.21' is not in the correct format

I also tried going the CREATE SERVER route but this produces a different error.

CREATE SERVER PGPRD
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'tomcat', PASSWORD '*****', HOST '10.64.7.21', DATABASE '%');

create table UGFPEV00
(
    EID        CHAR(4)      default ' '               not null,
    EDESCR     CHAR(25)     default ' '               not null,
    EGROUP     CHAR(1)      default ' '               not null,
    ESTATUS    CHAR(1)      default ' '               not null,
    EUSERID    VARCHAR(10)  default ''                not null,
    ETSTAMP    TIMESTAMP(6) default CURRENT_TIMESTAMP not null,
    EMODID     VARCHAR(10)  default ''                not null,
    EMODTSTAMP TIMESTAMP(6) DEFAULT 0                   not null
)
ENGINE=FEDERATED
CONNECTION='PGPRD/ugfpev00';

The response from this is

SQL Error (1434): Can't create federated table. Foreign data src error:  database: '%'  username: 'tomcat'  hostname: '10.*****.21'

Am I completely missing the point of federation and federated objects? Or is what I'm doing not supported in MySQL?

You can only link with other MySQL databases using `FEDERATED`, not other databases. — Barmar, Sep 15 '22 at 17:00
@Barmar, Thank you. That was unclear in the documentation. It looks like I can do it from the IBM side, but that would require a restart of DB2 which would be unacceptable. — Paul Stoner, Sep 15 '22 at 17:03
Yes, Db2-LUW can federate to mysql (with appropriate configuration and licensing). But not vice versa. — mao, Sep 15 '22 at 17:05

score 3 · Accepted Answer · answered Sep 15 '22 at 17:03

https://dev.mysql.com/doc/refman/8.0/en/federated-storage-engine.html

The FEDERATED storage engine lets you access data from a remote MySQL database...

Note it does not allow you to federate to a DB2 database, or any other brand.

Also https://dev.mysql.com/doc/refman/8.0/en/federated-usagenotes.html confirms this:

The following items indicate features that the FEDERATED storage engine does and does not support:

The remote server must be a MySQL server.

Veer · Answer 2 · 2023-08-01T09:03:45.573

just use this command CREATE SERVER 'servername' FOREIGN DATA WRAPPER mysql OPTIONS (USER 'username', HOST 'IPaddress', DATABASE 'databasename', PORT 3306,Password 'yourpassword');

and then run this command: DROP TABLE IF EXISTS tablename; CREATE TABLE tablename ( Id int(11) DEFAULT NULL, Name varchar(255) DEFAULT NULL ) ENGINE=FEDERATED CONNECTION='servername/TableName';

Note:federated table and Actual table must be same name and table structure must be same for ensuring check table create statement show create table 'table name'

Create IBM DB2 Federated Table in MySql

2 Answers2