Replicate reported errors during resume task if source MySQL running on Windows (while MySQL running on Linux then no problem)

[SOURCE_CAPTURE ]I: Stream positioning at context '$.000034:3506:-1:3506:0'
[SOURCE_CAPTURE ]T: Read next binary log event failed; mariadb_rpl_fetch error 1236 (Could not find first log file name in binary log index file)

Replicate reported errors at MySQL source endpoints sometimes (does not matter what's the MySQL source platforms):

[SOURCE_CAPTURE ]W: The given Source Change Position points inside a transaction. Replicate will ignore this transaction and will capture events from the next BEGIN or DDL events.

Environment:

• Replicate 2022.5   PR4 (2022.5.0.815)
• Replicate 2022.11 PR1 (2022.11.0.289)
• MySQL source database , any version

Fix Version & Resolution:

Upgrade to Replicate 2022.11 PR2 (2022.11.0.394, released already) or higher, or Replicate 2022.5 PR5 (coming soon)

Workaround:

If you are running 2022.5 PR3 (or lower), then keep run it, or upgrade to PR5 (or higher) .

No workaround for 2022.11 (GA, or PR01) .

Cause & Internal Investigation ID(s):

Jira: RECOB-6526 , Description: It would not be possible to resume a task if MySQL Server was on Windows

Jira: RECOB-6499 , Description: Resuming a task from a CTI event, would sometimes result in missing events or/and a redundant warning message

Related Content:

support case #00066196

support case #00063985 (#00049357)

Qlik Replicate 

While working with PostgreSQL ODBC DSN as source endpoint, The ODBC Driver is interpreting JSONB datatype as VARCHAR(255) by default, it leads the JSONB column values truncated no matter how the LOB size or data type length in target table were defined.
In general the task report warning as:

2022-12-22T21:28:49:491989 [SOURCE_UNLOAD ]W: Truncation of a column occurred while fetching a value from array (for more details please use verbose logs)

Resolution

There are several options to solve the problem (any single one is good enough😞

I) Change PostgreSQL ODBC source endpoint connection string

1. Open PostgreSQL ODBC source endpoint
2. Go to the Advanced tab
3. Open Internal Parameters
4.   Add a new parameter named additionalConnectionProperties
   
5. Press <Enter> and set the parameter's value to:
   
   MaxVarCharSize=0
   

    

   
   

II) Or on Windows/Linux Replicate Server, add one line to "odbc.ini" in the DSN definition:

    MaxVarCharSize=0

III) Or on Windows, set "Max Varchar" to 0 from default value 255 in ODBC Manager GUI (64-bit):

Environment

Qlik Replicate  all versions
PostgreSQL         all versions

Internal Investigation ID(s):

Support cases, #00062911

Ideation article, Support JSONB 

Qlik Replicate  

• Introduction
• System Requirements
• Pricing and Packaging
• Software updates
• Types of Models Supported
• Getting Started with AutoML
• Data Connections
• Data Preparation abilities
• Using realtime-prediction API
• Integration with Qlik Sense
• Contacting Support
• Additional resources
• Environment

This is a guide to get you started working with Qlik AutoML.

Introduction

AutoML is an automated machine learning tool in a code free environment. Users can quickly generate models for classification and regression problems with business data.

System Requirements

Qlik AutoML is available to customers with the following subscription products:

Qlik Sense Enterprise SaaS

Qlik Sense Enterprise SaaS Add-On to Client-Managed

Qlik Sense Enterprise SaaS - Government (US) and Qlik Sense Business does not support Qlik AutoML

Pricing and Packaging

For subscription tier information, please reach out to your sales or account team to exact information on pricing. The metered pricing depends on how many models you would like to deploy, dataset size, API rate, number of concurrent task, and advanced features.

Software updates

Qlik AutoML is a part of the Qlik Cloud SaaS ecosystem. Code changes for the software including upgrades, enhancements and bug fixes are handled internally and reflected in the service automatically.

Types of Models Supported

AutoML supports Classification and Regression problems. 

