Qlik Enterprise Manager Setup and User Guide



Qlik Enterprise Manager Setup and User

Guide

Qlik Enterprise Manager

May 2024

Last updated: September 02, 2024

HELP.QLIK.COM

trade names, trademarks and/or registered trademarks of the respective owners with which they

are associated.

Setup and User Guide - Enterprise Manager, May 2024 3

1 What's new? 9

1.1 New API methods: Support for viewing or editing the list of tables selected for a task 9

1.2 Support for Azure Active Directory as an IdP for OpenID Connect 9

2 Introduction 10

2.1 Example Enterprise Manager environment 11

2.2 Enterprise Manager architecture 12

3 Terminology 13

3.1 Change Data Capture (CDC) 13

3.2 Full load 13

3.3 Apply latency 13

Latency when applying large transactions 13

Latency when no transactions are being applied 13

3.4 Source latency 13

3.5 Target latency 13

3.6 Overall latency 14

3.7 Source endpoint 14

3.8 Target endpoint 14

3.9 Net Changes table 14

4 Installation and setup 15

4.1 Preparing your system for Enterprise Manager 15

Hardware configuration guidelines 15

Sizing guidelines 16

Software requirements 16

Compatibility with related Qlik Products 17

Replication Management license 17

4.2 Installing Enterprise Manager 18

Installing Qlik Enterprise Manager using the Setup Wizard 19

Upgrading Enterprise Manager 19

Migrating the Enterprise Manager Repository 20

Silently installing Enterprise Manager 21

Silently upgrading Enterprise Manager 22

Silently uninstalling Enterprise Manager 23

4.3 Changing the Enterprise Manager service account 24

4.4 Setting the login authentication method 25

Setting Single Sign-on authentication 25

Setting Single Sign-on authentication with Kerberos 25

Setting form authentication 26

Setting up SAML authentication 27

Setting up Personal Access Token authentication for the API 31

4.5 Starting to work with the Qlik Enterprise Manager Console 36

Registering Licenses 37

5 Security considerations 38

5.1 Setting up HTTPS for the Enterprise Manager console 38

Checking if an SSL Certificate is installed 38

Using the self-signed certificate 39

5.2 Setting up HSTS on Enterprise Manager 41

Contents

Setup and User Guide - Enterprise Manager, May 2024 4

Enabling HSTS 41

Disabling HSTS 41

5.3 Replacing the self-signed certificate on Windows 42

5.4 Setting the hostname and changing the SSL port 43

5.5 Replacing the Master User Password 44

The Master Key 44

High Availability mode 45

5.6 Encrypting the user permissions file 45

5.7 Controlling execution of user-defined commands 46

Executing operating system commands as a different user 47

6 Managing servers 48

6.1 Server requirements 48

Qlik Replicate Server requirements 48

Qlik Compose Server requirements 49

6.2 Adding Servers 49

6.3 Monitoring servers 51

Customizing server columns 52

Searching for servers 52

6.4 Server settings 53

Global error handling 53

Resource Control 54

File Transfer Service 55

External utilities 57

Logging 58

More options 61

Server management permissions 62

6.5 Additional server management options 64

6.6 Registering a license for a monitored server 65

6.7 Viewing server messages 67

7 Defining and managing tasks 68

7.1 Adding tasks 68

Bidirectional replication 70

7.2 Editing and viewing a task description 73

7.3 Adding a source and target endpoint to a task 74

7.4 Selecting tables and/or views for replication 75

Searching for tables/views to use in a replication task 77

Selecting specific tables/views for replication 78

Setting load order 79

Removing specific tables/views from a replication task 80

Creating table/view selection patterns 80

7.5 Editing a replication task 82

7.6 Searching for tasks 82

7.7 Deleting a replication task 82

7.8 Exporting and importing tasks 83

8 Defining and managing endpoints 85

8.1 Defining an endpoint 85

Contents

Setup and User Guide - Enterprise Manager, May 2024 5

8.2 Setting user permissions for a specific endpoint 86

8.3 Editing endpoint configuration information 88

8.4 Viewing endpoint configuration information 89

8.5 Testing an endpoint connection 89

8.6 Duplicating endpoints 90

8.7 Searching for endpoints 90

8.8 Deleting endpoints 90

9 Customizing tasks 91

9.1 Table Settings 91

Performing General tasks for a single table/view 92

Defining transformations for a single table/view 93

Using filters 102

Parallel Load 109

Handling LOB columns 113

Message format 117

Full Load 117

9.2 Defining global rules 118

Starting the Global Transformation Rules wizard 118

129

130

Starting the Global Filter Rules wizard 143

Managing global rules 147

9.3 Using the Expression Builder 148

Overview of the Expression Builder 149

Build an expression 150

Parse an expression 151

Test an expression 151

Using elements in the Expression Builder 153

9.4 Task Settings 178

Metadata 179

Bidirectional 185

Full Load 185

Change Processing 188

Error handling 202

Logging 210

Character substitution 211

File uploads 213

Message Format 214

Transformations and Filters 219

More options 220

10 Monitoring and controlling tasks 221

10.1 Monitoring Replicate tasks 221

Task progress summary 221

Viewing specific tasks 225

Monitoring Full Load replication 228

Monitoring Change Processing replication 233

10.2 Monitoring Compose tasks and workflows 241

Contents

Setup and User Guide - Enterprise Manager, May 2024 6

Task progress summary 241

Monitoring Data Lake tasks 244

Monitoring Data Warehouse tasks 245

Monitoring workflows 246

10.3 Searching for tasks 249

10.4 Customizing task columns 250

10.5 Grouping tasks 251

10.6 Running a task 254

How to run a task 254

Using the Run button options 255

Start Processing 255

Reload target 256

Advanced Run Options 256

Recovering from data folder loss or corruption 262

10.7 Error handling 264

Task error handling 264

Data error handling 264

10.8 Using the monitor tools 265

Logging 265

Downloading a memory report 267

Downloading a diagnostics package 267

10.9 Scheduling jobs 268

11 Messages and notifications 271

11.1 Message Center overview 271

11.2 Customizing the Message Center 273

Searching for messages 274

11.3 Viewing additional information 274

11.4 Notifications 274

Setting a task notification 275

Setting a server notification 282

Managing notifications 286

Required permissions 287

Event IDs in Windows Event Log 288

11.5 Viewing and downloading log files 289

12 Administration 292

12.1 Enterprise Manager settings 292

Enterprise Manager logging settings 292

Message Center purge settings 294

Repository connection settings 295

Qlik Catalog Server connection 296

Analytics - Data collection and purge settings 296

Configuring mail server settings 299

Registering and managing licenses 299

12.2 User permissions 302

Encrypting the User Permissions File 302

Granular access control 303

Contents

Setup and User Guide - Enterprise Manager, May 2024 7

Roles and permissions 307

Working with local groups 309

Managing user permissions 310

Managing Personal Access Tokens 312

12.3 Creating an audit trail 313

Decoding an encoded stream payload 314

13 Configuring Enterprise Manager using the CLI 316

13.1 Setting the Enterprise Manager host address 316

13.2 Setting the Enterprise Manager HTTP port 316

13.3 Setting the Enterprise Manager HTTPS port 317

13.4 Setting the Enterprise Manager root URL 317

13.5 Showing the Enterprise Manager version 317

13.6 Showing the Enterprise Manager CLI Help 317

13.7 Service Commands 317

13.8 Cleaning the self-signed certificate 318

13.9 Setting the audit trail retention size and age 318

13.10 Master User Password commands 318

Generating a random Master User Password 318

Setting or changing the MUK (Master User Key) 318

Setting or changing the Java MUK (Master User Key) 319

13.11 Showing the connectivity and login settings 320

Connectivity settings 320

SAML settings 320

13.12 Fine tuning performance 320

Turning off the Analytics Server 321

Changing the update intervals 321

14 Cataloging tasks in Qlik Catalog 323

14.1 Terminology 323

14.2 Prerequisites 324

14.3 Setting up connectivity to Qlik Catalog 324

Catalog columns 324

14.4 Limitations and considerations 325

14.5 Catalog operations 325

Cataloging tasks 325

Uncataloging tasks 328

Recataloging tasks 328

15 Analytics 329

15.1 Prerequisites 329

Install PostgreSQL 329

Create a dedicated database and assign the required privileges 330

Configure connectivity to PostgreSQL 330

Set up data collection and purging from PostgreSQL 330

Obtaining a license 330

Port 331

Hardware 331

Contents

Setup and User Guide - Enterprise Manager, May 2024 8

15.2 Permissions 331

15.3 Analytics dashboards 331

Trends 332

Trends by server 333

Trends by tasks 334

Top servers 335

Top tasks 336

Capacity planning 338

15.4 Exporting to TSV 339

15.5 Creating filters 340

15.6 Using the Pivot Menu 341

15.7 Analytics repository schema 341

aem_endpoint_type 342

aem_meta_source_database 342

aem_meta_target_database 342

aem_server 342

aem_source_database 343

aem_target_database 343

aem_target_processes 343

aem_task 343

aem_task_name 347

aem_task_previous_metrics 347

aem_task_profile 347

aem_task_state 348

aem_task_stop_reason 348

Sample Queries 348

A Setting up High Availability 352

A.1 Installing Qlik Enterprise Manager in a Windows cluster 352

A.2 Upgrading Qlik Enterprise Manager in a Windows cluster 355

A.3 Uninstalling Qlik Enterprise Manager from a Windows cluster 357

B Impact of DST change on Qlik Replicate 360

Contents

1 What's new?

1  What's new?

This section describes the new and enhanced features in Enterprise Manager May 2024.

In addition to these release notes, customers who are not upgrading from the latest GA

version are advised to review the release notes for all versions released since their

current version.

Customers should also review the Enterprise Manager release notes in ≤ Qlik Community for

information about the following:

l

Migration and upgrade

l

End of life/support features

l

Newly supported OSplatforms, databases, and third-party software versions.

l

Resolved issues

l

Known issues

1.1  New API methods: Support for viewing or editing

the list of tables selected for a task

This version introduces the ability to view or edit the list of tables or views currently selected for a

specific task via the API. To facilitate this new functionality, the following methods have been

added to the REST, .NET, and Python APIs:

l

GetTaskTableSelection:Call this method to view the list of tables or views selected for

inclusion in a specific Replicate task.

l

PutTaskTableSelection: Call this method to override the list of tables or views selected for

inclusion in a specific Replicate task.

1.2  Support for Azure Active Directory as an IdP for

OpenID Connect

In previous versions, Okta was the only IdP supported for OpenID Connect login via the API. Starting

from this version, you can also use Azure Active Directory as an IdP for logging in using OpenID

Connect.

For more information, see

Setting the login authentication method (page 25)

Setup and User Guide - Enterprise Manager, May 2024 9

2 Introduction

2  Introduction

Qlik Enterprise Manager, also referred to as Enterprise Manager, provides a single point of control

for designing, executing, and monitoring Qlik Replicate and Compose tasks throughout your

organization. If your site has multiple Qlik servers with tens, if not hundreds of tasks, Enterprise

Manager greatly eases the design, management, and monitoring of these tasks. Whether your site

deploys a single Qlik server or multiple servers, Enterprise Manager is your single go-to interface to

create data endpoints, design tasks, execute them, and monitor the replication process in near real-

time. In addition, Enterprise Manager lets you view all tasks in a tabular format that offers advanced

grouping and filtering capabilities.

The following figures show a high-level view of a possible Enterprise Manager installation

environment and a more detailed architecture diagram.

Note that components labeled as "Qlik Server" can either be Qlik Replicate or Qlik Compose.

Setup and User Guide - Enterprise Manager, May 2024 10

2 Introduction

2.1  Example Enterprise Manager environment

Setup and User Guide - Enterprise Manager, May 2024 11

2 Introduction

2.2  Enterprise Manager architecture

Setup and User Guide - Enterprise Manager, May 2024 12

3 Terminology

3  Terminology

The following section describes some key terms used throughout this Help.

3.1  Change Data Capture (CDC)

Captures changes in the source data or metadata as they occur and applies them to the target

endpoint as soon as possible, in near-real-time. The changes are captured and applied as units of

single committed transactions and several different target tables may be updated as the result of a

single source commit. This guarantees transactional integrity in the target endpoint. The CDC

process for any file or table starts as soon as the data loading operation for the file or table begins.

3.2  Full load

Creates all defined files or tables on the target endpoint, automatically defines the metadata that is

required at the target, and populates the tables with data from the source.

3.3  Apply latency

The gap in seconds between capturing a change in one of the source tables and applying that

change to the target endpoint.

Latency when applying large transactions

This is best explained by way of example. When the most recent Apply Latency value was 10

seconds and now a transaction of one million rows gets committed at the source endpoint,

Replicate starts to apply that transaction to the selected target and it will take some time to write all

the changes to the target (for example 60 seconds). During the next 60 seconds, the latency value

gradually grows to 70 seconds for the last change in the transaction. Once the transaction is

committed, the latency drops back to the 'regular' latency (10 seconds in this case).

Latency when no transactions are being applied

When a time period passes with no changes applied to the target, the latency calculation is based

on the time difference between the current time and the timestamp of the last change event read

from the transaction log. This could happen, for example, if there is a high volume of activity on

tables that were not selected for replication in the current task.

3.4  Source latency

The gap in seconds between when the source database wrote an event to its transaction log and

when Replicate captured that change.

3.5  Target latency

The gap between when a commit is seen by Replicate (reading the source transaction log) and

when the changes of that commit are seen in the target.

Setup and User Guide - Enterprise Manager, May 2024 13

3 Terminology

3.6  Overall latency

The overall latency is defined as the time gap between when a change is committed in the source

database and when it is visible in the target database.

3.7  Source endpoint

A collection of files or tables managed by an endpoint management system (such as, Oracle, SQL

Server) that is part of the main computing service of the IT organization of an enterprise. This

source continuously updated, may need to provide a high throughput rate, may have strict 24/7 up-

time requirements, and may reference or update a number of tables in the course of a single logical

transaction while providing transactional consistency and integrity for the data.

3.8  Target endpoint

A collection of files or tables managed by an Endpoint Management System (DBMS), which may be

different from the DBMS managing the source endpoint. It contains data that is derived from the

source. It may contain only a subset of the tables, columns, or rows that appear in the source. Its

tables may contain columns that do not appear in the source but are transformations or

computations based on the source data.

3.9  Net Changes table

Replicate performs data replication based on changes that appear in the source database's

transaction log. A single update operation on the source, such as "UPDATE MyTable SET f1=...,

f2=..." could potentially update many rows in the source database and create a large number of

change records that Replicate will need to apply to the target. Replicate offers two Change

Processing modes:Transactional apply and Batch optimized apply. In Transactional apply

Change Processing mode, Replicate essentially applies each change to the target, which may take

much longer than the original UPDATE took on the source. Batch optimized apply mode, on the

other hand, is designed to handle efficient replication of a large number of changes. In this mode,

Replicate accumulates changes for multiple tables in a memory cache. Repeated changes to the

same row are updated in the memory cache. When the maximum memory cache size defined for

the task is reached (or when the configured time has elapsed), Replicate does the following:

a. Writes the cached (net) changes to a special table on the target (the Net Changes table),

b. Bulk uploads the changes to the target table

c. Uses SQL statements to update the target tables based on the data in the Net Changes

table.

Note that for Oracle, Replicate uses a Net Changes table per each source table with

changes, while for other source endpoints a single Net Changes table is used.

Setup and User Guide - Enterprise Manager, May 2024 14

4 Installation and setup

4  Installation and setup

This section describes how to install and set up Qlik Enterprise Manager (Enterprise Manager).

For instruction on installing Enterprise Manager in a Windows Cluster, see Installing Qlik

Enterprise Manager in a Windows cluster (page 352).

Enterprise Manager collects information from Qlik Replicate and/or Qlik Compose Servers to allow a

central point of monitoring for all Replicate and/or Compose Servers in your organization.

Therefore, you also need to install Qlik Replicate and/or Qlik Compose in your organization. For a

description of the respective installation procedures, refer to the Qlik Replicate and/or Qlik

Compose product Help.

In this section:

l

Preparing your system for Enterprise Manager (page 15)

l

Installing Enterprise Manager (page 18)

l

Changing the Enterprise Manager service account (page 24)

l

Setting the login authentication method (page 25)

l

Starting to work with the Qlik Enterprise Manager Console (page 36)

4.1  Preparing your system for Enterprise Manager

This section describes the hardware and software requirements for Qlik Enterprise Manager and

the monitored Replicate Servers.

In this section:

l

Hardware configuration guidelines (page 15)

l

Software requirements (page 16)

l

Sizing guidelines (page 16)

l

Compatibility with related Qlik Products (page 17)

l

Replication Management license (page 17)

Hardware configuration guidelines

It is recommended that the machine hosting Qlik Enterprise Manager meets or exceeds the

hardware configuration shown in the following table:

Medium System Large System

Processor 4-core base 16-core base

Memory 8 GB 32 GB

Hardware requirements

Setup and User Guide - Enterprise Manager, May 2024 15

4 Installation and setup

Medium System Large System

Disk requirements 128 GB 256 GB

Network 1 Gbps 1 Gbps

Sizing guidelines

Depending on the number of Replicate tasks and concurrent users, you might need to balance the

system load between multiple Enterprise Manager machines. For example, if your hardware

configuration is set up for a large system (40 Replicate servers), it is recommended to monitor no

more than 4000 Replicate tasks on a single Enterprise Manager machine. When you near this

threshold, then the recommendation is to split the tasks between at least two Enterprise Manager

machines by dividing the number of monitored Replicate servers between the Enterprise Manager

machines. Similarly, if your hardware configuration is set up for a medium system and the number of

Replicate tasks and users concurrently accessing the system is approaching the maximum

threshold (see table below), then best practice is to split the monitored Replicate servers between

at least two Enterprise Manager machines.

For information on hardware configuration guidelines, see

Hardware configuration guidelines (page

15)



Medium

System

Large

System

Replicate servers 8 40

Tasks 800 4000

Concurrent users(Rate of public API calls may affect these numbers) 10 80

Sizing guidelines

To some extent, how you set up granular access control might also impact performance.

This is usually only a factor when many users are assigned different roles and

permissions.

Software requirements

Parts of the software use Java components utilizing OpenJDK JRE, which is included in

the Enterprise Manager installation.

Supported Windows platforms

It is strongly recommended to install Qlik Enterprise Manager on a dedicated Windows server,

separate from the Replicate and/or Compose Servers that it manages.

Enterprise Manager can be installed on any of the following Windows platforms:

Setup and User Guide - Enterprise Manager, May 2024 16

4 Installation and setup

l

Windows Server 2016 (64-bit)

l

Windows Server 2019 (64-bit)

l

Windows Server 2022 (64-bit)

Supported browsers

The following browsers can be used to access the Enterprise Manager Console:

l

Microsoft Edge (with automatic updates turned on)

l

Mozilla Firefox (with automatic updates turned on)

l

Google Chrome (with automatic updates turned on)

Port

Port 443 must be opened for inbound connections on the Enterprise Manager machine.

Additional software requirements

Qlik Enterprise Manager requires the following software:

l

Microsoft Visual Studio C++ 2017 x64 Redistributable

l

.NET Framework 4.8 or later

l

To use the Analytics feature, PostgreSQL 12.16 or later should be installed either on the

Enterprise Manager machine or on a machine that is accessible from Enterprise Manager.

l

TLS 1.2 or later must be supported in the underlying OS.

On Windows Server 2012 R2, TLS 1.2 should be turned on by default. If it is not,

refer to the Microsoft online help for instructions on how to turn it on.

See also:

Compatibility with related Qlik Products (page 17)

Compatibility with related Qlik Products

This version of Enterprise Manager is compatible with the following related products only:

l

Qlik Replicate November 2023 SR1, Qlik Replicate November 2023, Qlik Replicate May 2023,

Qlik Replicate November 2022, Qlik Replicate May 2022.

l

Qlik Compose November 2023 (and its Service Releases) only.

l

Qlik Catalog May 2023 SR2 only.

Replication Management license

This section explains how to obtain a Replication Management license and lists the processes that

continue even when the license expires or is invalid.

Obtaining a Replication Management license

A Replication Management license is required in order to use Qlik Enterprise Manager. If no license

is detected, a Register License message will be displayed when you open the Qlik Enterprise

Manager Console.

Setup and User Guide - Enterprise Manager, May 2024 17

4 Installation and setup

The procedure below does not apply when installing Enterprise Manager in a

High

Availability Cluster

. To obtain a Replication Management license for Enterprise Manager

in a High Availability Cluster, you must provide your Qlik Sales Representative with the

following information, depending on which Windows version the cluster is running:



Windows Server 2016: The official cluster FQDN.



Windows Server 2012 R2: The FQDN of each of the cluster nodes and the official

cluster FQDN.

To obtain a license

1. Open the Qlik Enterprise Manager Console and copy the Enterprise Manager machine name

from either of the following locations:

l

The Register License message that is displayed when you open the Qlik Enterprise

Manager Console.

l

The bottom of the Licenses tab in the Settings window.

2. Request a license from your Qlik Sales Representative, making sure to include the Enterprise

