SQL error 204 when we try to access tables via alias

[Santiago Ordax Solivellas]

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.

[Thiru]

Hi,
Is enough privileges availables for both the libraries to access the
other library's object?

Cheers,
Thiru

[Knut Stolze]

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

[Santiago Ordax Solivellas]

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

[Mark Yudkin]

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********@yahoo.com> wrote in message
news:cc**************************@posting.google.c om...

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

[Santiago Ordax Solivellas]

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***********************@boing.org> wrote in message news:<ct**********@ngspool-d02.news.aol.com>...

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.