Your database artifacts such as tables and procedures are defined using source files that belong to a database module within a project. When you build the database module, a run-time container is generated where all run-time database objects are stored.
When your database objects refer to other database objects, those objects must be part of the same container. To achieve this, you need to ensure all database objects that are referred to are defined using source files in the same database module. This means that when the container is generated, all run-time objects are part of the same container.
But very often, you need to refer to database objects that are outside your container. For example, you need to read data from a classic database schema, or call a procedure in another database schema. By default, this is not permitted. The same is true if you want to read from other containers. XSA enforces strict isolation of database objects. This is ideal in many scenarios, but not always.
Quite often the data that needs to be read is stored in tables that are not defined in a database module of your project. This is the case when we need to load data into the data warehouse. In this case we don't need source files to define each table. The tables exist only as run-time objects in a classic schema. The good news is that a container can access these objects with some simple setup.
To provide access to database objects that are stored in external schemas, you need to setup up two key components:
- User-provided service - also known as a cross-schema service, this is a one-time setup typically performed by an adminstrator to provide the linkage service. A developer simply needs to know the name of the service and adds this to the project's mta.yaml descriptor file.
- Synonyms - for each database object that is referred to, a synonym is needed. The developers can define synonyms in the database module using synonym source files, but quite often the synonyms are shared across projects so they could be maintained centrally by the administrators and the synonym files imported to each project. This helps save a lot of time. Plus if the location of the database objects change (move to a new schema), or the name of the objects change, the synonyms can be managed centrally and the soiurce files re-imported to each project.
As well as the two key components mentioned above, there are other settings to take care of, including granting privileges to the owner (technical user) of the container so that it is able to access the external database objects. This is usually a select privilege. A .hdbgrants source file stored in the database module is used to define the required privileges. Yo also need to add the name of the cross-schema service to the mta.yaml file in your project.
To provide access to database objects that are stored in other containers within your run-time space, you need to setup up only one of the components:
- Synonyms - for each database object that is referred to, a synonym is still needed.
A user provided service is not needed in this case. In order to reach the external container you will still have to include the name of a service in your mta.yaml file. But this service is not a user-provided service, but a service that is automatically generated for each container, called quite simply, the container service. You will also need to define the privileges in the .hdbgrants file.
Note
