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.

1 Comment

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

  1. Lars Breddemann

    Thanks for sharing your experiences.

    Not sure I really follow your points here.

    The standard object name resolution checks

    1. current schema
    2. public namespace (public synonyms)

    in this order.

    It really doesn’t matter who owns the base object here.

    As for procedures, there is the option to explicitly specify a default schema. Doing this is common practice, especially when several developers build code for the same shared application schema.

    That way, it really doesn’t matter how has written the code. After the activation, the runtime objects are technically owned by _SYS_REPO and a common public synonym gets created, too.

    For your example, it would have been enough to set the current schema for the session of each user to the respective target schema. Right after logon the default target schema is always the current user’s schema, but one can simply run

    SET SCHEMA target_schema_name;

    to change that.

     

    (2) 

Leave a Reply