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?
Best Answer
First you need to catalog the remote instance.
Issue
db2 terminate
to make sure the catalog entry remains.Now you can actually catalog the remote database.
And once again issue
db2 terminate
Here is an example of what this could look like:
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.