Skip to main content

Suggest an Idea

Vote for your favorite Qlik product ideas and add your own suggestions.

Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW

SAP HANA source endpoint - Trigger-based CDC - Triggers cleanup

sidneyb
Contributor III
Contributor III

SAP HANA source endpoint - Trigger-based CDC - Triggers cleanup

When removing a Table from a replication task Qlik Replicate doesn't remove the triggers that it has created which requires a manual process to identify and delete triggers that are no longer needed.

Also, when  moving a given table between tasks that have different schemas predefined by the "Create CDC table in schema" parameter at the endpoint level. It end up having duplicated triggers, one set of triggers pointing to the old schema and the new one pointing to the new schema.

Qlik Replicate should handle the trigger removal when a Table is removed from a given replication task to avoid misbehavior and additional manual process to identify and cleanup triggers created by Qlik Replicate.

10 Comments
john_wang
Support
Support

Hello @sidneyb ,

Greetings,

Personally I do not think adding complex logic to Replicate task to manage the triggers  is good approach.  Sometimes the tables are added to a task explicitly (choose the tables during the task design), sometimes the tables are added automatically in runtime by table name patterns (Table Selection Pattern), or both. We may manage/remove/redefine these triggers more freedly by DBA.

There are different options you could adopt:

 

1. You can find the detailed information from User Guide (from Replicate 6.6, page #505) about how to remove the triggers

Removing Replicate Artifacts from the Database

     Unfortunately prior to version 6.6 the User Guide does not contains this information. you can use subsequent options.

 

2. In Replicate Server task folder, all the triggers generation scripts files are kept for your reference. You can get the triggers name and the tirgger definition DDL from these files.

    The default location of the folder on Windows is:

C:\Program Files\Attunity\Replicate\data\tasks\<TaskName>\scripts

 

3. You could get the trigger names list by query Hana System view TRIGGERS and compose a removal script for your own use. For example:

SELECT 'DROP TRIGGER ' || TRIGGER_NAME || ';' FROM "PUBLIC"."TRIGGERS" WHERE TRIGGER_NAME LIKE '%attrep_cdc%' and SCHEMA_NAME='SYSTEM';

 

john_wang_1-1606462632675.png

    You may add more restricts in WHERE subclause to filter the triggers which you want to remove.

 

hope this helps,

 

John.

sidneyb
Contributor III
Contributor III

Hi John,



Thank you for your response.



It seems there is no alternative other than define a manual process outside Qlik Replicate to manage and cleanup triggers created by Qlik Replicate.

Also we've noticed that with some new features such as the CDC Log Table mode (attrep_cdc_log), Qlik replicate creates new triggers but still keep the old ones.


With regards to the User Guide, the page #505 does not mention anything about how to remove triggers, see screenshot below, it would be good if Qlik can update the user guide to state that it's a known limitation and users will have to handle it manually.

QR 6.6QR 6.6


QR userguide_v7.jpg

bobvecchione
Employee
Employee

Hi - It is not in our current plans to change the existing behaviour.

 

bob

john_wang
Support
Support

Hi @sidneyb ,

 

thanks @bobvecchione for your confirmation.

 

The page #505 in the updated PDF file mentions the removal, just like in your picture it says "you can safely remove these artifacts" and the hyper link explains what artifacts being created.

Regarding the another issue, I'd like suggest you create a support case and support team will address it.

 

Regards,

John.

 

 

 

aplima
Contributor III
Contributor III

More than a year has gone, have anything changed in the plans towards adding automated clean up of triggers when tables are removed from tasks?

WalterSchmidt
Contributor II
Contributor II

I think the minimum should be a possibility to remove a trigger via a button in the replicate ui.

Not everybody want to do this manually in SAP and not everybody have the necessary rights/authorizations to do this. And sometime this user is on vacation.

I can create the triggers with no knowledge - why is there no part in replicate to delete them in the same way.

On every change of a table structure in SAP you have to delete the triggers before the SAP transport from development to productive system can be done. Something like a temporary deactivation of this trigger would be a nice feature. 

KattiyaFD
Contributor II
Contributor II

We would love to have a button in the replicate ui to remove a trigger from database.  Since we do not maintain SAP HANA database, we have another business partner who is responsible for maintaining HANA db.  When we transport configurations, we have to ask them to drop the triggers so we can do the transport.  We would like to have the option to drop the trigger ourselves.  Thanks! 

eltonpereira
Contributor II
Contributor II

Echoing the suggestion from @KattiyaFD and @WalterSchmidt 

We are at least three customers now 🙂

Meghann_MacDonald

From now on, please track this idea from the Ideation portal. 

Link to new idea

Meghann

NOTE: Upon clicking this link 2 tabs may open - please feel free to close the one with a login page. If you only see 1 tab with the login page, please try clicking this link first: Authenticate me! then try the link above again. Ensure pop-up blocker is off.

Ideation
Explorer II
Explorer II
 
Status changed to: Closed - Archived