1. Both CREATE ALIAS and CREATE SYNONYM work in DB2.
What is the difference between the two?

2. Created synonyms XYZ.tables for ABC.tables and granted SELECT on the ABC.tables to XYZ. I can do select count(*) on the XYZ.tables but unable to describe XYZ.tables when issuing 'db2 describe XYZ.salary_code'.
The table is NOT empty.

Column Type Type
name schema name Length Scale Nulls
------------------------------ --------- ------------------ -------- -----
0 record(s) selected.

SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000


What am I missing? Any other privilege is needed besides SELECT?

Thank you in advance.

An alias is typically used to point a table, view or alias in a different DB2 subsystem. The existance of the object will NOT be verified at the time of alias creation but will produce a warning if referenced and doesn't exist on the local subsystem. A synonym is created as another name for a table or view.

I presume 'db2 describe select * from XYZ.salary_code' works.

Which DB2 is the server - I note the DB2 V8 LUW documentation doesn't describe synonynms. So it must be DB2 for zOS. I have noticed that DB2 LUW clients seem to handle synonyms better than aliases. Probably because synonyms existed when the DB2 client code was first written, and it was never updated to handle aliases - which were developed because of the deficiencies of synonyms.

The DESCRIBE command TABLE table-name section has the sentence "[a]n alias for the table cannot be used in place of the actual table." Probably the problem.

James Campbell

Thanks for responding.

We use DB2 v8.1 on AIX 5.2.
Since I found a bug in the 3rd party software which prevents us from displaying data directly from the schema, I tried to create a synonym or alias to reference the data but none of the tables can be displayed from the software selection page.

I was surprised I cannot even describe the alias/synonym in DB2.
This is doable in Oracle.

Thanks again...

1. ALIAS is a synonym for SYNONYM.
2. You can describe aliases.

Per IBM Support, alias is the same as synonym and it cannot be displayed via DESCRIBE. I'm using views now and they work perfectly. Many thanks for all your inputs.