How to replicate STXL table (INDX-like) using SDI

[gustavo_simil]

The Problem

The purpose of this document is to describe our approach to replicate table STXL from SAP ECC to HANA DB using SAP HANA SDI. Replicating STXL is challenging because it is an INDX-like table. Its content must be parsed before being saved into HANA DB.

In our previous projects, SAP SLT was the tool used to perform the replication. There is good documentation on SCN explaining how to configure SLT to replicate INDX-like tables. However, we could not find similar materials when SAP HANA SDI is used.

Hopefully this document will help others with a similar requirement. It might also promote a discussion on different and better ways to achieve the same result with SDI.

Solution Overview

The approach we followed consists of creating a flowgraph in SDI which is capable of executing a virtual procedure on the Remote Source that points to SAP ECC. The virtual procedure encapsulates a custom BAPI, that reads the STXL table, parses the cluster fields, and returns the results to SDI. In the end of the flow, SDI stores the BAPI results on a HANA Table in SCP.

The solution architecture and its most important components are showed below:



The SAP ECC environment used Oracle DB. The SAP HANA SDI agent was installed on a Linux Server – SUSE Distribution. Our SAP Cloud Platform environment was on Cloud Foundry.

In a nutshell, these are the main activities required for this replication. They will be detailed in the next sections:

1. Create a BAPI to read STXL content from SAP


2. Configure SDI (Adapter and Remote Source)


3. Create a virtual procedure in SAP HANA to consume the BAPI


4. Create a table in HANA DB to save STXL data


5. Create a SDI Flowgraph to consume the virtual procedure


6. Execute the SDI Flowgraph

1. Create a BAPI to read STXL content from SAP

This is the BAPI code we implemented. Three input parameters were created:

• IV_INITIAL_LOAD: Flag to indicate an initial load will be done


• IV_DATE_INITIAL: Start date used in the delta loads


• IV_DATE_END: End date used in the delta loads

As the output we decided to define a table which is a combination of STXH and STXL tables:





The BAPI Code is given below. In our project we didn’t want to replicate the full STXL content. A TVARV parameter was created to filter the STXL records as per our requirements.

FUNCTION zvs_bapi_stxl.

*"----------------------------------------------------------------------

*"*"Interface local:

*"  IMPORTING

*"     VALUE(IV_INITIAL_LOAD) TYPE  XFELD OPTIONAL

*"     VALUE(IV_DATE_INITIAL) TYPE  DATUM OPTIONAL

*"     VALUE(IV_DATE_END) TYPE  DATUM OPTIONAL

*"  EXPORTING

*"     VALUE(ET_STXL_BAPI) TYPE  ZVS_STXL_BAPI_T

