Accessing Remote Tables using the Linked Database Feature

With SAP HANA smart data access connections based on ODBC adapters, there's a way to consume remote tables and views that doesn’t require the creation of virtual tables. It's called linked database.

A benefit of the linked database feature is that it reduces the maintenance of database objects on the local system. You can directly consume remote tables or views, for example, in SQL queries, without creating a virtual table for each of them.

Linked database only works in data virtualization scenarios. In a configuration where you need to implement data replication, you need to create virtual tables the classic way.

In particular, the linked database feature is available between two SAP HANA systems, either cloud or on-premise, as shown in the figure.

The concept of the linked database is that you access an object (table or view) on the remote system by referencing it directly in a SQL statement.

In this SQL statement, as shown in the figure, the classical "<schema_name>"."<object_name>" notation is replaced by a three-part notation: "remote_source"."remote_schema"."remote_object".

When you access a remote table or view with the linked database feature, SAP HANA creates a hidden virtual table located in the schema _SYS_LDB.

This hidden virtual table is used to store the metadata of the remote object and isn’t meant to be queried.

To use the linked database feature, a specific privilege LINKED DATABASE on the remote source must be granted to the consumer of remote objects.

Launch this video to learn how to access remote tables and views using the linked database feature.

In scenarios where both the source and target systems are SAP HANA databases (and connected via an SAP HANA smart data access remote source), it's possible to use an optimized version of the linked database feature.

Compared with the standard linked database feature, this one does NOT create hidden virtual tables, but caches locally the metadata of the remote objects you select data from. So there's no housekeeping needed (no hidden virtual tables) and no need to refresh statistics for remote objects: They're fetched automatically when needed.

There are a few limitations to SAP HANA features with the linked database – optimized mode. Please refer to SAP Note 2605574.

The optimized mode is disabled by default. To enable it, you add an additional setting in the configuration string of your remote source: linkeddatabase_mode=optimized.

Here's an example of a remote source definition

1234
CREATE REMOTE SOURCE MY_HANA1
ADAPTER "hanaodbc"
CONFIGURATION 'Driver=libodbcHDB.so;ServerNode=myserver:30115;linkeddatabase_mode=optimized'
WITH CREDENTIAL TYPE 'PASSWORD' USING 'user=user1;password=Test1234';

On remote sources that do NOT use the optimized mode, hidden virtual tables can be maintained with SQL statements. The objective is to either refresh them to keep the metadata in sync with the state of the remote objects, or to delete them when they're not needed any longer.

To refresh the metadata, you can run one of the following ALTER REMOTE SOURCEstatements:

• To refresh the metadata for all linked objects:
  Code Snippet

  Copy code

  Copy code

  Switch to dark mode

  Switch to dark mode

  1
  ALTER REMOTE SOURCE <remote_source> REFRESH LINKED OBJECTS;

• To refresh the metadata for a single object:
  Code Snippet

  Copy code

  Copy code

  Switch to dark mode

  Switch to dark mode

  12
  ALTER REMOTE SOURCE <remote_source>
  REFRESH LINKED TABLE <remote_source>.<remote_schema>.<table_name>;

To delete the internally generated objects associated with linked database access, you execute an ALTER REMOTE SOURCE statement as follows:

12
ALTER REMOTE SOURCE <remote_source_name>
DROP LINKED OBJECTS [CASCADE | RESTRICT];

• The statement without option deletes linked objects on which no other database objects depend but keeps linked objects that have dependencies.
• With the CASCADE option, the statement deletes all linked objects as well as any database object that depend on them.
• With the RESTRICT option, the statement deletes all linked objects ONLY if no database object at all depend on them. If there's even a single dependency on a single linked object, no linked object at all is deleted.