A Qlik Replicate task fails in writing to ADLS in parquet format from source DB2 z/os. The error in the task log is:

[TARGET_LOAD     ]E:  Failed to convert file from csv to parquet
Error:: failed to read csv temp file

Resolution

The ODBCINST.ini may not be directed to the accurate DB2 64-bit ODBC driver. Resolving the issue may require a manual edit of the ODBCINST.ini file.

Original value: 

./opt/ejvdb2odbc/lib/libdb2.so 

Corrected to:

./opt/ejvdb2odbc/lib/libdb2o.so 

After the changes have been made and saved, reload the task. 

After an upgrade of Qlik Replicate to May 2023 (2023.5.0.322), Databricks tasks fail with the following error:

[METADATA_MANAGE ]E: The host configured in the proxy settings is not valid [1020401] (cloud_imp.c:2138)

[METADATA_MANAGE ]E: Failed while preparing stream component 'Databricks_AdageAZ'. [1020401] (metadatamanager.c:855)

[METADATA_MANAGE ]E: Cannot create the target utility component [1020401] (metadatamanager.c:760)

[TASK_MANAGER ]E: Creating Metadata Manager's utility components failed [1020401] (replicationtask.c:3896)

[TASK_MANAGER ]W: Task 'Adage_AZ_Apply_Changes_All' encountered a fatal error (repository.c:6064)

Resolution

An upgrade of Qlik Replicate may set the proxy settings in the Databricks Lakehouse (Delta), even if no proxy has previously been used. Disable the proxy to resolve the issue.

1. Open the Databricks Lakehouse (Delta) target endpoint
2. Open the Advanced tab
3. Expand Proxy Sever
4. Uncheck Use Proxy Server 
   
   

The Qlik Sense PostgreSQL database was previously updated and unbundled using the Qlik PostgreSQL Installer (QPI).

Following an upgrade of Qlik Sense Enterprise on Windows, the Qlik Repository Service may not start or the Management Console open to the following error:

Failed to load properties. No data found.

Environment

Qlik Sense on Windows May 2023 (upgraded to)
Previous use of Qlik PostgreSQL Installer (QPI) while Qlik Sense had a patch installed

Resolution:

Repair the installation. See Repairing an installation.

Cause 

Previously to using QPI, all patches need to be removed. See Requirements for QPI.

Related Content 

Upgrading and unbundling the Qlik Sense Repository Database using the Qlik PostgreSQL Installer 

Internal Investigation ID(s) 

QB-20581

In the data integration process from a Salesforce source to a Snowflake target, it's crucial to ensure all records are accurately captured. However, sometimes gaps can occur in Change Data Capture (CDC) operations, resulting in missing records.

In this article, we aim to explore such an issue and guide you through the required troubleshooting steps and eventual solution.

Issue

One of the source table in Salesforce is 'WorkOrder,' and discrepancies in data capture were observed in the logs. The root of the problem becomes evident when examining consecutive SELECT queries:

Query 1:

Timestamp: 2023-07-17 13:41:04.251

Query: SELECT count() FROM WorkOrder WHERE SystemModstamp > 2023-07-17T17:24:28.000Z and SystemModstamp < 2023-07-17T17:40:04Z

Query 2:

Timestamp: 2023-07-17 13:56:31.411

Query: SELECT count() FROM WorkOrder WHERE SystemModstamp > 2023-07-17T17:41:04.000Z and SystemModstamp < 2023-07-17T17:55:31Z

Upon closer examination, it's evident that the tracking column 'SystemModstamp' in Query 2, which is "> 2023-07-17T17:41:04.000Z," does not align with the previous query's 'SystemModstamp' value, which was "< 2023-07-17T17:40:04Z."

Consequently, any changes within this time period (SystemModstamp between '>= 2023-07-17T17:40:04Z' and '<= 2023-07-17T17:41:04.000Z') were not captured, leading to missing records.

Resolution

After a thorough analysis, the root cause of this issue was identified. The transaction consistency timeout of 60 seconds was found to be inadequate. To address this problem effectively, the following steps are recommended:

• Analyze Longest Transactions: Salesforce transactions vary in duration. To ensure comprehensive data capture, it's crucial to identify the longest transactions that may exceed the default consistency timeout.
  
  
• Set Transaction Consistency Timeout: Adjust the "Transaction consistency timeout" value in your CDC configuration to accommodate the longest transactions. This adjustment ensures that CDC operations span the entire duration of transactions, preventing data gaps and missing records.
  
  To set the Transaction Consistency Timeout:
  
  
  1. Open the Salesforce Source Endpoint
  2. Navigate to the Advanced tab 
  3. Locate Transaction consistency timeout (sec): and set the value accordingly
     
     

By following these steps, you can enhance the accuracy and completeness of your data capture process when loading data from a Salesforce source to a Snowflake target. This proactive approach will help prevent future instances of missing records and maintain the integrity of your data integration pipeline.

The Qlik Compose Monitor UI does not show Inserted Rows or Updated Rows for the data mart even though the rows are inserted and updated in the database.

Resolution

