Skip to Content

INTRODUCTION:


This blog is about my experience working on synonyms in a HANA migration project.

This blog will give you an idea of how synonyms behave in different situations and how to overcome them.



My strange experience with synonym:


Let us assume there is a schema called SRIVATSAN and the owner for that schema is the user named SRIVATSAN.


A table is created in SRIVATSAN schema : TABLE_1.


The same table structure with same name is also created in another schema called SRIVATSAN_NEW.


So now, there are two tables on two different schemas.


Contents of SRIVATSAN.TABLE_1:


NAME

CITY

SRIVATSAN

CHENNAI

NEHAL

PUNE

Contents of SRIVATSAN_NEW.TABLE_1:


NAME

CITY

VIGNESH

CHENNAI

PANKAJ

VARANASI

The user SRIVATSAN logs into the system.

Creates a procedure in a schema called PROD as follows:


PROCEDURE PROD.“TEST.DEV.SYNO::P_TEST_SYNONYM_SRIVATSAN” ( )

                LANGUAGE SQLSCRIPT

                SQL SECURITY INVOKER

                AS

BEGIN

SELECT * FROM TABLE_1;

END;


RESULT:


/wp-content/uploads/2014/10/sy1_553830.jpg

Now , another user named PANKAJ logs into the system.

Creates a procedure in the same schema (PROD):


PROCEDURE PROD.“TEST.DEV.SYNO::P_TEST_SYNONYM_PANKAJ” ( )

                LANGUAGE SQLSCRIPT

                SQL SECURITY INVOKER

                AS

BEGIN

SELECT * FROM TABLE_1;

END;


When this procedure is executed, the system throws an error saying the table TABLE_1 NOT FOUND in PANKAJ schema.


Now, SYNONYM is created for the table TABLE_1:


         CREATE PUBLIC SYNONYM TABLE_1 FOR SRIVATSAN_NEW.TABLE_1;


Now, the table used in the procedure P_TEST_SYNONYM_PANKAJ will refer SRIVATSAN_NEW schema.


So, the output would be,


/wp-content/uploads/2014/10/sy2_553831.jpg


But, this public synonym will not override the table TABLE_1 used in P_TEST_SYNONYM_SRIVATSAN,

because the OWNER OF THE TABLE (SRIVATSAN) OWNS THE SCHEMA (SRIVATSAN) WHICH ALSO HAS THE SAME TABLE NAME.

So, even after the synonym is created, the result of P_TEST_SYNONYM_SRIVATSAN will be:



/wp-content/uploads/2014/10/sy3_553841.jpg


Hereby, I experienced that, the tables that are used in procedures of an user who owns a schema (with same name) , always references to the schema which the owner owns .

If the table doesn’t exist, then the HANA system looks into the SYNONYMS definition.


Therefore, creating PUBLIC SYNONYMS will not override all the table references.


Overcoming and migrating the procedures to different projects:


In order to avoid the table used in a procedure to look into the owner’s schema by default, the procedure has to be moved by logging as a different user who doesn’t have the particular table in his/her schema which will force the SAP HANA to look for SYNONYM and choose the schema for the table.


In short, the user who logs in and create a procedure in a project should not own the tables (used in the procedure) in his/her schema so as the public synonym is always referred by HANA to decide on the schema for the table.


To check the synonyms that are created for a table, the following query will be helpful:


SELECT * FROM SYNONYMS WHERE OBJECT_NAME LIKE ‘%<tablename>%’


To report this post you need to login first.

7 Comments

You must be Logged on to comment or reply to a post.

    1. Srivatsan Sundaravaradan Post author

      Thanks Sree 🙂 .

      I tried to export the public synonyms in many ways from DEV (Development system) to QAS (Quality system) but couldn’t do it.

      So I extracted the entire synonyms list from a view named “SYNONYMS” which stores the info about synonyms.

      If this was a table , I could have exported but its a VIEW.

      I extract the result set and create the synonym creation script which I run in QAS to create them as available in DEV.

      If you have any method to migrate the synonyms pls share ℹ .

      Regards

      S.Srivatsan

      (0) 
  1. Lars Breddemann

    Well, not sure where you see a strange behavior here.

    The search order for object names is always:

    – local namespace (e.g. current schema, including private synonyms)

    – public namespace

    You can also find this in the Synonyms chapter of Book Announcement: SAP HANA Administration:

    “Public synonyms are database-wide indirections and valid for all sessions and users in the database. If the name resolution does not find the addressed

    objects in the current schema, then the PUBLIC namespace is searched, and with that the public synonyms are accessed.
    Due to this database-wide effect, public synonyms typically are used for framework or database management object— such as the SAP HANA system views, for each of which a public synonym is present.”

    – Lars

    (0) 
    1. Srivatsan Sundaravaradan Post author

      Hi Lars,

      Thanks for the valuable ℹ nfo. I haven’t checked out this document yet.

      Is there a way to make the search start from public synonym definitions rather than local objects?

      Also is there a way to migrate the synonyms from one system to other?

      Regards

      S.Srivatsan

      (0) 
      1. Lars Breddemann

        Nope, there’s no way to change the namespace evaluation order.

        And I also don’t know about any DBMS that provides such a feature.

        To “migrate” synonyms, you should be able to simply export/import the referenced objects.

        A specific functionality centered on synonyms is not available.

        In case synonyms really play a major role for your application, then a good start would probably be to manually maintain a SQL file for the synonym creation.

        – Lars

        (0) 

Leave a Reply