XS Advanced features: Synonyms.

[sanampreet_singh]

This blog will give you information on how to use objects of one HDI container into another and objects of a non-HDI container schema into HDI container .

A word about HDI Containers

As we enter the world of XS Advanced, we come across many new terms and one of them is "HDI container".

You can think of it as a database schema. It abstracts the actual physical schema and provides schema-less development. You can read more about them in the blog written by Thomas Jung. Please visit http://scn.sap.com/community/developer-center/hana/blog/2015/12/08/sap-hana-sps-11-new-developer-fea...

The key points that we need to emphasize while working with the HDI containers are:

• A database schema and a technical user also gets created in the HANA database for every container. All the run time objects from the container like tables, views, procedures etc. sit in this schema and not in the schema bind to your database user.
• All the database object definitions and access logic has to be written in a schema-free way.
• Only local object access is allowed. It means that you can only access the objects local to your container. You can also access the objects of other containers and non-HDI container schemas (foreign schemas) but via synonyms as long as the technical user of the HDI schema has been granted access to this foreign schema.

1. Creating Synonyms for non-HDI schema objects

Now we will be looking at an example of creating synonyms for the objects of a non-HDI container schema (foreign schema) in a HDI container.

This example is based on SPS 12 and uses both XS command line tool and SAP Web IDE for SAP HANA (XS Advanced) tool.

Prerequisites:

• You should have a database user who should be able to access XSA Web IDE tool.
• Your database user should have the authorization (WITH GRANT OPTION) on the foreign schema.

Let's start with the example step by step.

Create a user provided service.

We have to create a user provide service for the foreign schema. Open XSA client tools and login using your user by issuing 'xs login' command.

Now create user service by issuing 'xs create-user-provided-service' or 'xs cups' command. You can use the following syntax:

xs cups <service-name> -p "{\"host\":\"<host-name>\",\"port\":\"<port-number>\",

\"user\":\"<username>\",\"password\":\"<password>\",\"driver\":\"com.sap.db.jdbc.Driver\",

\"tags\":[\"hana\"] , \"schema\":\"<foreign schema name>\" }"

Modifying mta.yaml file.

We have to correctly configure all services including the user provided service in the mta.yaml file. This allows using the user provided service within the project.

Add an entry of the user provided service you created in 'resources' section of mta.yaml file.

Figure 1: CUPS entry in mta.yaml file example

Also, add a dependency of this service in HDB module of your project.

Figure 2: CUPS dependency in HDB module in mta.yaml file

Creating .hdbsynonymgrantor file.

This file specifies the necessary privileges to access external tables. Open XSA Web IDE and under HDB module of your project create a new folder with name 'cfg'. Just like the 'src' folder, its name is special. This tells the HDI deployer that this folder contains configuration files and treats them appropriately.

Create your .hdbsynonymgrantor file under this folder.

Figure 3: .hdbsynonymgrantor file example

Creating synonym for external object

Create a .hdbsynonym file in 'src' folder of your HDB module. In one .hdbsynonym file you can define multiple synonyms to be used in your project.

Figure 4: .hdbsynonym file example

Now, you should be able to use these tables of foreign schema in your container using these synonyms.

2. Creating Synonyms for other HDI container objects

All the steps for creating synonyms for other HDI container objects are very much similar to the above steps with some minor changes.

Prerequisites:

Before you start doing anything please keep in mind that HDI container object privileges can only be granted to other containers via container local roles.

So you have to deploy one or more .hdbrole files defining object privileges to the 'grantor container' (foreign container).

In this scenario we don't have to create CUPS for the other container as a service already exist for that. We can find the name of this service using HRTT tools and checking the details of the HDI container or you check that by issuing 'xs services' command in XSA client tools.The service name should be having your database user and workspace prefixed to it.

Modifying mta.yaml file.

After we have the required service name, we have to modify mta.yaml file.

Add an entry of the user provided service you created in 'resources' section.

Figure 5: HDI container service entry in mta.yaml file example

Add a dependency of this service in HDB module of your project.

Figure 6: HDI container service dependency in HDB module in mta.yaml file

Creating .hdbsynonymgrantor file.

This file specifies the necessary privileges to access tables of other container.

Create your .hdbsynonymgrantor file under 'cfg' folder of your HDB module. You have to reference the local role(s) of the foreign schema in the 'container_roles' sections of a this file.

Figure 7: .hdbsynonymgrantor file using container specific roles example

Creating synonym

Create a .hdbsynonym file in 'src' folder of your HDB module.

Figure 8: .hdbsynonym file using container specific schema example

Now you should be able to use the objects of other HDI container using these snonyms.