Public Synonyms in SAP HANA

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:

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,

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:

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>%’