Upgrade to 2022.5.1032 (2022, May – sp13).

 Cause

RECOB-7493

Information provided on this defect is given as is at the time of documentation. For up-to-date information, please review the most recent Release Notes with RECOB-7493 for reference.

Environment

    Qlik Compose 2021.05 - sp11 (2022.5.0.958)

Previewing Qlik NPrinting report templates in the Qlik NPrinting Designer leads to the error:

An error occurred while elaborating the entity tags.
Please check if the entities are properly placed inside their level tags.
Error message:
TemplateEntities list does not contain this column
Parameter name: templateEntities

Environment

Qlik NPrinting Designer May 2023 IR, May 2023 SR 1

Resolution

Upgrade to Qlik NPrinting May 2023 SR 2.

Information on this defect is given as is at the time of documenting. For up-to-date information, please review the most recent Release Notes with the ID OP-95892 for reference.

Workaround:

Replace the current Qlik.Reporting.Adapters.dll file in C:\Program Files (x86)\NPrinting\Designer\libs.

For this workaround to function, the installed Qlik NPrinting designer version must be May 2023 SR1.

1. Take a backup of the current Qlik.Reporting.Adapters.dll file
2. Download the attached Qlik.Reporting.Adapters.zip file
3. Extract the .dll.
4. Replace the Qlik.Reporting.Adapters.dll file

Upgrade to May 2023 SR2 when released.

Fix Version:

Qlik NPrinting May 2023 SR 2

Cause

OP-95892

Creating and adding a new target endpoint to Azure SQL DB and testing the connection fails with:

SYS-E-HTTPFAIL, Failed to load dynamic endpoint properties.
SYS,GENERAL_EXCEPTION,Failed to load dynamic endpoint properties,Failed to get 'SQLServer_New' syntax

Resolution

Review the active Internal Parameters set in the endpoint. 

1. Go to the  Endpoint connection
2. Switch to the Advanced tab
3. Click Internal Parameters
4. Review the set parameters for a syntax parameter set to a specific value (in our example: SQLServer_New).
5. Remove the Internal Parameter.
   
   

Environment

Qlik Replicate 

Due to an issue with MySQL ODBC Driver 8.0.027 to 8.0.033, empty TEXT columns may not be replicated correctly during Full Load.

Running the full load on a table, it appears the data alignment is off because the target .csv file has null values when the source field is defined as not nullable. 

Environment

Qlik Replicate  
Aurora Postgres

Identifying the root cause:

Verify the source table DDL:

Examining the source table DDL will show a datatype with no precision (varchar NOT NULL,). 

Example:

Resolution

This is a documented limitation. See VARCHAR(n).

VARCHAR without a length (n) is not recognized as a valid data type by target endpoints. Consequently, if a source column data type is set to VARCHAR without an explicit length, Replicate will set a default length of 8000 bytes.

Workaround:

You can change the default by deploying an internal parameter, unboundedVarcharMaxSize, and setting a custom value. 

1. Go to the  Endpoint connection
2. Switch to the Advanced tab
3. Click Internal Parameters
4. Add unboundedVarcharMaxSize
   
   
   
   For more information about Internal Parameters, see Qlik Replicate: How to set Internal Parameters and what are they for?

Task requirements

This task needs to capture soft deletes and allow the source table to reuse primary key values without a PK violation on the target.

Summary

Setting up this task requires four steps:

1. Add a table to the task and two fields to the target primary key.
2. Add transformations to the two new fields.
3. Configure the task to use upsert mode.
4. Show results of updates and deletes on the target table

Adding the two extra fields to the target side primary key and having the NOW timestamp ensures that the target record will be unique compared to the source record with only one field primary key.

This will allow the source to reuse a deleted records primary key without throwing a PK violation on the target. The transformations on the fields set unique values into the additional key segments.

Setting error handling and the apply conflicts handling policy to Upsert Mode (No record found for applying an Update: INSERT the messing target record) normally works by converting the source statement (whether Insert, Update, Delete) into a Delete followed by and Update. The unique three segment primary key is not able to be found during the delete which is why the records persist on the target.

The Steps

Add a table to the task and two additional fields to the target primary key

Example source table structure:

Note the single field Primary Key MyPK.

In our example, we have added Operation and OperationDateTime as the additional fields.

Example source table records:

In the Table Settings for your task, go to Transform and mark your newly added fields as target side primary keys.

In our example, we have marked Operation and OperationDateTime.

Add transformations to the two new fields

Transformation are added through the Expression Builder, located in the Table Settings. You will modify the fields marked as keys in the previous step, Operation and OperationsDateTime.

Operation: set the expression to INSERT, UPDATE or DELETE based on the source transaction. 

Transformation function:

CASE
WHEN $AR_H_STREAM_POSITION = ''
THEN "INSERTED"
ELSE operation_indicator("DELETED", "UPDATED", "INSERTED" )
END

OperationDateTime: set the expression to to NOW date time

Transformation function:

Datetime('now')

Configure the task to use Upsert mode

1. Go to Task Settings...
2. Open Error Handling and navigate to Apply Conflicts
3. Set No record found for applying an UPDATE to INSERT the missing target record
   
   
   

