473,695 Members | 2,404 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL error 204 when we try to access tables via alias

Hi. We have almost all our tables defined on library lib1 and some on
lib2. We have alias defined on lib1 to access tables on lib2 so there
is no need to qualify library name. Alias for tables on lib2 are
defined this way:

CREATE ALIAS lib1.table1 FOR lib2.table1;

Both table owner and alias owner is the same. Tables reside on a
AS/400 server and we are accesing database from a DB2 client on a PC
workstation (DB2 is version 7)

When we try to access one of these tables (for example select * from
table1) we get error

SQL0204N "OWNER .TABLE1" is an undefined name. SQLSTATE=42704

Where OWNER is the name of the table/alias owner.

We have no problem if we qualify the library name where alias is
defined (select * from lib1.table1). There is no need to qualify
library name for tables on lib1.

Any ideas?

Thanks in advance.
Nov 12 '05 #1
5 9648
Hi,
Is enough privileges availables for both the libraries to access the
other library's object?

Cheers,
Thiru

Nov 12 '05 #2
Santiago Ordax Solivellas wrote:
Hi. We have almost all our tables defined on library lib1 and some on
lib2. We have alias defined on lib1 to access tables on lib2 so there
is no need to qualify library name. Alias for tables on lib2 are
defined this way:

CREATE ALIAS lib1.table1 FOR lib2.table1;

Both table owner and alias owner is the same. Tables reside on a
AS/400 server and we are accesing database from a DB2 client on a PC
workstation (DB2 is version 7)

When we try to access one of these tables (for example select * from
table1) we get error

SQL0204N "OWNER .TABLE1" is an undefined name. SQLSTATE=42704

Where OWNER is the name of the table/alias owner.

We have no problem if we qualify the library name where alias is
defined (select * from lib1.table1). There is no need to qualify
library name for tables on lib1.

Any ideas?


I don't quite understand your question. A table is uniquely identified by
its schema name, combined with the table name. If no explicit schema name
is given, then DB2 will consult a so-called special register named CURRENT
SCHEMA. Per default, the value in that special register is set to the name
of the user who connected to the database.

In the above examples, the schema name used to find the tables is OWNER,
lib1, or lib2. Do you actually want to do away with the schemas
alltogether?

Personally, I'd recommend to _always_ qualify your table names explicitly
with schema names. I don't see a good reason speaking against it, and it
does make things clearer.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #3
To Thiru:
Hi,
Is enough privileges availables for both the libraries to access the
other library's object?

Cheers,
Thiru
Well, if you are talking about the privileges granted to the users
that have the same name as the library, lib1 user has enough
privileges on lib2 library. There is no lib2 user.

To Knut:

Knut Stolze <st****@de.ibm. com> wrote in message > I don't quite understand your question. A table is uniquely identified by its schema name, combined with the table name. If no explicit schema name
is given, then DB2 will consult a so-called special register named CURRENT
SCHEMA. Per default, the value in that special register is set to the name
of the user who connected to the database.

In the above examples, the schema name used to find the tables is OWNER,
lib1, or lib2. Do you actually want to do away with the schemas
alltogether?

Personally, I'd recommend to _always_ qualify your table names explicitly
with schema names. I don't see a good reason speaking against it, and it
does make things clearer.


We are using an user with the same name as the library lib1, so the
user in the above examples is lib1.

We are developing and testing or programs on PC workstations and
accessing database on a AS/400 host via DB2 connect. Once the programs
are finished they are generated to host where they run. We do not
qualify table names on our programs cause we want them to use the
database library the AS/400 user has defined in his library list.

There is no problem with alias once the programs are running on the
AS, our problem is when accessing via DB2 connect with our developing
tool or any DB2 client.

Thanks both
Nov 12 '05 #4
The error message refers to the statement you wrote, not the aliases. The
statement:
select * from table1
qualifies table1 with the current schema or owner as determined by the
statement's context.

You must either qualify the table (as Knut recommends), or specify an
alternative default . I use qualification for ad-hoc SQL, and the
precompiler QUALIFIER option with unqualified names in embedded SQL.

"Santiago Ordax Solivellas" <sa********@yah oo.com> wrote in message
news:cc******** *************** ***@posting.goo gle.com...
To Thiru:
Hi,
Is enough privileges availables for both the libraries to access the
other library's object?

Cheers,
Thiru


Well, if you are talking about the privileges granted to the users
that have the same name as the library, lib1 user has enough
privileges on lib2 library. There is no lib2 user.

To Knut:

Knut Stolze <st****@de.ibm. com> wrote in message > I don't quite
understand your question. A table is uniquely identified by
its schema name, combined with the table name. If no explicit schema
name
is given, then DB2 will consult a so-called special register named
CURRENT
SCHEMA. Per default, the value in that special register is set to the
name
of the user who connected to the database.

In the above examples, the schema name used to find the tables is OWNER,
lib1, or lib2. Do you actually want to do away with the schemas
alltogether?

