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.
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.
No comments:
Post a Comment