Cross Database Queries using IBM Data Studio

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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>.

Method 2

I’m not familiar with DataStudio – their are other tools in the market like https://dbeaver.com/databases/ or https://www.dbvis.com.
Their is a workaround for the last mentioned tool
https://support.dbvis.com/support/solutions/articles/1000196586-can-i-access-tables-across-multiple-databases-within-one-statement
PS
I agree with – mustaccio – the best way is the setup federation –
depending on license issue’s
https://www.ibm.com/docs/en/db2/11.1?topic=editions-functionality-in-db2-product-db2-offerings

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply