Take the 2-minute tour ×
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's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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 '12 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 '12 at 17:08
    
@GabrielR. - by marking this the answer, I am assuming you didn't need federation then? –  Chris Aldrich Dec 11 '12 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 '12 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 '12 at 19:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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