12

We recently migrated a large DB2 database to a new server. It got trimmed a lot in the migration, for instance 10 years of data chopped down to 3, to name a few. But now I find that I need certain data from the old server until after tax season.

How can I run a UNION query in DBeaver that pulls data from two different connections..? What's the proper syntax of the table identifiers in the FROM and JOIN keywords..?

I use DBeaver for my regular SQL work, and I cannot determine how to span a UNION query across two different connections. However, I also use Microsoft Access, and I easily did it there with two Pass-Through queries that are fed to a native Microsoft Access union query.

But how to do it in DBeaver..? I can't understand how to use two connections at the same time.

For instance, here are my connections:

enter image description here

And I need something like this...

SELECT      *
FROM        ASP7.F_CERTOB.LDHIST 
UNION
SELECT      *
FROM        OLD.VIPDTAB.LDHIST

...but I get the following error, to which I say "No kidding! That's what I want!", lol... =-)

SQL Error [56023]: [SQL0512] Statement references objects in multiple databases.

How can this be done..?

Improve this question
1
  • on the iseries maybe CRTDDMF point to old tables. Good Luck. Commented Dec 11, 2018 at 20:50

2 Answers 2

Reset to default
10

This is not a feature of DBeaver. DBeaver can only access the data that the DB gives it, and this is restricted to a single connection at a time (save for import/export operations). This feature is being considered for development, so keep an eye out for this answer to be outdated sometime in 2019.

You can export data from your OLD database and import it into ASP7 using DBeaver (although vendor tools for this are typically more efficient for this). Then you can do your union as suggested.

Many RDBMS offer a way to logically access foreign databases as if they were local, in which case DBeaver would then be able to access the data from the OLD database (as far as DBeaver is concerned in this situation, all the data is coming from a single connection). In Postgres, for example, one can use a foreign data wrapper to access foreign data.

I'm not familiar with DB2, but a quick Google search suggests that you can set up foreign connections within DB2 using nicknames or three-part-names.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Hmmm, I'll have to ponder that. The problem is the locations of the two severs. The old server a private one on our LAN, and the new server is a hosted cloud-based service with multiple connection types, one of which is VPN for my SQL work. So the foreign connection might get complicated. I could place it on the old server, and connect to the new one, but if (when) the old server goes away, so would the connection. BUT...you did give me the idea of copying the old table to the new server, and I think that would be best. I'll just call it LDHIST_OLD and that should take care of it. Thanks.
-2

If you check this github issue:

https://github.com/dbeaver/dbeaver/issues/3605

The way to solve this is to create a task and execute it in different connections:

https://github.com/dbeaver/dbeaver/issues/3605#issuecomment-590405154

SQL Script execute settings

Improve this answer

1 Comment

The OP asked for a way to execute a join query on two different connections. This only allows to execute a script/query on two (or more) connections. There's no join involved.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.