Manager machine name in your request.

Process that continue if the license expires or is invalid

The following processes will continue, even if the Replication Management license expires or is

invalid:

l

Notifications will continue to be sent.

l

Tasks monitoring information and messages will continue to be collected from Replicate.

However, they will not be visible until a valid Replication Management license is installed.

In such a situation, do one of the following:

l

Stop the Enterprise Manager service or uninstall the product if you do not intend to use it

anymore.



4.2  Installing Enterprise Manager

Enterprise Manager must be installed under an Administrator account.

In this section:

l

Installing Qlik Enterprise Manager using the Setup Wizard (page 19)

l

Silently installing Enterprise Manager (page 21)

Setup and User Guide - Enterprise Manager, May 2024 18

4 Installation and setup

l

Silently upgrading Enterprise Manager (page 22)

l

Silently uninstalling Enterprise Manager (page 23)

Installing Qlik Enterprise Manager using the Setup Wizard

The following section describes how to install Enterprise Manager.

To install Enterprise Manager:

1. Run the Enterprise Manager setup file (

QlikEnterpriseManager_<version.build>_

<systemtype>.exe

, such as

QlikEnterpriseManager_7.0.0.105_X64.exe

The Enterprise Manager setup wizard opens.

2. Optionally, change the installation directory; then click Next.

3. Optionally, change the data directory; then click Next.

All of the data that is created when you use Enterprise Manager is stored in a directory called

data. By default, this directory is located in the installation directory where you install

Enterprise Manager.

If you change the data directory location, you must prefix all command line actions

with:

-d path_to_the_data_directory

Example:

<product_dir>\bin\AemCtl.exe -d F:\data configuration set -a 123.123.12.1

4. Click Next again to start the installation.

5. When the installation completes, click Finish.

As part of the installation, a new Windows Service called Enterprise Manager is created.

The Enterprise Manager analytics module require a PostgreSQL database. If you plan on

using this module, you will need to install PostgreSQL on either the Enterprise Manager

machine or on a machine that is accessible from Enterprise Manager.

For your convenience, PostgreSQL is included with Enterprise Manager and you will be

prompted to install it after clicking Finish.

6. Click Yes to install PostgreSQL on the Enterprise Manager server or No to exit without

installing PostgreSQL. You can always install PostgreSQL at a later time by running the

PostgreSQL installer from the following location:

<Enterprise_Manager_INSTALLATION_FOLDER>\postgresqlkit

For instructions on installing and maintaining PostgreSQL, refer to the PostgreSQL Help.

7. Open the Enterprise Manager console as described in

Starting to work with the Qlik

Enterprise Manager Console (page 36)

Upgrading Enterprise Manager

The upgrade process also describes how to back up the Enterprise Manager "data" folder, which

will allow you to restore your settings if you encounter any issues with the upgrade.

To upgrade Enterprise Manager:

Setup and User Guide - Enterprise Manager, May 2024 19

4 Installation and setup

1. Back up your currently installed version, by copying the Enterprise Manager data folder to a

location outside the product folder. The default data folder location is C:\Program

Files\Attunity\Enterprise Manager\data.

2. Run the Enterprise Manager setup wizard to install the new version.

3. If you notice an issue with the upgrade, you can revert to the previous version as described

below or do the following:

a. Stop all the Enterprise Manager services.

b. Overwrite the data folder with the data folder that you backed up earlier.

c. Start all the Enterprise Manager services.

Reverting to a Previous Version

To revert to a previous version:

1. Back up the Enterprise Manager data directory to a location outside the product folder.

2. Uninstall the product and then reinstall to the same directory.

3. Once the installation is complete, stop all Enterprise Manager services.

4. Overwrite the data directory with the data directory that you backed up earlier.

5. Start all Enterprise Manager services.

Migrating the Enterprise Manager Repository

In certain situations, you may need to migrate Replicate or Compose Servers and settings from one

Enterprise Manager Server to another. This may be required, for example, if you need to move from

a test environment to a production environment or if you need to decommission the Enterprise

Manager Server machine. In the migration procedure, which is described below, Server A is the

Enterprise Manager Server configured with Replicate or Compose Servers, while Server B is a clean

installation of Enterprise Manager Server.



All commands should be run as administrator.



The same user must be used for the installation on both servers.

1. On Server A:

a. Run the following commands:

Command 1 - Sets the Master User Key:

<INSTALL_DIR>\bin\aemctl.exe [-d data_folder_path] masterukey set -

p password

where -d data_folder_path is only required if the <INSTALL_DIR>\data folder is in

a non-default location.

Command 2 - Sets the Java Master User Key:

<INSTALL_DIR>\java\bin\atajs.bat [-d java_data_folder_path]

masterukey set password

where -d java_data_folder_path is only required if the <INSTALL_DIR>\data\java

Setup and User Guide - Enterprise Manager, May 2024 20

4 Installation and setup

folder is in a non-default location.

b. Restart the QlikEnterprise Manager service.

2. On Server B:

a. Stop the Qlik Enterprise Manager service.

b. Delete the <INSTALL_DIR>\data folder.

c. Copy the data folder from Server A to Server B.

d. Run the following commands:

Command 1:

<INSTALL_DIR>\bin\aemctl.exe [-d data_folder_path] masterukey set -

p password

where -d data_folder_path is only required if the <INSTALL_DIR>\data folder is in

a non-default location.

Command 2:

<INSTALL_DIR>\java\bin\atajs.bat [-d java_data_folder_path]

masterukey set password

e. Start the QlikEnterprise Manager service.

f. Log in to Enterprise Manager and verify that the Replicate and Compose Servers have

been migrated from Server A to Server B, and that all of Server A's configuration

settings have been migrated as well.

Silently installing Enterprise Manager

Enterprise Manager can be installed silently (i.e. without requiring user interaction). This option is

useful, for example, if you need to install Enterprise Manager on several machines throughout your

organization.

Before commencing the installation, make sure that the prerequisites have been met.

The installation process consists of two stages:

1.

Creating a response file (page 21)

2.

Running the silent install (page 22)

Creating a response file

Before starting the installation, you need to create a response file.

To create the response file:

1. From the directory containing the Qlik Enterprise Manager setup file, run the following

command(note that this will also install Enterprise Manager):

QlikEnterpriseManager_version_X64.exe /r /f1my_response_file

where:

my_response_file is the full path to the response file that will be generated.



Setup and User Guide - Enterprise Manager, May 2024 21

4 Installation and setup

Example:

QlikEnterpriseManager_6.1.0.536_X64.exe /r /f1C:\Enterprise Manager_install.iss

At the end of the Enterprise Manager installation, when you are prompted to run

the PostgreSQL installer, click No.

2. To change the default installation directory, open the response file in a text editor and edit

the first szDir value as necessary.

3. To change the default data directory, edit the third szDir value as necessary.

4. Save the file as <name>.iss, e.g. silent_inst_64.iss.

Running the silent install

To silently install Qlik Enterprise Manager, open a command prompt and change the working

directory to the directory containing the Enterprise Manager setup file. Then issue the following

command:

Syntax:

QlikEnterpriseManager_version_X64.exe /s /f1my_response_file [/f2log_file]

where:

my_response_file is the full path to the response file you created earlier and log_file is the path to

the optional log file.

Example:

C:\>QlikEnterpriseManager_6.1.0.536_X64.exe /s /f1C:\temp\1\Enterprise Manager_install.iss

/f2C:\temp\1\silent_x64_install.log

If the installation was successful, the log file should contain the following rows:

[ResponseResult]

ResultCode=0

Silently upgrading Enterprise Manager

Silently upgrading Enterprise Manager consists of two stages:

1.

Creating a response file (page 22)

2.

Running a silent upgrade (page 22)

Creating a response file

Before starting the upgrade, you need to create a response file. You may also want to back up your

current installation as described in

Upgrading Enterprise Manager (page 19)

For an explanation of how to create a response file, see Step 1 of

Creating a response file (page 21)

Running a silent upgrade

To silently upgrade Enterprise Manager, open a command prompt and change the working

directory to the directory containing the Enterprise Manager setup file.

Then issue the following command:

Setup and User Guide - Enterprise Manager, May 2024 22

4 Installation and setup

Syntax:

QlikEnterpriseManager_version_X64.exe /s /f1my_response_file [/f2log_file]

where:

my_response_file is the full path to the response file you created earlier and log_file is the path to

the optional log file.



Example:

C:\>QlikEnterpriseManager_6.1.0.536_X64.exe /s /f1C:\temp\1\Enterprise Manager_upgrade.iss

/f2C:\temp\1\silent_x64_up.log



If the upgrade was successful, the log file should contain the following rows:

[ResponseResult]

ResultCode=0

Silently uninstalling Enterprise Manager

Silently uninstalling Enterprise Manager consists of two stages:

1.

Creating a response file (page 23)

2.

Running a silent uninstall (page 24)

Creating a response file

Before starting the uninstall, you need to create a response file.

To create the response file:

1. Copy the following (response file) text into a text editor:

[{999A7077-16C9-4B3B-AFD2-CBBA9FA72C15}-DlgOrder]

Dlg0={999A7077-16C9-4B3B-AFD2-CBBA9FA72C15}-SdWelcomeMaint-0

Count=3

Dlg1={999A7077-16C9-4B3B-AFD2-CBBA9FA72C15}-MessageBox-0

Dlg2={999A7077-16C9-4B3B-AFD2-CBBA9FA72C15}-SdFinish-0

[{999A7077-16C9-4B3B-AFD2-CBBA9FA72C15}-SdWelcomeMaint-0]

Result=303

[{999A7077-16C9-4B3B-AFD2-CBBA9FA72C15}-MessageBox-0]

Result=6

[{999A7077-16C9-4B3B-AFD2-CBBA9FA72C15}-SdFinish-0]

Result=1

bOpt1=0

bOpt2=0

2. Save the file as <name>.iss, e.g. silent_uninst_64.iss.

Setup and User Guide - Enterprise Manager, May 2024 23

4 Installation and setup

Running a silent uninstall

To silently uninstall Enterprise Manager, open a command prompt and issue the following

command:

Syntax:

"C:\Program Files (x86)\InstallShield Installation Information\<directory_containing_setup_

file>\setup.exe" /s /f1my_response_file /f2log_file

where:

my_response_file is the full path to the response file you created earlier and log_file is the path to

the optional log file.

The directory containing the Enterprise Manager setup file always ends with the

following string: CBBA9FA72C15

Example:

C:\>"C:\Program Files (x86)\InstallShield Installation Information\{999A7077-16C9-4B3B-AFD2-

CBBA9FA72C15}\setup.exe" /s /f1C:\temp\response.iss /f2C:\temp\1\silent_uninstall.log



If the uninstall was successful, the log file should contain the following rows:

[ResponseResult]

ResultCode=0

4.3  Changing the Enterprise Manager service account

By default, Enterprise Manager is installed with Administrator privileges. For secuirty reasons, you

may want Enterprise Manager to run under a user account that does not have Administrator

privileges.

To do this:

1. Install Enterprise Manager.

2. Create a local user without administrative privileges.

3. Reserve the URL for the user you just created by running the following commands:

netsh http add urlacl url=https://+:443/AttunityEnterpriseManager

user=DOMAIN\youruser

netsh http add urlacl url=http://+:80/AttunityEnterpriseManager

user=DOMAIN\youruser

4. Grant the new user the Full control permission for the Enterprise Manager data folder (<EM_

INSTALL_DIR>\data).

5. Open the Local Security Policy window and select Local Policies>User Rights

Assignment. Then grant the Log on as a service policy to the new user.

6. Stop the Enterprise Manager service.

Setup and User Guide - Enterprise Manager, May 2024 24

4 Installation and setup

7. In the Log On tab of the Enterprise Manager service properties, select This account and

specify the new user name in the following format:

.\NewUser

8. Save your changes.

9. Start the Enterprise Manager service.

4.4  Setting the login authentication method

By default, Enterprise Manager uses Single Sign-on through Windows Authentication to

authenticate users. This allows users to open the Enterprise Manager Console without providing

additional authentication. To require users to authenticate themselves at login, you can change the

authentication method to Form or SAML. Setting the authentication method is done using the

Enterprise Manager CLI, as described below.

To see the current authentication settings, run the command described in

Showing the connectivity

and login settings (page 320)



All commands in this section should be "Run as administrator" from the Enterprise

Manager bin directory. The default location is C:\Program

Files\Attunity\Enterprise Manager\bin.



When the Enterprise Manager data folder is in a non-default location (such as in a

cluster installation), make sure to include the

--d data_folder

parameter in all

commands, where

data_folder

is the location of the data folder. The parameter

should immediately follow the name of the Enterprise Manager executable file

(e.g.

aemctl --d f:\mydatafolder {command} {parameters}

)



Changes to the authentication method will take affect only after you restart the

Enterprise Manager service.



If Form authentication is used, all Login/Logout operations are reported to the

Audit Trail

Setting Single Sign-on authentication

This is the default authentication method, which uses Windows authentication.

To set the authentication method to single sign-on, run:

aemctl.exe configuration set --authentication_method sso

Abbreviated form of --sso: -w

Setting Single Sign-on authentication with Kerberos

Kerberos is an enterprise authentication protocol that uses the concept of tickets and three-way

authentication to enable users and computers to identify themselves and secure access to

resources.

Setup and User Guide - Enterprise Manager, May 2024 25

4 Installation and setup

Using Kerberos SSO, users can seamlessly log into Enterprise Manager and administrators can

completely externalize and centrally manage users or group memberships using their existing

Kerberos infrastructure.

To set the authentication method to single sign-on with Kerberos, run:

aemctl.exe configuration set --authentication_method sso-kerberos

If the Kerberos protocol fails, Enterprise Manager will try to log in using NTLM

authentication. If NTLM authentication is not enabled in the system, an error will be

returned.

Setting form authentication

As opposed to Single Sign-on through Windows Authentication, this method requires users to

provide a user name and password at login.

To set the authentication method to Form, run:

aemctl.exe configuration set --authentication_method form

Abbreviated parameter: -f

Setting a user timeout

Parameter: --user_timeout

Abbreviated form: -u

When setting --form authentication, you can use this parameter to override the default user idle

timeout period (5 minutes) or to disable user idle timeout entirely.

When a user idle timeout value is specified, Enterprise Manager will automatically log out users that

have been inactive for the specified time period (or longer).

To set a user timeout when using Form authentication, run:

aemctl.exe configuration set --authentication_method form --user_timeout

timeout

Where timeout is the length of time in minutes after which users will be logged out. The minimum

permitted value is 1 minute.

For example, to set a user-idle timeout period of two minutes, run:

aemctl.exe configuration set --authentication_method form --user_timeout 2

To disable the user-idle timeout entirely, run:

aemctl.exe configuration set --authentication_method form --user_timeout -1

Specifying an Active Directory domain

Parameter: --domain

Setup and User Guide - Enterprise Manager, May 2024 26

4 Installation and setup

Abbreviated form: -m

When setting --form authentication, you can use this parameter to specify an Active Directory

domain name that will be used when a user logs in with a user name only (i.e. without a domain

name).

To set a user timeout when using Form authentication, run:

aemctl.exe configuration set --authentication_method form --domain

DomainName

Where DomainName is the name of the domain.

For example, to set the domain to ad2_acme, run:

aemctl.exe configuration set --authentication_method form --domain ad2_acme

By default, when only a user name is provided in the login form, the domain of the server is used. If

the server does not belong to any domain the server machine name will be used instead.

A user who logs in as "doe" will be assumed to identify as "ad2_acme\doe". If a user specifies a fully

qualified domain name when logging in, this parameter is ignored.

Setting up SAML authentication

This login method requires you to log in via your organization's SAML Identity Provider. The

command parameters for setting SAML authentication are the same regardless of your SAML

provider, although the parameter values are slightly different.

The setup procedure consists of the following steps:

l

Step 1: Set up SAML on Enterprise Manager (page 28)

l

Step 2: Set Up an Enterprise Manager Superuser (page 29)

l

Step 3: Log in to Enterprise Manager and create SAML users (page 30)

Setup and User Guide - Enterprise Manager, May 2024 27

4 Installation and setup

Before running the commands, you must have already configured Enterprise Manager as

an application in your SAML Identity Provider.

When Enterprise Manager is not installed in a Cluster, the Enterprise Manager

Assertion Consumer Service (ACS) URL is:

https://{host_name}/attunityenterprisemanager/rest/?action=login_saml

When Enterprise Manager is installed in a Cluster, make sure to fulfill the following

prerequisites:



Finish the cluster install on all nodes before configuring SAML.



In order to propagate the configuration changes, make sure to include the

-d

data_folder

parameter in the SAML configuration commands described below,

where

data_folder

is the location of the cluster's shared data folder.



The Assertion Consumer Service (ACS) URL which the IDP should call when

redirecting SAML calls should be the cluster wide DNS name (as opposed to a

specific machine name).

This is how it should look:

https://{em-cluster-fqdn}/attunityenterprisemanager/rest/?action=login_saml

For more information about setting up Enterprise Manager in a cluster, see Installing Qlik

Enterprise Manager in a Windows cluster (page 352).

Step 1: Set up SAML on Enterprise Manager

To set the Enterprise Manager authentication method to SAML, run:

Syntax:

aemctl [--d data_folder] configuration set --authentication_method SAML --idp_url

SsoUrl

idp_issuer

issuer_name

--idp_certificate_file

CertificateFile

[--idp_user_displayname_

attribute

displayname

] [--idp_username_attribute

username

] [--idp_user_groups_attribute

groups

]

Example: Using Microsoft Azure as the SAML IdP

aemctl configuration set --authentication_method SAML --idp_url

https://login.microsoftonline.com/12854727-3c42-4866-ab29-0c418b8310a1/saml2 --idp_issuer

aemdevtest --idp_certificate_file AEMDevTest.pem

Where:

l

SsoUrl is the URL of the SAML IdP (Identity Provider) that handles sign-in requests.

When using Okta, this is the Okta Single Sign-On URL.

When using Microsoft Azure, this is the AzureAD SAML single sign-on URL.

Enterprise Manager will direct users to this URL to complete the SAML login operation.

l

issuer_name is a unique name that the identity provider uses for SAML 2.0.

When using Okta, this should be a URL.

Setup and User Guide - Enterprise Manager, May 2024 28

4 Installation and setup

When using Azure, this should be a string.

l

CertificateFile - The certificate is used by the IdP to sign the SAML assertions sent to

Enterprise Manager. The certificate file can be anywhere on the Enterprise Manager

machine, but only needs to be specified with a path when it does not reside in the Enterprise

Manager bin directory.

When using Okta, the certificate must be in .cert format.

When using Microsoft Azure, the certificate must be in .pem format.

l

data_folder - The location of the data folder when it is not the default location such as when

Enterprise Manager is installed in a Windows cluster.

Optional Parameters:

The following parameters are optional and should only be used if required by your SAML IdP:

l

--idp_user_displayname_attribute - The user display name attribute.

l

--idp_username_attribute - By default, with SSO, the SAML Assertion’s “Subject” attribute is

used to define the username. Using the subject is the right solution in most situations, but in

extreme cases (such as the subject being a transient ID) it may be necessary to provide the

username in some other form.

l

--idp_user_groups_attribute - The user group attribute.

Once you have set up SAML, you need to restart the Qlik Enterprise Manager service for

the settings to take effect.

Step 2: Set Up an Enterprise Manager Superuser

The first time you log in to Enterprise Manager using SAML, you must log in as an Enterprise

Manager superuser. This is because none of the existing (or default) Enterprise Manager users are

authorized SAML users.

The instructions below assume that you have already changed the default Master User

key. For instruction on how to do this, see Generating a random Master User Password

(page 318) and Setting or changing the MUK (Master User Key) (page 318) respectively.

To set up a superuser, run the following command:

aemctl authorization setsuperuser -s username -e ExpirationTimeoutInMinutes -

m MasterUserKey

Where:

l

username is the superuser user name. The user must be an existing SAML user and can

contain any Unicode character up to 255 characters.

l

ExpirationTimeInMinutes is the expiration time for the specified user. The maximum is

60 minutes.

l

MasterUserKey is your Master User Key.

Setup and User Guide - Enterprise Manager, May 2024 29

4 Installation and setup

Step 3: Log in to Enterprise Manager and create SAML users

After setting up SAML authentication, you will be presented with the following page when you try to

open the Qlik Enterprise Manager console:

1. Click Log In with SAML.

You will be redirected to Okta or Microsoft Azure to provide your SAML login credentials.

2. Authenticate yourself with SAML.

After successful authentication, you will be redirected back to the Enterprise Manager

Console.

3. Add authorized SAML users and groups, as described in

Managing user permissions (page

310)

For information about other CLI options, see

Configuring Enterprise Manager using the CLI (page

316)

Switching between SAML and SSO/Form authentication

If you wish to switch from SAML to SSO/Form authentication (or vice versa), you may need to clear

the existing users from Enterprise Manager. When switching from SAML to SSO/Form

authentication, you will not be able to change user permissions or add users unless you clear the

existing SAML users. However, when switching from SSO/Form authentication to SAML, you do not