Binary Classification: used for models with a Target of only two unique values. Example payment default, customer churn.

Customer Churn.csv (see downloads at top of the article)

another churn example 

Multiclass Classification:  used for models with a Target of more than two unique values. Example grading gold, platinum/silver, milk grade. 

MilkGrade.csv (see downloads at top of the article)

Regression: used for models with a Target that is a number. Example how much will a customer purchase, predicting housing prices 

AmesHousing.csv (see downloads at top of the article)

Getting Started with AutoML

What is AutoML (14 min)

Exploratory Data Analysis (11 min)

Model Scoring Basics (14 min)

Prediction Influencers (10 min)

Qlik AutoML Complete Walk Through with Qlik Sense (24 min)

Community Article for uploading data, training, deploying and predicting a model

Data Connections

Data for modeling can be uploaded from local source or via data connections available in Qlik Cloud. 

You can add a dataset or data connection with the 'Add new' green button in Qlik Cloud.

There are a variety of data source connections available in Qlik Cloud.   

Once data is loaded and available in Qlik Catalog then it can be selected to create ML experiments.

Data Preparation abilities

AutoML uses variety of data science pre-processing techniques such as Null Handling, Cardinality, Encoding, Feature Scaling. Additional reference here.

Using realtime-prediction API

Please reference these articles to get started using the realtime-prediction API

Example with Postman

Example with Python 

Integration with Qlik Sense

By leveraging Qlik Cloud, predicted results can be surfaced in Qlik Sense to visualize and draw additional conclusions from the data.

How to join predicted output with original dataset

How to analyze SHAP values 

Contacting Support 

If you need additional help please reach out to the Support group. 

It is helpful if you have tenant id and subscription info which can be found with these steps. 

Additional resources 

Please check out our articles in the AutoML Knowledge Base.

Or post questions and comments to our AutoML Forum. 

Environment

• Qlik AutoML 

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

Introduction:

Replicate environment was a redhat linux 7 installation which saw the following error on the browser page:

This site can’t be reached ec2-xxx.xxx.xxxxx unexpectedly closed the connection.
Try:
Checking the connection
Checking the proxy and the firewall
ERR_CONNECTION_CLOSED

From the repsrv.log, it showed:

[REST_SERVER ]W: Number of connections exceed the maximum number of concurrent http handlers, connection request handling is paused (number of busy handlers = 1000, maximum handlers = 1000)

Resolution

Please follow these steps carefully to increase the number of connections you can have:

1) Stop Qlik Replicate service

/opt/attunity/replicate/bin/areplicate stop

2) Navigate to your Replicate bin directory
3) Open and Edit repctl.cfg
4) Add a comma to the end of the last parameter

"enable_passthrough_filter": false,

5) On the next line add "max_concurrent_handlers": 1500

"enable_passthrough_filter": false,
"max_concurrent_handlers": 1500

6) Save the file
7 ) Restart the Replicate service

/opt/attunity/replicate/bin/areplicate start

Then try accessing the browser page.

Environment

• Qlik Replicate

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

Users can view all licenses by navigating between the Active and Expired tab.