*"----------------------------------------------------------------------



  TYPES: BEGIN OF ty_stxh,

           tdobject TYPE stxh-tdobject,

           tdname   TYPE stxh-tdname,

           tdid     TYPE stxh-tdid,

           tdspras  TYPE stxh-tdspras,

           tdldate  TYPE stxh-tdldate,

           tdltime  TYPE stxh-tdltime,

         END OF ty_stxh.



  TYPES: BEGIN OF ty_stxl_raw,

           clustr TYPE stxl-clustr,

           clustd TYPE stxl-clustd,

         END OF ty_stxl_raw.



  DATA lt_stxl_raw  TYPE STANDARD TABLE OF ty_stxl_raw.

  DATA lt_stxh      TYPE TABLE OF ty_stxh.

  DATA lt_stxl      TYPE TABLE OF stxl.

  DATA lt_tline     TYPE STANDARD TABLE OF tline.



  FIELD-SYMBOLS: <stxl> TYPE stxl.

  FIELD-SYMBOLS: <tline> TYPE tline.



  DATA r_object TYPE RANGE OF tdobject.

  DATA r_tdid   TYPE RANGE OF tdid.

  DATA r_date   TYPE RANGE OF tdldate.



  DATA ls_stxh      LIKE LINE OF lt_stxh.

  DATA ls_stxl_raw  TYPE ty_stxl_raw.

  DATA ls_stxl_bapi LIKE LINE OF et_stxl_bapi.

  DATA ls_date      LIKE LINE OF r_date.





  "Select TVARV ranges

  select_tvarv 'ZVSSTXL'.

  get_tvarv_range 'TDOBJECT' r_object.

  get_tvarv_range 'TDID' r_tdid.



  "Check if TVARV is filled

  CHECK r_object[] IS NOT INITIAL.

  CHECK r_tdid[]   IS NOT INITIAL.





  IF iv_initial_load EQ 'X'.

    SELECT tdobject

           tdname

           tdid

           tdspras

           tdldate

           tdltime

      INTO TABLE lt_stxh

      FROM stxh

      WHERE tdobject IN r_object

      AND   tdid     IN r_tdid.

  ELSE.



    CHECK iv_date_initial IS NOT INITIAL.



    IF iv_date_initial EQ iv_date_end.

      ls_date-sign   = 'I'.

      ls_date-option = 'EQ'.

      ls_date-low    = iv_date_initial.

      APPEND ls_date TO r_date.

    ELSE.

      ls_date-sign   = 'I'.

      ls_date-option = 'BT'.

      ls_date-low    = iv_date_initial.

      ls_date-high   = iv_date_end.

      APPEND ls_date TO r_date.

    ENDIF.



    SELECT tdobject

           tdname

           tdid

           tdspras

           tdldate

           tdltime

      INTO TABLE lt_stxh

      FROM stxh

      WHERE tdobject IN r_object

      AND   tdid     IN r_tdid

      AND   tdldate IN r_date.

      "AND   tdldate => iv_date_initial

      "AND   tdldate =< iv_date_end.



  ENDIF.



  "Check if there is at least one record

  CHECK lt_stxh[] IS NOT INITIAL.



  SORT lt_stxh BY tdobject tdname tdid tdspras.





  SELECT *

    INTO TABLE lt_stxl

    FROM stxl

         "PACKAGE SIZE 3000  "Verificar se esta clausula é necessária

    FOR ALL ENTRIES IN lt_stxh

    WHERE relid    = 'TX'

      AND tdobject = lt_stxh-tdobject

      AND tdname   = lt_stxh-tdname

      AND tdid     = lt_stxh-tdid

      AND tdspras  = lt_stxh-tdspras

      AND srtf2    = '0'.

  "ENDSELECT.





  LOOP AT lt_stxl ASSIGNING <stxl>.



    CLEAR ls_stxh.

    READ TABLE lt_stxh INTO ls_stxh WITH KEY tdobject = <stxl>-tdobject

                                             tdname   = <stxl>-tdname

                                             tdid     = <stxl>-tdid

                                             tdspras  = <stxl>-tdspras BINARY SEARCH.



    "Decompress text

    CLEAR: lt_stxl_raw[], lt_tline[].

    ls_stxl_raw-clustr = <stxl>-clustr.

    ls_stxl_raw-clustd = <stxl>-clustd.

    APPEND ls_stxl_raw TO lt_stxl_raw.

    IMPORT tline = lt_tline FROM INTERNAL TABLE lt_stxl_raw.



    CLEAR ls_stxl_bapi.

    ls_stxl_bapi-tdobject    = <stxl>-tdobject.

    ls_stxl_bapi-tdname      = <stxl>-tdname.

    ls_stxl_bapi-tdid        = <stxl>-tdid.

    ls_stxl_bapi-tdspras     = <stxl>-tdspras.

    ls_stxl_bapi-srtf2       = <stxl>-srtf2.

    ls_stxl_bapi-tdldate     = ls_stxh-tdldate.

    ls_stxl_bapi-tdltime     = ls_stxh-tdltime.



    CALL FUNCTION 'IDMX_DI_TLINE_INTO_STRING'

      EXPORTING

        it_tline       = lt_tline

      IMPORTING

        ev_text_string = ls_stxl_bapi-longtext.



    APPEND ls_stxl_bapi TO et_stxl_bapi.



  ENDLOOP.



  FREE lt_stxl.



ENDFUNCTION.

2. Configure SDI – ABAP Adapter and Remote Source

We will assume SDI is already installed and the ABAP Adapter is registered in the SDI Agent. If you need assistance with SDI installation and initial configuration, please reference this SAP Documentation.

After SDI is installed and an ABAP Adapter is registered, one needs to create a Remote Source. Creating an ABAP Adapter remote source is simple. We did it graphically using the SAP HANA Database Explorer:

a. Open the SAP HANA Database Explorer

b. Expand the Cockpit Resource selecting Catalog > Remote Sources



c. Right Click on Remote Sources and choose Add Remote Source



d. Provide the Remote Source details:

• Source Name: The name of your remote source


• Adapter Name: ABAPAdapter


• Source Location: The name of your SDI Agent


• Under connection info, provide the application server, client (mandant), and instance number of your SAP Server. It is possible to obtain this information from the entry of your SAP instance in the SAP Logon. If you don’t have it, you will need to contact your Basis team.


• Under credentials, you need to inform a username/password of an existing user in the SAP instance. The required role to be assigned to this user is documented here.

e. After the Remote Source is created, it is important to grant access to it:



3. Create a virtual procedure in SAP HANA to consume the BAPI

The next step is to create virtual procedure in the SAP HANA DB to consume the BAPI created in the previous step. This can be easily done with the help of the HANA built-in procedure GET_REMOTE_SOURCE_FUNCTION_DEFINITION. This function takes 02 input parameters and returns 03 outputs:

• Function input parameters and their types


• Function output parameters and their types


• Virtual Procedure configuration object

Here is procedure call for our BAPI:

CALL "PUBLIC"."GET_REMOTE_SOURCE_FUNCTION_DEFINITION" ('<REMOTE SOURCER NAME>','BAPI.ZVS_BAPI_STXL',?,?,?);

There are two tips that will save you a lot of time during the virtual procedure statement creation:

• Pay special attention on the input/output parameters types and their order. We were so certain about them that we didn’t check the parameter types provided by GET_REMOTE_SOURCE_FUNCTION_DEFINITION. We hit our head on the wall for a while until we realized we were using the wrong types.


• The second parameter of the procedure is the BAPI name. Please notice that you must include the ‘BAPI.’ Prefix.

This is how our virtual procedure creation statement looks like:

CREATE VIRTUAL PROCEDURE ZVS_BAPI_STXL ( 

IN IV_DATE_END VARCHAR(8),

IN IV_DATE_INITIAL VARCHAR(8),

IN IV_INITIAL_LOAD NVARCHAR(1),

OUT ET_STXL_BAPI TABLE (

TDOBJECT NVARCHAR(10),

TDNAME NVARCHAR(70),

TDID NVARCHAR(4),

TDSPRAS NVARCHAR(1),

SRTF2 SMALLINT,

TDLDATE VARCHAR(8),

TDLTIME VARCHAR(6),

LONGTEXT NCLOB

)

) CONFIGURATION '

{

    "__DP_UNIQUE_NAME__": "ZVS_BAPI_STXL",

    "__DP_HAS_NESTED_PARAMETERS__": false,

    "__DP_USER_DEFINED_PROPERTIES__": {},

    "__DP_INPUT_PARAMETER_PROPERTIES_": [],

    "__DP_RETURN_PARAMETER_PROPERTIES_": [],

    "__DP_VIRTUAL_PROCEDURE__": true,

    "__DP_HAS_INTERNAL_OUTPUT_PARMETER__": false,

    "__DP_DEFAULT_OUTPUT_PARAMETER_INDEX__": 0

}' AT "<REMOTE SOURCE NAME>";

