Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2005
    Posts
    8

    Unanswered: select * from anotherDB.someTable

    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
    ================================================== ==========

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Mar 2005
    Posts
    8

    thanks

    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

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •