CODEWITHSUNDEEP
mysqldb2federation
Paul Stoner
Paul Stoner

Reputation: 1512

Create IBM DB2 Federated Table in MySql

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?

Upvotes: 1

Views: 171

Answers (2)

Veer
Veer

Reputation: 1

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'

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562721

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.

Upvotes: 3

Related Questions