Solved

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

Posted on 2008-10-29
16
2,027 Views
Last Modified: 2012-05-05
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
Comment
Question by:mchiber
  • 8
  • 5
  • 2
16 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22832565
exactly the same, as long as they are on the same server
0
 

Author Comment

by:mchiber
ID: 22832619
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22832632
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
 

Author Comment

by:mchiber
ID: 22832703
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
 

Author Comment

by:mchiber
ID: 22832731
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22832780
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
 

Author Comment

by:mchiber
ID: 22832905
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 37

Expert Comment

by:momi_sabag
ID: 22832921
can you select from other tables in that database?
can you see that database in the control center?
0
 

Author Comment

by:mchiber
ID: 22833035
Yes and Yes.
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22833067
so it is either a permissions issue or a spelling mistake
0
 
LVL 45

Expert Comment

by:Kdo
ID: 22833143
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
 

Author Comment

by:mchiber
ID: 22833160
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
 

Author Comment

by:mchiber
ID: 22833180
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
 
LVL 45

Accepted Solution

by:
Kdo earned 35 total points
ID: 22833430
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
 

Author Closing Comment

by:mchiber
ID: 31511232
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now