• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2041
  • Last Modified:

How to reference a table from another database in your select statement?

I have a select statement in a user defined function created on one database. Inside the user defined function, i am referencing a table from a different database. In MS SQL you can just do databasename.dbo.tablename as long as its on the same server. How do you do this in DB2?
0
mchiber
Asked:
mchiber
  • 8
  • 5
  • 2
1 Solution
 
momi_sabagCommented:
exactly the same, as long as they are on the same server
0
 
mchiberAuthor Commented:
Well dbo. is unknown to DB2. What is that prefix in DB2? I tried prefixing table name with Schema name and with table space and it does not recogize it.
0
 
momi_sabagCommented:
you need to use the schema name,
if you don't know it, it might be your user name
you can query sysibm.systables in order to find it
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mchiberAuthor Commented:
I can see the schema name in the Control Center as ADMINISTRATOR. Name of the database is CL61DEV and table is called dimensions. but i keep getting this error

"CL61DEV.ADMINISTRATOR.DIMENSIONS" is an undefined name.  LINE
NUMBER=10.  SQLSTATE=42704

SQL0204N  "CL61DEV.ADMINISTRATOR.DIMENSIONS" is an undefined name.
0
 
mchiberAuthor Commented:
Also, querying sysibm.systables in the command editor only returns list of user and system tables in the current database that i am connected to.
0
 
momi_sabagCommented:
well, my first guess is that you don't have a select permission on that table
when you don't have select privilege, db2 reports an object not found error and since it is in the administrator schema, i believe this is the problem
0
 
mchiberAuthor Commented:
Nope. I am logged on to the server as Administrator. The database i am working has the same schema (ADMINISTRATOR). And if i change the database in command editor to point to the other database, i can select from that table.
0
 
momi_sabagCommented:
can you select from other tables in that database?
can you see that database in the control center?
0
 
mchiberAuthor Commented:
Yes and Yes.
0
 
momi_sabagCommented:
so it is either a permissions issue or a spelling mistake
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi mchiber,

Terminology may be an issue here.

Within an instance, databases are distinct entities.  If a query, running within one database needs access to data in another database, specific steps must be taken to allow the query to access the data.

Schemas, however, are simpler entities.  You can easily access data in another schema (within the same database) just by qualifying the table name with the correct schema name.

  SELECT * FROM schema1.table;


Kent
0
 
mchiberAuthor Commented:
I don't think so Momi.
I just tried this:
SELECT DimensionName
      FROM "CL61DEV.ADMINISTRATOR.DIMENSIONS"

and this time i got the error:

During SQL processing it returned:
SQL0204N  "ADMINISTRATOR.CL61DEV.ADMINISTRATOR.DIMENSIONS" is an undefined
name.  LINE NUMBER=10.  SQLSTATE=42704
So it looks like its prefixing it with administrator! Stupid DB2!
0
 
mchiberAuthor Commented:
Hi Kent,
I am trying to access a table in another database. So maybe i need to know what specific steps need to be taken for a query to access to data in another database.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi mchiber,

There may well be other ways to do this, but creating an ALIAS to the object is a sure-fire way to make things work out. The ALIAS creates an object in the current database that is a link to an object in another database.

CREATE ALAIS schema.name FOR database2.schema2.name2;

Then you can use "schema.name" in your query.

SELECT count(*) from schema.name;


Kent
0
 
mchiberAuthor Commented:
I will try that when i need it next. Right now we have abandoned the approach of having tables in two different databases. Thanks for your help.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 8
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now