Results 1 to 3 of 3

Thread: SYNONYMS in DB2

  1. #1
    Join Date
    Sep 2003
    Posts
    33

    Unanswered: SYNONYMS in DB2

    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.
    bs

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Try reading up on synonyms a bit more and I would suggest you look into using an alias.
    Dave

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Post

    Quote Originally Posted by chas_dba View Post
    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:

    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •