Capture Deleted Records-Using SLT

[sherryharminder]

Context/Background: As we all know using SLT we replicate the records from source system, so as part of the replication, SLT basically perform three actions which are insert, update and delete. So lets consider we have a below scenario, where are replicating records from S4 system to HANA database. This seems to be normal and this is what SLT suppose to do.

But actual problems arrives when you have further downstream system which read data from your HANA database directly. Also, if there is non-sap system which is reading from HANA database, it is difficult to capture deleted records on target, so it goes out of sync if it is not a trigger based solution or stores the data to the flat files.

So have you ever wondered how you can sync that data or how you can make sure those systems will remain in sync to the source system.

Ok, we have a below scenario:

Source: S4 system

Central system: SLT

Target System: HANA DB

Downstream System: NON SAP application ( Stores data in flat files)

So you all will be wondering how we can capture the deleted records which has been replicated by SLT. So to answer this puzzle is we have built/developed the custom solution which can help you to capture the deleted records into another table in HANA db.

How to retrieve deleted records?

Lets consider a scenario you have a table ACDOCA which has been replicated to target, in our case is EHANA. Now we perform the action to capture those deleted records, ok before we move to our topic, just want to introduce another enhancement, lets say if we have a requirement to add a another field also to be replicated as part of deletion. So have you wonder how we can add a field to logging table.

Yes we will cover all that information in the article.:) ok, lets first read about how we can add extra field to logging table.

Just for example, lets say we want to add FISCYEAR field also to be replicated or added as part of the logging table. FISCYEAR field is not a part of the logging table.

Logging Table Without FISCYEAR Field

Before you add new field make sure no records are available in logging table. Stop the SAP jobs, lock the users, work with your basis team for that.

Once assured there is 0 records in logging table. Stop the replication of table, in our example is ACDOCA.

Now go to se16 in table ( IUUC_LOGTB_FIELD) and maintain the below entries.

Custom Logging Table

After executing this, add the table into start recording, do NOT start replication as it will wipe out the data on target and it will do the initial load. So with this new logging table will be customized and new filed is added.

Logging Table With Custom Field

Hope you liked this piece of information, So lets stick back to our original topic of capturing the deleted records.

So to capture deleted records you need to create a custom include, which can be assigned to rule assignment and a custom table on SLT to capture deleted records. For this you have to work with your development team who can write a piece of code and creates a custom table.

Once developers has written a custom include you need to assign that to the ACDOCA table in rule assignment.

LTRS: Rule Assignment

Custom table on SLT will keep holding those deleted records and will be passed to HANA db into another custom table. So on HANA DB create a custom table and synonym.

Custom Table in HANA

Now activate the MTID to start the replication, Since we have Realtime replication, our deleted records will be replicated to another custom table and built a another logic to sync your third party application with deleted records.

Captured Deleted Records From Source

 

Conclusion: So after reading this blog you will know about 2 key things.

1. How to make the changes to logging table or how to customized the logging table, so required field can be replicated using SLT.


2. How to capture the deleted logs on source using SLT and save them into another custom table.

I hope you guys liked this article. Please let me know your feedback!!

[devaanand]

Hi Harminder,

Thank you for this post. I have a similar requirement to identify the deleted records in Source system & HANA  target system so that we can deleted those records from the Azure db.

Appreciate, if you could share the ABAP code logic  of the Include program used in the rule assignment.

Thank you in advance.

 

Regards,

Deva

 

[sherryharminder]

Hope the below code helps.

 

DATA: lv_dbcon_name               TYPE dbcon_name,
lv_operate_flag_condition   TYPE c LENGTH 1,
lt_target_operation_log_tab TYPE _ti_io_s_logtab,
lv_zlog_synonym_name        TYPE tabname.

FIELD-SYMBOLS: <lt_logtab_source>            TYPE _ti_io_s_logtab,
<ls_logtab_source>            TYPE _ts_io_s_logtab,
<lt_target_operation_log_tab> TYPE _ti_io_s_logtab.


CONCATENATE _mt_id ':R:R' INTO lv_dbcon_name.

*team decided to go with hardcoded value that won't change from dev to test to prod
lv_zlog_synonym_name = 'ZRHA01_ACDOCADEL'.

ASSIGN ('_IT_IO_S_LOGTAB') TO <lt_logtab_source>.
ASSIGN ('lt_target_operation_log_tab') TO <lt_target_operation_log_tab>.

IF sy-subrc <> 0.
allog_msg 'E' 'DMC_RT_MSG' '000' 'Custom Program Operation Error'
_cobj_alias 'FAILS' space 'IL'.
RAISE stopped_by_rule.
ENDIF.

*filter for desired operations into second internal table
*in this case, only deletes are passed on
LOOP AT <lt_logtab_source> ASSIGNING <ls_logtab_source>
WHERE iuuc_operat_flag = 'D'.
INSERT <ls_logtab_source> INTO TABLE <lt_target_operation_log_tab>.
ENDLOOP.

*Use MODIFY (same as SQL UPSERT) in case data transfer fails further along
*this way, data can still be successfully retransferred next data transfer
*attempt
MODIFY (lv_zlog_synonym_name) CLIENT SPECIFIED CONNECTION
(lv_dbcon_name) FROM TABLE <lt_target_operation_log_tab>.

IF sy-subrc <> 0.
allog_msg 'E' 'DMC_RT_MSG' '000' 'MODIFY to table'
'operation log' _cobj_alias 'FAILS' 'IL'.
RAISE stopped_by_rule.
ENDIF.

COMMIT CONNECTION (lv_dbcon_name).

[devaanand]

Thank you so much Harminder for sharing the details.

Regards,

Deva

[mcnonld]

Hi Harminder,

Thank you for this post. I have a similar requirement to identify the deleted records in Source system & HANA  target system so that we can deleted those records from the SQL SERVER db.

The Deletion Image record in the Logging Table contains only the Key Fields – hence when we change the image to Update (U) only the key fields will be preserved.

We hope still remain the non-key fields values . could I ask which way to realize it?

 

Thank you in advance.

 

Regards,

yaobin