Create IBM DB2 Federated Table in MySql-CodePudding

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?

CodePudding user response：

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.