Developer’s Journal: ABAP/HANA Connectivity via Secondary Database Connection

Introduction

In this first edition of this HANA Developer’s Journey I barely scratched the surface on some of the ways which a developer might begin their transition into the HANA world. Today I want to describe a scenario I’ve been studying quite a lot in the past few days: accessing HANA from ABAP in the current state.  By this, I mean what can be built today.  We all know that SAP has some exciting plans for ABAP specific functionality on top of HANA, but what everyone might not know is how much can be done today when HANA runs as a secondary database for your current ABAP based systems.  This is exactly how SAP is building the current HANA Accelerators, so it’s worth taking a little time to study how these are built and what development options within the ABAP environment support this scenario.

HANA as a Secondary Database

The scenario I’m describing is one that is quite common right now for HANA implementations.  You install HANA as a secondary database instead of a replacement for your current database.  You then use replication to move a copy of the data to the HANA system. Your ABAP applications can then be accelerated by reading data from the HANA copy instead of the local database. Throughout the rest of this blog I want to discuss the technical options for how you can perform that accelerated read.

ABAP Secondary Database Connection

ABAP has long had the ability to make a secondary database connection.  This allows ABAP programs to access a database system other than the local database. This secondary database connection can even be of a completely different DBMS vendor type. This functionality is extended to support SAP HANA for all the NetWeaver release levels from 7.00 and beyond. Service Note 1517236  (for SAP Internal) or Note  1597627  (for everyone) lists the preconditions and technical steps for connection to HANA systems and should always be the master guide for these preconditions, however I will summarize the current state at the time of publication of this blog.

Preconditions

  • SAP HANA Client is installed on each ABAP Application Server. ABAP Application Server Operating System must support the HANA Client (check Platform Availability Matrix for supported operating systems).
  • SAP HANA DBSL is installed (this is the Database specific library which is part of the ABAP Kernel)
  • The SAP HANA DBSL is only available for the ABAP Kernel 7.20
    • Kernel 7.20 is already the kernel for NetWeaver 7.02, 7.03, 7.20, 7.30 and 7.31
    • Kernel 7.20 is backward compatible and can also be applied to NetWeaver 7.00, 7.01, 7.10, and 7.11
  • Your ABAP system must be Unicode

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

This configuration can be done via table maintenance (Transaction SM30) for table DBCON. From the configuration screen you supply the DBMS type (HDB for HANA), the user name and password you want to use for all connections and the connection string. Be sure to include the port number for HANA systems. It should be 3<Instance Number>15. So if your HANA Database was instance 01, the port would be 30115.

DBCON can also be maintained via transaction DBACOCKPIT. Ultimately you end up with the same entry information as DBCON, but you get a little more information (such as the default Schema) and you can test the connection information from here.

 

Secondary Database Connection Via Open SQL

The easiest solution for performing SQL operations from ABAP to your secondary database connection is to use the same Open SQL statements which 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:

  SELECT * FROM sflight CONNECTION ('AB1')
    INTO TABLE lt_sflight
   WHERE carrid = 'LH'.

The advantage of this approach is in its simplicity.  With one minor addition to existing SQL Statements you can instead redirect your operation to 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 considering the data all resides in the local ABAP DBMS and gets replicated to HANA. In this situation we will always have local copies of the tables in the ABAP Data Dictionary.  This does mean that you can’t access HANA specific artifacts like Analytic Views or Database Procedures. You also couldn’t access any tables which use 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 you database specific SQL statements.  This allows you to access tables and other artifacts which only exist in the underlying database.  There is also syntax in Native SQL to allow you to call Database Procedures.  If we take the example from above, we can rewrite it using Native SQL:

EXEC SQL.
    connect to 'AB1' as 'AB1'
  ENDEXEC.
  EXEC SQL.
    open dbcur for select * from sflight where mandt = :sy-mandt and carrid = 'LH'
  ENDEXEC.
  DO.
    EXEC SQL.
      fetch next dbcur into :ls_sflight
    ENDEXEC.
    IF sy-subrc NE 0.
      EXIT.
    ELSE.
      APPEND ls_sflight TO lt_sflight.
    ENDIF.
  ENDDO.
  EXEC SQL.
    close dbcur
  ENDEXEC.
  EXEC SQL.
    disconnect 'AB1'
  ENDEXEC.

