Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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>.
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
