Solved

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

Posted on 2008-10-29
16
2,034 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

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 (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…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

705 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