Qlik Replicate

Contents:

• Qlik Replicate Installation Path

• Qlik Replicate Basic Commands

• Qlik Replicate Maintenance Guide

Qlik Replicate Installation Path

On the Hubble Accelerator server, Qlik Replicate is installed under the following directory:

/opt/attunity

The replication logs are created in the following directory:

/opt/attunity/replicate/data/logs

Qlik Replicate Basic Commands

• Startup Qlik Replicate:

  /etc/init.d/areplicate start

• Shutdown Qlik Replicate:

  /etc/init.d/areplicate stop

• Accessing Qlik Replicate Console:

  Open a web browser and enter the following URL: https://[accelerator_IP]:3552/attunityreplicate

User: admin

Password: As detailed in Installing Hubble Accelerator the password is generated on install.

Qlik Replicate Maintenance Guide

Contents:

• Shutdown Qlik Replication
• Importing Replication Tasks
• Exporting Replication Tasks
• Using add-on to Remove Invalid Characters during Replication

Shutdown Qlik Replication

Propagating the transaction log to disk will fail if processes are making database changes. As such the replication service must be stopped before attempting to propagate the log.

Propagating Accelerator In-Memory Changes and Condensing the Log.

1. Connect to Hubble accelerator server.

2. Switch user to the attunity replication user.

   # su attunity

3. Get a list of all the running replication tasks.

   # ps -ef | grep repctl

The running replication tasks are the processes that start repctl reptasksrv <TASK_NAME>. In the example above there are 3 running tasks:

• PRODDTA

• JDE812

• CUSTOM_TABLES

4. For each task returned by the above, run the following command to get the status of the task and if it is running to stop the replication task. (You can also stop the tasks in the Qlik Console, but you should check the status as instructed below)

   # ./repctl connect\; gettaskstatus <TASK_NAME>\; disconnect # ./repctl connect\; stoptask <TASK_NAME>\; disconnect

The stoptask command used above will signal the task to stop, if the task is currently showing latency, then the task will not stop immediately but will stop after processing the changes that are causing the latency. This will allow you to restart the task with the resume feature, this means you do not need to reload the data. If you run the stoptask command and manually kill the associated replication process, then you will not be able to use the resume command to restart the task, you will have to restart the task using the reload functionality in order to ensure data consistency. All the tasks must be stopped before moving on to Step 5 and 6.

5. Shutdown Qlik replication service. This will prevent users form connecting remotely using Qlik Console and starting new tasks.

   # ./S65areplicate stop

6. Confirm that the services has stopped by checking that there are no repctl processes running:

   # ps -ef | grep repctl

Import Replication Tasks

1. Copy the backup file (json) onto the accelerator server in the Linux directory:

   /opt/attunity/replicate/data/imports

2. Log onto the accelerator server and change the owner of the backup file to the user “attunity”, if necessary:

   chown attunity:attunity <FileName.json>

3. Log in or change the accelerator user account that you are logged in as to the “attunity” user:

   su - attunity

4. To import the backup file (json) by entering the following:

   repctl importrepository json_file=<Name_of_Json_File>

For example, for a json file named Ora_Rep_F0911.json, run the following command:

repctl importrepository json_file=Ora_REP_F0911

Export Replication Tasks

1. Log onto the Accelerator server and change directory to /opt/attunity/replicate/data/imports:

   cd /opt/attunity/replicate/data/imports

2. Change the accelerator user account to use the attunity user:

   su - attunity

3. To export an individual task, enter the following:

   repctl exportrepository task=<name_of_task>

For example, if the task is called PRODDTA, enter (note the task name is case sensitive):

repctl exportrepository task=PRODDTA

4. To export all tasks, enter the following:

   repctl exportrepository

A json file will be created in the /opt/attunity/replicate/data/imports directory. This can now be copied from the Accelerator (SCP) and to your backup location.

Using add-on to Remove Invalid Characters during Replication

Summary

This article describes a solution dealing with characters in the ERP source tables that are considered invalid during the replication to Vector and cause the replication task to fail with an error similar to the following:

VWLOAD output: loading Error in file '/opt/attunity/replicate/data/tasks/badora/ vectorwise/1/LOAD00000001.csv', record 1: Illegal character encountered in input Input record: '"@ @?@"' processed 1 records, loaded 0 records, 1 errors

The specific case addressed is when text columns representing descriptions in the ERP system were containing characters that are not UTF-8. A user-defined function called fix_latin was added to the transformation functions. This function accepts a string of characters with mixed encodings, for example it may contain predominantly UTF-8 characters but may also contain characters encoded with Windows-1252 or ISO 8859-1. It will create a UTF-8 output that is based on the following conversions:

0x00 - 0x7F    ASCII - passed through unchanged

0x80 - 0x9F    Converted to UTF8 using Windows-1252 (aka CP1252) mappings 0xA0 - 0xFF     Converted to UTF8 using ISO/IEC 8859-1 (aka Latin-1) mappings

Five characters that do not have a mapping are converted to a string %xx with xx being the hexadecimal value, for example 0x81 is mapped to %81. Multi-byte UTF8 characters will be passed through unchanged, although over-long UTF8 byte sequences will be converted to the shortest normal form (see the original documents for the perl module).

Note that this solution has been designed and tested using Oracle as the source database. The fix_latin function is also available when other source database types are being used but it is untested and unsupported in that scenario.

Manual Configuration Steps for Qlik Replicate

For any column that has invalid characters, a custom transformation can be set up in Qlik Replicate to replace these characters.

1. In the Table Settings, on the Transform tab, select the pen symbol to edit the transformation expression for the column in question:

2. Use the fix_latin function available on the Functions tab under User defined. Reference the source column that needs to be transformed as input to the function (M in the example below).

3. Repeat above steps for all text columns that have the same issue.