0

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?

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

2
  • My understanding from your answer is that we first need to connect to a DB2 database instance. If that instance manages multiple databases, say DB_X and DB_Y then we could write a query that joins DB_X.Schema.Table to DB_Y.Schema.Table. Is my understanding correct? Thanks. – user137481 May 13 at 3:17
  • You don't connect to an instance; you connect to a database (managed by an instance). – mustaccio May 13 at 13:21
-1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.