need to clear the existing users in order to add/delete users or edit user permissions. You may still

want to do this however if you find the presence of non-SAML users distracts you from managing

the SAML users.

Before clearing the users, it is strongly recommended to export them as this will save

time (by importing them) should you later need to revert the authentication type.

To export all users to a JSON file, run the following command:

aemctl repository export_acl -f [fullpath\]filename

where filename is the name of the file to which the users will be exported. By default, the file will be

exported to the Enterprise Manager bin directory. If you wish the file to be exported to a custom

path, include [fullpath\] in the command.

Setup and User Guide - Enterprise Manager, May 2024 30

4 Installation and setup

Example:

aemctl repository import_acl -f C:\temp\Enterprise ManagerUsers

To clear all users, run the following command:

aemctl repository clear_acl

To import users from the exported JSON file, run the following command:

aemctl repository import_acl -f [fullpath\]filename

where filename is the name of the file to import. Unless you specified a custom export path, the file

will be located in the Enterprise Manager bin directory. If you wish the file to be imported from a

custom path, include [fullpath\] in the command.

Example:

aemctl repository import_acl -f C:\temp\Enterprise ManagerUsers

Setting up Personal Access Token authentication for the API

Personal Access Token authentication is only supported with the Enterprise Manager API.

The high-level flow consists of the following steps:

l

Step 1: In Okta or Azure Active Directory, create an app integration that uses OpenID Connect

l

Step 2: In Enterprise Manager, enable Enterprise Manager to communicate with your IdP

(Okta or Azure Active Directory).

l

Step 3: In Enterprise Manager, generate a Personal Access Token

l

Step 4: Configure the Enterprise Manager API to log in using the Personal Access Token

Step 1: Create an app integration that uses OpenID Connect

Enterprise Manager uses OpenID Connect to log in with the API. Therefore, before you can use

OpenID Connect with Enterprise Manager, you must create a web integration in your IdP.

Creating a web integration in Okta

To create a web integration in Okta for use with Enterprise Manager:

1. Log in to your Okta account.

2. Navigate to Applications>Applications and click Create App Integration.

3. In the Create a new app integration dialog, select OIDC - Open IDConnect.

4. Select Web Application as the Application type and click Next.

5. In the New Web Integration page, configure the following fields:

l

App Integration Name:The name of your App integration. For example,QEM OpenID

Connect.

l

Select Refresh Token.

l

In the Sign-in redirect URIs field, enter the following:

Setup and User Guide - Enterprise Manager, May 2024 31

4 Installation and setup

https://

EnterpriseManagerMachine/attunityenterprisemanager/rest/login_

openid

Where EnterpriseManagerMachine is the host name or IP address of your

Enterprise Manager machine.

6. Clear the Enable immediate access with Federation Broker Mode option, and then click

Save.

7. Copy your Client ID and Client secret. You will need to provide these parameters in the next

stage.

8. Assign the app integration to the users or groups that you want to allow to use the Personal

Access Token.

For details, see ≤ Assign app integrations.

Creating a web integration in Azure Active Directory

To create a web integration in Azure Active Directory for use with Enterprise Manager:

