Qlik Replicate: How to custom target endpoint syntax

john_wang · Sep 17, 2024 5:05:37 AM

Sometimes the target default endpoint behavior does not meet our needs. This article is useful if we want to modify the default syntax.

For example, while MySQL is the target endpoint, Qlik Replicate creates a net changes table and uses it in batch apply mode. The net changes table is created with the default engine type "InnoDB" which has limitations while "MyISAM" and does not have the Row size too large limitation in MySQL.

The below steps demonstrate how to change the net changes table engine type from "InnoDB" (default) to "MyISAM". After the setup is done, Qlik Replicate will create the net changes table automatically with engine type "MyISAM".

Environment

Qlik Replicate All supported versions

Detailed Steps

From the Qlik Replicate computer where you want to import the task, open the Qlik Replicate command line console by doing the following:

From the Start menu, expand Qlik Replicate and then select Qlik Replicate Command Line.

A command-line console is displayed with the correct prompt for Qlik Replicate.

Alternatively, open a Windows Command Prompt using As Adinistrator and change to "<product dir>\Attunity\Replicate\bin" (default location)
Run the following command:
```
repctl.exe getprovidersyntax syntax_name=MySQL > MySQL_MyISAM.json
```
If the DATA folder is non-default location, add option -d data_directory in the command.
Edit the file MySQL_MyISAM.json
1. Remove the first line and the latest line and include:
```
command getprovidersyntax response:

[getprovidersyntax command] Succeeded
```
2. Modify the top few lines:
  
  Original:
```
"provider_syntax": {
 "name": "MySQL",
 "query_syntax": {
```
  Modified:
```
"provider_syntax": {
"name": "MySQL_MyISAM", 
"repository.provider_syntax": { 
"name": "MySQL_MyISAM",
"query_syntax": {
```
3. Add ENGINE type ENGINE=MyISAM
  
  Original:
  
  "create_temporary_table": "CREATE TEMPORARY TABLE ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC} ( ${COLUMN_LIST} ) CHARSET=utf8mb4",
  
  Modified:
  
  "create_temporary_table": "CREATE TEMPORARY TABLE ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC} ( ${COLUMN_LIST} ) ENGINE=MyISAM CHARSET=utf8mb4",
Save changes and move the file to "C:\Program Files\Attunity\Replicate\data\imports", or your custom DATA folder.
Run the following command:
```
repctl putobject data=MySQL_MyISAM
```
Do not add the additional suffix ".json" in the end of the command as this will cause the command to fail.
Add the Internal Parameter syntax to the target endpoint:
1. Go to the Endpoint connection
2. Switch to the Advanced tab
3. Click Internal Parameters
4. Add the parameter: syntax
5. Add the value: MySQL_MyISAM
  
  For more information about Internal Parameter, see Qlik Replicate: How to set Internal Parameters and what are they for?
Resume or Reload the task.

Qlik Replicate: How to custom target endpoint syntax