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 "NAME .TABLE1" is an undefined name. SQLSTATE=42704

Where NAME happens to be 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.

In my experience AS400 expects that the connection is set up through a library. If you do not specify another library as schema it will assume that you address the connection library.

May I ask how you set up connection to AS400 with the DB2 client (DB2/CLI)?

We use Client Access for this, would like to know how to connect through DB2 client....

Well, if you are asking for the connect statement we use to establish our conenction through DB2 it is:

connecto to DATABASE user LIB1 using PASSWORD

So user name is the same as the library name where alias are defined.