Its certainly more code than the Open SQL option and a little less elegant because we are working with database cursors to bring back an array of data.  However the upside is access to features we wouldn’t have otherwise. For example I can insert data into a HANA table and use the HANA database sequence for the number range or built in database functions like now().

    EXEC SQL.
      insert into "REALREAL"."realreal.db/ORDER_HEADER"
       values("REALREAL"."realreal.db/ORDER_SEQ".NEXTVAL,
                   :lv_date,:lv_buyer,:lv_processor,:lv_amount,now() )
    ENDEXEC.
    EXEC SQL.
      insert into "REALREAL"."realreal.db/ORDER_ITEM" values((select max(ORDER_KEY)
        from "REALREAL"."realreal.db/ORDER_HEADER"),0,:lv_product,:lv_quantity,:lv_amount)
    ENDEXEC.

The other disadvantage to Native SQL via EXEC SQL is that there are little to no syntax checks on the SQL statements which you create. Errors aren’t caught until runtime and can lead to short dumps if the exceptions aren’t properly handled.  This makes testing absolutely essential.

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 improves on some of the limitations.  This is the concept of ADBC – ABAP Database Connectivity.  Basically it is a series of classes (CL_SQL*) which simplify and abstract the EXEC SQL blocks. For example we could once again rewrite our SELECT * FROM SFLIGHT example:

****Create the SQL Connection and pass in the DBCON ID to state which Database Connection will be used
  DATA lr_sql TYPE REF TO cl_sql_statement.
  CREATE OBJECT lr_sql
    EXPORTING
      con_ref = cl_sql_connection=>get_connection( 'AB1' ).

****Execute a query, passing in the query string and receiving a result set object
  DATA lr_result TYPE REF TO cl_sql_result_set.
  lr_result = lr_sql->execute_query(
    |SELECT * FROM SFLIGHT WHERE MANDT = { sy-mandt } AND CARRID = 'LH'| ).

****All data (parameters in, results sets back) is done via data references
  DATA lr_sflight TYPE REF TO data.
  GET REFERENCE OF lt_sflight INTO lr_sflight.

****Get the result data set back into our ABAP internal table
  lr_result->set_param_table( lr_sflight ).
  lr_result->next_package( ).
  lr_result->close( ).

Here we at least remove the step-wise processing of the Database Cursor and instead read an entire package of data back into our internal table at once.  By default the initial package size will return all resulting records, but you can also specify any package size you wish thereby tuning processing for large return result sets.  Most importantly for HANA situations, however, is that ADBC also lets you access non-Data Dictionary artifacts including HANA Stored Procedures.  Given the advantages of ADBC over EXEC SQL, it is SAP’s recommendation that you always try to use the ADBC class based interfaces.

Closing

This is really just the beginning of what you could with this Accelerator approach to ABAP integration into SAP HANA. I’ve used very simplistic SQL statements in my examples on purpose so that I could instead focus on the details of how the technical integration works.  However, the real power comes when you execute more powerful statements (SELECT SUM … GROUP BY), access HANA specific artifacts (like OLAP Views upon OLTP tables), or database procedures.  These are all topics which I will explore more in future editions of this blog.

Social tagging: > > > > > > >

7 Responses to Developer’s Journal: ABAP/HANA Connectivity via Secondary Database Connection

  1. venkat says:

    Thanks for detailed explanation. Service Note 1517236 is not release yet.

    thanks, Venkat

  2. Thomas Jung says:

    Thanks for that note. I’ve added a second service note reference which is also released for everyone. It has a little less information less than the internal note, but still quite good.

  3. Rich K says:

    This is a great blog. Really helped me understand and put Hana in perspective. Looking forward to more

  4. Ed Herrmann says:

    Thanks for the great blog, Tom!

    In your native SQL example, you did an insert directly into HANA. What would this do to data integrity since the HANA db is supposed to be a copy of your local db? Does the replication work both ways?

    • Thomas Jung says:

      I should have been clearer about the INSERT example. No, the replication isn’t bi-directional. It only works from ABAP to HANA. Therefore you wouldn’t want to do an insert via the secondary database connection for a table which also resided in the primary database. I was just giving an example of you could perform insert/updates/deletes to tables which only existed in the HANA database. If you were building a custom solution you could choose to place the primary persistence for new tables on the HANA side. In my example, this is exactly the case. The ORDER table I’m updating is in a separate Schema from my ABAP tables was created a completely native HANA table. Its actually part of a different project where all the modeling was done on the HANA side, yet I can still insert and read data from ABAP as well.

  5. Shishupal says:

    Hello Thomas,

    Kudos to you as always , Helpfull information for all who are looking forward to work with HANA…
    Waiting for more additions to the blog …
    Thanks again
    Shishupal.

  6. Manoj says:

    Hello Thomas,
    While checking my connection in Tcode : DBACOCKPIT i am getting SQL CODE :10709 error . Any idea please for error.
    Regards,
    Manoj

Leave a Reply