Solved

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

Posted on 2008-10-29
16
2,030 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

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
 
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:Kent Olsen
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:
Kent Olsen 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
OPENQUERY INSERT not recognizing linked server 12 567
how to apply the SSL on iseries and WAS application 1 127
SELECT * FROM [Stored Procedure] 6 102
DB2 return first match 3 115
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…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

808 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