I have two database server one is mysql another is db2 both are running on different machine.I want to fetch records from tables from both the database by using a join.i have studied about linked server concept but the problem is i couldnt find any example for creating a linked server with db2(all i can find is SSMS i.e use Sql Server Mannagement Studio for creating linked server) but mine is case is of mysql and db2 and i need to create a linked server to one of them/vice versa.
Please suggest some help how can i achieve this.
Thanks in advance!
In DB2, there is a feature called federation (part of Information Integration), that allows you to present external resources to DB2 (wrapper and nickname); you can query those external resources from DB2, and even you can do joins between different sources (Other DB2 databases, Informix | MSSQL server | Oracle | MySQL databases, flat files, etc.)
In order to query external resources, this feature requieres a special licence. Instead, if you want to query other DB2 or informix databases, this feature does not requiere extra license because it is included as free (these are the IBM databases).
In order hand, there is an option called table functions. These functions return a table when they are called, and then, you can join the returned data with other table. These functions can be developed in SQL PL (IBM procedure language), C or Java.
With this second option, you can create a table function in Java, that queries the MySQL table, and then returns the data to DB2.
I have written an example about how to query a 'topic' in Twitter, and return that data to DB2. You have to do almost the same, but instead of querying Twitter, you configure your other database.
http://angocadb2.blogspot.fr/2012/02/accediendo-tweeter-desde-db2-table.html
@AngocA it doesnt work but thanx for ur suggestion .
After a long search i myself come up with an answer for the above self post and thought of posting it here as it will be helpful for others in case of any combination of scenario where we need to fetch data from two different database server which r remote/local in nature and when linked server concept fails.
We may use a third party jar called as Unity Jdbc which we can use in our java code in simple manner for loading driver then getting connection same like old jdbc.
1)Load driver like thisClass.forName("unity.jdbc.UnityDriver");
2) Get connection like this DriverManager.getConnection(jdbc:unity://test/xspec/mysqldb2.xml);
3) Get Records(DDL/DML)
4)Close Connection
one can visit Unity Jdbc http://www.unityjdbc.com/
Using this jdbc in our code we actualy load an xml based file which has definition of desired datasource of our requirement.
once everything is set one can then easily form a query from two different tables from two different remote databases. syntax : dbname.tablename.fieldname
Addingly we dont need to handle any further xml configuration for closing internal connection created after closing the outer actual connection.
Any issues write revert back.
