Using SAP HANA as a Secondary Database from ABAP

In this scenario, you install SAP HANA as a secondary database instead of as a replacement for your current database. You then use replication to move a copy of the data into the SAP HANA system. Your ABAP applications can then be accelerated because they will read data from the SAP HANA copy instead of the local database.                       

ABAP Secondary Database Connection

You install HANA as a secondary database instead of a replacement for your current database. 

ABAP has long possessed the capability to make a secondary database connection. This capability allows ABAP programs to access a database system other than the local database, even a database from a completely different DBMS vendor.

This functionality is extended to support SAP HANA for all the NetWeaver release levels from 7.00 and beyond. Service Note 1597627 lists the preconditions and technical steps required to connect to HANA systems, and it should always be the master guide for these preconditions.

Preconditions

1. The SAP HANA Client is installed on each ABAP Application Server. The ABAP Application Server Operating System must support the  HANA Client.

2. SAP HANA DBSL is installed. (This is the Database-specific library that is part of the ABAP Kernel.)

3. The SAP HANA DBSL is available only for the ABAP Kernel  7.20 and higher.

4. Kernel 7.20 is already the default kernel for NetWeaver 7.02, 7.03, 7.20, 7.30, and 7.31.

5. Kernel 7.20 is backward compatible, so it can also be applied to NetWeaver 7.00, 7.01, 7.10, and 7.11.

6. Your ABAP system must be Unicode or Single Code Page 1100 (Latin 1/ISO-8850-1) —

Next, you must configure your ABAP system to connect to this alternative database. You have one central location where you maintain the database connection string, username, and password. Your applications, then need only to specify the configuration key for the database, thereby making the connection information application-independent.

Secondary Database Connection Via Open SQL

The easiest solution for performing SQL operations from ABAP to your secondary database connection is to use the Open SQL statements that ABAP developers are already familiar with. If you supply the additional syntax of CONNECTION (dbcon), you can force the Open SQL statement to be performed against the alternative database connection.

For instance, let’s take a simple Select, and perform it against our HANA database:

The advantage of this approach is its simplicity. By making one minor addition to existing SQL Statements, you can redirect your operation to SAP HANA. The downside is that the table or view you are accessing must exist in the ABAP Data Dictionary.

That isn’t a huge problem for this Accelerator scenario, however, because all of the data reside in the local ABAP DBMS and are replicated to SAP HANA. In this situation, we will always have local copies of the tables in the ABAP Data Dictionary. Note, however, that you can’t access SAP HANA-specific artifacts like Analytic Views and Database Procedures. You also can’t access any tables that use SAP HANA as their own/primary persistence.

Secondary Database Connection Via Native SQL

ABAP also has the ability to utilize Native SQL. In this situation, you write database-specific SQL statements. This process allows you to access tables and other artifacts that exist only in the underlying database. In addition, Native SQL contains syntax that allows you to call Database Procedures. If we take the example from above, we can rewrite it using Native SQL:

One disadvantage of using Native SQL via EXEC SQL in this example is that the statement contains significantly more code than the Open SQL option. It is also a little less elegant because it utilizes database cursors to bring back an array of data.

The upside is that it provides access to features you wouldn’t otherwise have. For example, you can insert data into an SAP HANA table and use the SAP HANA database sequence for the number range or for built-in database functions like now().

The other disadvantage of using Native SQL via EXEC SQL is that there are few, if any, syntax checks on the SQL statements you create. Errors aren’t caught until runtime, which can lead to short dumps if the exceptions aren’t properly handled. This limitation makes testing absolutely essential when you’re using Native SQL.

Secondary Database Connection via Native SQL — ADBC

There is a third option that provides the benefits of the Native SQL connection via EXEC SQL but also eliminates some of the limitations. This is the concept of ADBC — ABAP Database Connectivity. This approach is essentially a series of classes (CL_SQL*) that simplify and abstract the EXEC SQL blocks. For example, we could once again rewrite our SELECT *FROM SFLIGHT example:

In this iteration, we remove the step-wise processing of the Database Cursor and instead read an entire package of data back into our internal table all at once. The initial package size will return all of the resulting records by default.

However, you can specify any package size you wish, thereby tuning processing for large return result sets. Most importantly for SAP HANA situations, however,  is the fact that  ADBC  also lets you access non-Data Dictionary artifacts including SAP HANA Stored Procedures. Given the advantages of ADBC over EXEC SQL, SAP recommends that you always try to use the ADBC class-based interfaces.

This is really just the beginning of what you could accomplish with this secondary database approach to ABAP integration into SAP HANA. We’ve deliberately used very simplistic SQL statements in these examples so that we could focus on the details of how the technology integration works.

However, the real power comes when you execute more powerful statements ( SELECT SUM … GROUP BY), access SAP HANA-specific artifacts (like OLAP Views upon OLTP tables), or database procedures.