Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 target endpoint, Replicate creates net changes table and uses it in batch apply mode. The net changes table is being created with default engine type "InnoDB" which has limitation while "MyISAM" does not have that Row size too large limitations in MySQL.
The below steps demonstrate how to change the net changes table engine type from "InnoDB" (default) to "MyISAM". After the setup done, Replicate will create net changes table automatically with engine type "MyISAM".
1. "Run as administrator" to execute "Qlik Replicate Command Line"
2. Change to "C:\Program Files\Attunity\Replicate\bin" (default location)
3. Execute 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. Same in step (6.) below.
4. Edit the file "MySQL_MyISAM.json"
4.1 - Remove the first line and the latest line include
command getprovidersyntax response:
[getprovidersyntax command] Succeeded
4.2 - Modify the top few lines
From
{
"provider_syntax": {
"name": "MySQL",
"query_syntax": {
To (remove the remark of "<----- ...")
{
"name": "MySQL_MyISAM", <---- add this line
"repository.provider_syntax": { <---- add this line
"name": "MySQL_MyISAM",
"query_syntax": {
4.3 - Add ENGINE type ENGINE=MyISAM
From
"create_temporary_table": "CREATE TEMPORARY TABLE ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC} ( ${COLUMN_LIST} ) CHARSET=utf8mb4",
To
"create_temporary_table": "CREATE TEMPORARY TABLE ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC} ( ${COLUMN_LIST} ) ENGINE=MyISAM CHARSET=utf8mb4",
5. Save changes and move the file to "C:\Program Files\Attunity\Replicate\data\imports", or your DATA folder
6. Execute command
repctl putobject data=MySQL_MyISAM
Note do not add the additional suffix ".json" in the end of the command
7. In the target endpoint add an internal parameter "syntax" and input the value "MySQL_MyISAM"
8. Resume or Reload the task.
Backup Replicate DATA directory before you operate the repository.
Another sample Replicate - How to create PRIMARY INDEX rather than PRIMARY KEY in Teradata Target DB