1. Log in to Azure Portal (https://portal.azure.com/)

2. Navigate to Microsoft Entra ID.

3. In the Manage menu on the left, select Enterprise applications.

4. In the Enterprise applications | All applications screen, click New Application.

5. Under Browse Microsoft Entra Gallery, click Create your own application.

6. Name your application and choose one the available options under What are you looking to

do with your application?.

7. Click Create.

8. In the Manage menu on the left, select App registrations.

a. Make sure the All applications tab is selected on the right.

b. Select the app you created earlier.

c. Click Redirect URIs.

d. Click Add a platform.

e. Select Web.

f. Enter the Redirect URI:

https://

EnterpriseManager

/attunityenterprisemanager/rest/login_openid

Where

EnterpriseManager

is replaced with the host name or IP address of your

Enterprise Manager machine

g. Copy your Client ID (under Overview) and your Client secret (under Certificates &

secrets). You will need to provide these parameters in the next stage.

9. Assign the app integration to the users or groups that you want to allow to use the Personal

Access Token.

For instructions how to do this, see ≤ Assign a user account to an enterprise application.

Setup and User Guide - Enterprise Manager, May 2024 32

4 Installation and setup

Step 2: Enable Enterprise Manager to communicate with your IdP

Enabling Enterprise Manager to communicate with Okta

To enable Enterprise Manager to communicate with Okta, open a CMDprompt as admin and

change the working directory to

<Enterprise Manager-INSTALL-DIR>\bin

Then run the following command:

Syntax

aemctl.exe configuration set --open_id_authority

your-openid-connect-authority

--open_id_

client_id

your-client-id

--open_id_client_secret

your-secret

Required parameters

l

--open_id_authority is your Okta URL. For example, https://dev-

13465054.okta.com

l

--open_id_client_id is the client ID generated in Step 1: Create an app integration that

uses OpenID Connect above.

l

--open_id_client_secret is the client secret generated in Step 1: Create an app

integration that uses OpenID Connect above.

Optional parameters

The following parameters are optional and should only be used if required by Okta:

l

--open_id_additional_scopes - Additional scopes that are added to the scope list when

an OpenId Connect login occurs. The default is "groups"

l

--api_token_daily_maintenance_time - Determines when the API token maintenance

background process runs each day. This should be formatted as HH:mm. The default is

"00:30"

l

--api_token_lifetime - The number of days a Personal Access Token is valid. The

default is "180"

l

--open_id_refresh_token_lifetime - The number of days a refresh token is valid. The

default is "0" meaning it is valid forever.

l

--open_id_user_name_field - The field name for the OpenID Connect user name. The

default is "preferred_username".

l

--open_id_display_name_field - The field name for the OpenID Connect user display

name. The default is "name".

l

--open_id_group_field - The field name for an OpenID Connect group. The default is

"groups".

Example

aemctl.exe configuration set --open_id_authority "https://dev-13465054.okta.com" --open_id_

client_id "0oa8ohkl5ftweZNWTT5d7" --open_id_client_secret "FJxXqWOpJsROGrthsaVzfUIcNthG6JLA1-

cTHUJO"

Setup and User Guide - Enterprise Manager, May 2024 33

4 Installation and setup

After you have run the OpenID Connect command, you need to restart the Qlik

Enterprise Manager service for the settings to take effect.

Enabling Enterprise Manager to communicate with Azure Active Directory

To enable Enterprise Manager to communicate with Azure Active Directory, open a CMDprompt as

admin and change the working directory to

<Enterprise Manager-INSTALL-DIR>\bin

Then, run the following command:

Syntax

aemctl.exe configuration set --open_id_authority

your-openid-connect-authority

--open_id_

client_id

your-client-id

--open_id_client_secret

your-secret

Required parameters

l

--open_id_authority is your Azure ADURL. For example,

https://login.microsoftonline.com/146d3649-0e6e-4584-af13-

1063888e4915/v2.0

l

--open_id_client_id is the client ID generated in Step 1: Create an app integration that

uses OpenID Connect above.

l

--open_id_id_client_secret is the client secret generated in Step 1: Create an app

integration that uses OpenID Connect above.

Optional parameters

The following parameters are optional and should only be used if required by Azure Active

Directory:

l

--open_id_issuer - The issuer specified in the OpenID Connect Discovery document. This

parameter is only required if the issuer is different from the open_id_authority specified in

the command.

l

--open_id_trusted_url_prefixes - A list of space-separated trusted URL prefixes that

appear in the OpenID Connect Discovery document.

l

--open_id_claims_source - Instructs Enterprise Manager how to retrieve the claims for a

user connecting with OpenID Connect via the public API. The value can be UserInfo or

IdentityToken (case insensitive). The default is UserInfo, but you must specify

IdentityToken for Azure.

l

--open_id_additional_scopes - Additional scopes that are added to the scope list when

an OpenID Connect login occurs. The default is "groups".

Example

aemctl configuration set --open_id_authority https://login.microsoftonline.com/146d3649-0e6e-

4584-af13-1063888e4915/v2.0 --open_id_client_id a8883e53-528f-4fa3-956a-5997dca94cba --open_

id_client_secret XUDF5~WERGFWRE8J554230959TJI9540 --open_id_issuer

Setup and User Guide - Enterprise Manager, May 2024 34

4 Installation and setup

https://login.microsoftonline.com/146d3649-0e6e-4584-af13-1063888e4915/v2.0 --open_id_trusted_

url_prefixes https://login.microsoftonline.com https://graph.microsoft.com/oidc/userinfo --

open_id_additional_scopes "" --open_id_claims_source IdentityToken

After you have run the OpenID Connect command, you need to restart the Qlik

Enterprise Manager service for the settings to take effect.

Step 3: In Enterprise Manager, generate a Personal Access Token

1. Log in to Enterprise Manager as a SAML user. This must be one of the users/groups that was

assigned to the app integration in Step 1: Create an app integration that uses OpenID

Connect above.

For information on setting up SAML, see Setting up SAML authentication above.

2. In the top right of Enterprise Manager, click the inverted triangle to the right of the user name

and select Generate Personal Access Token.

The Generate Personal Access Token dialog opens.

In the Generate Personal Access Token dialog, you will see one of the following:

l

You do not have a Personal Access Token. This is shown if you have not previously

generated a Personal Access Token:

l

Your Personal Access Token expired on <Date>. This is shown if your Personal

Access Token has expired.

l

Your Personal Access Token expires on <Date>. This is shown if you already have a

Personal Access Token.

When regenerating a token, you will need to confirm that you want to

regenerate the token. This is because API login with the original token will

stop working as soon as you generate a new token.

3. Click Generate token.

The Copy Personal Access Token dialog is displayed.

4. Copy your personal access token. You will need this to log in with the Enterprise Manager

API.

See also:

Managing Personal Access Tokens (page 312)

Step 4: Configure the Enterprise Manager API to log in using the Personal

Access Token

For instructions, see:

Setup and User Guide - Enterprise Manager, May 2024 35

4 Installation and setup

l

REST API: Login

l

.NET API: Getting started - Login

l

Python API: Getting started - Login

4.5  Starting to work with the Qlik Enterprise Manager

Console

To start working with Enterprise Manager, you need to open the Qlik Enterprise Manager Console

and register a Replication Management license.

You can use a Web browser to access the Console from any computer in your network. For

information on supported browsers, see

Preparing your system for Enterprise Manager (page 15)

The user logged into Enterprise Manager must be an authorized Qlik Enterprise Manager

user.

To access the Qlik Enterprise Manager Console:

l

From the machine on which it is installed, select All Programs > Qlik Enterprise Manager >

Qlik Enterprise Manager Console from the Windows Start menu.

Type the following address in the address bar of your Web browser:

https://<computer name>/attunityenterprisemanager

On a machine running Microsoft Windows 10 or Windows Server 2012, you need to run the

Console as Administrator.

l

From a remote browser, type the following address in the address bar of your Web browser:

https://<computer name>/attunityenterprisemanager

where <computer name> is the name or IP address of the computer where Qlik Enterprise

Manager is installed.

If no server certificate is installed on the Enterprise Manager machine, a page stating that the

connection is untrusted opens. This is because when Enterprise Manager detects that no server

certificate is installed, it installs a self-signed certificate. Because the browser has no way of

knowing whether the certificate is safe, it displays this page.

For more information, see

Setting up HTTPS for the Enterprise Manager console (page 38)

If prompted, enter your user name and password.

The user name may need to include domain information in the following format:

For more information, see

Setting the login authentication method (page 25)

Setup and User Guide - Enterprise Manager, May 2024 36

4 Installation and setup

Registering Licenses

If this is the first time you are using Enterprise Manager, you will be prompted to register a

Replication Management license when the console opens. You may also need to register a

Replication Analytics license (required for the

Analytics (page 329)

module), depending on whether

you have obtained such a license from your Qlik Sales Representative.

For information on registering licenses, see

Registering and managing licenses (page 299)

Setup and User Guide - Enterprise Manager, May 2024 37

5 Security considerations

5  Security considerations

This section provides a detailed rundown of the various security-related procedures that need to

be performed to ensure that your data is secure.

In this section:

l

Setting up HTTPS for the Enterprise Manager console (page 38)

l

Setting up HSTS on Enterprise Manager (page 41)

l

Replacing the self-signed certificate on Windows (page 42)

l

Setting the hostname and changing the SSL port (page 43)

l

Replacing the Master User Password (page 44)

l

Encrypting the user permissions file (page 45)

l

Controlling execution of user-defined commands (page 46)

5.1  Setting up HTTPS for the Enterprise Manager

console

Industry-standard security practices dictate that web user interface for enterprise products must

use secure HTTP (HTTPS). Qlik Enterprise Manager enforces the use of HTTPS and will not work if

HTTPS is configured incorrectly.

As Enterprise Manager uses the built-in HTTPS support in Windows, it relies on the proper setup of

the Windows machine it runs on to offer HTTPS access. In most organizations, the IT security group

is responsible for generating and installing the SSL server certificates required to offer HTTPS. It is

strongly recommended that the machine on which Enterprise Manager is installed already has a

valid SSL server certificate installed and bound to the default HTTPS port (443).

Checking if an SSL Certificate is installed

To check whether an SSL certificate is installed, you can use the following command:

netsh http show sslcert | findstr /c:":443"

If an SSL certificate is installed, the output should look like this:

netsh http show sslcert | findstr /c:":443 "

IP:port : 192.168.1.13:443

IP:port : 192.168.1.11:443

IP:port : [fe80::285d:599c:4a55:1092%11]:443

IP:port : [fe80::3d0e:fb1c:f6c3:bc52%23]:443

With a valid SSL certificate installed, the Enterprise Manager web user interface will automatically

be available for secure access from a web browser using the following URL:

https://<machine-name>/attunityenterprisemanager

Setup and User Guide - Enterprise Manager, May 2024 38

5 Security considerations

Using the self-signed certificate

Due to the way the HTTPS protocol works, there is no way for Enterprise Manager to automatically

provide and install a valid SSL server certificate. Still, in the event that no SSL server certificate is

installed, Enterprise Manager automatically generates and installs a self-signed SSL server

certificate (as a temporary measure). This certificate is generated on the Enterprise Manager

machine and cannot be exported or used elsewhere.

It should be noted that browsers do not consider the certificate to be valid because it was not

signed by a trusted certificate authority (CA). When connecting with a browser to a server that uses

a self-signed certificate, a warning page is shown such as this one in Chrome:

Or this one in Firefox:

Setup and User Guide - Enterprise Manager, May 2024 39

5 Security considerations

The warning page informs you that the certificate was signed by an unknown certificate authority.

All browsers display a similar page when presented with a self-signed certificate. If you know that

the self-signed certificate is from a trusted organization, then you can instruct the browser to trust

the certificate and allow the connection. Instructions on how to trust the certificate vary between

browsers and even between different versions of the same browser. If necessary, refer to the help

for your specific browser.

Some corporate security policies prohibit the use of self-signed certificates. In such

cases, it is incumbent upon the IT Security department to provide and install the

appropriate SSL server certificate (as is the practice with other Windows products such

as IIS and SharePoint). If a self-signed certificate was installed and needs to be

removed, then the following command can be used:

<product_dir>\bin\AemCtl.exe certificate clean

Note that after the self-signed certificate is deleted, connections to the Enterprise

Manager machine will not be possible until a valid server certificate is installed. Should

you want to generate a new self-signed certificate (to replace the deleted certificate),

simply restart the Enterprise Manager service.

Setup and User Guide - Enterprise Manager, May 2024 40

5 Security considerations

5.2  Setting up HSTS on Enterprise Manager

HSTS is a web security policy mechanism that helps to protect websites against man-in-the-middle

attacks such as protocol downgrade attacks and cookie hijacking. It allows web servers to declare

that web browsers (or other complying Dilqam) should automatically interact with it using only

HTTPS connections, which provide Transport Layer Security (TLS/SSL).

You can force the Enterprise Manager Web UI and/or the Enterprise Manager REST API connections

to use HSTS (HTTP Strict Transport Security). To do this, run the commands described below.

All commands should be run from as Admin from the product bin folder.

Enabling HSTS

Command syntax

aemctl.exe configuration set --static_http_headers

header_list

--rest_http_headers

header_list

Parameters

Parameter Description

--static_http_headers The headers required to connect to the Enterprise Manager Web

UI.

--rest_http_headers The headers required to connect using the API.

Headers should be specified using the following format:

aemctl.exe configuration set --static_http_headers "header1:value1" "header2:value2" --rest_

http_headers "header1:value1" "header2:value2"

Example

aemctl.exe configuration set --static_http_headers "Strict-Transport-Security:max-

age=31536000; includeSubDomains;" --rest_http_headers "Strict-Transport-Security":"max-

age=31536000; includeSubDomains;"

Disabling HSTS

You can also revert to regular HTTPS connections.

Command syntax

aemctl.exe configuration set --static_http_headers ""|--rest_http_headers ""

Parameters

Parameter Description

--static_http_headers Use this parameter to revert the headers required to connect to

the Enterprise Manager Web UI.

--rest_http_headers Use this parameter to revert the headers required to connect using

the API.

Setup and User Guide - Enterprise Manager, May 2024 41

5 Security considerations

Example

Disable static_http_headers

aemctl.exe configuration set --static_http_headers ""

Disable rest_http_headers

aemctl.exe configuration set --rest_http_headers ""

5.3  Replacing the self-signed certificate on Windows

The instructions below are intended for organizations who wish to replace the built-in self-signed

certificate automatically generated by the Enterprise Manager UI Server on Windows with their own

certificate. This is achieved by removing the self-signed certificate and then importing the new

certificate.

See also:

Creating table/view selection patterns (page 80)

Selecting specific tables/views for replication

This topic walks you through selecting specific tables/views to replicate.

When you select specific tables/views, all selected tables/views are replicated in full unless you

define transformations or filters for the table/view. If you need to make changes to the table/view

structures in the target endpoint or if you only want to select specific columns, then you need to

perform the procedures described in

Defining transformations for a single table/view (page 93)

and

Using filters (page 102)

respectively.

To select specific tables/views:

1. Open the Select Tables/Views dialog box.

2. Select a Schema.

3. Optionally, select the Use exact table name check box. This option is useful if your schema

contains numerous tables as it will save you having to scroll through the entire list to find one

specific table.

4. If you selected the Use exact table name check box, type the exact name of the table you

want to replicate in the Table/View field.

Setup and User Guide - Enterprise Manager, May 2024 78

7 Defining and managing tasks

5. Click Search.

The table or tables (If you did not select the Use exact table name check box) will be shown

in the search results.

6. Select the table by adding it to the list on the right.

7. To add additional tables from the same schema, repeat steps 3-6. To add additional tables

from a different schema, repeat steps 2-6.

8. Click OK to save your settings.

If you rename a table in the database, the Designer tab will still show the original table

name. The Monitor tab, on the other hand, will show the new table name.

Setting load order

You can set the load order for each of the selected tables. This may be useful, for example, if your

selected tables list contains tables of different sizes and you want the smaller tables to be loaded

before the larger tables. When a group of tables are set with the same load order, Replicate will load

the tables according to the table ID.

Load order can be set and modified (see note below) in the following places:

l

The Select Tables window (opened in Designer view by clicking the Table Selection button

in the right of the console).

l

The Patterns and Selected Tables list in the right of the console (in Designer view).



Load order cannot be changed while the task is running. If you want to change the

load order, first stop the task, then change the load order as desired, and finally

reload the target.



Load order cannot be set for "Exclude" patterns.

To set the load order for a specific table:

1. Select the desired table in the Selected Tables list.

2. From the Load Order drop-down list, select one of the available priority levels (Lowest

Priority, Low Priority, Normal Priority, High Priority, and Highest Priority).

3. This step is only relevant if you are setting load order in the Select Tables window. Click OK

to save your settings and close the Select Tables window.

To set the same load order for multiple tables:

1. Select the desired tables in the Selected Tables list.

2. From any of the selected items' Load Order drop-down list, select one of the available

priority levels.

3. This step is only relevant if you are setting load order in the Select Tables window. Click OK

to save your settings and close the Select Tables window.

Setup and User Guide - Enterprise Manager, May 2024 79

7 Defining and managing tasks

Removing specific tables/views from a replication task

This topic walks you through removing specific tables/views from the replication task.

To remove tables from the Selected Tables list:

1. From the Selected Tables list, select a table that you want to remove from the replication

task and then click the button with a single left-facing arrowhead (Remove).

2. To remove all of the tables/views from the Selected Tables or Selected Tables/Views list,

click the button with two left-facing arrowheads (Remove All).

3. Click OK to close the Select Tables or Select Tables/Views dialog box.

4. Click Save to make sure that Enterprise Manager saves the table information for this task.

Creating table/view selection patterns

This topic walks you through selecting tables/views using patterns. For example, you can include all

tables/views that belong to the HR schema except for one or two tables/views that you exclude.

You can also only exclude one or more table/view schemas or tables/views. This replicates the

entire endpoint, except for those tables/views that you excluded.

The following example shows a pattern that replicates all tables that are members of the dbo

schema except for the dbo.PRODUCT_1% table.

Include dbo.%

Exclude dbo.PRODUCT_1%

You can also use the "_" wildcard character to match a single character. For example, specifying

Exclude m_d% will exclude all tables that begin with m and end with d%, such as model or msdb.

Do not escape wildcard characters as this will instruct Replicate to interpret them as

standard characters. As escape character conventions differ across databases, you

should consult your database Help for guidance about supported escape characters.

Some examples (where an underscore is the wildcard character) are as follows:



MySQL and PostgreSQL -

\\_



Microsoft SQL Server -

[_]



Oracle - For Oracle, use the

escapeCharacter

internal parameter to define a

custom escape character.

When you explicitly select tables/views, all selected tables/views are replicated in full unless you

define transformations or filters for the table/view. If you need to make changes to the table/view

structures in the target endpoint or if you only want to select specific columns, then you need to

perform the procedures described in

Defining transformations for a single table/view (page 93)

and

Using filters (page 102)

respectively.

Setup and User Guide - Enterprise Manager, May 2024 80

7 Defining and managing tasks

To view all of the tables/views included when you use a table selection pattern, click the

Full Table List tab in Designer view. The Full Table List lists all of the tables/views

included in any table pattern you defined as well as all explicitly selected tables/views.

To view only patterns and explicitly selected tables/views, click the Patterns and

Selected Tables tab in Designer view.

To create table/view selection patterns:

1. In the Designer view, in the Select Tables/Views dialog box, do any of the following:

l

Select a schema from the Schema drop-down list. All tables/views that belong to that

schema are included in the table/view selection pattern.

l

Type the name or partial name of a table/view in the Table/View field. Any string that

you enter here is included in the table/view selection pattern.

l

If the table/view that you type here is a member of the schema you selected in the

Schema drop-down list, then you only have to type the name of the table/view.

l

If you did not select a schema or the table/view belongs to another schema, include

the schema with the table name in the following format: HR.Employees, where HR is the

schema.

2. Click Include to include all of the tables/views that match the selection criteria.

3. Click Exclude to exclude any tables that match the selection criteria.

4. Click OK to close the Select Tables/Views dialog box.

5. Click Save to make sure that Enterprise Manager saves the table/view information for this

task.

Excluding specific tables from the replication task

You can easily exclude specific tables from being replicated.

To do this:

1. Open the the Select Tables/Views dialog box.

2. Select a Schema and then click Search.

Any tables in that schema will be shown in the search results.

3. Select the tables by adding them to the list on the right.

4. Click the Include button.

Include <schema_name>.% will be added to the Table Selection Patterns list.

5. Select the Use exact table name check box.

6. Type the name of the table you want to exclude in the Table/View field.

7. Click the Exclude button.

Exclude <schema_name>.<table_name> will be added to the Table Selection Patterns list.

8. To exclude additional tables from the same schema, repeat Steps 6-7. To exclude tables

from a different schema, clear the Use exact table name check box and then repeat Steps

2-7.

9. Click OK to save your settings.

Setup and User Guide - Enterprise Manager, May 2024 81

7 Defining and managing tasks

Filters containing wildcard escape characters that excluded/included tables during Full

Load will not exclude/include matching tables added during CDC. For example, if there is

an exclude pattern dbo.pc[_]% and a new table dbo.pc_table2 is created during CDC, the

table will be added to replication task (as opposed to being excluded).

7.5  Editing a replication task

You can make changes to tasks that you previously created. Just open the task and make the

changes in the same way that you did when you created the task.

To edit a task:

1. In Tasks view, select the task and click Open.

The task opens, displaying the source and target endpoints and which tables have been

selected for replication.

2. Continue with any of the following procedures:

l

Adding a source and target endpoint to a task (page 74)

l

Selecting tables and/or views for replication (page 75)

l

Defining transformations for a single table/view (page 93)

l

Using filters (page 102)

l

Task Settings (page 178)

7.6  Searching for tasks

In Tasks view, you can search for tasks by typing a sequence of letters in theSearch Tasks box

above the tasks. For example, to search for all tasks with names that begin with "Oracle-to", type

"or". Only tasks that match the search string are displayed.

7.7  Deleting a replication task

You can delete tasks that you created. To prevent complications, it is recommended not to use the

name of a deleted task for a new task you create. Such a task would be created with the same

settings as the deleted task.

If you use a Microsoft SQL Server endpoint, a Microsoft SQL Server system

administrator must delete the Microsoft SQL Server Replication Publisher definitions for

the endpoint that was used in the task from SQL Server.

For more information, see the "Limitations" section in the Microsoft SQL Server chapter

in the Qlik Replicate Setup and User Guide.

Setup and User Guide - Enterprise Manager, May 2024 82

7 Defining and managing tasks

To delete a task:

1. Stop the task that you want to delete.

2. In Tasks view, click Delete Task.

The task is deleted.

7.8  Exporting and importing tasks

This functionality is supported with Replicate tasks only.

The ability to export tasks is useful if you need to migrate tasks between different Enterprise

Manager machines, which may be necessary if you need to decomission a machine or when moving

from a test machine to a production machine, for example. Tasks can be exported with or without

endpoints.

Export

Task

When

With

Endpoints

l

The task's endpoints do not exist in the target environment. This way, the

task will be created with endpoints when it is imported.

l

The endpoints already exist in the target environment but with a different

configuration that you would like to override.

In both of the above cases, after importing the task, you need to

edit the endpoints and re-enter the passwords. This will encrypt

the passwords using the Master User Key of the target machine.

Without

Endpoints

The task's endpoints already exist in the target environment with a suitable

configuration.

Task export use cases

For information on what permissions are required to export and import tasks, see

Roles and

permissions (page 307)

To export a task:

1. In Tasks view, do one of the following:

l

Select or open the task you want to export and then click the Export Task toolbar

button.

l

Right-click the task you want to export and select Export Task from the context menu.

2. Select Without Endpoints or With Endpoints accordingly.

Depending on your browser settings, the task JSON file will either be downloaded to your

default Downloads folder or you will be prompted to save it to you preferred location.

Setup and User Guide - Enterprise Manager, May 2024 83

7 Defining and managing tasks

The file name format is as follows:

AEM_<ReplicateServerName>_<TaskName>_<Date>_<Time>.json

To import a task:

1. If the task is running on the target server, stop the task.

2. In Servers view, do one of the following:

l

Select the target server (i.e. the server to which you want the task to be imported).

l

Right-click the target server and select Import Task from the context menu.

The Import Task window opens.

3. Either select the task JSON file using the Browse button or drag the file to the window.

4. Click Import.

5. Optionally, when the import completes, start the task.



Setup and User Guide - Enterprise Manager, May 2024 84

8 Defining and managing endpoints

8  Defining and managing endpoints

Enterprise Manager requires information to connect to the source and target endpoints that you

want to use in a task. For a list of endpoints you can work with in Qlik Replicate, see the Qlik

Replicate Setup and User Guide.

You use the Manage Endpoint Connections window to add endpoints and edit and view the

endpoint connection information.

The name cannot exceed 32 characters, contain non-Latin characters, or contain any of

the following characters: | \ / : * ? " < >

l

Defining an endpoint (page 85)

l

Setting user permissions for a specific endpoint (page 86)

l

Editing endpoint configuration information (page 88)

l

Viewing endpoint configuration information (page 89)

l

Testing an endpoint connection (page 89)

l

Duplicating endpoints (page 90)

l

Searching for endpoints (page 90)

l

Deleting endpoints (page 90)

8.1  Defining an endpoint

Before you can begin to design a task, you must add endpoints to the Replicate server. To use an

endpoint, you must have access to it somewhere in your system. When you add the endpoint to the

Replicate server, you must provide connection information and proper user credentials.

Once you add endpoints to the Replicate server, you can begin to use them to build a replication

task. For information on how to add an endpoint to a replication task, see

Adding a source and

target endpoint to a task (page 74)

To add an endpoint:

1. In the Servers view or on a dedicated task tab, click Manage Endpoint Connections.

The Manage Endpoint Connections window opens. The server is already selected and

cannot be changed.

2. In the Manage Endpoint Connections window, click New Endpoint.

3. Select the type of endpoint you are using. The information that you must enter depends on

which endpoint you select.

For a list of supported endpoints and for more information on setting up a specific endpoint,

see the Qlik Replicate Setup and User Guide.

Setup and User Guide - Enterprise Manager, May 2024 85

8 Defining and managing endpoints

8.2  Setting user permissions for a specific endpoint

This topic explains how to edit user permissions for a specific endpoint, add and remove users or

groups, disable or enable inheritance, restore inherited permissions if they were overridden, and

view effective permissions for a user.

To do this:

1. In the Manage Endpoint Connections (<server-display-name>) dialog, click the Endpoint

Permissions toolbar button.

The User permissions for endpoint '<Display-Name>' dialog opens.

2. See the sections below for the procedures you can perform in the User permissions for

endpoint '<Display-Name>' dialog.

Adding and removing users

To add a user or group:

1. In the User permissions for endpoint '<Display-Name>' dialog, click Add.

2. In the Add User/Group dialog box, select User or Group.

3. Enter the name for the new user or group in the following format:

l

NetBIOS_name\user (for example:qa\qa)

l

machine_name/local_user (for example: re2008r2js1\JohnMil1)

l

username - This format is supported with SAML authentication only. The user/group

name can contain any Unicode character up to 255 characters and must be a valid

Identity Provider user (Okta or Microsoft Azure).

4. Click OK to add the user or group and close the dialog box.

5. Click OK to accept the changes, or Cancel to undo them.

To remove a user or group:

1. In the User permissions for endpoint '<Display-Name>' dialog, select the user or group you

want to remove.

2. Click Remove.

3. When prompted, click Yes to confirm.

4. Click OK to accept the changes, or Cancel to undo them.

Editing user permissions

Only an Admin can edit user permissions.

To edit a user's permissions:

1. In the User permissions for endpoint '<Display-Name>', adjust the permission slider for a

user or group as desired.

Setup and User Guide - Enterprise Manager, May 2024 86

8 Defining and managing endpoints

Adjusting the slider stops inheritance from the parent object.

2. Click OK to accept the changes or Cancel to undo them.

The following table summarizes the roles required for adding and editing the endpoint.

Operation Viewer Operator Designer Admin

Add and edit

endpoint

No No Yes Yes

View

endpoint

settings

Partial. Viewers can only see the Name,

Description, Role, and Type fields.

Yes Yes Yes

Endpoint operation roles

Inheritance

By default, inheritance is enabled for all objects (users and groups). This means that permissions

are automatically carried over from the parent object. You can turn inheritance on or off for all

objects at the current level.

To turn off inheritance:

1. In the User permissions for endpoint '<Display-Name>' dialog, click Disable Inheritance.

This option disconnects the entire authorization level from the parent level.

2. In the Disable Inheritance dialog box, select whether you want to:

l

Convert inherited permissions on this object into explicit permissions: This option

changes inherited permissions to explicit permissions. Any new users or groups will

not inherit permissions from the parent.

l

Remove all inherited permissions from this object: This option removes all existing

permissions inherited from the parent level. Any new users or groups will not inherit

permissions from the parent.

3. Click Disable.

If you chose to convert inherited permissions, the check mark in the Inherited column

changes into an X. If you chose to remove inherited, all users and groups disappear from the

list.

4. Click OK to accept the changes or Cancel to undo them.

To turn on inheritance:

1. In the User permissions for endpoint '<Display-Name>' dialog, click Enable Inheritance.

This option enables inheritance for all users and groups on this level.

Setup and User Guide - Enterprise Manager, May 2024 87

8 Defining and managing endpoints

2. In the Enable Inheritance dialog box, select whether you want to:

l

Inherit all permissions from parent and override any definition manually made at

this level: This option reinstates inherited permissions for all users and groups that

are already defined, and new users and groups will inherit their permissions from the

parent level.

l

Inherit all permissions from parent but keep definitions manually made at this

level: This option preserves the permissions already defined for the existing users and

groups and adds all permissions from the parent level. New users and groups will

inherit permissions from the parent level.

3. Click Enable.

4. Click Save or OK to accept the changes, or Discard Changes or Cancel to undo them.

To restore inherited permissions for a single user or group if they were overridden:

1. In the User permissions for endpoint '<Display-Name>' dialog, select the user or group.

2.

Click Restore Inheritance .

The check mark returns to the Inherited column to indicate that permissions for this user or

group are inherited from the parent.

Viewing effective permissions

Effective permissions are the permissions that are in effect for a user at any particular level.

To view effective permissions for a user:

1. In the User permissions for endpoint '<Display-Name>' dialog, do one of the following:

l

Select a user in the list on the left.

l

If a user does not appear in the list but exists in the system and is part of a group, enter

the user name in the text field in the Effective Permissions pane on the right.

Make sure to use the following format:

l

NetBIOS_name\user (for example: qa\qa)

l

machine_name/local_user (for example: re2008r2js1\JohnMil1)

2. Click Get Effective Permissions.

The effective permissions for the user you entered appear below the button.

For more information on Enterprise Manager’s security roles, see

User permissions (page 302)

. For

more information on the underlying concepts, see

Granular access control (page 303)

and

Inheritance and overrides (page 304)

8.3  Editing endpoint configuration information

After you add the endpoint to the Replicate server and provide the connection information, you can

make changes to some of the information.

Setup and User Guide - Enterprise Manager, May 2024 88

8 Defining and managing endpoints

You cannot change the following information in the endpoint window:



The name you provided for the endpoint.



The endpoint Type, for example Oracle or Microsoft SQL Server.



The endpoint role, either SOURCE or TARGET.

To edit endpoint configuration information:

1. In the Manage Endpoint Connections window, select the endpoint you want to edit.

In the Endpoints list on the left of the Designer view, double-click the endpoint you want to

edit. Note that this option is only available when editing a specific task.

The Manage Endpoint Connections window opens with the selected endpoint settings.

2. Make changes to the information in any of the tabs in the window.

For more information, see the chapter for the specific Qlik Replicate endpoint you are using in

the Qlik Replicate Setup and User Guide. For a list of supported endpoints, see the Qlik

Replicate Setup and User Guide.

8.4  Viewing endpoint configuration information

After you add the endpoint to the Replicate server and provide the connection information, you can

view the information in the Manage Endpoint Connections window.

To view endpoint configuration information:

l

Select an endpoint from the Endpoints list in the left pane; then click the tabs to view the

information.

8.5  Testing an endpoint connection

You can try to contact the endpoint to make sure that you are connected to the endpoint you want

to work with.

To test the endpoint connection:

1. In the Manage Endpoint Connections window, select the endpoint you want to work with.

2. At the bottom of the endpoint’s General tab, click Test Connection.

If the connection is successful, a success message is displayed and a green check mark icon

appears next to the Test Connection button.

If the connection fails, an error message is displayed at the bottom of the dialog box and the

View Log button becomes available.

3. If the connection is successful, click Close.

If the connection fails, click View Log to view the server log entry with information for the

connection failure.

Setup and User Guide - Enterprise Manager, May 2024 89

8 Defining and managing endpoints

8.6  Duplicating endpoints

You can duplicate an endpoint if you need to define a new endpoint with similar settings. Except for

the name, all endpoint settings are duplicated to the new endpoint.

To duplicate an endpoint:

1. In the left panel of the Manage Endpoint Connections window, click the endpoint you want

to duplicate.

2. Click Duplicate.

3. On the General tab, edit the name for the endpoint.

4. Make any other necessary changes.

5. Click Save; then click Close.

8.7  Searching for endpoints

You can search for endpoints by typing a sequence of letters in the Filter by box above the

endpoints list. For example, to search for all endpoints whose names contain the string "Oracle",

type "or". Only endpoints that match the search string are displayed.

8.8  Deleting endpoints

You can delete endpoints that you no longer require. Note that to delete an endpoint that is defined

as a source or target in a task, you first need to remove the endpoint from the task.

To delete an endpoint:

l

In the left panel of the Manage Endpoint Connections window, Select the endpoint and click

Delete.

Setup and User Guide - Enterprise Manager, May 2024 90

9 Customizing tasks

9  Customizing tasks

This section describes how to customize a replication task. For example, you can create new tables

or columns for the target endpoint or select only some of the data from each column to be

replicated. This is done using transformations and filters.

Although the descriptions in this section only refer to tables, the procedures described

herein are applicable to views as well. When a transformation is defined for a view, the

word "View(s)" appears in the UI instead of the word "Table(s)".

In this section:

l

Table Settings (page 91)

l

Defining global rules (page 118)

l

Using the Expression Builder (page 148)

l

Task Settings (page 178)

9.1  Table Settings

In the <Table_Name> - Table Settings window, you can define how the data for each individual

table/view is replicated to the target.

Some of the table settings are not available in a Log Stream Staging setup.

For information on the availability of table settings in a Log Stream Staging setup, refer

to the Qlik Replicate Setup and User Guide.

To open the Table Settings window:

1. Open the task you are working with.

For information on opening a task, see

Editing a replication task (page 82)

2. In Designer view, select the desired table from one of the following tabs on the right of the

console:

l

The Patterns and Selected Tables tab - if the desired table was explicitly selected.

l

The Full Table List tab - if the desired table was selected using a table inclusion

pattern.

For information on how to define table selection patterns, see

Creating table/view

selection patterns (page 80)

3. Click the Table Settings button above the table list.

The <Table_Name> - Table Settings window opens.

4. In the Table Settings window, perform any of the following tasks:

Setup and User Guide - Enterprise Manager, May 2024 91

9 Customizing tasks

l

Performing General tasks for a single table/view (page 92)

l

Defining transformations for a single table/view (page 93)

l

Using filters (page 102)

l

Parallel Load (page 109)

l

Handling LOB columns (page 113)

l

Message format (page 117)

l

Full Load (page 117)

5. Click OK to close the Table Settings window.

6. Click Save in the main toolbar to preserve the table and column information for this task.

To restore the default table values:

l

Click Restore Table Defaults at the bottom left of the Table Settings window. This option is

available in all tabs.

Any changes you made will be discarded and the table's default settings will be restored.

The names of modified tables will be followed by the word (changed), enabling you to

easily identify which tables have been modified.

Performing General tasks for a single table/view

Although the descriptions in this section only refer to tables, the procedures describe

herein are applicable to views as well. When a task is being performed for a view, the

word "View(s)" will appear in the UI instead of the word "Table(s)"

The General tab in the Table Settings window displays basic information about the selected table

and allows you to define new names for the table/schema on the target as well as override the

default tablespace for the table and its index (Oracle target only).

To edit the general table settings:

1. Open the

Table Settings (page 91)

window.

2. Click the General tab on the left side of the window, as shown below.

Setup and User Guide - Enterprise Manager, May 2024 92

9 Customizing tasks

In the Map to target table section, the following options are available:

l

Table Schema: Specify the schema in which you want the table to be created on the

target.

l

Table Name: Specify a new name for the table on the target.

l

Table tablespace: This option is only available when the task is defined with an Oracle

target endpoint.

Specify the name of the tablespace in which you want the table to be created on the

target. By default (i.e. when this field is empty), the table will either be created in the

source table tablespace on the target (when replicating from an Oracle source) or in

the default tablespace (when replicating from any other source).

l

Index tablespace: This option is only available when the task is defined with an Oracle

target endpoint.

Specify the name of the tablespace in which you want the table's index to be created

on the target. By default (i.e. when this field is empty), the index will either be created

in the source table tablespace on the target (when replicating from an Oracle source)

or in the default tablespace (when replicating from any other source).

Defining transformations for a single table/view

Although the descriptions in this section only refer to tables, the procedures describe

herein are applicable to views as well. When a transformation is defined for a view, the

word "View(s)" will appear in the UI instead of the word "Table(s)".

Setup and User Guide - Enterprise Manager, May 2024 93

9 Customizing tasks

This section describes how to define data transformations. Data transformations are performed

when the task is run. They are optional. If you do not define any transformations, the data is

replicated "as is" from the source to the target.

Enterprise Manager lets you make the following changes to the tables and columns:

l

Rename any column for the target table

l

Delete a target column

l

Change the data type and/or the length of any target column

l

Add additional target columns

l

Designate which target columns (i.e. segments) will comprise the Unique Index

l

Recalculate the data

Limitations

Transformations are subject to the following limitations:

l

Calculating columns of right-to-left languages is not supported.

l

Transformations cannot be performed on columns that contain special characters (e.g. #, \, /,

-) in their name.

l

Transformations cannot be performed on columns that have a pound character (#) in their

name.

l

The only supported transformation for LOB/CLOB data types is to drop the column on the

target.

l

Using a transformation to rename a column and then add a new column with the same name

is not supported.

You can use the method described here for transformations that are specific to a single table or a

few tables in your task. To make a similar change over multiple tables, see

Starting the Global

Transformation Rules wizard (page 118)

For an explanation of how to configure transformations, see

Using the Transform tab (page 95)

To define a data transformation for a single table:

1. Select the table you want to transform and open the

Table Settings (page 91)

window.

2. Click Transform on the left side of the window.

The following figure shows the information in the Transform tab of the Table Settings window.

Setup and User Guide - Enterprise Manager, May 2024 94

9 Customizing tasks

Using the Transform tab

In the Transform tab, you can define transformations using Replicate's built-in functionality.

Customers that requires functionality not provided by Replicate's built-in

transformations can write their own transformations, and then access them from the

Replicate Expression Builder

. For an explanation of how to create user-defined

transformations (requires basic programming skills), see User-defined transformations

(page 176).

The Transform tab in the Table Settings window consists of the following elements:

Setup and User Guide - Enterprise Manager, May 2024 95

9 Customizing tasks

l

Input: This lists the columns on which you can perform transformations.

When creating a transformation for the SAP Application source endpoint, you can

hover your mouse cursor over an Input column to see a tooltip with the table’s

actual name:

l

Output: This table shows the defined output for the columns in the table where you are

performing the transformation(s). See Transformation Options below for information on how

to change the default output.

Limitations and considerations

l

Dropping a column, saving your changes, and then adding a column with the same name and

defining an expression corresponding to the dropped column's data, is not supported. If you

mistakenly drop a column, simply add the column back again without an expression.

l

If you stop a task and define a metadata transformation for one of the tables (such as

dropping a column), make sure the DROP and CREATE table option is selected (the default)

in the Task Settings' Full Load Settings tab before resuming the task.

l

In homogeneous replication tasks (such as Oracle to Oracle), modifying a single table column

(by changing the column data type or length for example), will break the homogeneity for the

entire table.

l

Transformation of numeric data types must fall between the range -9223372036854775808

to +9223372036854775807.

Transformation options

The following table describes the transformation options available in the Transform tab.

To Do This

Rename a column Select the Name column for the table column you want to change.

Type in a new name.

The top right corner turns blue when the name is changed. To view the

original name, hover the mouse pointer over the field and the original

name is displayed.

Transform actions

Setup and User Guide - Enterprise Manager, May 2024 96

9 Customizing tasks

To Do This

Set a column as a

primary key/unique key

or disable a column's

primary key/unique key

1. Select the desired row in the Output table and then click the cell

in the Key column.

A key icon will be displayed.

2. Repeat to set primary keys/unique keys for additional columns.

3. To disable the primary key/unique key, click the key icon.

Change the order of

the primary key

columns

Replicate relies on primary key columns (or indexes) defined in the

target tables to be able to correctly apply changes to them.

In some cases - for example, if the target table consolidates data from

multiple sources - you might need to define additional primary key

columns on the target table and arrange them in a specific order to

improve performance.

When upgrading, to preserve the behavior of existing tasks

(as opposed to new tasks), this improvement is turned off

by default. To turn it on for existing tasks, after upgrading,

open the task settings and either delete the use_

manipulation_pk_for_apply parameter from the More

Options tab or set the value to Off.

To change the order of the primary key columns in the target table:

1. Click the Set Key Column Order button.

The Key Column Order dialog will open showing the table's

primary key columns.

2. Use the arrows to rearrange the columns in the desired order.

3. Click OK to save your changes and close the Key Column Order

dialog.

A number will now appear next to each of the keys, indicating

their order in the target table.

Change the data type

for a column

Select the Type column for the table column you want to change and

select a new data type from the drop-down list. Make sure that the

data type you select is compatible with the data in that column.

For a description of Qlik Replicate data types, information about data-

type mapping from the native endpoint to Qlik Replicate, and for a list

of endpoints supported by Qlik Replicate, see the Qlik Replicate Setup

and User Guide.

Setup and User Guide - Enterprise Manager, May 2024 97

9 Customizing tasks

To Do This

Change the data

subtype for a column

This option is available for the CLOB, NCLOB, STRING, and WSTRING

data types only.

Select the Subtype column for the table column whose data type you

want to change, and then select either JSON or XML from the drop-

down list. Make sure that the data in the column is compatible with the

selected subtype. The default is Regular, which means that data type

in the Type column will be used with no subtype.

For a description of Qlik Replicate data types, information about data-

type mapping from the source endpoint to Qlik Replicate, and for a list

of endpoints supported by Qlik Replicate, see the Qlik Replicate online

help.

Add a new column Click Add Column to add a new column. When you add a column, the

Name is blank and the Type is listed as string(50).

Type a name for the new column in the Name column. If needed

(according to the column data), click in the Type column and select a

data type from the list.

Add an existing column From the Input pane, select one or more columns and click the right

facing arrow button.

To add all of the columns, click the right-facing double arrow.



By default all tables columns are included in the

Output list. To include only some of the columns

clear the By default include all columns check box

at the top of the Transform tab. This removes all of

the columns from the list. You can then add back any

existing column as required.



If a column is explicitly added at the table level but

then dropped at the global level (using a global

transformation rule), the column will still be created

on the target, but without any data.

Delete a column From the Output list, select the row with the column you want to

delete and click the left-facing arrow button.

To remove all columns, click the left-facing double arrow. Note that all

the columns except for columns defined as a primary key/unique index

are deleted.

Setup and User Guide - Enterprise Manager, May 2024 98

9 Customizing tasks

To Do This

Recalculate the data

for a column in the

target endpoint

Click in the Expression column in the row with the table column you

want to change the data for. Enter an expression using SQLite syntax.

See

Creating an expression for transformations (page 101)

and

Using

SQLite syntax with transformations (page 101)

for information on

creating expressions.

Once you add a calculated expression, you can test the expression.

See

Using the Expression Builder (page 148)

Change the data type

for a specific input

column

Supported

with the IBM

DB2 for

iSeries and

IBM DB2 for

z/OS source

endpoints

only.

This is required if a source column is defined as character type but the

data stored in that column is binary or vice versa.

When the source column type is STRING, WSTRING, CLOB,

or NCLOB, you must also select a Character Set, otherwise

an error will be shown and the OK button will be disabled.

In the Input table, click the relevant cell in the Type column and then

select either STRING or BYTES from the drop-down list as required.

If you change a column's Type in the Input table, you also

need to set the same Type for the corresponding column in

the Output table.

Note that if you select STRING, you can also change the character set,

as explained below.

Modified cells will display a triangle in the top right corner.

To see the original value, click the triangle.

Setup and User Guide - Enterprise Manager, May 2024 99

9 Customizing tasks

To Do This

Change the Character

Set for a specific input

column

Supported

with the IBM

DB2 for

iSeries and

IBM DB2 for

z/OS source

endpoints

only.

This is required if a source character column is wrongly encoded. For

example, if a source character column is described as encoded in CCSID

X, but the data stored in that column is actually encoded in CCSID Y.

You can also set a custom character set as described in

Setting a

custom character set (page 100)

below.

In the Input table:

1. Click the relevant cell in the Type column and select STRING

from the drop-down list.

2. Click the relevant cell in the Character Set column and then

select the appropriate character set from the drop-down list.



Only character sets compatible with the

selected Type will be available for selection.



Modified cells will display a triangle in the top

right corner. To see the original value, click the

triangle.

Setting a custom character set

The following procedure is supported with the IBM DB2 for iSeries and IBM DB2 for z/OS source

endpoints only.

Perform the steps below if the source table is defined with an incorrect CCSID and the correct

definition is actually in a UCM file.

1. Create a mapping data file with the file extension .ucm.

If you edit an existing UCM file, you must also change the values of the <code_set_

name> and <icu:alias> properties. If the file does not contain an <icu:alias>

property, then you only need to change the value of the <code_set_name> property.

2. Create a CNV file for the UCM file by running the following command:

<product_dir>\bin\makeconv.exe -v <file_name>.ucm

Example: 

"c:\Program Files\Attunity\Replicate\bin\makeconv.exe" -v 1047_EX.ucm

This will create a CNV file with the same name as the UCM file (for example,

1047_EX.cnv

3. Create a new subfolder named icudt<XX>l under

<product_dir>\bin

where the XX is the

same as the number in the icudt<XX>.dll file name.

For example, If the DLL file name is icudt69.dll, create a new subfolder named icudt69l.

Setup and User Guide - Enterprise Manager, May 2024 100

9 Customizing tasks

4. Copy the CNV file to the subfolder you just created (

<product_dir>\bin\icudt69l

in the

example above).

When using the Replicate File Channel, the file should be copied to the same

location on both Replicate servers.

5. Restart the Qlik Replicate UI Server service.

5. Select the custom character set from the Character Set drop-down list; it will appear as the

CNV file name followed by the word "Custom" e.g. 1047_EX.cnv (Custom).

Using a column's before-image data in a transformation

You can use a column's before-image data in a transformation. This is useful if you need to store

the before-image data on the target.

To do this, simply specify the source column name in Output table's Expression column, in the

following format:

$BI__MyColumn

Where $BI__ is a mandatory prefix (that instructs Replicate to capture the before-image data) and

MyColumn is the source column name.

Although you can store the before-image data in an existing target column, it is recommended to

create a new target column (using the Add Column button) in which to store the before-image

data.

Creating an expression for transformations

Use an expression to define the contents of a new or re-calculated column.

To create an expression:

1. In the Transform tab, select the row with the column for which you want to create an

expression.

Click Add Column to add a new column.

2.

Click the button in the Expression column.

The Expression Builder opens.

3. Build an expression as described in

Using the Expression Builder (page 148)

Using SQLite syntax with transformations

The following table lists the SQLite operators that are supported with transformations.

Setup and User Guide - Enterprise Manager, May 2024 101

9 Customizing tasks

Operator Description

|| Concatenate strings.

FIRST_NAME||LAST_NAME

PHONE_NUMBER||<Office Only> (adds the string Office Only to the telephone number).

+ Adds two values together.

DEPARTMENT_ID+100 (adds 100 to each ID number). Any column used in an

expression with this operator must be a numeric data type.

- Subtracts a value from another value.

MANAGER_ID-100 (subtracts 100 from each ID number). Any column used in an

expression with this operator must be a numeric data type.

% Uses the remainder of a division expression as the value.

%SALARY/7 (Divides the value of the Salary column by 7 and uses any remainder

from the expression as the column value).

/ Divides one value into another.

SALARY/.16 (Divides the value of the Salary column by .16.

If the two values in the division expression are integers (two NUMERIC

columns with no digits after the decimal) and the result is a fractional

value, the result returned will be 0.

* SALARY*.16 (Multiplies the value of the Salary column by .16. This could be used to

calculate taxes that are subtracted from a salary).

SQLITE syntax operators

For more information about SQLite syntax, see the SQLite documentation.

Using filters

Filters let you include or exclude records from a replication task based on the value(s) of the source

table columns, thereby allowing you to replicate only the specific data that you need.

In this section:

l

Filter limitations (page 103)

l

Opening the Filter tab (page 103)

l

Creating a filter condition for a specified column (page 104)

l

Creating a record selection condition for one or more columns (page 105)

l

Adding or removing filter ranges (page 107)

l

Using SQLite syntax with filtering (page 108)

Setup and User Guide - Enterprise Manager, May 2024 102

9 Customizing tasks

Filter limitations

When creating a filter, the following limitations apply:

l

Filters are not supported for calculating columns of Right-to-Left languages.

l

Filters can only be applied to immutable columns.

l

Filters on mutable columns:

When a filter is created to exclude/include specific rows in a column, the specified rows will

always be excluded/included, even if the rows that were originally excluded/included are

later changed. For example, if you chose to exclude/include rows "1-10" in a column named

"Age" and those rows were later changed to "11-20", the rows will continue to be

excluded/included, even though the data is no longer the same.

Additionally, if a row outside the filter scope was changed (i.e. updated or updated and then

deleted) so that it should now be excluded/included (as defined by the filter), it will not be

replicated to the target. So, for example if you created a filter to exclude/include rows less

than 5 and then changed row 6 to -6, it will not be replicated (even though it is included in the

filter's criteria range).

l

Filter cannot be applied to LOB columns.

l

When specifying numeric data as a filtering condition, the data preceding the decimal point

cannot exceed int64.

Opening the Filter tab

The Filter Table tab contains the following information:

l

Data Columns list: This list contains a list of the columns for the table where you filtering

data. You can use these to select the columns to use in the filtering operations.

This list has the following tabs:

l

Source: This tab lists the original source columns in the table.

l

Header: This tab lists the available header columns. You can create filters using these

columns and include them in expressions. For information on these header columns,

see

Headers (page 172)

l

Calculated: This tab lists the columns added to the table. You add columns through

transformations. For more information, see

Defining transformations for a single

table/view (page 93)

l

Filter Conditions table: This table has the following columns:

l

Name: The name of the column where you are filtering the data.

l

Type: The data type for the column.

l

Include/Exclude: Indicate whether to include or exclude the filtered data for this

column.

l

Ranges: Click the button on the right of the Ranges field to open the Range Builder.

For information on creating a value or ranges with the Range Builder, see

Adding or

removing filter ranges (page 107)

For more information on typing in the filter ranges manually, see

Using SQLite syntax

with filtering (page 108)

Setup and User Guide - Enterprise Manager, May 2024 103

9 Customizing tasks

l

Record Selection Condition: Enter a complex condition that can include multiple columns.

The condition must evaluate to TRUE to be accepted. You can create a condition using

SQLite operators or by

Using the Expression Builder (page 148)

. For information on using the

SQLite operators, see

Creating a record selection condition for one or more columns (page

105)

The following figure is an example of the information in the Filter tab of the Table Settings window.

Table Settings: Filter

To open the Filter tab:

1. Select the table you want to filter and then open the

Table Settings (page 91)

window.

2. Click the Filter tab on the left side of the window.

Creating a filter condition for a specified column

You can create a simple condition for a single column in the table you are working with. You can

include any combination of ranges or specific values in the filter and determine whether to include

or exclude the defined data.

To create a filter condition:

1. Select a column from the data columns list and then click the right-facing arrow next to the

Filter Conditions table.

To remove the column, click on it in the Filter Conditions table and then click the left-facing

arrow. Any data entered for this column in the Include/Exclude or Values columns is also

deleted.

Setup and User Guide - Enterprise Manager, May 2024 104

9 Customizing tasks

2. Click in the Include/Exclude column to select whether to include or exclude the data that

meets this condition.

3. Click the Edit Ranges button in the Ranges column.

4. The <Name> <Include|Exclude> Ranges window opens. Continue from

Adding or removing

filter ranges (page 107)

Creating a record selection condition for one or more columns

You can create a record selection condition manually and/or by using the Expression Editor.

When entering a string, you can use the following special characters:

l

%: Matches any string of zero or more characters. For example, Mc% searches for every name

that begins with Mc or %bob% includes every name that contains bob.

l

_:Matches a single character (as a wildcard). For example: ’Sm_th’ includes names that begin

with Sm and end with th, such as Smith or Smyth. To search for an underscore character,

use [_]".

l

[..]: Includes a range or set of characters. For example, [CK]ars[eo] includes names Carsen,

Karsen, Carson, and Karson or [M-Z]inger includes all words that end in inger with the first

letter between M and Z, such as Ringer, Singer, or Zinger.

For more information, see documentation on how to use Transact-SQL.

For information on what SQLite operators can be used to create Record Selection Condition filters,

see

Using SQLite syntax with filtering (page 108)

To create a record selection condition:

1. From the Data Columns list, select a source column, header column or calculated column

and then click the arrow to the left of the Record Selection Condition pane.

2. Use SQLite operators, such as < or = to create the condition. Use any amount of strings or

columns as you need to create a condition.

For example $EMPLOYEE_ID < 100 AND $SALARY > 100,000

In this case only rows that satisfy both of these conditions are replicated in the replication

task.

The following example provides an example using SQL search pattern strings. Only rows that

satisfy this condition are replicated.

$EMPLOYEE_NAME IS ’Sm_th’

To create a record selection condition using the Expression Builder:

l

Click Open Expression Builder. This button is located directly under the record selection

condition box. Follow the directions for creating an expression in the section

Using the

Expression Builder (page 148)

Applying updates to specific columns only

You can define an expression that instructs Replicate only to apply UPDATEs when a user-defined

condition has been met, for example, only when specific columns have changed. This is useful in

situations when there are many updates in the source that the user has deemed not relevant for the

Setup and User Guide - Enterprise Manager, May 2024 105

9 Customizing tasks

target, as "irrelevant" updates will be ignored.

Limitations

l

Does not support columns that do not have Before-Image data (e.g. LOB columns)

l

Does not support the following sources (i.e. sources that do not support Before-Image

records):

l

ODBC with CDC

l

Teradata

l

Subject to the existing expression builder and filter limitations

l

Content-based filtering may result in loss of data or data corruption. For example, if the

Primary Key value changes (an UPDATE operation), the expression may ignore the UPDATE if

the columns that were specified in the expression did not change. The result in this case

would be that a "phantom" row with the old row contents will remain, even if a change was

later applied to the columns specified in the expression.

How to

Assume that you have a source table named table1 with columns c1-c10 but you are only

interested in updating columns c7, c8 and c9 on the target.

To do this, you would need to:

1. Open the Table Settings for table1 and select the Filter tab.

2. Click the Expression Builder button at the bottom right of the tab.

The Expression Builder opens.

3. Optionally, select the Headers tab.

Although selecting the Headers tab is optional, selecting it will enable you to add

$AR_H_OPERATION to your expression (as required in Step 4 below) simply by

double-clicking the column on the left of the tab.

4. Enter the following expression in the Build Expression pane and then click OK:

($AR_H_OPERATION != 'UPDATE') OR

(($AR_H_OPERATION = 'UPDATE') AND (( ifnull($BI__c7,0) != ifnull($c7,0)) OR ( ifnull

($BI__c8,0) != ifnull($c8,0)) OR ( ifnull($BI__c9,0) != ifnull($c9,0))))

The above expression means that changes will be applied to c7, c8 and c9 only if one of the

following is true:

l

The operation is not an UPDATE.

l

The value of c7, c8 or c9 has changed as the result of an UPDATE operation.

When used in an expression, Before-Image columns must be prefixed with $BI__.

For operations other than UPDATE, the value of the specified columns will be

NULL.



Setup and User Guide - Enterprise Manager, May 2024 106

9 Customizing tasks

Adding or removing filter ranges

You can add one or more values to the Ranges column using the Range Builder. Values that match

any of the ranges in the list are included in the replication.

You can also delete a filter range using the Range Builder.

Filter ranges that you enter manually are also displayed in the Filter Builder. You can use

the Filter Builder to delete them.

To use the Range Builder:

1. In the Filter tab of the

Table Settings (page 91)

window, select a column to filter. For more

information, see

Using filters (page 102)

2. Click the button to the right of the Ranges column.

The Ranges Builder opens.

3. Click Add Range. Select any of the following from the drop-down list displayed.

l

Equal to: Select Equal to to enter a single value. The following is displayed in the

range list.

Equal to = [N]

Click the [N] and type a value in the field that is displayed.

When the value in the selected column equals the value you enter, the result is

included or excluded in the replication task depending on the option selected in the

Include/Exclude column.

l

Between: Click Between to enter a range of values. The following is displayed in the

range list.

Between [N] - [N]

Click each [N] and type a value in the fields that are displayed.

When the column contains the values between the two values entered, the result is

included or excluded in the replication task depending on the option selected in the

Include/Exclude column.

l

Less than or equal to: Select Less than or equal to and enter a maximum value. The

following is displayed in the range list.

Less than or Equal to =< [N]

Click the [N] and type a value in the field that is displayed.

When the value in the selected column is equal to or less than the value you enter, the

result is included or excluded in the replication task depending on the option selected

in the Include/Exclude column.

l

Greater than or equal to: Select Greater than or equal to and enter a minimum value.

The following is displayed in the range list.

Greater than or Equal to => [N]

Click the [N] and type a value in the field that is displayed.

When the value in the selected column is equal to or more than the value you enter,

the result is included or excluded in the replication task depending on the option

selected in the Include/Exclude column.

Setup and User Guide - Enterprise Manager, May 2024 107

9 Customizing tasks

To delete a filter range from the Range Builder:

1. In the Filter tab of the

Table Settings (page 91)

window, select the column with the filter

condition you want to delete.

2. Click the button to the right of the Ranges column. The Ranges Builder opens.

3. Click the X next to the range you want to delete. The deleted range is removed from the list.

Using SQLite syntax with filtering

Qlik Replicate supports the following SQLite operators when creating Record Selection Condition

filters.

You must put the ($) in front of each input as shown below.

Operator Description

< Is less than.

$SALARY<100000

<= Is less than or equal to

$SALARY<=100000

> Is greater than

$SALARY>100000

>= Is more than or equal to

$SALARY>=100000

= Is equal to

$SALARY=100000

!= or <> Is not equal to

$SALARY!=100000

IS Is the same as

$HIRE_DATE IS 2014-09-29

IS functions the same as = unless one or both of the operands are NULL. In this

case, if both operands are NULL, then the IS operator evaluates to 1 (true). If one

operand is NULL and the other is not, then the IS operator evaluates to 0 (false).

SQLITE syntax operators

Setup and User Guide - Enterprise Manager, May 2024 108

9 Customizing tasks

Operator Description

IS NOT Is not the same as

$HIRE_DATE IS NOT 2014-09-29

IS NOT functions the same as != unless one or both of the operands are NULL. In

this case, if both operands are NULL, the IS NOT operator evaluates to 0 (false). If

one operand is NULL and the other is not, then the IS NOT operator evaluates to 1

(true).

AND Both operands are true.

$MANAGER_ID AND EMPLOYEE ID >100

OR Either operand is true.

$MANAGER_ID OR EMPLOYEE ID >100

For more information on how to use the SQLite syntax, see the SQLite documentation.

Parallel Load

In Full Load replication mode, you can accelerate the replication of large tables by splitting the table

into segments and loading the segments in parallel. Tables can be segmented by data ranges, by

partitions, or by sub-partitions.

Supported endpoints

The task must be defined with a combination of the following source and target endpoints:

Supported source endpoints:

l

Amazon RDS for Microsoft SQL Server

l

IBM DB2 for LUW

l

IBM DB2 for z/OS

Table segmentation by partitions or sub-partitions is not supported with the IBM

DB2 for z/OS source endpoint.

l

Microsoft SQL Server

l

MySQL

l

Oracle

l

PostgreSQL

Table segmentation by partitions or sub-partitions is not supported with the

PostgreSQL source endpoint.

l

SAP Sybase ASE

Setup and User Guide - Enterprise Manager, May 2024 109

9 Customizing tasks

l

SAP Application

l

SAP Application (DB)

Tables are by default client dependent with the SAP Application (DB) source

endpoint. The MANDT column is automatically taken directly from the endpoint.

l

SAP HANA

l

Teradata

Supported target endpoints:

l

Amazon EMR

l

Amazon MSK

l

Amazon Redshift

l

Amazon S3

l

Cloudera Data Platform (CDP) Private Cloud

l

Databricks (Cloud Storage)

l

File

l

Google Cloud BigQuery

l

Google Cloud SQL for MySQL

l

Google Cloud SQL for PostgreSQL

l

Google Cloud Storage

l

Google Dataproc

l

Hadoop (Hortonworks and Cloudera)

l

Hortonworks Data Platform (HDP)

l

Kafka

l

Microsoft Azure ADLS

l

Microsoft Azure Database for MySQL

l

Microsoft Azure Database for PostgreSQL

l

Microsoft Azure Data Warehouse

l

Microsoft Azure HDInsight

l

Microsoft Azure SQL Database

l

Microsoft Fabric Data Warehouse

l

Microsoft SQL Server

l

MySQL

l

Oracle

l

PostgreSQL

l

Snowflake on Google

l

Snowflake on AWS

l

Snowflake on Azure

l

Sybase ASE

Setup and User Guide - Enterprise Manager, May 2024 110

9 Customizing tasks

To prevent deadlocks when performing a full parallel load of partitioned tables

into Sybase ASE, it is strongly recommended to enable the Create primary key or

unique index after full load completes option in the Full Load settings.

Setting up Parallel Load

To define segment boundaries by data range:

1. In the Parallel Load tab's Select Parallel Load Method section, select Use Data Ranges.

2. In the Select Details section, click Select Segment Columns.

The Columns window opens

3. For all endpoints, the Unique Index column is automatically selected. Select which additional

columns whose data you wish to use to delineate the ranges and then click OK.



Selecting indexed columns will significantly improve performance



You can select up to ten columns (multi-selection is supported)



Records with null values will not be replicated



The following data types cannot be used to define segments by ranges:

DOUBLE, FLOAT, and LOB (BLOB, CLOB, NCLOB)

4. In the Define Segment Boundaries section:

a. Click Add Segment to add a segment.

The columns that you selected will appear as table headings.

b. Enter the upper data range for the segment in the selected columns.

Values in DATE columns must be entered in the format supported by the

source. For example, for an Oracle source, the correct format would be:



ALTER SESSION SET NLS_DATE_FORMAT:

'YYYY-MM-DD HH24:MI:SS' (specifying YYYY-MM-DD only is also valid)



ALTER SESSION SET NLS_TIMESTAMP_FORMAT:

'YYYY-MM-DD HH24:MI:SS.FF9'



ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT:

'YYYY-MM-DD HH24:MI:SS.FF9 TZH:TZM'

c. Add additional segments as required.

d. Click Validate to validate that the specified data corresponds to the source column

data type and that all of the defined segments contain values.

e. To delete a segment, select the desired segment and then click Delete.

5. Click OK to save your settings.

Setup and User Guide - Enterprise Manager, May 2024 111

9 Customizing tasks

When Use Data Ranges is selected, all of the table data will be replicated, even if data

ranges are not defined for all of the columns.

Usage example

Let's assume that the following segments are defined in the Define Segment Boundaries table:

Column_1 Column_2 Column_3

10 30 105

20 20 120

100 12 99

Example table data

In this case, the following "WHERE" clauses will be created for each load segment:

l

Segment 1: ((COL1 < 10) OR ((COL1 = 10) AND (COL2 < 30)) OR ((COL1 = 10) AND (COL2 =

30) AND (COL3 < 105)))

l

Segment 2: NOT ((COL1 < 10) OR ((COL1 = 10) AND (COL2 < 30)) OR ((COL1 = 10) AND (COL2

= 30) AND (COL3 < 105))) AND ((COL1 < 20) OR ((COL1 = 20) AND (COL2 < 20)) OR ((COL1 =

20) AND (COL2 = 20) AND (COL3 < 120)))

l

Segment 3: NOT ((COL1 < 20) OR ((COL1 = 20) AND (COL2 < 20)) OR ((COL1 = 30) AND (COL2

= 20) AND (COL3 < 120))) AND ((COL1 < 100) OR ((COL1 = 100) AND (COL2 < 12)) OR ((COL1 =

100) AND (COL2 = 12) AND (COL3 < 99)))

l

Segment 4: NOT ((COL1 < 100) OR ((COL1 = 100) AND (COL2 < 12)) OR ((COL1 = 100) AND

(COL2 = 12) AND (COL3 < 99)))

To define segment boundaries by all of the table partitions:

Only select this method if you are sure that the table is already partitioned.

1. In the Parallel Load tab's Select Parallel Load Method section, select Use Partitions.

2. In the Select Partitions section, select Use all table partitions. This will segment the table

according to partitions that already exist in the source database.

3. Select one the following:

l

Use main partitions

l

Use sub partitions

This option will be disabled if the source database does not support sub-

partitions.

4. Click OK.

Setup and User Guide - Enterprise Manager, May 2024 112

9 Customizing tasks

To define segment boundaries by specific partitions:

Only select this method if you are sure that the table is already partitioned.

1. In the Parallel Load tab's Select Parallel Load Method section, select Use Partitions.

2. In the Select Partitions section, select Specify partitions. This will split the data according

to the specified source partitions.

When Specify partitions is selected, only the specified partitions will be

replicated.

3. Click Add Partition.

4. Specify the name of an existing partition or sub-partition.

5. If you specified the name of a sub-partition, select the check box in the Sub-Partition

column.

The check box will be disabled if the source database does not support sub-

partitions.

6. Add additional partitions/sub-partitions as required.

7. To delete a partition/sub-partition, select the partition/sub-partition and then click Delete.

8. Click OK to save your settings.

Adjusting the number of segments that can be loaded in parallel

You can increase or decrease the number of segments that will be loaded in parallel. For example, if

you selected the Use all table partitions option and the source table has 20 partitions, increasing

the default number of concurrent tasks (5) may improve performance.

Sub-tasks are allocated for each segment \ partition \ sub partition.

For example: If you select a table with 6 partitions and load the table using the Use

Partitions method, 5 partitions will be loaded in parallel, corresponding with the default

number of concurrent tasks (5). When one of the sub-tasks completes its run, it will be

assigned to loading the sixth partition.

The currently set value is displayed at the bottom of the Parallel Load tab. You can modify this

value in the Maximum number of tables to load in parallel field in the

Full Load Tuning (page 188)

tab.



Handling LOB columns

You can override the task's LOB settings for individual tables.

Setup and User Guide - Enterprise Manager, May 2024 113

9 Customizing tasks

This option is only available for tasks defined with any combination of the following

source and target endpoints: Oracle source, Oracle target, PostgreSQL source,

PostgreSQL target, Microsoft SQL Server source, Microsoft SQL Server target, MySQL

source, and MySQL target.



During CDC or during Full Load when the Allow unlimited LOB size option is

enabled, LOB data types are supported only in tables with a primary key or unique

index.



When replicating from Microsoft SQL Server, inline LOBS will always be read

directly from the logs (i.e. without lookup).

The following LOB handling options are available:

Option Description

Replicate

LOB columns

When this option is selected (the default), LOB columns will be replicated.

Note that replicating LOBs may impact performance. This is especially true in the

case of the large LOBs which require Replicate to perform a lookup from the

source table in order to retrieve the source LOB value.

Allow

unlimited

LOB size

Select this option - also known as Full LOB mode - to ensure that all LOBs are

replicated without being truncated. This option should be selected when all (or

nearly all) of the LOBs you wish to replicate are large (i.e. exceed 1 GB).

NoteIf the task's Change Processing Mode is set to "Batch

optimized apply" (the default), Replicate will switch to "Transactional

apply" mode to apply tables with LOBs.

LOB handling options

Setup and User Guide - Enterprise Manager, May 2024 114

9 Customizing tasks

Option Description

Optimize

handling

when LOB

size is less

than (KB)

Select this option when you need to replicate both small and large LOBs, and

most of the LOBs are small.

This option is supported with the following endpoints only:



Sources: Oracle, Microsoft SQL server, MySQL, PostgreSQL,

IBM DB2 for LUW, and Sybase ASE.



Targets: Oracle, Microsoft SQL Server, MySQL, PostgreSQL,

IBM DB2 for z/OS, and Sybase ASE.

When this option is selected, during Full Load, the small LOBs will be replicated

"inline" (which is more efficient), and the large LOBs will be replicated by

performing a lookup from the source table.

During Change Processing, however, both small and large LOBs will be replicated

by performing a lookup from the source table.

When this option is selected, Replicate will check all of the LOB sizes

to determine which ones to transfer "inline". LOBs larger than the

specified size will be replicated using Full LOB mode.

Therefore, if you know that most of the LOBs are larger than the

specified setting, it is better to use the Allow unlimited LOB size

option instead.

Chunk size

(KB)

Optionally, change the size of the LOB chunks to use when replicating the data to

the target. The default chunk size should suffice in most cases, but if you

encounter performance issues, adjusting the size may improve performance.

With some databases, data type validation occurs when the data is

inserted or updated. In such cases, replication of structured data

types (e.g. XML, JSON, GEOGRAPHY, etc.) may fail if the data is

bigger than the specified chunk size.

Setup and User Guide - Enterprise Manager, May 2024 115

9 Customizing tasks

Option Description

Limit LOB

size to (KB)

Select this option if you only need to replicate small LOBs or if the target

endpoint does not support unlimited LOB size. The maximum permitted value for

this field is 102400 KB (100 MB).

When replicating small LOBs, this option is more efficient than the Allow

unlimited LOB size option since the LOBs are replicated "inline" as opposed to

via "lookup" from the source. During Change Processing, small LOBs are usually

replicated via "lookup" from the source.

As the value of the Limit LOB size to is in bytes, the size should be calculated

according to the following formulas:

l

BLOB – The length of the largest LOB.

l

NCLOB – The length of the longest TEXT

in characters

multiplied by two

(as each character is handled as a double-byte).

If the data includes 4-byte characters, multiply it by four.

l

CLOB – The length of the longest TEXT in characters (as each character is

handled as a UTF8 character).

If the data includes 4-byte characters, multiply it by two.



Any LOBs larger than the specified size will be truncated.



During Change Processing from Oracle source, inline BLOBs are

replicated inline.



Changes to this setting will only affect existing tables after they

are reloaded.

In some scenarios, tasks configured to replicate tables with multiple LOB columns may

consume a large amount of memory. This is because Replicate allocates memory by

multiplying the Limit LOB size to value by the

Commit rate during full load

value, the

sum of which, it multiplies by the number of LOB columns being replicated. So, for

example, if LOB size is limited to 5 MB and the default commit rate is used (10000

events), a task replicating 6 LOB columns will consume 300 GB of memory. Note that

other factors such as the database type and version may also affect memory

consumption.

Should you encounter memory consumption issues and suspect that a combination of

the above factors may be the cause, stop the task and lower the value in the

Commit

rate during full load

field. Then resume the task. Repeat this process until acceptable

performance/memory levels are reached.

These instructions apply to Change Processing and Full Load tasks.

Setup and User Guide - Enterprise Manager, May 2024 116

9 Customizing tasks

Changes to a column’s LOB size while a task is running will not be reflected in the

Change Table, unless the target tables are created by Qlik Replicate. In such cases, the

task must be configured to drop and create the Change Table (the default) and the

target tables need to be reloaded (after the LOB size has changed).

For more information on the Change Table, see Store Changes Settings (page 189). For

information on reloading target tables, see the Qlik Replicate User Guide and Reference.

Message format

This tab is only available for tasks defined with a supported streaming endpoint.

When a task is defined with such an endpoint, you can specify a custom message format that will

override the default Replicate message format. This may be useful if the consumer application

needs to process the message in a particular format.

The custom message format can be defined at task level and/or at table level. When it is defined at

both task

and

table level, the message format defined for the table will take precedence over the

message format defined for the task.

To define a custom message at table level:

1. Select a table.

2. Open the Table Settings window as described in

Table Settings (page 91)

3. Select the Message Format tab and click the Change to Table Policy button.

4. Configure the message format as described in

Message Format (page 214)

5. To use the message format defined for the task, click the Change to Task Policy button.

For information on defining a custom message at task level, see

Message Format (page 214)

Full Load

This tab is available for tasks defined with the IBMDB2 for z/OS and IBM DB2 for iSeries source

endpoints only.

Select the Eliminate creation of duplicate records on full load option if you need to prevent

duplicate records from being replicated during Full Load. You can either set the option at task level

or per table.

Note that selecting this option could impact performance as Replicate instructs the source

database to return the table records by Primary Key order and then removes any duplicate records.

To prevent creation of duplicate records per table:

1. Select the desired table and then open the Table Settings window as described in

Table

Settings (page 91)

Setup and User Guide - Enterprise Manager, May 2024 117

9 Customizing tasks

2. Select the Full Load tab and click the Change to Table Policy button.

3. Select the Prevent creation of duplicate records on the target check box.

For information on preventing creation of duplicate records at task level, see

Full Load Settings

(page 185)

9.2  Defining global rules

Global rules are a useful way of making changes across multiple tables and columns in the same

task. You can define

transformation rules

that modify the source data or metadata before it reaches

the target, and/or you can define

filter rules

that determine which records will be replicated to the

target.

Global rules are not available in a Log Stream Staging setup.

For information on Log Stream Staging, refer to the Qlik Replicate Setup and User Guide.

l

Transformations - One way transformations can be used is to change the names of all tables

in a task. You can change the names using wildcards and patterns. For example, you may

want to change the names of the tables from account_% to ac_%. This is helpful when

replicating data from a Microsoft SQL Server endpoint to an Oracle endpoint where the

Microsoft SQL Server endpoint has a limit of 128 characters for a table name and the Oracle

endpoint has a limit of 31 characters.

You may also need to change a specific data type in the source to a different data type in the

target for many or all of the tables in the task. Global transformation will allow you to

accomplish this without having to define a transformation for each individual table.

Table-specific transformations override global transformations. For example, you

can define a global transformation that changes the data type for all tables from

DATE to DATETIME(6) and then define another transformation for a specific table

that changes the data type from DATE to STRING(50).

For information on defining a transformation for a specific table, see

Defining

Transformations for a Single Table/View

For an explanation of how to create global transformation, see

Starting the Global

Transformation Rules wizard (page 118)

l

Filters - Use filter rules to determine which records will be replicated to the target. Filter can

be based on column data (e.g. only replicate records where Age is greater than 18) or record

attributes (e.g. only replicate UPDATED records).



For an explanation of how to create global filters, see

Starting the Global Filter Rules wizard

(page 143)

Starting the Global Transformation Rules wizard

You define global transformations using the Global Transformation Rules wizard.

Setup and User Guide - Enterprise Manager, May 2024 118

9 Customizing tasks

To start the Global Transformations wizard:

1. Open the task for which you want to create a global transformations or a global filter.

You can click View Task above the Tasks list or double-click the task.

2. If you are not in the Designer mode, click Designer at the top right of the screen.

For more information on the Designer mode, see

Designer mode (page 225)

3. In Designer mode, click Global Rules.

The Global Rules window opens.

4. Click the New Rule toolbar button and select Transformation.

The New Transformation Rule wizard opens.

5. Enter the information to define a global transformation rule. The first step is selecting the

Transformation type (page 119)

Limitations for global transformations

The following limitations apply to global transformations:

l

Transformations are not supported for columns with Right-to-Left languages.

l

Transformations cannot be performed on columns that contain special characters (e.g. #, \, /,

-) in their name.

l

The only supported transformation for columns that are mapped to BLOB/CLOB data types

(by Replicate) is to drop the column on the target.

l

Expressions must be written using SQLite syntax only.

l

Changing a global transformation value will not reload affected tables automatically. The

target must be reloaded manually for the changes to take effect.

For information on reloading the target, see

Using the Run button options (page 255)

Transformation type

In the Transformation type step of the New Transformation Rule wizard, you define the type of

transformation you want to be performed.

You can only create one rule for each transformation type on the same object (e.g. a

column). If you create multiple rules for a single transformation type on the same object,

only the last rule you create will be valid. For example, if you create the following rules (in

order) to rename a schema:

Rename Schema: Add Prefix

Rename Schema: Add Suffix

Rename Column: Add Prefix

Rename Column: Add Suffix

Only the second rule (adding a suffix) will be executed.

Setup and User Guide - Enterprise Manager, May 2024 119

9 Customizing tasks

To select the transformation type:

1. Enter a name for the rule.

The name cannot exceed 32 characters, contain non-Latin characters, or contain any of the

following characters: \/:*?"<>|

2. Select one of the following:

Table or Schema:

l

Rename schema: Select this to change the schema name for multiple tables. For

example, if you want all HR tables to be renamed PERS.

l

Rename table: Select this to change the name of multiple tables. For example, if you

want all tables named SALARY to be called WAGES.

Tablespace:

l

Change table tablespace: Select this to change the table tablespace on the target.

You can change the table tablespace regardless of what objects it contains or you can

specify a condition for it to be renamed. For example, change all table tablespaces

that contain the table Employees in the schema Company.

By default (i.e. when this option is not selected), the tables will either be created in the

source table tablespace on the target (when replicating from an Oracle source) or in

the default database tablespace (when replicating from any other source).

This option is only available for tasks with an Oracle target endpoint.

l

Change index tablespace: Select this to change the index tablespace on the target.

You can change the index tablespace regardless of what objects it contains or you can

specify a condition for it to be renamed. For example, change all table tablespaces

that contain the table Employees in the schema Company.

By default (i.e. when this option is not selected), the indexes will either be created in

the source table tablespace on the target (when replicating from an Oracle source) or

in the default database tablespace (when replicating from any other source).

This option is only available for tasks with an Oracle target endpoint.

Column:

l

Rename column: Select this to change the name of multiple columns. For example, if

you want to change all columns with word MINIMUM to MIN.

l

Add column: Select this to add a column with a similar name to multiple tables.

l

Drop column: Select this to drop a column with a similar name from multiple tables.

l

Convert data type: Select this if you want to change a specific data type to a different

one across multiple tables. For example, if you want to change all Integer data types to

a string.

l

Replace column data: Select this to replace column data across multiple target

tables.

Setup and User Guide - Enterprise Manager, May 2024 120

9 Customizing tasks

In homogeneous replication tasks (such as Oracle to Oracle), modifying a single

table column (by changing the column data type or length for example), will break

the homogeneity for the entire table.

Change Table:

Change Table transformations are only available when the Store Changes replication option

is enabled.

For more information on Change Tables, refer to the

Qlik Replicate

Setup and User Guide.

l

Rename Change Table: Select this to rename the Replicate Change Table for all

tables or for any table that matches the specified schema name and/or table name.

l

Rename Change Table schema: Select this to change the schema under which the

Replicate Change Table will be created, for all tables or for any table that matches the

specified schema name and/or table name.

3. Click Next to proceed to the

Transformation scope (page 121)

step.

Transformation scope

In the Transformation scope screen, you define which tables will be affected by the

transformation. For example, you can apply the rule to all tables that contain the word SALARY as part

of their name.

The options displayed in this screen depend on the selected

Transformation Type

The following table describes all available options. The second column lists the Transformation

Type where the option to be available.

Setup and User Guide - Enterprise Manager, May 2024 121

9 Customizing tasks

Option Transformation Type Description

Schema name

is like %

All Leave the % sign to include all schemas in your

global transformation.

Click the % sign to add a filter. In this case you can

enter any name combination to include only that

schema in your global transformation rule.

For example, enter HR to include only tables that

have the schema HR.

You can use the % sign as a wildcard. For example,

H% includes all tables with a schema that begins

with the letter H, such as HR, HELLO, or HQ.

The % wildcard can be used in any position. For

example, if you use it at the beginning, %H, then all

table names that end in H are included in the

transformation rule. The % can also be used in a

middle position.

If you are using an Oracle target, you

must enter a schema that exists on the

target endpoint. Qlik Replicate does

not create new schemas on an Oracle

endpoint. If you want to use a new

schema for the target, create the

schema on the Oracle endpoint before

running the task. For more information,

see the topic "Configuring an Oracle

database as a Qlik Replicate Target" in

the Qlik Replicate User and Reference

Guide.

Transformation conditions

Setup and User Guide - Enterprise Manager, May 2024 122

9 Customizing tasks

Option Transformation Type Description

Table

tablespace is

like %

Change table tablespace

This option is only available if the task

is defined with an Oracle target

endpoint.

Leave the % sign to include all table tablespace

names in your global transformation.

Click the % sign to add a filter. In this case, you can

enter any name combination to include only the

specified table tablespace in your global

transformation rule.

For example, enter SYSTEM to include only table

tablespaces called SYSTEM.

You can also use the % sign as a wildcard

anywhere in the string. For example, H% includes

all table tablespaces that begin with the letter "H"

whereas %H includes all table tablespaces that end

with the letter "H".

Index

tablespace is

like %

Change index tablespace

This option is only available if the task

is defined with an Oracle target

endpoint.

Leave the % sign to include all index tablespace

names in your global transformation.

Click the % sign to add a filter. In this case, you can

enter any name combination to include only the

specified index tablespace in your global

transformation rule.

For example, enter SYSTEM to include only index

tablespaces called SYSTEM.

You can also use the % sign as a wildcard

anywhere in the string. For example, H% includes

all index tablespaces that begin with the letter "H"

whereas %H includes all index tablespaces that

end with the letter "H".

Setup and User Guide - Enterprise Manager, May 2024 123

9 Customizing tasks

Option Transformation Type Description

Table name is

like %

All Leave the % sign to include all table names in your

global transformation rule.

Click the % sign to add a filter. In this case you can

enter any name combination to include only tables

with that specific name in your global

transformation rule.

You can use the % sign as a wildcard. For example,

J% includes all tables with a name that begins with

the letter J, such as JOBS, JOBS_HISTORY, or

JACKSONVILLE.

The % wildcard can be used in any position. For

example, if you use it at the beginning, %H, then all

table names that end in H are included in the

transformation rule. The % can also be used in a

middle position.

Column name

is like %

Rename column

Drop column

Convert data type

Replace column value

Leave the % sign to include all column names in

your global transformation rule.

Click the % sign to add a filter. In this case you can

enter any name combination to include only

columns with that specific name in your global

transformation rule.

You can use the % sign as a wildcard. For example,

N% includes all columns with a name that begins

with the letter N, such as NAME, NAME_FIRST, or NAME_

LAST.

The % wildcard can be used in any position. For

example, if you use it at the beginning, %IES, then

all column names that end in with the string "IES"

are included in the transformation rule. The % can

also be used in a middle position.

Data type is Convert data type

Replace column value

Select a new data type from the drop-down list.

Make sure that the data type you select is

compatible with the data in that column.

For a description of Qlik Replicate data types,

information about data type mapping from the

native endpoint to Qlik Replicate, or for a list of

endpoints supported by Qlik Replicate, see the

Qlik Replicate User and Reference Guide.

Setup and User Guide - Enterprise Manager, May 2024 124

9 Customizing tasks

Option Transformation Type Description

Scope

expression

All Click Advanced options to define a scope

expression using the Expression Builder.

After you complete defining the transformation rule definitions, click Next to go to the

Transformation action (page 125)

step.

If the global transformation type you are defining is Drop Column, you do not need to

create a

Transformation Rule

. In this case, click Finish to add the rule to the

Global Rules

list.

Transformation action

In the Transformation action screen, you define what happens to the objects affected by the

transformation rule. For example, you can define a new name for the affected objects or add a

prefix to the table names. Only objects that fall within the

Transformation scope (page 121)

will be

affected.

The following transformation options are available:

l

Rename Schema (page 126)

l

(page 129)

l

(page 130)

l

Rename Table (page 130)

l

Rename Column (page 132)

l

Add column (page 135)

l

Drop Column (page 136)

l

Convert data type (page 136)

l

Rename Change Table schema (page 140)

l

Rename Change Table (page 137)

When done, click Next.

Limitations for transformation rules

The following limitations apply to transformation rules:

l

Transformations are not supported for columns with Right-to-Left languages.

l

Transformations cannot be performed on columns that contain special characters (e.g. #, \, /,

-) in their name.

l

The only supported transformation for columns that are mapped to BLOB/CLOB data types

(by Replicate) is to drop the column on the target.

l

Expressions must be written using SQLite syntax only.

l

Changing a global transformation value will not reload affected tables automatically. The

Setup and User Guide - Enterprise Manager, May 2024 125

9 Customizing tasks

target must be reloaded manually for the changes to take effect.

For information on reloading the target, see

Using the Run button options (page 255)

The options displayed in this screen depend on the

Transformation Type

selected.

Rename Schema

If your transformation type is Rename Schema, you can do the following:

l

Rename schema to (string) (page 126)

l

Add a prefix or suffix (page 126)

l

Remove a prefix or suffix (page 127)

l

Replace a prefix or suffix with different characters (page 127)

l

Convert schema name to uppercase (page 128)

l

Convert schema name to lowercase (page 128)

l

Rename schema (Expression) (page 128)

Rename schema to (string)

Use the Rename schema to: [string] option to change the name of all table schemas that you

defined in the

Transformation scope (page 121)

step to a different name. For example, if you have a

schema called Human_Resources and want to change all instances of this name to HR then enter the

string HR. You can enter any string in this field.

Add a prefix or suffix

Use the Add a prefix or suffix option to add additional characters to the beginning or end of the

schema name for all schemas that fit the definition you created in the

Transformation scope (page

121)

step. For example, if the schema name is HR, you can add a suffix, such as TAR or _TAR to the

schema name for all tables with that schema name. In this case, the resulting schema name will be

HRTAR or HR_TAR.

If you are using Oracle as your target endpoint, Qlik Replicate does not create a new

schema. Therefore, the schema name that is the result of replacing a prefix or suffix with

a different string of characters must exist in the Oracle target endpoint. If the resulting

schema name does not exist, you must create the schema in the Oracle endpoint before

carrying out this task.

For more information, see the Qlik Replicate Setup and User Guide.

To globally add a prefix or suffix

1. Select Add <Prefix/Suffix> Insert Characters to matching schema names.

2. Click the word Prefix or Suffix and select one of these two from the list.

3. Click [string] to activate the field.

Setup and User Guide - Enterprise Manager, May 2024 126

9 Customizing tasks

4. Type the characters you want as the prefix or suffix. If you want to include an underscore or

other legal character to separate the prefix/suffix from the original name, you must add it as

part of the character string.

5. Click Finish to add the rule to the Global Rules list.

Remove a prefix or suffix

Use the Remove a prefix or suffix option to remove a string of characters from the beginning or

end of a schema name for all schema that fit the definition you created in the

Transformation scope

(page 121)

step.

For example, you can use this option to remove the letters _REV from the schema name for all tables

in the schema HR_REV. In this case the schema name in the target will be HR.

If you are using Oracle as your target endpoint, Qlik Replicate does not create a new

schema. Therefore, the schema name that is the result of replacing a prefix or suffix with

a different string of characters must exist in the Oracle target endpoint. If the resulting

schema name does not exist, you must create the schema in the Oracle endpoint before

carrying out this task.

For more information, see the Qlik Replicate Setup and User Guide.

To globally remove a prefix or suffix

1. Select Remove <Prefix/Suffix> Insert Characters from matching schema names.

2. Click the word Prefix or Suffix and select one of these two from the list.

3. Click [string] to activate the field.

4. Type the characters you want to remove. If you want to remove an underscore or other legal

character from the original name, you must add it as part of the character string.

5. Click Finish to add the rule to the Global Rules list.

Replace a prefix or suffix with different characters

Use the Replace a prefix or suffix option to replace a string of characters with a different string of

characters. You determine whether to replace the characters at the beginning or end of a schema

name for all schema that fit the definition you created in the

Transformation scope (page 121)

step.

For example, you can use this option to replace the letters _ORIG with _REPL in the schema name for

all tables in the schema HR_ORIG. In this case the schema name in the target will be HR_REPL.

If you are using Oracle as your target endpoint, Qlik Replicate does not create a new

schema. Therefore, the schema name that is the result of replacing a prefix or suffix with

a different string of characters must exist in the Oracle target endpoint. If the resulting

schema name does not exist, you must create the schema in the Oracle endpoint before

carrying out this task.

For more information, see the Qlik Replicate Setup and User Guide.

Setup and User Guide - Enterprise Manager, May 2024 127

9 Customizing tasks

To globally replace a prefix or suffix

1. Select Replace <Prefix/Suffix> Insert Characters by Insert Characters for all matching

schema names.

2. Click the word Prefix or Suffix and select one of these two from the list.

3. Click the first [string] to activate the field.

4. Type the characters from the existing (source) schema that you want to replace. If you want

to include an underscore or other legal character from the original name in the string that you

want to replace, you must add it as part of the character string.

5. Click the second [string] to activate the field.

6. Type the characters you want to use in the target. These characters replace the original

(source) characters in the target.

7. Click Finish to add the rule to the Global Rules list.

Convert schema name to uppercase

Use the convert to uppercase option to convert all of the letters in a schema name to upper case.

For example:

l

Schema_cat, becomes SCHEMA_CAT

l

schema_cat, becomes SCHEMA_CAT

l

sChEMa_Cat, becomes SCHEMA_CAT

To globally change the schema name to all uppercase

1. Select Convert schema name to uppercase.

2. Click Finish to add the rule to the Global Rules list.

Convert schema name to lowercase

Use the convert to lowercase option to convert all of the letters in a schema name to lower case.

For example:

l

Schema_cat, becomes schema_cat

l

SCHEMA_CAT, becomes schema_cat

l

sChEMa_Cat, becomes schema_cat

To globally change the schema name to all uppercase

1. Select Convert schema name to lowercase.

2. Click Finish to add the rule to the Global Rules list.

Rename schema (Expression)

Use the Rename schema to [expression] option to change the name of all table schemas that you

defined in the

Transformation scope (page 121)

step to a different name. For example, if you have a

schema called Human_Resources and want to change all instances of this name to HR.

Setup and User Guide - Enterprise Manager, May 2024 128

9 Customizing tasks

If you are using Oracle as your target endpoint, Qlik Replicate does not create a new

schema. Therefore, the schema name that is the result of replacing a prefix or suffix with

a different string of characters must exist in the Oracle target endpoint. If the resulting

schema name does not exist, you must create the schema in the Oracle endpoint before

carrying out this task.

For more information, see the Qlik Replicate Setup and User Guide.

To globally change a schema name

1. Select Rename schema to [expression]

2. Click the button to the right of the Rename schema option to open the Expression Editor. For

information on how to use the Expression Editor, see

Using the Expression Builder (page

148)

. Then go to step 4.

Click [expression] to activate the field and continue with step 3.

3. Type an SQLite expression or a string (in quotes) to rename the schema. For example:

l

"New_Schema"

l

’PREF_’||$SCHEMA_NAME_VAR||’_SUFF’

You can use the following variables in the SQLite expression:

l

$SCHEMA_NAME_VAR

l

$TABLE_NAME_VAR

l

$COLUMN_NAME_VAR

l

$COLUMN_DATATYPE_VAR

4. Click Finish to add the rule to the Global Rules list.

Change table tablespace

If your transformation type is Change table tablespace, you can change the table tablespace on an

Oracle target. You can also set certain conditions that must exist in the source for the table

tablespace to be changed. These include schema name, table name and table tablespace name.

For more information, see the following topics:

l

Transformation type (page 119)

l

Transformation action (page 125)

Change index tablespace

If your transformation type is Change index tablespace, you can change the index tablespace on

an Oracle target. You can also set certain conditions that must exist in the source for the tablespace

to be changed. These include schema name, table name and index tablespace name.

For more information, see the following topics:

Setup and User Guide - Enterprise Manager, May 2024 129

9 Customizing tasks

l

Transformation type (page 119)

l

Transformation action (page 125)

Rename Table

If your transformation type is Rename Table, you can do the following:

l

Rename table to (string) (page 130)

l

Add a prefix or suffix (page 130)

l

Remove a prefix or suffix (page 130)

l

Replace a prefix or suffix with different characters (page 131)

l

Convert table name to uppercase (page 131)

l

Convert table name to lowercase (page 131)

l

Rename table (expression) (page 132)

Rename table to (string)

Use the Rename table to: [string] option to change the name of all tables that you defined in the

Transformation scope (page 121)

step to a different name. For example, if you have a table called

EMPLOYEE and want to change all instances of this name to EMP then enter the string EMP. You can

enter any string in this field.

Add a prefix or suffix

Use the Add a prefix or suffix option to add additional characters to the beginning or end of the

table name for all tables that fit the definition you created in the

Transformation scope (page 121)

step. For example, if the table name is EMPLOYEES, you can add a suffix, such as TAR or _TAR to the

table name for all tables with that table name. In this case, the resulting table name will be

EMPLOYEESTAR or EMPLOYEES_TAR.

To globally add a prefix or suffix:

1. Select Add <Prefix/Suffix> Insert Characters to matching table names.

2. Click the word Prefix or Suffix and select one of these two from the list.

3. Click [string] to activate the field.

4. Type the characters you want as the prefix or suffix. If you want to include an underscore or

other legal character to separate the prefix/suffix from the original name, you must add it as

part of the character string.

5. Click Finish to add the rule to the Global Rules list.

Remove a prefix or suffix

Use the Remove a prefix or suffix option to remove a string of characters from the beginning or

end of a table name for all tables that fit the definition you created in the

Transformation scope

(page 121)

step.

For example, you can use this option to remove the letters _REV from the table name for all tables

with the name EMPLOYEES. In this case the table name in the target will be EMPLOYEES.

Setup and User Guide - Enterprise Manager, May 2024 130

9 Customizing tasks

To globally remove a prefix or suffix:

1. Select Remove <Prefix/Suffix> Insert Characters from matching table names.

2. Click the word Prefix or Suffix and select one of these two from the list.

3. Click [string] to activate the field.

4. Type the characters you want to remove. If you want to remove an underscore or other legal

character from the original name, you must add it as part of the character string.

5. Click Finish to add the rule to the Global Rules list.

Replace a prefix or suffix with different characters

Use the Replace a prefix or suffix option to replace a string of characters with a different string of

characters. You determine whether to replace the characters at the beginning or end of a table

name for all tables that fit the definition you created in the

Transformation scope (page 121)

step.

For example, you can use this option to replace the letters _ORIG with _REPL in the table names for all

tables called EMPLOYEE_ORIG. In this case the table name in the target will be EMPLOYEE_REPL.

To globally replace a prefix or suffix:

1. Select Replace <Prefix/Suffix> Insert Characters by Insert Characters for all matching

schema names.

2. Click the word Prefix or Suffix and select one of these two from the list.

3. Click the first [string] to activate the field.

4. Type the characters from the existing (source) schema that you want to replace. If you want

to include an underscore or other legal character from the original name in the string that you

want to replace, you must add it as part of the character string.

5. Click the second [sting] to activate the field.

6. Type the characters you want to use in the target. These characters replace the original

(source) characters in the target.

7. Click Finish to add the rule to the Global Rules list.

Convert table name to uppercase

Use the convert to uppercase option to convert a table name to all upper case. For example:

l

Table_cat, becomes TABLE_CAT

l

table_cat, becomes TABLE_CAT

l

taBLe_Cat, becomes TABLE_CAT

To globally change the table name to all uppercase:

1. Select Convert table name to uppercase.

2. Click Finish to add the rule to the Global Rules list.

Convert table name to lowercase

Use the convert to lowercase option to convert a table name to all lower case. For example:

Setup and User Guide - Enterprise Manager, May 2024 131

9 Customizing tasks

l

Table_cat, becomes table_cat

l

TABLE_CAT, becomes table_cat

l

taBLe_Cat, becomes table_cat

To globally change the table name to all lowercase:

1. Select Convert table name to lowercase.

2. Click Finish to add the rule to the Global Rules list.

Rename table (expression)

Use the Rename table to [expression] option to change the name of all tables that fit the definition

you created in the

Transformation scope (page 121)

step. For example, if you have a table called

EMPLOYEE and want to change all instances of this name as defined in the previous step it to EMP.

To change the table name:

1. Select Rename table to: [expression]

2. Click the button to the right of the Rename table option to open the Expression Editor. For

information on how to use the Expression Editor, see

Using the Expression Builder (page

148)

. Then go to step 4.

Click [expression] to activate the field and continue with step 3.

3. Type an SQLite expression or a string (in quotes) to rename the table. For example:

l

"New_Table"

l

’PREF_’||$TABLE_NAME_VAR||’_SUFF’

You can use the following variables in the SQLite expression:

l

$SCHEMA_NAME_VAR

l

$TABLE_NAME_VAR

l

$COLUMN_NAME_VAR

l

$COLUMN_DATATYPE_VAR

Rename Column

If your transformation type is Rename Column, you can do the following:

l

Rename column to (string) (page 133)

l

Add a prefix or suffix (page 133)

l

Remove a prefix or suffix (page 133)

l

Replace a prefix or suffix with different characters (page 133)

l

Convert column name to uppercase (page 134)

l

Convert column name to lowercase (page 134)

l

Rename column (expression) (page 134)

Setup and User Guide - Enterprise Manager, May 2024 132

9 Customizing tasks

Rename column to (string)

Use the Rename column to: [string] option to change the name of all columns that you defined in

the

Transformation scope (page 121)

step to a different name. For example, if you have a table

called SALARY and want to change all instances of this name to EMP then enter the string SAL. You can

enter any string in this field.

Add a prefix or suffix

Use the Add a prefix or suffix option to add additional characters to the beginning or end of the

column name for all columns that fit the definition you created in the

Transformation scope (page

121)

step. For example, if the column name is SALARY, you can add a suffix, such as TAR or _TAR to the

table name for all tables with that table name. In this case, the resulting table name will be SALARYTAR

or SALARY_TAR.

To globally add a prefix or suffix:

1. Select Add <Prefix/Suffix> Insert Characters to matching column names.

2. Click the word Prefix or Suffix and select one of these two from the list.

3. Click the [string] to activate the field.

4. Type the characters you want as the prefix or suffix. If you want to include an underscore or

other legal character to separate the prefix/suffix from the original name, you must add it as

part of the character string.

5. Click Finish to add the rule to the Global Rules list.

Remove a prefix or suffix

Use the Remove a prefix or suffix option to remove a string of characters from the beginning or

end of a column name for all columns that fit the definition you created in the

Transformation scope

(page 121)

step.

For example, you can use this option to remove the letters _REV from the column name for all

columns with the name SALARY. In this case the column name in the target will be SALARY.

To globally remove a prefix or suffix:

1. Select Remove <Prefix/Suffix> Insert Characters from matching column names.

2. Click the word Prefix or Suffix and select one of these two from the list.

3. Click [string] to activate the field.

4. Type the characters you want to remove. If you want to remove an underscore or other legal

character from the original name, you must add it as part of the character string.

5. Click Finish to add the rule to the Global Rules list.

Replace a prefix or suffix with different characters

Use the Replace a prefix or suffix option to replace a string of characters with a different string of

characters. You determine whether to replace the characters at the beginning or end of a column

name for all columns that fit the definition you created in the

Transformation scope (page 121)

step.

For example, you can use this option to replace the letters _ORIG with _REPL in the column names for

all columns called SALARY_ORIG. In this case the column name in the target will be SALARY_REPL.

Setup and User Guide - Enterprise Manager, May 2024 133

9 Customizing tasks

To globally replace a prefix or suffix:

1. Select Replace <Prefix/Suffix> Insert Characters by Insert Characters for all matching

schema names.

2. Click the word Prefix or Suffix and select one of these two from the list.

3. Click the first [string] to activate the field.

4. Type the characters from the existing (source) column that you want to replace. If you want

to include an underscore or other legal character from the original name in the string that you

want to replace, you must add it as part of the character string.

5. Click the second [string] to activate the field.

6. Type the characters you want to use in the target. These characters replace the original

(source) characters in the target.

7. Click Finish to add the rule to the Global Rules list.

Convert column name to uppercase

Use the convert to uppercase option to convert a column name to all upper case. For example:

l

Column_cat, becomes COLUMN_CAT

l

column_cat, becomes COLUMN_CAT

l

coLUMnM_Cat, becomes COLUMN_CAT

To globally change the table name to all uppercase

1. Select Convert column name to uppercase.

2. Click Finish to add the rule to the Global Rules list.

Convert column name to lowercase

Use the convert to lowercase option to convert a column name to all lower case. For example:

l

Column_cat, becomes column_cat

l

column_cat, becomes column_cat

l

coLUMnM_Cat, becomes column_cat

To globally change the column name to all lowercase:

1. Select Convert column name to lowercase.

2. Click Finish to add the rule to the Global Rules list.

Rename column (expression)

Use the Rename column to [expression] option to change the name of all tables that fit the

definition you created in the

Transformation scope (page 121)

step. For instance, if you have a

column called SALARY and want to change it to SAL.

Setup and User Guide - Enterprise Manager, May 2024 134

9 Customizing tasks

To change the column name:

1. Select Rename column to: [expression]

2. Click the button to the right of the Rename column option to open the Expression Editor. For

information on how to use the Expression Editor, see

Using the Expression Builder (page

148)

. Then go to step 4.

Click [expression] to activate the field and continue with step 3.

3. Type an SQLite expression or a string (in quotes) to rename the column. For example:

l

"New_Column"

l

’PREF_’||$COLUMN_NAME_VAR||’_SUFF’

You can use the following variables in the SQLite expression:

l

$SCHEMA_NAME_VAR

l

$TABLE_NAME_VAR

l

$COLUMN_NAME_VAR

l

$COLUMN_DATATYPE_VAR

Add column

When you add a column to multiple tables, you must provide a name, define the data type for the

column and define the data that the column contains. The column that you define here is added to

all tables that fit the definition you created in step

Transformation scope (page 121)

The following describes the information you must enter in the transformation rule page for adding a

column.

l

Column name: Click the [string] to activate the field. Type the name for the column in the

field. A column with this name is added to all tables that fit the definition you created in step

Transformation scope (page 121)

l

Add to Primary Key: Select to add the column to the target tables' Primary Key.

l

Expression: Click the button to the right of this field to open the Expression Builder or type

an expression using SQLite operators to define the data in the column.

For information on how to use the Expression Editor to create an expression, see

Using the

Expression Builder (page 148)

For more information on creating expressions, see

Creating an expression for

transformations (page 101)

and

Using SQLite syntax with transformations (page 101)

l

Set target data type to: Click the drop-down for a list of data types and select a new data

type from the drop-down list. Make sure that the data type you select is compatible with the

data in that column. When BYTES, STRING or WSTRING is selected, specify a Length as

well. When NUMERIC is selected, specify a Precision and Scale as well.

l

Subtype: When CLOB, NCLOB, STRING, or WSTRING data types are selected, you can also

set a data subtype. Select either JSON or XML from the Subtype drop-down list. Make sure

that the data in the new column will be compatible with the selected subtype. The default is

Regular, which means that the regular data type will be used without a subtype.



Setup and User Guide - Enterprise Manager, May 2024 135

9 Customizing tasks

For a description of the available data types and for a list of endpoints supported by Qlik

Replicate, and for information about data type mapping from source endpoints to Qlik

Replicate data types, see the Qlik Replicate online help

Drop Column

This option does not require a transformation rule. For this option you complete the Global

transformation rule after the

Transformation scope (page 121)

step.

Convert data type

The data type that you define in this step is applied to all columns and tables that fit the definition

you created in the

Transformation scope (page 121)

step. Make sure that the data type you select is

compatible with the data in the columns you defined.

l

Set target data type to - If you change the target value type (e.g. string to numeric), you

may also need to change the data type of the target columns as well.

For the BYTES, STRING, and WSTRING data types, you can optionally specify the Length as

well. If you leave the default value ("0"), Replicate will calculate the data type length based

on the source column definition. You can also set the length using an expression. When you

click the fx button to the right of the Length field, the Expression Builder opens showing the

Metadata tab. For an explanation of the variables in this tab, see

Metadata (Global

transformations only) (page 154)

Example: 

The following expression multiplies the modified data type length by two.

$AR_M_MODIFIED_DATATYPE_LENGTH * 2



For the NUMERIC data type, you can optionally set the Precision and Scale. If you leave the

default value ("0"), Replicate will calculate the precision and/or scale based on the source

column value.

l

Subtype: When CLOB, NCLOB, STRING, or WSTRING data types are selected, you can also

set a data subtype. Select either JSON or XML from the Subtype drop-down list. Make sure

that the new data in the column will be compatible with the selected subtype. The default is

Regular, which means that the regular data type will be used without a subtype.

For a description of the available data types and for a list of endpoints supported by Qlik Replicate,

and for information about data type mapping from source endpoints to Qlik Replicate data types,

see the Qlik Replicate online help

Replace column value

Use the Replace column value transformation to replace the values in the source columns (set in

the Transformation scope) with different values in the corresponding target columns.

The following options are available:

l

Replace target value with - Create an expression for replacing the value in the source

column values with a different value in the target columns. When you click the fx button to

the right of the field, the Expression Builder opens showing the Data tab. For an explanation

Setup and User Guide - Enterprise Manager, May 2024 136

9 Customizing tasks

of the variables in this tab, see

Data (global transformations only) (page 153)

Example: 

The following expression appends the string "_new" to the original source column values.

$AR_M_SOURCE_COLUMN_DATA || '_new'

l

Set target data type to - If you change the target value type (e.g. string to numeric), you

may also need to change the data type of the target columns as well.

For the BYTES, STRING, and WSTRING data types, you can optionally specify the Length as

well. If you leave the default value ("0"), Replicate will calculate the data type length based

on the source column definition. You can also set the length using an expression. When you

click the fx button to the right of the Length field, the Expression Builder opens showing the

Metadata tab. For an explanation of the variables in this tab, see

Metadata (Global

transformations only) (page 154)

Example: 

The following expression multiplies the modified data type length by two.

$AR_M_MODIFIED_DATATYPE_LENGTH * 2



For the NUMERIC data type, you can optionally set the Precision and Scale. If you leave the

default value ("0"), Replicate will calculate the precision and/or scale based on the source

column value.

l

Subtype: When CLOB, NCLOB, STRING, or WSTRING data types are selected, you can also

set a data subtype. Select either JSON or XML from the Subtype drop-down list. Make sure

that the new data in the column will be compatible with the selected subtype. The default is

Regular, which means that the regular data type will be used without a subtype.

See also:

Using the Expression Builder (page 148)

Rename Change Table

This transformation is only available when the Store Changes replication option is turned

on.

If your transformation type is Rename Change Table, you can do the following:

l

Rename Change Table to (string) (page 138)

l

Add a prefix or suffix (page 138)

l

Remove a prefix or suffix (page 138)

l

Replace a prefix or suffix with different characters (page 139)

l

Convert Change Table name to uppercase (page 139)

l

Convert Change Table name to lowercase (page 139)

l

Rename Change Table (expression) (page 140)



Setup and User Guide - Enterprise Manager, May 2024 137

9 Customizing tasks



Globally renaming a Change Table will override the Change Table

suffix defined in

the task settings



The Change Table name must be different from the source table names.

Otherwise, a table error will occur.

Rename Change Table to (string)

Use the Rename Change Table to: [string] option to change the name of all Change Tables that

you defined in the

Transformation scope (page 121)

step to a different name. For example, if you

have a Change Table called EMPLOYEE and want to change all instances of this name to EMP then enter

the string EMP. You can enter any string in this field.

Add a prefix or suffix

Use the Add a prefix or suffix option to add additional characters to the beginning or end of the

Change Table name for all Change Tables that fit the definition you created in the

Transformation

scope (page 121)

step. For example, if the Change Table name is EMPLOYEES, you can add a suffix,

such as TAR or _TAR to the Change Table name for all Change Tables with that name. In this case, the

resulting Change Table name will be EMPLOYEESTAR or EMPLOYEES_TAR.

To globally add a prefix or suffix:

1. Select Add <Prefix/Suffix> <String> to matching Change Table names.

2. Click the word Prefix or Suffix and select one of these two from the list.

3. Click [string] to activate the field.

4. Type the characters you want as the prefix or suffix. If you want to include an underscore or

other legal character to separate the prefix/suffix from the original name, you must add it as

part of the character string.

5. Click Finish to add the rule to the Global Rules list.

Remove a prefix or suffix

Use the Remove a prefix or suffix option to remove a string of characters from the beginning or

end of a Change Table name for all Change Tables that fit the definition you created in the

Transformation scope (page 121)

step.

For example, you can use this option to remove the letters _REV from the Change Table name for all

Change Tables with the name EMPLOYEES. In this case the Change Table name in the target will be

EMPLOYEES.

To globally remove a prefix or suffix:

1. Select Remove <Prefix/Suffix> <String> from matching Change Table names.

2. Click the word Prefix or Suffix and select one of these two from the list.

3. Click [string] to activate the field.

4. Type the characters you want to remove. If you want to remove an underscore or other legal

character from the original name, you must add it as part of the character string.

5. Click Finish to add the rule to the Global Rules list.

Setup and User Guide - Enterprise Manager, May 2024 138

9 Customizing tasks

Replace a prefix or suffix with different characters

Use the Replace a prefix or suffix option to replace a string of characters with a different string of

characters. You determine whether to replace the characters at the beginning or end of a Change

Table name for all Change Tables that fit the definition you created in the

Transformation scope

(page 121)

step.

For example, you can use this option to replace the letters _ORIG with _REPL in the Change Table

names for all Change Tables called EMPLOYEE_ORIG. In this case the Change Table name in the target

will be EMPLOYEE_REPL.

To globally replace a prefix or suffix:

1. Select Replace <Prefix/Suffix> <String> by <String> for all matching Change Table

names.

2. Click the word Prefix or Suffix and select one of these two from the list.

3. Click the first [string] to activate the field.

4. Type the characters from the existing (source) schema that you want to replace. If you want

to include an underscore or other legal character from the original name in the string that you

want to replace, you must add it as part of the character string.

5. Click the second [sting] to activate the field.

6. Type the characters you want to use in the target. These characters replace the original

(source) characters in the target.

7. Click Finish to add the rule to the Global Rules list.

Convert Change Table name to uppercase

Use the convert to uppercase option to convert a Change Table name to all upper case. For

example:

l

Table_cat, becomes TABLE_CAT

l

Change Table_cat, becomes TABLE_CAT

l

taBLe_Cat, becomes TABLE_CAT

To globally change the Change Table name to all uppercase:

1. Select Convert Change Table name to uppercase.

2. Click Finish to add the rule to the Global Rules list.

Convert Change Table name to lowercase

Use the convert to lowercase option to convert a Change Table name to all lower case. For

example:

l

Table_cat, becomes Change Table_cat

l

TABLE_CAT, becomes Change Table_cat

l

taBLe_Cat, becomes Change Table_cat

Setup and User Guide - Enterprise Manager, May 2024 139

9 Customizing tasks

To globally change the Change Table name to all lowercase:

1. Select Convert Change Table name to lowercase.

2. Click Finish to add the rule to the Global Rules list.

Rename Change Table (expression)

Use the Rename Change Table to [expression] option to change the name of all Change Tables

that fit the definition you created in the

Transformation scope (page 121)

step. For example, if you

have a Change Table called EMPLOYEE and want to change all instances of this name as defined in the

previous step it to EMP.

To change the Change Table name:

1. Select Rename Change Table to: [expression]

2. Click the button to the right of the Rename Change Table option to open the Expression

Editor. For information on how to use the Expression Editor, see

Using the Expression Builder

(page 148)

. Then go to step 4.

Click [expression] to activate the field and continue with step 3.

3. Type an SQLite expression or a string (in quotes) to rename the table. For example:

l

"New_Change_Table_Name"

l

’PREF_’||$AR_M_SOURCE_TABLE_NAME||’_SUFF’

You can use the following metadata in the SQLite expression:

l

$AR_M_SOURCE_COLUMN_DATATYPE

l

$AR_M_SOURCE_COLUMN_NAME

l

$AR_M_SOURCE_SCHEMA

l

$AR_M_SOURCE_TABLE_NAME

Rename Change Table schema

This transformation is only available when the Store Changes replication option is turned

on.

If your transformation type is Rename Change Table schema, you can do the following:

l

Rename Change Table schema to (string) (page 141)

l

Add a prefix or suffix (page 141)

l

Remove a prefix or suffix (page 141)

l

Replace a prefix or suffix with different characters (page 141)

l

Convert Change Table schema name to uppercase (page 142)

l

Convert Change Table schema name to lowercase (page 142)

l

Rename Change Table schema (expression) (page 142)

Setup and User Guide - Enterprise Manager, May 2024 140

9 Customizing tasks

Rename Change Table schema to (string)

Use the Rename Change Table schema to: [string] option to change the name of all Change

Table schemas that you defined in the

Transformation scope (page 121)

step to a different name.

For example, if you have a Change Table schema called EMPLOYEE and want to change all instances

of this name to EMP then enter the string EMP. You can enter any string in this field.

Add a prefix or suffix

Use the Add a prefix or suffix option to add additional characters to the beginning or end of the

Change Table schema name for all tables that fit the definition you created in the

Transformation

scope (page 121)

step. For example, if the Change Table schema name is EMPLOYEES, you can add a

suffix, such as TAR or _TAR to the Change Table schema name for all Change Table schemas with

that Change Table schema name. In this case, the resulting Change Table schema name will be

EMPLOYEESTAR or EMPLOYEES_TAR.

To globally add a prefix or suffix:

1. Select Add <Prefix/Suffix> Insert Characters to matching Change Table schema names.

2. Click the word Prefix or Suffix and select one of these two from the list.

3. Click [string] to activate the field.

4. Type the characters you want as the prefix or suffix. If you want to include an underscore or

other legal character to separate the prefix/suffix from the original name, you must add it as

part of the character string.

5. Click Finish to add the rule to the Global Rules list.

Remove a prefix or suffix

Use the Remove a prefix or suffix option to remove a string of characters from the beginning or

end of a Change Table schema name for all tables that fit the definition you created in the

Transformation scope (page 121)

step.

For example, you can use this option to remove the letters _REV from the Change Table schema

name for all Change Table schemas with the name EMPLOYEES. In this case the Change Table schema

name in the target will be EMPLOYEES.

To globally remove a prefix or suffix:

1. Select Remove <Prefix/Suffix> <String> from matching Change Table schema names.

2. Click the word Prefix or Suffix and select one of these two from the list.

3. Click [string] to activate the field.

4. Type the characters you want to remove. If you want to remove an underscore or other legal

character from the original name, you must add it as part of the character string.

5. Click Finish to add the rule to the Global Rules list.

Replace a prefix or suffix with different characters

Use the Replace a prefix or suffix option to replace a string of characters with a different string of

characters. You determine whether to replace the characters at the beginning or end of a Change

Table schema name for all tables that fit the definition you created in the

Transformation scope

(page 121)

step.

Setup and User Guide - Enterprise Manager, May 2024 141

9 Customizing tasks

For example, you can use this option to replace the letters _ORIG with _REPL in the Change Table

schema names for all Change Table schemas called EMPLOYEE_ORIG. In this case the Change Table

schema name in the target will be EMPLOYEE_REPL.

To globally replace a prefix or suffix:

1. Select Replace <Prefix/Suffix> <String> by <String> for all matching schema names.

2. Click the word Prefix or Suffix and select one of these two from the list.

3. Click the first [string] to activate the field.

4. Type the characters from the existing (source) Change Table schema name that you want to

replace. If you want to include an underscore or other legal character from the original name

in the string that you want to replace, you must add it as part of the character string.

5. Click the second [sting] to activate the field.

6. Type the characters you want to use in the target. These characters replace the original

(source) characters in the target.

7. Click Finish to add the rule to the Global Rules list.

Convert Change Table schema name to uppercase

Use the convert to uppercase option to convert a Change Table schema name to all upper case. For

example:

l

Table_cat, becomes TABLE_CAT

l

table_cat, becomes TABLE_CAT

l

taBLe_Cat, becomes TABLE_CAT

To globally change the Change Table schema name to all uppercase:

1. Select Convert Change Table schema name to uppercase.

2. Click Finish to add the rule to the Global Rules list.

Convert Change Table schema name to lowercase

Use the convert to lowercase option to convert a Change Table schema name to all lower case. For

example:

l

Table_cat, becomes table_cat

l

TABLE_CAT, becomes table_cat

l

taBLe_Cat, becomes table_cat

To globally change the Change Table schema name to all lowercase:

1. Select Convert Change Table schema name to lowercase.

2. Click Finish to add the rule to the Global Rules list.

Rename Change Table schema (expression)

Use the Rename Change Table schema to [expression] option to change the name of all tables

that fall within the scoped you defined in the

Transformation scope (page 121)

step. For example, if

you have a Change Table schema called EMPLOYEE and want to change all instances of the Change

Setup and User Guide - Enterprise Manager, May 2024 142

9 Customizing tasks

Table schema name as defined in the previous step to EMP.

To rename the Change Table schema:

1. Select Rename Change Table schema to.

2. Click the button to the right of the Rename Change Table schema to option to open the

Expression Editor. For information on how to use the Expression Editor, see

Using the

Expression Builder (page 148)

Click [expression] to activate the field and continue with step 3.

3. Type an SQLite expression or a string (in quotes) to rename the table. For example:

l

"New_Change_Table_Schema"

l

’PREF_’||$AR_M_SOURCE_SCHEMA||’_SUFF’

You can use the following metadata in the SQLite expression:

l

$AR_M_SOURCE_COLUMN_DATATYPE

l

$AR_M_SOURCE_COLUMN_NAME

l

$AR_M_SOURCE_SCHEMA

l

$AR_M_SOURCE_TABLE_NAME

4. When you're done, click Next to see a summary of your rule and replace the default name

and description, or Finish to add the rule to the Global Rules list.

Starting the Global Filter Rules wizard

You define global filters using the Global Filter Rules wizard.

To start the Global Filters wizard:

1. Open the task for which you want to create a global transformations or a global filter.

You can click View Task above the Tasks list or double-click the task.

2. If you are not in the Designer mode, click Designer at the top right of the screen.

For more information on the Designer mode, see

Designer mode (page 225)

3. In Designer mode, click Global Rules.

The Global Rules window opens.

4. Click the New Rule toolbar button and select Filter.

The New Filter Rule wizard opens.

5. Enter the information to define a global transformation rule. The first step is selecting the

Filter type (page 143)

Filter type

In the Filter Type screen of the New Filter Rule wizard, you define the type of filtering you want to

be performed.

l

Filter by columns - Only include records that match specific column data. For example, only

include records where Birth Date is later than 02-Feb-2021.

Setup and User Guide - Enterprise Manager, May 2024 143

9 Customizing tasks

Filtering by columns containing changeable values (e.g. Age) may result in

inconsistent data on the target.

l

Filter by record attributes - Only include records that match specific record attributes. For

example, only include UPDATE or INSERTED records.

Filter scope

In the Filter Scope screen of the New Filter Rule wizard, you define which tables will be filtered. For

example, you can limit the filter to all tables that contain the word SALARY as part of their name.

The options displayed in this screen depend on selected

filter type

Scope options when filtering by columns

The following table describes the options that are available when filtering by columns.

Option Description

Schema

name is like %

Leave the % sign to apply the rule to all source schemas (the default).

Alternatively, enter a custom string. The rule will only be applied to columns in

schemas that match the specified string.

For example, enter HR to include only columns that belong to schema HR.

You can use the % symbol as a wildcard. For example, specifying H% will include all

columns in tables that belong to schemas beginning with the letter H, such as HR,

HELLO, or HQ.

The % wildcard can be used in any position. For example, if you use it at the

beginning (%H), then the rule will be applied to all columns in schemas that end

with the letter "H". The % symbol can also be used in the middle of a string.

If you are using an Oracle target, you must enter a schema that exists

on the target endpoint. Qlik Replicate does not create new schemas

on an Oracle endpoint. If you want to use a new schema for the target,

create the schema on the Oracle endpoint before running the task. For

more information, see the topic "Configuring an Oracle database as a

Qlik Replicate Target" in the Qlik Replicate User and Reference Guide.

Columns filtering scope

Setup and User Guide - Enterprise Manager, May 2024 144

9 Customizing tasks

Option Description

Table name

is like %

Leave the % sign to apply the rule to all source tables (the default).

Alternatively, enter a custom string. The rule will only be applied to columns in

tables that match the specified string.

You can use the % symbol as a wildcard. For example, specifying J% will include all

columns in tables with names beginning with the letter J, such as JOBS, JOBS_

HISTORY, or JACKSONVILLE.

The % wildcard can be used in any position. For example, if you use it at the

beginning (%H), then the rule will be applied to all columns in tables that end with

the letter "H". The % symbol can also be used in the middle of a string.

Column

name is like %

Leave the % sign to apply the rule to all source columns (the default).

Alternatively, enter a custom string. The rule will only be applied to columns that

match the specified string.

You can use the % symbol as a wildcard. For example, specifying N% will include all

columns with names beginning with the letter N, such as NAME, NAME_FIRST, or

NAME_LAST.

The % wildcard can be used in any position. For example, if you use it at the

beginning (%n), then the rule will be applied to all columns that end with the letter

"n". The % symbol can also be used in the middle of a string.

Data type is Optionally, select a specific data type from the drop-down list. Make sure the

data type you select is compatible with the data in that column.

For a description of Qlik Replicate data types, information about data type

mapping from the native endpoint to Qlik Replicate, or for a list of endpoints

supported by Qlik Replicate, see the Qlik Replicate User and Reference Guide.

Scope

expression

Click Advanced options to define a scope expression using the expression

builder.

Scope options when filtering by record attributes

The following table describes the options that are available when filtering by record attributes.

Setup and User Guide - Enterprise Manager, May 2024 145