Show results of updates and deletes on the target table

Results in target table after updates and deleting source record #6 and reuse of primary key on source.

Target table after full load:

Target table results after updates and delete on source:

Target table after reuse of source primary key new record inserted.

Outcome

Results show that the target table is acting like an Audit table; all source transactions are stored.

The target table can have multiple records that have the same primary key on the source (MyPk 6).

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.

Related Content

Upsert/Merge
Soft Delete

Environment

Qlik Replicate 

Sometimes it might be necessary to deallocate licenses using the Qlik Command Line Interface, for instance in case of licenses allocated to users no longer in the system.

Environment

• Qlik Cloud 

Resolution

1. Download and configure the Qlik-cli 
   
   
2. run "qlik license assignment ls" in the Powershell  or UNIX shell to see currently assigned licenses. You will get something like this:
   
   

   [                     
     {
       "created": "2023-04-03T11:24:05.101Z",
       "excess": false,
       "name": “Enzo Bearzot”,
       "subject": "auth0|12345678”,
       "type": "professional",
       "userId": “userid1”
     },
     {
       "created": "2022-08-17T11:50:04.647Z",
       "excess": false,
       "name": “Vittorio Pozzo”,
       "subject": "auth0|910111213”,
       "type": “analyzer”,
       "userId": “userid2”
     },
     {
       "created": "2022-08-17T11:50:04.647Z",
       "excess": false,
       "name": “Ferruccio Valcareggi”,
       "subject": "auth0|14151617”,
       "type": "professional",
       "userId": “userid3”
     },
     {
       "created": "2022-06-07T11:36:15.704Z",
       "excess": false,
       "subject": “18192021”,
       "type": “professional”
     },
     {
       "created": "2022-06-03T13:50:14.105Z",
       "excess": false,
       "subject": “22232425”,
       "type": "analyzer"
     },
   ]
   ​

    