Personally, I'd recommend to _always_ qualify your table names explicitly
with schema names. I don't see a good reason speaking against it, and it
does make things clearer.


We are using an user with the same name as the library lib1, so the
user in the above examples is lib1.

We are developing and testing or programs on PC workstations and
accessing database on a AS/400 host via DB2 connect. Once the programs
are finished they are generated to host where they run. We do not
qualify table names on our programs cause we want them to use the
database library the AS/400 user has defined in his library list.

There is no problem with alias once the programs are running on the
AS, our problem is when accessing via DB2 connect with our developing
tool or any DB2 client.

Thanks both

Nov 12 '05 #5
Lets see, table1 is the table i want to access on lib2, and alias1 is
the alias i have on lib1 pointing to lib2.table1. If I...

connect to database user lib1 using password

and then

select * from alias1

it should look for lib1.alias1 (the alias) as it do with normal tables
on lib1 but it fails and it seems its looking for owner.alias1, but if
i

select * from lib1.alias1

it works!

Why does it put "lib1" as the default schema for tables and "owner" as
the default schema for aliases???

I cannot qualify tables cause programs must run non qualified on
target environment.
"Mark Yudkin" <my************ ***********@boi ng.org> wrote in message news:<ct******* ***@ngspool-d02.news.aol.co m>...
The error message refers to the statement you wrote, not the aliases. The
statement:
select * from table1
qualifies table1 with the current schema or owner as determined by the
statement's context.

You must either qualify the table (as Knut recommends), or specify an
alternative default . I use qualification for ad-hoc SQL, and the
precompiler QUALIFIER option with unqualified names in embedded SQL.

Nov 12 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
7382
by: James Foreman | last post by:
We have 5 users of our database (DB2 UDB 8.1 on SuSE linux) : db2inst1 bill fred sarah jessica The tables are created under db2inst1, which has the ability to drop/create/alter/etc. bill, fred, sarah and jessica all have select
4
2933
by: shaun palmer | last post by:
when or Where do you use a union query ? how can I wright sql, tblPopulation,* tblcustomer,* one to one with all the appropriate attributes(field). Your help would be greatly appreciated. Thank you.
9
3204
by: Tim D | last post by:
Hi, I originally posted this as a reply to a rather old thread in dotnet.framework.general and didn't get any response. I thought it might be more relevant here; anyone got any ideas? My questions are below... "David Good" wrote: > We have a network running both Win2k and Win2k3 webservers and our web sites > reside on a UNC network share that happens to be a Network Appliance NAS.
8
7828
by: pmud | last post by:
Hi, I am using a compare validator in asp.net application(c# code). This Custom validator is used for comparing a value enterd by the user against the primary key in the SQL database. IF the VALUE ENTERED BY THE USER EXISTS IN THE DB , then THE ERROR MESSAGE OF THE COMPARE VALIDATOR SHOULD BE DISPLAYED. For this, I used the reference artiicle "http://msdn.microsoft.com/library/default.asp?url=/library/en-...
10
6725
by: Robert | last post by:
How do you get an accurate count of the number of records returned from a query when using linked tables. I have an access 2003 database as a front end to another access 2003 database that contains only tables. I have linked the tables for the front end to the back end database. I am trying to set the recordsource of a form to a query established by the user to narrow the scope but I don't want to display the form if there are no...
669
25944
by: Xah Lee | last post by:
in March, i posted a essay “What is Expressiveness in a Computer Language”, archived at: http://xahlee.org/perl-python/what_is_expresiveness.html I was informed then that there is a academic paper written on this subject. On the Expressive Power of Programming Languages, by Matthias Felleisen, 1990. http://www.ccs.neu.edu/home/cobbe/pl-seminar-jr/notes/2003-sep-26/expressive-slides.pdf
4
1992
by: sbowman | last post by:
I'm linking a bunch of tables that are in SQL to my Access db and I get the following error on 3 of the tables: "Seq.Group is not a valid name." I tried doing an import instead of a link, but I got the same error. ??? FYI I am not the SQL dba and I'm not able to change table names or reload data. I need a way around this issue stat!! Thanks, Shelley
4
9085
by: lspoulin | last post by:
Hi, We have a webpage browsing folders within the wwwroot. We need to access a remote shared directory the same way. We created a virtual directory in IIS with an alias pointing on this remote directory. The virtual directory is accessible when we type it in the address bar of IE but when we try accessing it with a Scripting.FileSystemObject.
5
5889
by: Tony | last post by:
I am continuing to develop an Access 2007 application which was originally converted from Access 2003. In Access 2003 I was able to disable the Access Close button in the top righthand corner of the screen. I have been unable to find any way to disable this button in Access 2007 and subsequently I have been forced to find ways to detect and handle the situations after the Access Close button has been clicked. I have been largely...
0
8586
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9137
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8864
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8838
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7684
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectplanning, coding, testing, and deploymentwithout human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6507
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupr who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5842
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3025
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
1986
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.