MySql - Connect to an external database table

Question

I have two database connected to the same network (but different servers), one in MySql and one in DB2 (AS/400). From a Windows client application i'm developing, i need to connect to tables located on both database, but i want the client to have access only to the MySql server.

I need in MySql to have a "replica" of some table located in the DB2 database, so that mysql act as a "bridge" between client and db2. I'm trying with federated tables but it seemed that it works only between two mysql database. I can access to the db2 database through ODBC driver, if it helps.

To better explain:

Now:
    C        MySql         DB2
    L ------ table1
    I ------ table2
    E                      table3
    N                      table4
    T

I need:
    C        MySql         DB2
    L ------ table1
    I ------ table2
    E ------ table3 ------ table3
    N ------ table4 ------ table4
    T                 ^
                      |___ How?

Referring the schema, everytime time a client try to query 'table3' in MySql, the MySql server should first query DB2 for table3, then return the table to the client. Is it possible with MySql?

Shadow Shadow · Accepted Answer · 2016-02-18T17:13:23

No, there is no such functionality in mysql that would allow you to connect to another type of db. Federated table engine can connect to a remote mysql db only.

You have to look into external tools (mysql proxy, mysql datacontroller?) to achieve the desired results. If there are any tools that support connection to DB2 at all.

Technically, you can create your own table engine building on the existing federated table engine that can connect to a DB2 database, but this seems an overkill for a project.

You may have to implement a kind of an application server, that can connect to both databases and the clients would only be in touch with the application server layer.

MySql - Connect to an external database table

1 Answers