Qlik Replicate Trigger SAP HANA conversion from Version 1 to Version 2 or Version 3

[Bill_Steinagle]

The Qlik Replicate SAP HANA source endpoint allows you to convert to using the log table with CDC changes. This article documents how to convert the existing triggers into Version 2 or CTS Mode Version 3.

Contact Qlik Support if you plan to convert your triggers, as additional migration steps may be needed to avoid data loss.

SAP HANA considered as running in “V1” mode:

V1 attrep_cdc_changes:

When a task is started for the first time with HANA Trigger Based and without “Log Table” it will create the following artifacts:

• Table attrep_cdc_changes
• Triggers for insert/update/delete with “V1” in their trigger name, for each table in the task list.
• The resulting stream position LSN will always start with “V1:”.

In “V1” mode, the INDX column of the attrep_cdc_changes table is used in the stream position for change data capture.

NOTE:  The “V1” endpoint supports Advanced Run Start From Timestamp.

• the table attrep_cdc_changes
• triggers for insert/update/delete with v1 in their trigger name for each table in the task list
• the resulting stream position LSN will always start with V1:

SAP HANA considered as running in “V2” mode:

V2 attrep_cdc_changes plus attrep_cdc_log:

When Log Table is enabled (Advanced tab) the endpoint will be considered to be running in “V2” mode. 

Migrated endpoints: For existing “V1” endpoints being migrated to “v2”, the customer must manually create the necessary attrep_cdc_log table and follow the instructions for switching into this mode.   The underlying table triggers will still show “v1” in their name.  If the “SAP Archiving User” filter is specified, the following steps will happen when the task using this endpoint is resumed:

• “v1” triggers will be dropped.
• “v2” triggers will be created.
• attrep_cdc_log and attrep_cdc_changes will be altered to add an APPLICATION_USER column to each table.

Contact Support for more migration instructions when moving from “V1” to V2”.

New endpoints: For new endpoints in new artifact schemas (no prior attrep* tables), the “V2” endpoint upon first usage will create both attrep_cdc_changes and attrep_cdc_log tables. All table triggers will be “v2”. 

In “V2” mode, the $rowid$ column of attrep_cdc_log is used in the stream position for change data capture.

Artifacts involved in “V2” mode are:

• Tables attrep_cdc_changes from before
• Table attrep_cdc_log
• “v1” triggers from before or “v2” triggers (not both).

NOTE: The “V2” endpoint does not support Advanced Run Start From Timestamp. Use the following special query to create the LSN for Advanced Run:

SELECT 'V1:'||MIN("$rowid$")||':'||MAX("$rowid$")||':0:'||MIN("$rowid$") AS "STREAM POSITION" from "$SCHEMA$"."attrep_cdc_log" WHERE "CHANGE_EVENT_TIME" >= 'YYYY-MM-DD HH:MM:SS'

The LSN for “V1” and “V2” endpoints both start with “V1:”.

SAP HANA considered as running in “V3” mode:

CTS Mode must be enabled on SAP HANA

1. Open More Options,
2. Click into the Add feature name textbox and enter: sapHanaCtsMode
3. Use value On
4. Click Add 
   
   
   
   
   

V3 attrep_cdc_changes_cts, known as “CTS Mode”

A “V3” endpoint can be created, or a “V1” or “V2” endpoint can be modified.  In both cases a new Feature Flag sapHanaCtsMode must be added and set to ON. In addition for the “V2” endpoint being converted to “V3” the “Log Table” Advanced parameter must be unchecked.

When the task using this endpoint is started for the first time the following steps will be performed:

• Table attrep_cdc_changes_cts will be created.
• “v3” triggers will be created for each table in the task list.
• The old “V1” and “V2” tables will not be removed.
• The old “v1” and/or “v2” triggers will not be removed.
• The internal LSNs will start with “V3:”.
• The stream position will be based on internal column $rowcts$ from table attrep_cdc_changes_cts.

Note that:

• to avoid data loss when moving to V3, tables should be fully reloaded.  Alternatively, a set of migration steps may be carefully followed.  Contact Support for those details.
• CTS Mode DOES support SAP Archive User filtering when specified on the endpoint.
• “V3” mode does support Advanced Run Start From Timestamp.

Related Content 

How to enable SAP HANA CTS Mode support in Qlik Replicate

Environment

Qlik Replicate