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.
Best Answer
H
Hans Bergsten
said
over 7 years ago
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.
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:
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
Hans Bergsten
said
over 7 years ago
Answer
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.
Vincent Wang
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,
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
- Oldest First
- Popular
- Newest First
Sorted by PopularHans Bergsten
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
Vincent Wang
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
Hans Bergsten
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
Vincent Wang
Thanks, Hans. Will try.
-
Forward Engineering
-
DB2 Database Connection Dropping every 20-30mins or so ?
-
roles are not unfolded
-
Install4j: Java(Tm) VM could not be started
-
Worksheet name default when exporting to Excel?
-
sqlformatter and extract
-
Bug with empty password
-
dbvis 5.0 gives error after opening
-
Saving file makes it incompatible with other SQL tools!!!?!?!?!?
-
Error Schema Name in References view.
See all 1311 topics