SYNONYMS in DB2

chas_dba · #1 (**permalink**) 04-23-10, 00:53

Hello every one.

Need help with a DB2 synonym. We have 2 schemas in our Z/os of DB2 ver 8.1
NT1 and NT2. We have created a synonym for TAB1. Expected result is
if we do SELECT * from NT1.TAB1 or SELECT * from NT2.TAB1, it should result the same.

(TAB1 is created as in schema NT1. )

Problem is that we are encountering a -204 code(Nothing with this name exists in the database)., for the SQL executed either through QMF or SPUFI or any thing.

User have SELECT access to all table in both the schema's. Are we missing any thing here?

Any help is greately appreciated.

rgds
Chas.

dav1mo · #2 (**permalink**) 04-23-10, 09:03

Try reading up on synonyms a bit more and I would suggest you look into using an alias.
Dave

Lenny77 · #3 (**permalink**) 04-23-10, 11:33

Quote:

Originally Posted by chas_dba

Hello every one.

Need help with a DB2 synonym. We have 2 schemas in our Z/os of DB2 ver 8.1
NT1 and NT2. We have created a synonym for TAB1. Expected result is
if we do SELECT * from NT1.TAB1 or SELECT * from NT2.TAB1, it should result the same.

(TAB1 is created as in schema NT1. )

Problem is that we are encountering a -204 code(Nothing with this name exists in the database)., for the SQL executed either through QMF or SPUFI or any thing.

User have SELECT access to all table in both the schema's. Are we missing any thing here?

Any help is greately appreciated.

rgds
Chas.

You have to use synonym name instead of table name.

Rules:

Quote:

Description synonym Names the synonym.
The name must not identify a synonym, table, view, or alias that is owned by the owner of the synonym that is being created. The owner of the synonym being created is determined by how the CREATE SYNONYM statement is invoked: v If the statement is embedded in an application program, the owner is the authorization ID of the plan or package. v If the statement is dynamically prepared, the owner is the SQL authorization ID in the CURRENT SQLID special register.FOR authorization-name.table-name or authorization-name.view-name Identifies the object to which the synonym applies. The name must consist of two parts and must identify a table, view, or alias that exists at the current server. If a table is identified, it must not be an auxiliary table or a declared temporary table. If an alias is identified, it must be an alias for a table or view at the current server and the synonym is defined for that table or view.

Lenny

Internet Services

Web Development

Digital Marketing

Consumer Tech