3

I'm using DB2 9.7.0.3 for AIX.

I want to select data from a remote database, also running DB2 9.7.0.3 for AIX, and insert it into the local database I'm connected to.

I believe the query for this is 

INSERT INTO SCHEMA.TABLE 
    SELECT * FROM REMOTEDB.SCHEMA.TABLE

How do I define REMOTEDB as an alias to a remote database connection?

Improve this question

1 Answer 1

Reset to default
2

First you need to catalog the remote instance.

db2 catalog tcpip node <my alias node name> remote <host or host alias name> server <port>

Issue db2 terminate to make sure the catalog entry remains.

Now you can actually catalog the remote database.

db2 catalog db <remote database name> as <local alias> at node <node alias supplied above>

And once again issue db2 terminate

Here is an example of what this could look like:

db2 catalog tcpip node devinst1 remote devservera server 50100;
db2 terminate;
db2 catalog db crmdb as remotedb at node devinst1;
db2 terminate;

Now you can connect to that local database using either the db name (crmdb) or the alias name (remotedb) depending on your needs.

Note: Sometimes you can run into issues where it will say things about a null package id. This would mean that particular packages need to be bound for the connection to work. Easiest way to solve this is to have your instance owner id (of the remote database) make the first connection and then disconnect or to have your id you are using have BINDADD authority. Then the correct packages will get bound. You may not run into this, especially if you are on the same version and fix pack level of DB. If you ever differ between "client" and "server" is when you for sure have to do this.

Improve this answer
5
  • Cataloging the remote database works fine. However, trying to access tables in the remote database while connected to the local one results in this error: SQL0204N "REMOTEDB.SCHEMA.TABLE" Running db2 connect to REMOTEDB doesnt give any response.
    – Gabriel R.
    Dec 6, 2012 at 15:43
  • Ok....then I think you are getting into federation. Which edition of DB2 do you have? Enterprise Server Edition?
    – Chris Aldrich
    Dec 6, 2012 at 17:08
  • @GabrielR. - by marking this the answer, I am assuming you didn't need federation then?
    – Chris Aldrich
    Dec 11, 2012 at 15:34
  • We ended up using a different solution to our data transfer problem. We have many tables to transfer, some with a very large amount of data. So we did a export to ixf of the source database, followed by a ftp transfer of the ixf files, followed by a load into the target database.
    – Gabriel R.
    Dec 17, 2012 at 16:23
  • Makes sense. We do very similar for large transfers and are mostly using federation for stored procedures that need to access the data in two different databases.
    – Chris Aldrich
    Dec 17, 2012 at 19:17

Your Answer

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

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