4. Create a table in HANA DB to save STXL data

The HANA DB table was created using a hdbcds file:

context ECCReplication {



    entity ZSTXL {

		key TDOBJECT: String(10);

		key TDNAME_KEY1: String(70);

		key TDNAME_KEY2: String(70);

		key TDID:  String(4);

		key TDSPRAS: String(1);

		key SRTF2: Integer;

		TDLDATE: String(8);

		TDLTIME: String(6);

		LONGTEXT: LargeString;

    } technical configuration {

        fulltext index ZSTXL_LONGTEXT ON (LONGTEXT) FUZZY SEARCH INDEX ON SYNC;

    }; 

};

The key fields are the same of ECC table STXL. We just broke the TDNAME into two new distinct fields to help joining ZSTXL with other objects as in some cases TDNAME holds the concatenation of two SAP ECC fields (e.g.: TDNAME holds the combination of EKPO-EBELN and EKPO-EBELP ofr Purchasing Contracts/Orders Long Texts). It is more efficient to break this information during the replication of its content.

5. Create a SDI Flowgraph to consume the virtual procedure

A simple .hdbflowgraph was created to call the virtual procedure and to store the results in the ZSTXL table in HANA.



Here are the properties of the Flow Graph:





The first node is of type Procedure and it calls the Virtual Procedure ZBAPISTXL:



It is important to map the BAPI input parameters with the variables created in the Flow Graph definition.

The second node it is a projection on the BAPI resultset. The main purpose of this node is to break the source TDNAME field into TDNAME_KEY1 and TDNAME_KEY2. This step makes sense to our business requirements, but it might be irrelevant to you and it can be ignored.



We used a mapping rule to split the TDNAME field content:

Rule for TDNAME_KEY1:

CASE WHEN "TDOBJECT"='EKPO' OR "TDOBJECT"='ESLL' THEN LEFT("TDNAME",10) ELSE "TDNAME" END 

Rule for TDNAME_KEY2:

CASE WHEN "TDOBJECT"='EKPO' OR "TDOBJECT"='ESLL' THEN SUBSTRING("TDNAME",11) ELSE '' END 

The last node in the Flow Graph is responsible for storing the data into table ZSTXL. As our table were created in HANA, it just need to be selected using the Object Name input field. As the field names from the Source Node and Destination table are the same, columns are mapped automatically.



In the settings tab, the Upsert option was selected as the Writer Type:



6. Execute the SDI Flowgraph

After building the Flowgraph, it can be manually executed using the SQL statement below:

START TASK "ESTACIOSCH"."FG_STXL" ("IV_DATE_INITIAL" => START_DATE, "IV_DATE_END" => END_DATE, "IV_INITIAL_LOAD" => IS_INITIAL_LOAD)

When the Flowgraph is built, a procedure is internally created in the HANA DB:



Here is the procedure call statement:

CALL "<SCHEMA NAME>"."FG_STXL_SP"(

	IV_DATE_END => ''/*<NVARCHAR(8)>*/,

	IV_DATE_INITIAL => ''/*<NVARCHAR(8)>*/,

	IV_INITIAL_LOAD => ''/*<NVARCHAR(1)>*/

);

This procedure can be scheduled in a job to ensure table ZSTXL gets synchronized with SAP ECC.

Conclusion

Until this moment we were not able to find a simple way to replicate STXL table from SAP ECC to SAP HANA using SDI. The approach explained above works quite well, but it requires at least a basic/medium knowledge of SAP HANA and its components. Another downside on this solution is that it does not provide real time replication of STXL table: flowgraphs that use virtual procedures does not support real time replication. This problem can be minimized with the adoption of the Job Scheduler service (unfortunately it may not be available to your region) or with a Custom Job service developed on NodeJs (we will cover this second option on a future post).