select * from anotherDB.someTable

bfmaclean · #1 (**permalink**) 03-03-05, 11:05

I'm a SQL Server and Oracle guy and new to DB2. I have 4 databases (dbA, dbB, dbC, dbD) that are all on the same schema (s1). When connected to database dbA I want to be able to get data out of dbB. The documentation says that this should work:

SELECT * FROM S1.TBL_HOURS

But I get this error.

SQL0204N "S1.TBL_HOURS" is an undefined name. SQLSTATE=42704

The error sometimes means there is a problem with the table name case. I don't think case is an issue (cause I've tried double quotes and many possible upper/lower case options). I have migrated this data from SQL Server so maybe there's a problem with that.

Any insights would be appreciated.

Blaise
================================================== ==========
About DB2 Administration Tools Environment
================================================== ==========
DB2 administration tools level:
Product identifier SQL08020
Level identifier 03010106
Level DB2 v8.1.7.445
Build level s040812
PTF WR21342
================================================== ==========
Java development kit (JDK):
Level IBM Corporation 1.4.1
================================================== ==========

n_i · #2 (**permalink**) 03-03-05, 11:29

Quote:

Originally Posted by bfmaclean

I have 4 databases (dbA, dbB, dbC, dbD) that are all on the same schema (s1). When connected to database dbA I want to be able to get data out of dbB.

A schema is a part of a database, not the other way around. If I understand you correctly, here's what you've got:

Code:

create database dbA ... ;
connect to dbA ...;
create table s1.tbl_hours ... ;

create database dbB ... ;
connect to dbB .. ;
create table s1.tbl_minutes ...;
select * from s1.tbl_hours;

If that's the case you will need to read about federated databases. Search this forum for that topic, which has been discussed umpteen times.

bfmaclean · #3 (**permalink**) 03-03-05, 11:37

thank you for the reply, I will check out federated databases.

I said that all of the databases were on the same schema because when I look at any of the tables in any of the 4 databases the schema is always the same. I assumed the schema was an owner thing. So saying all 4 dbs are on the same schema would be the same as saying all 4 dbs are owned by the same owner. Just curious, no reply really needed.

Blaise

n_i · #4 (**permalink**) 03-03-05, 12:49

No that wouldn't be the same. Saying "all 4 dbs are on the same schema" is like saying "all houses are on the same street". I bet every city and town in North America has a Main Street; however, it doesn't mean that it's the same street. The street name repeats across different cities but these are different streets.

Internet Services

Web Development

Digital Marketing

Consumer Tech