Unanswered: DB2 How to query database in same instance
I have two databases in the same instance, DATABASE_A and DATABASE_B, on a Windows 2003 server (DB2 V8.1). According to the documentation and forum discussions I have found, if I connect to DATABASE_A, I should be able to issue a query like
SELECT COUNT(*) FROM DATABASE_B.USER.TABLE_B;
where USER is the schema in DATABASE_B for TABLE_B (and in fact is the same user I used to connect to DATABASE_A).
However, I get the error
DBA2191E SQL execution error.
database manager error occurred. : [IBM][CLI Driver][DB2/NT]
SQL0204N "DATABASE_B.USER.TABLE_B" is an undefined name.
What do I have to do to get SQL to recognize tables in DATABASE_B when I am connected to DATABASE_A in the same instance?
You can only process tables/views etc in the database you are currently connected to. If you want to access tables in another database you need to
- create a federated database server;
- create a nickname for the remote table; or
- connect to the other db before processing it.
Which is appropriate for you will depend on your circumstances.