Capture Deleted Records-Using SLT
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.
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.
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.
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.
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.
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.
Conclusion: So after reading this blog you will know about 2 key things.
- How to make the changes to logging table or how to customized the logging table, so required field can be replicated using SLT.
- 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!!
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.
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'.
*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>.
*Use MODIFY (same as SQL UPSERT) in case data transfer fails further along
*this way, data can still be successfully retransferred next data transfer
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'.
COMMIT CONNECTION (lv_dbcon_name).
Thank you so much Harminder for sharing the details.