Join tables in different database

I can only read the tables in our company's DB2 database, can not import local spreadsheet. Also I have another database on my local drive which I can edit. Can I write queries to join tables in these two different database? If that is not possible, what is a recommended way to handle data in different database?

Thanks for your answer.

Hi Vincent,

What happens when you try to edit tables in the company DB2 database? Do you get any error messages? Do you have the privileges needed to modify the tables?

I don't think DB2 natively supports joining tables in two databases, but please see this article for how to use an H2 database as an intermediate to accomplish this:

https://support.dbvis.com/solution/articles/1000196586-can-i-access-tables-across-multiple-databases-within-one-statement-

Best Regards,

Hans

Thanks for your answer, Hans.

Just checked the error code, and verified that I don't have the privileges to import tables or modify the tables. Here is the scenario, I can visit the database on server which has large datasets, but can not modify it. Then my boss send me a spreadsheet with ~100 rows, and I need to write a query to join the list and the table on server to get the desired features for each row. I have a local accdb and build the connection so that I can visit and import the spreadsheet into my accdb. But the problem is I can not write a query to join tables in two databases. Any solutions for that?

For the H2 data link, how is the efficiency of the joining? my concern is the db on server is huge (~20 million rows) that H2 joining may cause out of memory.

Thank you,

Vincent

Hi Vincent,

You may very well run into memory issues with the H2 work-around, but it is the only thing I can think of since DB2 does not offer a native solution. But give it a try, it may work.

Other than that, you may want to talk to the DBA for the company database to see if you can get privileges in a private schema where you can import the spreadsheet and then join that table with the large table.

Best Regards,

Hans 

Thanks, Hans. Will try.