1. Login to the legacy Support Portal by following this link or
   
   
   1. Login to Community (https://community.qlik.com) and click Support in the navigational ribbon
   2. Then click Licenses & Archived Cases 
   3. And choose View Licenses and Archived Cases to be redirected
      
      
      
      
2. On the Legacy Portal, click on View License Information or Archived Cases at the top of the page and select View My Licenses.
   
   
   
   
3. You will be redirected to the License List page or Legacy Cases list.
   
   Select Active (1) to view Active License.
   Select Expired (2) to view Expired Maintenance Licenses
   
   
   
   
   

1. If you would like to see a particular license, you can filter the list with the license key.
   
   

When working with Qlik Replicate, log.key file(s) are used for Decrypt Qlik Replicate Verbose Task Log Files , the log.key file can be re-created by restarting tasks, or restarting Replicate services if the log.key file is missed/deleted. However sometimes we need the file creation prior to the first time task run eg

(1) Set proper file protection manually by DBA

(2) Task movement among different environment eg UAT and PROD

(3) In rare cases the file auto-creation failed due to some reasons

This article provide some methods to generate file "log.key" manually.

Environment

• Qlik Replicate      Version 2021.11 and later

Resolution

There are several methods to get a "log.key" file manually.

1.  Copy an existing "log.key" file from UAT/TEST task folder;

      It's better to make sure the "log.key" uniqueness, so below method (2) is recommended:

2.  Run "openssl" command on Linux or Windows

openssl rand -base64 32 >> log.key

      The command will return a 44-chars random unique string (the latest char is "=") in "log.key" file. For example
      n1NJ7r2Ec+1zI7/USFY2H1j/loeSavQ/iUJPaiOAY9Y=

Related Content

Support cases, #00059433

Qlik Replicate  

Introduction

"This prediction failed to run successfully due to schema errors"  can occur during the prediction phase if there is a mismatch in the table schema between training and prediction datasets.

In this example, we will show how a column in the prediction dataset was profiled as a categorical rather than numeric because it contained dashes '-' for empty values.

Steps

1. Upload test_dash_training.csv and test_dash_prediction.csv to Qlik Cloud. See attachments on the article if you would like to download.

Training dataset:

Prediction sample:

2. Create a new ML experiment and choose test_dash_training.csv, and click 'Run Experiment'

3. Deploy the top model

5. Create a new prediction and select test_dash_prediction.csv as the apply dataset

You will encounter a warning message, "Feature type does not match the required model schema."

If you continue, you will encounter another message after clicking 'Save and predict now'.

6. If you click 'Save configuration', the prediction will attempt to run but will not produce prediction dataset and will display an error.

Resolution

Clean up dashes from 'chats' column in test_dash_prediction.csv. Either remove them from the prediction dataset or transform to a numeric value such as zero.

Environment

• Qlik AutoML 

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

Introduction

I needed to test a ML experiment recently with a QVD instead of a csv file.

Here are the steps I followed below to create a QVD file which was then available in Catalog.

Steps

1. Upload the local csv dataset (or xlsx,etc) and analyze which will create an analytics app

2. Open up the app and navigate to "Data Load Editor"

3. Add a new section under the Auto-generated section (with the + symbol marked with a red arrow above. Note this section must run after the Auto-generated section or will error the data is not loaded.

Add the following statement:

Store train into [lib://DataFiles/train.qvd];

or

Store tablename into [lib://DataFiles/tablename.qvd];

4. Run "Load data"

5. Check Catalog for recently created QVD

Environment

• Qlik AutoML 

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

Introduction

During the 'Create' phase of an AutoML experiment, there is a section at the right hand pane called 'Data Treatment'.

This section tracks any Feature Type changes you make to your training dataset.

Example

I started the process of creating a ML experiment for the Ames housing dataset.

Then I changed 'Wood Deck SF' and 'Open porch SF' to Categorical instead of Numeric type under the 'Feature Type' column.

The result is these changes are now tracked in the Data Treatment section.

Environment

• Qlik AutoML 

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

When working with Qlik Replicate, there is a limitation applies to LOB columns:

• When replicating a table that has no Primary Key or Unique Index, LOB columns will not be replicated 

This is because Replicate uses SOURCE_LOOKUP function to retrieve the LOB columns thru PK/UI position to the row(s) in source DB table. Hence the PK/UI is mandatory. 

Another limitation about ROWID data type:

• As the ROWID data type is not supported, materialized views based on a ROWID column are also not supported 

This article describes how to overcome the above limitations and setup Replicate task to replicate LOB columns if the source table has no Primary Key or Unique Index. The workaround works for Oracle source only as the Oracle materialized view is introduced, and the Oracle internal hidden ROWID Pseudocolumn will be used as PK/UI of the materialized view.

Basically the idea is

(1) Expose hidden column ROWID as a regular column in materialized view (in step 3.)

(2) Change the data type ROWID to CHAR (explicitly, or implicitly) (in step 3.)

(3) Define the ROWID column as the PK/UI of the materialized view (in step 4.)

Then the materialized view can be replicated just as same as a regular Oracle table.

Environment

• Qlik Replicate      All supported versions
• Oracle database All supported versions

Resolution

1.  Assume there is a table in Oracle source database which has NO Primary Key nor Unique Index. Column "NOTES" is a CLOB column.

   CREATE TABLE kitclobnopk (
   id     integer,
   name   varchar(20),
   notes  clob
   );

2.  Create materialized view log for the above table

   create materialized view log on kitclobnopk WITH ROWID including new values;

3.  Create materialized view which exposes ROWID hidden column as regular column (alias KITROWID) to Replicate.

   CREATE MATERIALIZED VIEW KITCLOBNOPK_MV                                       
   REFRESH FAST ON COMMIT                                                        
   WITH ROWID                                                                    
   AS                                                                            
   select ROWIDTOCHAR(t.rowid) kitrowid, t.id, t.name,t.notes from kitclobnopk t;

      The materialized view data refreshes whenever the base master table changes (I/U/D) being committed. The MV can be custom to meet other requirements and add other rich logics etc.

4. Define the column ROWID as the Primary Key of the materialized view

   ALTER MATERIALIZED VIEW KITCLOBNOPK_MV ADD CONSTRAINT KITCLOBNOPK_MV_PK PRIMARY KEY (kitrowid);

5.  Add the materialized view kitclobnopk_mv to task as same as it's a regular table, the MV meets both Full Load and CDC demand. (NO need to include the original source master table "kitclobnopk" in Qlik Replicate task). 

6. Limitations and considerations of the WA

      (1) The single UPDATE operation in master table maybe translates to DELETE+INSERT pair operations in materialized view, depends on how the materialized view data refreshes. We may see that in Replicate GUI monitor perspective. 

      (2) If the ROWID changed in the materialized view (eg ALTER TABLE <tableName> SHRINK SPACE special operations ), then table or task reload requires. Just like the RRN Column in DB400 .

      (3) This is only a sample which works in internal labs (sanity test based on Oracle 12c source + Replicate 2022.5). No huge data stress test done, or being verified in a PROD system yet. For implementation and further questions, Professional Service engaged. 

Qlik Replicate  

After successfully registering for a Qlik Account, no Activation email is received to activate the Qlik Account.

OR:

No Password Reset email is received when attempting to reset the password.

Troubleshooting steps: 

1. Check your Spam/Junk folder for the Activation or Password email.
2. Ensure Qlik emails are not blocked by your Mail-Server Firewall
3. Ensure that "qlik.com" and "sendgrid.net" are added to your safe senders list
4. Then trigger a new Activation/Password Reset email.

If the Activation or Password Reset email still does not arrive at your email inbox, contact us either by chat or through the Support Portal.  


Related articles:
How to Register for a Qlik Account 


*sendgrid.net = the third party app used to send activation and password reset email

Replicate Oracle source endpoint got errors:

2022-08-03T10:48:53 [SOURCE_UNLOAD ]W: ALL COLUMN supplemental logging is required for table '<schemaName>.<tableName>' (oracle_endpoint_utils.c:600)

2022-08-03T10:48:53 [SOURCE_UNLOAD ]E: Supplemental logging for table '<schemaName>.<tableName>' is not enabled properly [1022310] (oracle_endpoint_unload.c:190).

Cause 

When task Apply Conflicts set to use UPSERT, it requires supplemental logging for all columns.

User Guide description: Step 4: When the Insert the missing target record Apply Conflicts option is selected, supplemental logging must be enabled for ALL the source table columns.

Resolution

1- Turn off UPSERT, use other options rather than "INSERT the missing target record"

      If UPSERT is necessary then:

2- Add supplemental logging for all columns:

ALTER TABLE <schema>.<table name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Environment

• Oracle source endpoint for Qlik Replicate  
• Qlik Replicate all supported versions

Related Content 

1. Internal support case ID: # 00047346.

2. Michael_Litz Oracle ALL Column Supplemental Logging 

3. Michael_Litz Qlik Replicate: Implementing UPSERT and MERGE modes by applying a Conflicts Handling Policy 

Qlik Replicate  

In Replicate Oracle source endpoint there was a limitation:

Object names exceeding 30 characters are not supported. Consequently, tables with names exceeding 30 characters or tables containing column names exceeding 30 characters will not be replicated.

Cause 

The limitation comes from low versions Oracle behavior. However since Oracle v12.2, Oracle can support object name up to 128 bytes, long object  name is common usage at present. The limitation in User Guide Object names exceeding 30 characters are not supported  can be overcome now.

There are two major types of long identifier name in Oracle, 1- long table name, and 2- long column name.

Resolution

1- Error messages of long table name

[METADATA_MANAGE ]W: Table 'SCOTT.VERYVERYVERYLONGLONGLONGTABLETABLETABLENAMENAMENAME' cannot be captured because the name contains 51 bytes (more than 30 bytes)

Add an internal parameter skipValidationLongNames to the Oracle source endpoint and set its value to true (default is false) then re-run the task:

2- Error messages of long column name

      There are different messages if the column name exceeds 30 characters

[METADATA_MANAGE ]W: Table 'SCOTT.TEST1' cannot be captured because it contains column with too long name (more than 30 bytes)

Or

[SOURCE_CAPTURE ]E: Key segment 'CASE_LINEITEM_SEQ_NO' value of the table 'SCOTT.MY_IMPORT_ORDERS_APPLY_LINEITEM32' was not found in the bookmark

Or (incomplete WHERE clause)

[TARGET_APPLY ]E: Failed to build update statement, statement 'UPDATE "SCOTT"."MY_IMPORT_ORDERS_APPLY_LINEITEM32"
SET "COMMENTS"='This is final status' WHERE ', stream position '0000008e.64121e70.00000001.0000.02.0000:1529.17048.16']

There are 2 steps to solve above errors for long column name :

(1) Add internal parameter skipValidationLongNames (see above) in endpoint

(2) It also requires a parameter called "enable_goldengate_replication" is enabled in Oracle. This can only be done by end user and their DBA:

alter system set ENABLE_GOLDENGATE_REPLICATION=true;

    Take notes this is supported when the user has GoldenGate license. Consult with the user DBA before alter the system settings.

Environment

• Oracle (version 12.2 and up) source endpoint for Qlik Replicate  
• Qlik Replicate versions 2021.5/2021.11/2022.5 and up

Related Content 

Internal support case ID: # 00045265.

Qlik Replicate  

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

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

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

Environment

• Qlik Replicate All supported versions

Resolution

1.  "Run as administrator" to execute "Qlik Replicate Command Line"

2.  Change to "C:\Program Files\Attunity\Replicate\bin" (default location)

3. Execute command

repctl.exe getprovidersyntax syntax_name=MySQL > MySQL_MyISAM.json

    If the DATA folder is non-default location, add option -d data_directory  in the command. Same in step (6.) below.

4. Edit the file "MySQL_MyISAM.json"

    4.1 - Remove the first line and the latest line include

command getprovidersyntax response:

[getprovidersyntax command] Succeeded

    4.2 - Modify the top few lines

              From

{
	"provider_syntax":	{
		"name":	"MySQL",
		"query_syntax":	{

             To (remove the remark of "<----- ...")

{
	"name": "MySQL_MyISAM",                   <---- add this line
		"repository.provider_syntax": {       <---- add this line
		"name": "MySQL_MyISAM",
		"query_syntax": {

     4.3 - Add ENGINE type ENGINE=MyISAM

              From

"create_temporary_table":	"CREATE TEMPORARY TABLE ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC} ( ${COLUMN_LIST} ) CHARSET=utf8mb4",

              To

"create_temporary_table":	"CREATE TEMPORARY TABLE ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC} ( ${COLUMN_LIST} ) ENGINE=MyISAM CHARSET=utf8mb4",

5. Save changes and move the file to "C:\Program Files\Attunity\Replicate\data\imports", or your DATA folder

6. Execute command

repctl putobject data=MySQL_MyISAM

    Note  do not add the additional suffix ".json" in the end of the command

7. In the target endpoint add an internal parameter "syntax" and input the value "MySQL_MyISAM"

8. Resume or Reload the task.

Related Content

Backup Replicate DATA directory before you operate the repository.

Another sample Replicate - How to create PRIMARY INDEX rather than PRIMARY KEY in Teradata Target DB 

Qlik Replicate 

Replicate Error for SAP Hana source endpoint:

[SOURCE_UNLOAD ]E: RetCode: SQL_ERROR SqlState: S1000 NativeError: 129 Message: [SAP AG][LIBODBCHDB DLL][HDBODBC]General error;129 transaction rolled back by an internal error: Search result size limit exceeded: 2254282483 [1022502] (ar_odbc_stmt.c:2789)

Cause 

There is a limitation in SAP HANA , on the number of the records. In very specific scenarios limits for (intermediate) result sets exist, e.g. 2147483648 (2 billion).

If the source table total rows number exceeds the 2 billion limitation , Replicate reports above error (in this sample, the user table total row number is 2254282483).

Resolution

Because the table is too big to do Full Load by a single retrieve query from source SAP Hana, we need to split the single source table read return set to multiple return sets. There are several options:

1. In a single task,  Parallel Load helps if both source and target endpoints are in supported list; however if target is not in parallel load support list, eg for Kafka target, we need to break down the table into multiple tasks by using filter, and "passthru filter" should be used (passthru filter take actions on Full Load only, CDC will not be affected). see below options 2 and 3.

2. If part of the data (aka history data rows) will not being updated during the load
     Let's say the table will be divided into 2 tasks. Each task loads part of records.

    (1). Create following tasks
           TASK1: Full Load Only with passthru filter on PK which are history data rows, or a history rows partition
           TASK2: Full Load + CDC with passthru filter on PK which maybe being updated

    (2). Start TASK1 first, after it's done, and then start TASK2

3. Sometimes it's impossible to know if a row will be updated or not, or any row maybe being updated, then option 2 does not work. We may use this method.
     Let's say the table will be divided into 3 tasks. Each task loads 1M records.

    (1). Create following tasks
            TASK1: Full Load Only with passthru filter on PK   (1...1,000,000)
            TASK2: Full Load Only with passthru filter on PK   (1,000,001 ... 2,000,000)
            TASK3: Full Load + CDC with passthru filter on PK (2,000,001 ... 3,000,000)

    (2). In the TASK3, change the Full Load settings as below:
           Enable the "Before cached changes have been applied" option box

    (3). Start TASK3 first, and then start other tasks.

    (4). Once Full Load processes are completed for all tasks, resume TASK3 for CDC.

Environment

• SAP Hana as source endpoint for Qlik Replicate  
• All supported Replicate versions

Related Content 

Internal support case ID: # 00043314.

Qlik Replicate 

SAP Column MANDT will not show up as a valid Column in Replicate when doing a Parallel Load for SAP Tables with this column.

Environment

• Qlik Replicate 2021.5
• Qlik Replicate 2021.11
• Qlik Replicate 2022.5

The MANDT Column is the Client ID that is defined on the SAP Application DB Source Endpoint as you can have only one client setup for the SAP Application DB Source endpoint. This column will not be shown when trying to do a Parallel Load within Replicate for SAP Table that contain this column. 

The SAP Application DB Source Endpoint connection information (client) Parameter:

When you go into Replicate and the Parallel Load for Table with this Column it will not show as a column to add the load options (segments) due to this Client number and single client per connection.

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

In Qlik Replicate 2021.5 (SP04), 2021.11 and 2022.5 the Source Endpoint SAP Application (DB) changed how the Metadata for character based columns is processed during Load and CDC processing of SAP Tables. 

Environment

• Replicate 2021.5 SP04
• Replicate 2021.11
• Replicate 2022.5

Cause

Prior to the versions listed above Replicate used data length semantics to process character based columns. Upgrading to any of the listed versions automatically changes this processing of these columns to character length semantics. This can be seen on the Target for Tables that have been reloaded. For example the column MANDT is defined as a VARCHAR(9) on Targets with data length semantics. The same column would appear as a VARCHAR(3) with character length semantics. 

Resolution 

With Replicate 2021.11 SP06 and 2022.5 SP01 data length semantics has been restored as the default for character based columns when using the SAP Application (DB) Endpoint. If your Tables are reloaded with either of these versions they will automatically be defined with data length semantics. In addition there is a new Internal Parameter you can set on the SAP Application (DB) Source Endpoint to continue with character length semantics.

Internal Parameter: useLengthUnitsInSAPDB

The Source Table defined in the Replicate Task has space in the Table Name but the Target Endpoint does not allow space in the Table Name. How can you Rename the Target Table using a Transformation within Replicate.

Environment

• Replicate 2021.5
• Replicate 2021.11
• Replicate 2022.5

To rename a Table in the Replicate Task you will need to create a Global Rule using the Transformation option and Rename Table. Steps below will show the steps to create the Expression for the Rename of the Table to the Target.

Open the Task in Designer Mode and click on Global Rule and then the Transformation

Then you make sure the Rename Table radio button is selected

Click Next and Next until you get to the Rename Table and Expression

Then from here click on the fx (expression)

The expression build would show

You can test the Expression as well before saving the Global Rule

More information on Expressions within Replicate can be found:

https://help.qlik.com/en-US/replicate/November2021/Content/Global_Common/Content/SharedEMReplicate/Customize%20Tasks/task_expression_builder.htm

Currency data from SAP appears to replicate incorrectly when source values are compared to the target system. In this scenario, Japanese currency data values shown through the SAP business view for documents derived from the VBAP table were determined to be incorrect when compared to the target (Snowflake) for validation.   These values were most typically seen through the SAP document view of the invoice or order where the currency values were already converted to the correct locale and format.  

Environment

• Replicate 2021.11
• Replicate 2022.5

SAP Business View of currency value (source):   

The currency value is shown below is in local currency as 378.000.

Snowflake View of the same currency value (target):

The Solution – Validation at the Data Level:

In the SAP GUI, transaction SE16 (Data Browser) can be used to view rows and columns of table data at or near the “data level”.   This approach works for transparent, clustered, and pooled SAP tables.

SAP SE16 Data Browser View:   As seen below, while there is still some numeric formatting applied to the value in NETWR, it can be compared correctly with the Snowflake target.  

Additional Validation:

Further investigation was able to confirm that the values in Snowflake were correct.   This was determined by cross checking the values in the physical database.   Note that this approach is only practical when the SAP table in question is transparent.

DB2 database view:   

Note that this level of investigation was possible because VBAP is a transparent table:

Conclusion:

Replication from a source to a target is focused on the accuracy of data movement at the “data level”.   Validation at the data level is the safest way to compare the source to the target, must be used when equivalent business logic is not available on the target.   If business logic is required to correctly format and validate the data, the customer can choose to build some of the logic into the Replication task via Transformation Expressions.  Alternatively a separate project outside of Replication may be needed to create or “port” business logic from the source to the target. 

Environment

• Qlik Compose - 2021.8.139 
• Snowflake database

Issue

In the 2021 May release, VALIDATE of the  Snowflake Data Warehouse was extremely fast with the metadata caching. But after upgrading to 2021 August the validation of the Data Warehouse is extremely slow and slowness occurs on subsequent validate clicks also (ie. it's not just the first one when metadata may be cached).

Information provided on this defect is given as is at the time of documenting. For up-to-date information, please review the most recent Release Notes, or contact support with iD XX-11111 for reference.

Fix Version

Fixed in 2021.8-sp01 (2021.8.197) and higher.

Cause

Jira issue: RECOB-3799