Solved

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

Posted on 2008-10-29
16
2,033 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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DB2 Drivers Windows Server - Database port number 4 455
Problem to script 14 205
Exposing an EAV database model as a mart for visualisation 9 134
DB2 V9.7 restore 5 59
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Suggested Courses

739 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