IBM Data Studio – Cross Database Queries

db2db2-luw

I'm currently using IBM Data Studio 4.1 to run queries against DB2 LUW database(s).
My understanding is that I can do cross database queries by prefixing the database(s) names.

For example, database.schema.table.

However, when I open a SQL Query window, there is a dropdown that requires the user to select the database connection. Since it only allows one database connection, how do we run cross database queries in IBM Data Studio?

Best Answer

For you to execute any SQL statement you have to be connected to some database server; Data Studio does not execute SQL statements. Subsequently, for the three part name (<server>.<schema>.<object>) to work the server you're connected to has to know what the <server> part is.

In the simple case of accessing objects in a database that belongs to the same Db2 for LUW database instance, <server> is the other database name, and no additional setup is required.

However, if the other table is in a database managed by a different instance, or if it belongs to a different DBMS (Db2 for z/OS, Oracle, etc.), you will need to set up a federated data source, whose name you will then use for <server>.

Related Question