3. identify any user that should not have a license assigned. There is a higher probability that you'll find them among the users who have no "name:" and "userid:" fields
   
   
4. for each of this user contruct the string
   
   

   The syntax below is for Unix shells. For usage in Windows Powershell add a "backslash" before each "doublequote" sign. '[{"subject"... will have to become '[{\"subject\"...

   
   
   
   

   '[{"subject":"USERSUBJECT","type":"ASSIGNEDLICENSE"}]'
   
   For instance, in the case of the final user in the list above it will be  '[{"subject":"22232425","type":"analyzer"}]'
   
   
   

5.  feed the string into this command:

   qlik license assignment delete --delete STRING
   
   With the example above you will run:
   qlik license assignment delete --delete '[{"subject":"22232425","type":"analyzer"}]'
   
   

6. Run the command and wait for the result.
   A "200" will confirm that the removal worked
   
   
   

   % qlik license assignment delete --delete '[{"subject":"22232425","type":"analyzer"}]'
   [                    
     {
       "status": 200,
       "subject": "22232425",
       "type": "analyzer"
     }
   ]
   

    

7. Check the "Home" section of the management console to confirm that the allocation was removed

    

Related Content

When using Replicate to replicate data to Big Query Target the attrep_change Table gets dropped and recreated for the batches to send to Big Query which is causing the DML generated for the Table to exceed the 1500 default Quota Limit for Big Query Table.

Environment

• Replicate 7.0
• Replicate 2021.5

Resolution

For the Big Query Target in Replicate you can add an internal parameter below to use the Truncate method for the attrep_change Table so the data is truncated to reduce the Quota cost for the given Tables being Replicated in the Task.

Advanced Tab on Big Query Target Internal Parameters

$info.query_syntax.truncate_table (won't show up in the list, just hit enter)

With a value of: TRUNCATE TABLE ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC}

Cause

The GCP (Google Cloud Provider) has Quota Limit setup on the given tables in Big Query with the default of 1500. During CDC processing with Replicate the attrep_change (temp table) to keep track of the batches to send to Target is dropping and recreating as the bulk process to send to Target. These are DML calls to Big Query which are the used via the Quota's per Table.

Reference

https://cloud.google.com/bigquery/docs/troubleshoot-quotas

This article provides step-by-step instructions for implementing Azure AD as an identify provider for Qlik Cloud. We cover configuring an App registration in Azure AD and configuring group support using MS Graph permissions.

It guides the reader through adding the necessary application configuration in Azure AD and Qlik Sense Enterprise SaaS identity provider configuration so that Qlik Sense Enterprise SaaS users may log into a tenant using their Azure AD credentials.

Content:

• Prerequisites
• Helpful vocabulary
• Considerations when using Azure AD with Qlik Sense Enterprise SaaS
• Configure Azure AD
• Create the app registration
• Create the client secret
• Add claims to the token configuration
• Add group claim
• Collect Azure AD configuration information
• Configure Qlik Sense Enterprise SaaS IdP
• Recap
• Addendum
• Related Content (VIDEO)

Prerequisites

• An Microsoft Azure account
• A Microsoft Azure Active Directory instance
• A Qlik Sense Enterprise SaaS tenant
• The BYOIDP feature in your Qlik license is set to YES. Contact customer support to find out if you are entitled to bring your own identity provider to your tenant.

Helpful vocabulary

Throughout this tutorial, some words will be used interchangeably.

• Qlik Sense Enterprise SaaS: Qlik Sense hosted in Qlik’s public cloud
• Microsoft Azure Active Directory: Azure AD
• Tenant: Qlik Sense Enterprise SaaS tenant or instance
• Instance: Microsoft Azure AD
• OIDC: Open Id Connect
• IdP: Identity Provider

Considerations when using Azure AD with Qlik Sense Enterprise SaaS

• Qlik Sense Enterprise SaaS allows for customers to bring their own identity provider to provide authentication to the tenant using the Open ID Connect (OIDC) specification (https://openid.net/connect/)
• Given that OIDC is a specification and not a standard, vendors (e.g. Microsoft) may implement the capability in ways that are outside of the core specification. In this case, Microsoft Azure AD OIDC configurations do not send standard OIDC claims like email_verified. Using the Azure AD configuration in Qlik Sense Enterprise SaaS includes an advanced option to set email_verified to true for all users that log into the tenant.
• The Azure AD configuration in Qlik Sense Enterprise SaaS includes special logic for contacting Microsoft Graph API to obtain friendly group names. Whether those groups originate from an on-premises instance of Active Directory and sync to Azure AD through Azure AD Connect or from creation within Azure AD, the friendly group name will be returned from the Graph API and added to Qlik Sense Enterprise SaaS.

   

Configure Azure AD

Create the app registration

1. Log into Microsoft Azure by going to https://portal.azure.com.
2. Click on the Azure Active Directory icon in the browser Or search for "Azure Active Directory" in the search bar on the top. The overview page for the active directory will appear.
   
   
   
   
3. Click the App registrations item in the menu to the left.
   
   
   
   
4. Click the New registration button at the top of the detail window. The application registration page appears.
   
   
   
   
5. Add a name in the Name section to identify the application. In this example, the name of the hostname of the tenant is entered along with the word OIDC.
   
   
   
   
   
   
6. The next section contains radio buttons for selecting the Supported account types. In this example, the default – Accounts in this organizational directory only – is selected.
   
   
   
   
7. The last section is for entering the redirect URI. From the dropdown list on the left select “web” and then enter the callback URL from the tenant. Enter the URI https://<tenant hostname>/login/callback.
   
   
   

   The tenant hostname required in this context is the original hostname provided to the Qlik Enterprise SaaS tenant.

   
   
   Using the Alias hostname will cause the IdP handshake to fail.
8. Complete the registration by clicking the Register button at the bottom of the page.
   
   
   
   
9. Click on the Authentication menu item on the left side of the screen.
   
   
   
   
10. On the middle of the page, the reference to the callback URI appears. There is no additional configuration required on this page.
    
    

Create the client secret

1. Click on the Certificates and secrets menu item on the left side of the screen.
   
   
   
   
2. In the center of the Certificates and secrets page, there is a section labeled Client secrets with a button labeled New client secret. Click the button.
   
   
   
   
3. In the dialog that appears, enter a description for the client secret and select an expiration time. Click the Add button after entering the information.
   
   
   
   
4. Once a client secret is added, it will appear in the Client secrets section of the page.
   

   Copy the "value of the client secret" and paste it somewhere safe.

   After saving the configuration the value will become hidden and unavailable.
   
   

Add claims to the token configuration

1. Click on the Token configuration menu item on the left side of the screen.
   
   
   
   
2. The Optional claims window appears with two buttons. One for adding optional claims, and another for adding group claims. Click on the Add optional claim button.
   
   
   
   
3. For optional claims, select the ID token type, and then select the claims to include in the token that will be sent to the Qlik Sense Enterprise SaaS tenant. In this example, ctry, email, tenant_ctry, upn, and verified_primary_email are checked. None of these optional claims are required for the tenant identity provider to work properly, however, they are used later on in this tutorial.
   
   
   
   
4. Some optional claims may require adding OpenId Connect scopes from Microsoft Graph to the application configuration. Click the check mark to enable and click Add.
   
   
   
   
5.  The claims will appear in the window.
   
   

Add group claim

1. Click on the API permissions menu item on the left side of the screen.
   
   
   
   
2. Observe the configured permissions set during adding optional claims.
   
   
   
   
3. Click the Add a permission button and select the Microsoft Graph option in the Request API permissions box that appears. Click on the Microsoft Graph banner.
   
   
   
   
4. Click on Delegated permissions. The Select permission search and the OpenId permissions list appears.
   
   
   
   
   

   In the OpenID permissions section, check email, openid, and profile. In the Users section, check user.read.

5. In the Select permissions search, enter the word group. Expand the GroupMember option and select GroupMember.Read.All. This will grant users logging into Qlik Sense Enteprise SaaS through Azure AD to read the group memberships they are assigned.
   
   
   
   
6. After making the selection, click the Add permissions button.
   
   
   
   
7. The added permissions will appear in the list. However, the GroupMember.Read.All permission requires admin consent to work with the app registration. Click the Grant button and accept the message that appears.
   
   
   
   

Failing to grant consent to GroupMember.Read.All may result in errors authenticating to Qlik using Azure AD. Make sure to complete this step before moving on.

Collect Azure AD configuration information

1. Click on the Overview menu item to return to the main App registration screen for the new app. Copy the Application (client) ID unique identifier. This value is needed for the tenant’s idp configuration.
   
   
   
   
2. Click on the Endpoints button in the horizontal menu of the overview.
   
   
   
   
3. Copy the OpenID Connect metadata document endpoint URI. This is needed for the tenant’s IdP configuration.
   
   

Configure Qlik Sense Enterprise SaaS IdP

1. With the configuration complete and required information in hand, open the tenant’s management console and click on the Identity provider menu item on the left side of the screen.
   
   
   
   
2. Click the Create new button on the upper right side of the main panel.
   
   
   
   
3. Select Interactive from the Type drop-down menu item, and select  'AZURE AD' from the Provider drop-down menu item.
   
   
   
   
4. Scroll down to the Application credentials section of the configuration panel and enter the following information:
   1. ADFS discovery URL: This is the endpoint URI copied from Azure AD.
   2. Client ID: This is the application (client) id copied from Azure AD.
   3. Client secret: This is the value copy and pasted to a safe location from the Certificates & secrets section from Azure AD.
   4. The Realm is an optional value used if you want to enter what is commonly referred to as the Active Directory domain name.
      
      
      
      
5. Scroll down to the Claims mapping section of the configuration panel. There are five textboxes to confirm or alter.
   
   
   
   
   1. The sub field is the subject of the token sent from Azure AD. This is normally a unique identifier and will represent the UserID of the user in the tenant. In this example, the value “sub” is left and appid is removed. To use a different claim from the token, replace the default value with the name of the desired attribute value.
      
      
      
      
   2. The name field is the “friendly” name of the user to be displayed in the tenant. For Azure AD, change the attribute name from the default value to “name”.
      
      
      
      
   3. In this example, the groups, email, and client_id attributes are configured properly, therefore, they do not need to be altered.
      
      
      

      In this example, I had to change the email claim to upn to obtain the user's email address from Azure AD. Your results may vary.

      
      
      
      
6. Scroll down to the Advanced options and expand the menu. Slide the Email verified override option ON to ensure Azure AD validation works. Scope does not have to be supplied.
   
   
   
   
7. The Post logout redirect URI is not required for Azure AD because upon logging out the user will be sent to the Azure log out page.
8. Click the Save button at the bottom of the configuration to save the configuration. A message will appear confirming intent to create the identity provider. Click the Save button again to start the validation process.
   
   
   
   
9. The validation procedure begins by redirecting the person configuring the IdP to the login page for the IdP.
   
   
   
   
10. After successful authentication, Azure AD will confirm that permission should be granted for this user to the tenant. Click the Accept button.
    
    
    
    
11. If the validation fails, the validation procedure will return a window like the following.
    
    
    
    
12. If the validation succeeds, the validation procedure will return a mapped claims window. If the validation states it cannot map the user's email address, it is most likely because the email_verified switch has not been turned on. Go ahead and confirm, move through the remaining steps, and update the configuration as per the previous step. Re-run the validation to map the email.
    
    
    
    
13. After confirming the information is correct, the account used to validate the IdP may be elevated to a TenantAdmin role. It is strongly recommended to do make sure the box is checked before clicking continue.
    
    
    
    
14. The next to last screen in the configuration will ask to activate the IdP. By activating the Azure AD IdP in the tenant, any other identity providers configured in the tenant will be disabled.
    
    
    
    
15. Success.
    
    
    
    
16. Please log out of the tenant and re-authenticate using the new identity provider connection. Once logged in, change the url in the address bar to point to https://<tenanthostname>/api/v1/diagnose-claims. This will return the JSON of the claims information Azure AD sent to the tenant. Here is a slightly redacted example.
    
    
    
    
17. Verify groups resolve properly by creating a space and adding members. You should see friendly group names to choose from.
    
    
    
    
    
    
    
    

Recap

While not hard, configuring Azure AD to work with Qlik Sense Enterprise SaaS is not trivial. Most of the legwork to make this authentication scheme work is on the Azure side. However, it's important to note that without making some small tweaks to the IdP configuration in Qlik Sense you may receive a failure or two during the validation process.

Addendum

For many of you, adding Azure AD means you potentially have a bunch of clean up you need to do to remove legacy groups. Unfortunately, there is no way to do this in the UI but there is an API endpoint for deleting groups. See Deleting guid group values from Qlik Sense Enterprise SaaS for a guide on how to delete groups from a Qlik Sense Enterprise SaaS tenant.

Related Content (VIDEO)

Qlik Cloud: Configure Azure Active Directory as an IdP

This article provides information on how to get started with the ServiceNow connector in Qlik Application Automation.
The connector for ServiceNow is making use of Basic Authentication. To connect you will also need the name of your instance.

Authentication and Authorization

When you connect to ServiceNow in Qlik Application Automation you will be presented with the following screen.
You can obtain the instance name from the URL that you use to access ServiceNow. You can use your username / password of your account provided it has enough privileges for what you aim to do. It is recommended to create a service account for integrations and limit it's roles to what is necessary.

Working with the ServiceNow blocks

Most of the blocks for the ServiceNow connector make use of the Table API of ServiceNow.
ServiceNow documentation for the Table API can be found at: https://developer.servicenow.com/dev.do#!/reference/api/rome/rest/c_TableAPI

The following data types have easy to use blocks for all CRUD operations:
• Incident
• Change Task
• Problem
• Change Request

Furthermore it is possible to work with user objects, listing journal items, audit lines, obtaining and adding attachments.
When modifying or deleting objects from the Table API, make sure to be using the "sys_id" field of objects as the identifier.

Custom API blocks

With the blocks "get table content by id", "create table content", "delete table content", "update table content", "list table content", it is possible to work with every object through the use of the Table API. These blocks also allow you to look up the name of a table with the "do lookup" functionality. As this lookup obtains information from the sys_db_object table in ServiceNow, it's possible that this lookup does not work due to your permissions.

There is also a "Raw API Request" available. This block allows providing your own sub path starting from the Base URL of ServiceNow, HTTP method and optionally a JSON body.
This can be used to contact other API groups than the Table API of ServiceNow as well as custom scripted API's.

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.

With Microsoft SQL as an endpoint, Qlik Replicate can be set up to Delete processed backup logs.

When the delete processed backup logs option is enabled, logs are deleted after a task processed the given log. This can lead to other tasks needing the log to complain about the missing log. 

This is working as intended. Qlik Replicate tasks do not communicate with each other.

Resolution

Disable Delete processed backup logs.

1. Open the MSSQL endpoint
2. Open the Advanced tab
3. Uncheck Delete processed backup logs
   
   

This will retain all log files. The files will need to be manually deleted.

As such, we advise reviewing latency and once it is zero, deleting the old logs. If Qlik Enterprise Manager has been deployed, we advise using API calls to check latency and to create a PowerShell script to delete the old files to automate the process. 

Environment

Qlik Replicate 

In this article, we walk you through the requirements and process of how to upgrade and unbundle an existing Qlik Sense Repository Database (see supported scenarios) as well as how to install a brand new Repository based on PostgreSQL. We will use the Qlik PostgreSQL Installer (QPI).

For a manual method, see How to manually upgrade the bundled Qlik Sense PostgreSQL version to 12.5 version.

Using the Qlik Postgres Installer not only upgrades PostgreSQL; it also unbundles PostgreSQL from your Qlik Sense Enterprise on Windows install. This allows for direct control of your PostgreSQL instance and facilitates maintenance without a dependency on Qlik Sense. Further Database upgrades can then be performed independently and in accordance with your corporate security policy when needed, as long as you remain within the supported PostgreSQL versions. See How To Upgrade Standalone PostgreSQL.

Index

• Supported Scenarios
• Upgrades
• New installs
• Requirements
• Installing anew Qlik Sense Repository Database using PostgreSQL
• Qlik PostgreSQL Installer - Download Link
• Upgrading anexisting Qlik Sense Repository Database
• The Upgrade:
• Finalizing the Upgrade/Uninstalling the old Database Service:
• Compatibility with PostgreSQL installers
• Troubleshooting
• Video Walkthrough
• Related Content

Supported Scenarios

Upgrades

The following versions have been tested and verified to work with QPI 1.3.0 (May 2023): 

February 2022 to May 2023.

If you are on any version prior to these, upgrade to at least February 2022 before you begin.  

Note that November 2022 and later do not support 9.6, and a warning will be displayed during the Qlik Sense upgrade. 

New installs

The Qlik PostgreSQL Installer supports installing a new Qlik Sense Repository Database cluster on a new server, setting up a new environment or migrating an existing Database to a separate host.

Requirements

• Review the QPI Release Notes before you continue
• Using the Qlik PostgreSQL Installer on a patched Qlik Sense version can lead to unexpected results. If you have a patch installed, either:
  • Uninstall all patches before using QPI or
  • Upgrade to an IR release of Qlik Sense which supports QPI
• This installer will not detect a standalone PostgreSQL database installed by any other means, other than through an official Qlik installer package.
• The user who runs the installer must be an administrator.
• The backup destination must have sufficient free disk space to dump the existing database  
• The backup destination must not be a network path or virtual storage folder. It is recommended the backup is stored on the main drive. 
• The installer itself does not provide an automatic rollback feature.
• There will be downtime during this operation, please plan accordingly 

Installing a new Qlik Sense Repository Database using PostgreSQL

1. Run the Qlik PostgreSQL Installer as an administrator 
   
   
2. Click on Install 
   
   
3. Accept the Qlik Customer Agreement 
   
   
4. Set your Local database settings and click Next. You will use these details to connect other nodes to the same cluster. 
   
   
5. Set your Database superuser password and click Next
   
   
6. Set the database installation folder, standard: C:\Program Files\PostgreSQL\14
   
   
7. Set the database data folder, standard: C:\Program Files\PostgreSQL\14\data
   
   
8. Review your settings and click Install, then click Finish
   
   
9. Start installing Qlik Sense Enterprise Client Managed. Choose Join Cluster option.
   
   The Qlik PostgreSQL Installer has already seeded the databases for you and has created the users and permissions. No further configuration is needed.
   
   
10. The tool will display information on the actions being performed. Once installation is finished, you can close the installer.  
    
    If you are migrating your existing databases to a new host, please remember to reconfigure your nodes to connect to the correct host. How to configure Qlik Sense to use a dedicated PostgreSQL database 
    

Qlik PostgreSQL Installer - Download Link

Download the installer here.

Qlik PostgreSQL installer version 1.3.0 Release Notes

Upgrading an existing Qlik Sense Repository Database

The following versions have been tested and verified to work with QPI 1.3.0 (May 2023): 

February 2022 to May 2023.

If you are on any version prior to these, upgrade to at least February 2022 before you begin.  

Note that November 2022 and later do not support 9.6, and a warning will be displayed during the Qlik Sense upgrade. 

The Upgrade:

1. Stop all services on rim nodes 
   
   
2. On your Central Node, stop all services except the Qlik Sense Repository Database 
   
   
   
   
3. Run the Qlik PostgreSQL Installer. An existing Database will be detected. 
   
   
   
   
4. Highlight the database and click Upgrade 
   
5. Read and confirm the (a) Installer Instructions as well as the Qlik Customer Agreement, then click (b) Next.
   
   
   
   
6. Provide your existing Database superuser password and click Next.
   
   
7. Define your Database backup path and click Next.
   
   
   
   
8. Define your Install Location (default is prefilled) and click Next.
   
   
   
   
9. Define your database data path (default is prefilled) and click Next.
   
   
    
10. Review all properties and click Upgrade.
    
    The review screen lists the settings which will be migrated. No manual changes are required post-upgrade.
    
    
    
    
11. The upgrade is completed. Click Close.
     
12. Open the Windows Services Console and locate the Qlik Sense Enterprise on Windows services.
    
    You will find that the Qlik Sense Repository Database service has been set to manual. Do not change the startup method. 
    
    
    
    You will also find a new postgresql-x64-12 service. Do not rename this service. 
    
    
    
    
13. Start all services except the Qlik Sense Repository Database service. 
    
    
14. Start all services on your rim nodes.
    
    
15. Validate that all services and nodes are operating as expected. The original database folder in C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\X.X_deprecated

If the upgrade was unsuccessful and you are missing data in the Qlik Management Console or elsewhere, contact Qlik Support.  

Finalizing the Upgrade/Uninstalling the old Database Service:

Before upgrading Qlik Sense Enterprise on Windows to the next major release

• for example: November 2022 or higher versions,

The old Qlik Sense Repository Database service must be removed from the server.

Perform the following steps to delete the Qlik Sense Repository Database Service:

1. Open a command prompt and run the command:
   cd "C:\ProgramData\Package Cache"
   
   
2. From there, run:
   
   If 9.6:
   From "c:\ProgramData\Package Cache\"
   Run following command: dir /s PostgreSQL.msi
   
   If 12.5:
   From "c:\ProgramData\Package Cache\"
   Run following command: dir /s PostgreSQL125.msi
   
   
3. The folder containing the msi will be revealed.
   
   
4. Right-click on the msi file and select uninstall from the menu.

Compatibility with PostgreSQL installers

This version of the upgrade tool is fully compatible with the official PostgreSQL installers from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads.

If you have previously used the Qlik Postgres Installer (version 1.2.1 or earlier), you can simply install the latest PostgreSQL version (within your major release).

Example: You have used the old QPI to upgrade to 12.5. You can now easily upgrade to a later version in the same major release, such as 12.15.

Troubleshooting

1. If the installation crashes, the server reboots unexpectedly during this process, or there is a power outage, the new database may not be in a serviceable state. Installation/upgrade logs are available in the location of your temporary files, for example:
   
   C:\Users\Username\AppData\Local\Temp\2
   
   A backup of the original database contents is available in your chosen location, or by default in:
   
   C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\backup\X.X
   
   The original database data folder has been renamed to:
   
   C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\X.X_deprecated
   
   
2. Upgrading Qlik Sense after upgrading PostgreSQL with the QPI tool fails with:
   
   This version of Qlik Sense requires a 'SenseServices' database for multi cloud capabilities. Ensure that you have created a 'SenseService' database in your cluster before upgrading. For more information see Installing and configuring PostgreSQL.
   
   See Qlik Sense Upgrade fails with: This version of Qlik Sense requires a _ database for _.
   
   To resolve this, start the postgresql-x64-XX service.

Video Walkthrough

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. The video in this article was recorded in a earlier version of QPI, some screens might differ a little bit.

Related Content

Qlik PostgreSQL installer version 1.3.0 Release Notes  
Techspert Talks - Upgrading Qlik Sense Repository Service
Backup and Restore Qlik Sense Enterprise documentation
Migrating Like a Boss
Optimizing Performance for Qlik Sense Enterprise
Qlik Sense Enterprise on Windows: How To Upgrade Standalone PostgreSQL
How-to reset forgotten PostgreSQL password in Qlik Sense
How to configure Qlik Sense to use a dedicated PostgreSQL database
Troubleshooting Qlik Sense Upgrades 

Accessing the Qlik Replicate or Qlik Enterprise Manager console errors out with the following:

SYS,GENERAL_EXCEPTION,The requested security protocol is not supported.

After an upgrade of Windows to Windows 2019, the previously installed .NET 4.8 install may be reverted to an older .NET version. A reinstall of .NET may be required. 

Qlik Replicate and Qlik Enterprise Manger require .NET framework 4.8 or later. See Windows software requirements.

To verify the currently installed .NET Framework version, open a command line prompt and run:

reg query "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\NET Framework Setup\NDP\v4\full" /v version

A typical output looks like (in my sample the .NET Framework 4.8 was installed):

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\NET Framework Setup\NDP\v4\full
version REG_SZ 4.8.03761

Resolution

Once you have confirmed that the wrong .NET Framework version is listed, reinstall .NET Framework 4.8.

.NET Framework 4.8.1 is not supported until Windows Server 2022 version, so version 4.8 is required.

This is .NET Framework 4.8 English edition Offline Installer from Microsoft Download Center.

Environment

Qlik Replicate 
Qlik Enterprise Manager 

Global expressions are not propagated to all tables. 

Defining a soft delete transformation can be done on each individual table as needed. However, if you want to do it for all tables in the task you will want to use a Global Transformation.  

Issue

The issue manifested when defining a Global Expression to do a soft delete.

The field is supposed to be automatically added to all tables in the task. The field was not added to all tables. It was random results as some tables had the field and others did not.

When reviewing the process of defining the expression we noticed there are two screens which would allow you to enter the expression.

When we used both the Transformation Scope / Advanced Options screen and the Transformation Action / Value screen, not all tables got the field. 

Example: Transformation Scope, with Advanced Options expanded:

Resolution

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.

Related Content

Transformation - Operation Indicator - Archive User - Soft Delete

It is important to understand the options which PostgreSQL uses to allow connectivity. These principles are valid no matter whether you are using the Qlik Sense Repository Database service to host the database or using a standalone PostgreSQL instance. The two key files which PostgreSQL uses to define who can connect, how they connect, where they can connect from, and how many connections are allowed are:

• postgresql.conf
• pg_hba.conf

For bundled PostgreSQL instances, the files are located in: 

C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\x.x

If you have unbundled PostgreSQL using the Qlik PostgreSQL installer (QPI), the default location is:

C:\Program Files\PostgreSQL\xx\data

For any other standalone PostgreSQL instances, consulting with your database admin to locate the data directory. 

Note: Any changes to these files will require a restart of the Qlik Sense Repository Database or PostgreSQL.
Note: Any lines prefixed with a # are commented out and not active.

postgresql.conf

In the postgresql.conf, in a basic Qlik Sense configuration, there are two key configuration options which are important:

• listen_addresses
  • This configuration defines which NICs PostgreSQL will listen on. For standalone PostgreSQL servers or multi-node Qlik Sense sites, the recommended setting is * (example below). This allows PostgreSQL to bind to any NICs presented to the server. Multiple comma-separated DNS entries may be added as well. (e.g.  listen_addresses = 'localhost,QlikServer2.domain.local' ) More advanced configurations are possible here but will not be covered in this guide.
• max_connections
  • This configuration defines how many connections PostgreSQL will allow.  It is best to use a rule of thumb of about 110 connections per node +20.  For example, for 4 nodes set to 460. See Installing Qlik Sense in a multi node for more details. 

Where these lines are can vary but an expected single node Qlik Sense site would look like this:

listen_addresses = '*'
max_connections = 100

pg_hba.conf

The pg_hba.conf file defines a variety of aspects:

• What IP addresses or ranges can connect to PostgreSQL and for what accounts
• What method of authentication those IP ranges / accounts can use
• For more details on configuration options for IP listeners, such as samenet, see https://www.postgresql.org/docs/9.6/auth-pg-hba-conf.html

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::/0                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5
host	all	            all	            0.0.0.0/0	            md5

In this example pg_hba.conf file there two active lines. The first line specifies that all users can connect to all databases from the local loopback IP using MD5 authentication (host all all 127.0.0.1/32 md5). The second line specifies that all users can connect to all databases from any valid IPv4 address using MD5 authenication (host all all 0.0.0.0/0 md5)

Please review our help for references to more sophisticated configurations where specific IP addresses or IP address ranges are covered.

To recap, when ensuring connectivity in a Qlik Sense Shared Persistence environment, especially a multi-node site, the core configurations which are needed are:

• PostgreSQL configured to listen to an external IP address (listen_address in postgresql.conf)
• Sufficient connections available for Qlik Sense (max_connections in postgresql.conf)
• Explicit allowing the IP address which is used to connect (pg_hba.conf)