Saturday 5 August 2017

Public Synonyms in SAP HANA

Introduction:


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

SAP HANA, SAP All Modules, SAP Tutorial and Materials, SAP Certifications

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:
SAP HANA, SAP All Modules, SAP Tutorial and Materials, SAP Certifications

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,

SAP HANA, SAP All Modules, SAP Tutorial and Materials, SAP Certifications

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:

SAP HANA, SAP All Modules, SAP Tutorial and Materials, SAP Certifications

Here by, 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>%’
SAP Online Guides, Tutorials, Materials and Certifications.

Related Posts

1 comment: