Replicate - How to custom target endpoint syntax

[john_wang]

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".

 

Environment

• Qlik Replicate All supported versions

Resolution

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.

Related Content

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 

Qlik Replicate