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
• Known limitations
• Installing anew Qlik Sense Repository Database using PostgreSQL
• Qlik PostgreSQL Installer - Download Link
• Upgrading an existing Qlik Sense Repository Database
• The Upgrade
• Next Steps and Compatibility with PostgreSQL installers
• How do I upgrade PostgreSQL from here on?
• Troubleshooting and FAQ
• Related Content

Video Walkthrough

Video chapters:

• 01:02 - Intro to PostgreSQL Repository
• 02:51 – Prerequisites
• 03:24 - What is the QPI tool?
• 05:09 - Using the QPI tool
• 09:27 - Removing the old Database Service
• 11:27 - Upgrading a stand-alone to the latest release
• 13:39 - How to roll-back to the previous version
• 14:46 - Troubleshooting upgrading a patched version
• 18:25 - Troubleshooting upgrade security error
• 21:15 - Additional config file settings

Supported Scenarios

Upgrades

The following versions have been tested and verified to work with QPI (1.4.0): 

Qlik Sense February 2022 to Qlik Sense November 2023.

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

Qlik Sense November 2022 and later do not support 9.6, and a warning will be displayed during the upgrade. From Qlik Sense August 2023 a upgrade with a 9.6 database is blocked.

New installs

The Qlik PostgreSQL Installer supports installing a new standalone PostgreSQL database with the configurations required for connecting to a Qlik Sense server. This allows 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 (see Installing and Uninstalling Qlik Sense Patches) or
  • Upgrade to an IR release of Qlik Sense which supports QPI

• The PostgreSQL Installer can only upgrade bundled PostgreSQL database listening on the default port 4432.
• 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. 
• There will be downtime during this operation, please plan accordingly
• If upgrading to PostgreSQL 14 and later, the Windows OS must be at least Server 2016

Known limitations

• Cannot migrate a 14.8 embedded database to a standalone
• Using QPI to upgrade a standalone database or a database previously unbundled with QPI is not supported.
• The installer itself does not provide an automatic rollback feature.

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, default: C:\Program Files\PostgreSQL\14

   Do not use the standard Qlik Sense folders, such as C:\Program Files\Qlik\Sense\Repository\PostgreSQL\ and C:\Programdata\Qlik\Sense\Repository\PostgreSQL\.

7. Set the database data folder, default: C:\Program Files\PostgreSQL\14\data

   Do not  use the standard Qlik Sense folders, such as C:\Program Files\Qlik\Sense\Repository\PostgreSQL\ and C:\Programdata\Qlik\Sense\Repository\PostgreSQL\.

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 Release Notes

Upgrading an existing Qlik Sense Repository Database

The following versions have been tested and verified to work with QPI (1.4.0): 

February 2022 to November 2023.

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

Qlik Sense November 2022 and later do not support 9.6, and a warning will be displayed during the upgrade.  From Qlik Sense August 2023 a 9.6 update is blocked.

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-14 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
    

     

16. Uninstall the old Qlik Sense Repository Database service.

    This step is required. Failing to remove the old service will lead the upgrade or patching issues.

    1. Open a Windows File Explorer and browse to C:\ProgramData\Package Cache
       
       
    2. From there, search for the appropriate msi file.
       
       
       
       If you were running 9.6 before the upgrade, search PostgreSQL.msi
       If you were running 12.5 before the upgrade, search PostgreSQL125.msi
       
       
    3. The msi will be revealed.
       
       
    4. Right-click the msi file and select uninstall from the menu.
       
       
       
       
    
    
17. Re-install the PostgreSQL binaries. This step is optional if Qlik Sense is immediately upgraded following the use of QPI. The Sense upgrade will install the correct binaries automatically. 
    

    Failing to reinstall the binaries will lead to errors when executing any number of service configuration scripts. 

    If you do not immediately upgrade:
    
    
    1. Open a Windows File Explorer and browse to C:\ProgramData\Package Cache
       
       
    2. From there, search for the .msi file appropriate for your currently installed Qlik Sense version
       
       
       
       For Qlik Sense August 2023 and later: PostgreSQL14.msi
       Qlik Sense February 2022 to May 2023: PostgreSQL125.msi
       
       
    3. Right-click the file
    4. Click Open file location
       
       
       
       
    5. Highlight the file path, right-click on the path, and click Copy
       
       
       
       
    6. Open a Windows Command prompt as administrator
       
       
    7. Navigate to the location of the folder you copied
       
       Example command line: 
       
       cd C:\ProgramData\Package Cache\{GUID}
       
       Where GUID is the value of the folder name.
       
       
    8. Run the following command depending on the version you have installed: 
       
       Qlik Sense August 2023 and later
       
       msiexec.exe /qb /i "PostgreSQL14.msi" SKIPINSTALLDBSERVICE="1" INSTALLDIR="C:\Program Files\Qlik\Sense"
       
       Qlik Sense February 2022 to May 2023
       
       msiexec.exe /qb /i "PostgreSQL125.msi" SKIPINSTALLDBSERVICE="1" INSTALLDIR="C:\Program Files\Qlik\Sense"
       
       This will re-install the binaries without installing a database. If you installed with a custom directory adjust the INSTALLDIR parameter accordingly. E.g. you installed in D:\Qlik\Sense then the parameter would be INSTALLDIR="D:\Qlik\Sense".
       
       
18. Finalize the process by updating the references to the PostgreSQL binaries paths in the SetupDatabase.ps1 and Configure-Service.ps1 files. For detailed steps, see Cannot change the qliksenserepository password for microservices of the service dispatcher: The system cannot find the file specified.

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

Next Steps and Compatibility with PostgreSQL installers

Now that your PostgreSQL instance is no longer connected to the Qlik Sense Enterprise on Windows services, all future updates of PostgreSQL are performed independently of Qlik Sense. This allows you to act in accordance with your corporate security policy when needed, as long as you remain within the supported PostgreSQL versions.

Your PostgreSQL database is fully compatible with the official PostgreSQL installers from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads. 

How do I upgrade PostgreSQL from here on?

See How To Upgrade Standalone PostgreSQL, which documents the upgrade procedure for either a minor version upgrade (example: 14.5 to 14.8) or a major version upgrade (example: 12 to 14). Further information on PostgreSQL upgrades or updates can be obtained from Postgre directly.

Troubleshooting and FAQ

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.

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 PostgreSQL Repository Troubleshooting
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 

The execution of TMC task fails to collect Metrics data from Remote Engine and causes a DNS error

Log

RestServletCollector | 299 - org.apache.karaf.decanter.collector.rest.servlet - 2.10.0 | Error processing event from servlet  
java.net.UnknownHostException: ip-10-216-119-xxx: Name or service not known  

ERROR | pool-27-thread-1 | ContainerStatusUpdater | 289 - org.talend.ipaas.engine.deployment-agent - 2.13.4 | Unable to retrieve host address  
java.net.UnknownHostException: ip-10-216-119-xxx: Name or service not known  
at org.talend.ipaas.rt.deployment.agent.impl.ContainerStatusUpdater.getContainerStatisticsInfo(ContainerStatusUpdater.java:257)  
at org.talend.ipaas.rt.deployment.agent.impl.ContainerStatusUpdater.run(ContainerStatusUpdater.java:229)  
Caused by: java.net.UnknownHostException: ip-10-216-119-201: Name or service not known  

Resolution

Add the host (ip-10-216-119-xxx) to `/etc/hosts`

1. Copy the hostname using `$ hostname`.
2. Open `/etc/hosts` with `vi`:
   
   

    $ vi /etc/hosts 

    

3. Add the following line to map the hostname to the localhost:
   
    

    127.0.0.1 localhost ip-10-216-119-xxx 

   
   
   

Cause 

Analysis

1. The remote engine uses Kafka MQ to send TMC job metrics and it is controlled by the default flag in configuration file: <RemoteEngineInstallationDirectory>/etc/org.talend.ipaas.rt.observability.cfg 
   

    

   omc.jobs.enabled  # This Enable/disable metrics from the jobs (both business and jvm)

   
   Based on configuration file checking,  the default property is set as "true"

   omc.enabled is a main/global property to enable metrics,  without it the others will not work

2. The primary log indicates that the host (RE ip-10-216-119-xxx) cannot resolve its own host name and it is suggested be a DNS resolution issue.

    

Related Content

For more information about Remote Engine metrics type, please look at this documentation: 

Available metrics for monitoring - Cloud

Environment

Talend Cloud 

Question

Where can I find information related to Talend Remote Engine?

Regarding basic information on Talend Remote Engine, please check here: Working with Talend Remote Engines.

Regarding the lifecycle of Talend Remote Engine, please check here: Talend Remote Engine lifecycle.

Regarding monthly releases (including bug fixes and other updates) on Talend Remote Engine, please check here: Talend Release Notes.

Environment

Talend Cloud

Environment

• Qlik Sense Enterprise SaaS
• Qlik Application Automation

This article describes how an Excel file can be sent as an attachment by using the Send Mail block in Qlik Application Automation.

Automation Structure

The creation of an Excel report from a Qlik Sense straight table is explained in the first part of this article.

Let's walk through the blocks used in the automation workflow to send the Excel file as an attachment using the "Send Mail" block.

1. Open the Excel file saved in Microsoft SharePoint using the "Open File on Microsoft SharePoint" block.
   
   
2. Use the "Send Mail" block from Mail Connector to send the Excel file as an attachment via email.
   
   
   

A JSON file containing the above automation is attached to this article.

Please follow the steps provided in the How to import automation from a JSON file article to import the automation.

Related Content 

• Generate Excel Report 
• Copy data from a Qlik Sense straight table to an Excel sheet 

This article describes an example on how to disable custom connector for particular user group for Qlik Sense

Disclaimer: This is just an example to show the availability what users can do in Qlik Sense. It may not fulfil all user scenarios.

Please engage with Qlik Consultant for further advice if you want to develop/implement custom security rules into your environment.
 

Resolution:

Setup example

• Domain\alex: Domain user account, belong to Windows user Group “Sydney”
• Domain\bob: Domain user account, belong to user Group “Sydney”
• Domain\craig: Domain user account, belong to none of user Group
• Above three users account are already fetched into Qlik Sense via UDC and Token is assigned.

Goal

• Users in user group “Sydney” CANNOT create “custom” data connection.
• Users in other group CAN create “custom” data connection.

Steps

1. Go to QMC > Security Rules
2. Select Security Rule Name DataConnection and press Edit button.
3. Tick Disable and press Apply button.
   
   
   
   
4. Press Create new button to create a new Security rule. Enter below.
   
   

   Name: DisableCustomConnectors
   Description: This custom rule disables Custom Connectors for user group “Sydney”
   Resource filter: DataConnection_*
   Action: Create
   Condition: ((resource.type!="folder" and resource.type!="Custom") and (user.group="Sydney"))
   Context: Only in hub

   
   
   
   
5. Press Apply button 
6. Press Create new button to create new Security rule. Enter below and press Apply button.
   
   

   Name: DataConnectionForNormalUsers
   Description: This custom rule is for Data Connections except User Group “Sydney”. Custom Connection creation enabled.
   Resource filter: DataConnection_*
   Action: Create
   Condition: ((resource.type!="folder" and user.group!="Sydney"))
   Context: Only in hub

   
   
   
   
   
7. Press Apply button
    

Result:

Domain\Alex: Custom connectors is disabled.

 

 

 

 

Domain\craig: Custom connectors is enabled.

Edit Script Screen for Domain\craig

To help Qlik customers manage costs more effectively, Qlik has developed the Qlik Snowflake Monitoring application, designed to provide invaluable insights about your Snowflake costs, usage, inventory, security, performance and contract utilization. This app utilizes Qlik's Associative Engine to connect directly to your Snowflake instance and reveal insights from Snowflake's detailed metadata, offering valuable information that traditional query-based tools and Snowflake's own reports are unable to provide.

Leveraging Qlik Application Automation, and Data Alerts, you can:

• Install* & update the app with a fully guided, click-through installer, using an out-of-the-box Qlik Application Automation template.
• Get alerted when new versions of the Snowflake Monitor are available using Data Alerts.

*Minor configuration is required on first run to create the required data connections.

Content:

• Part One: 'Qlik Snowflake Monitoring App Installer' templateoverview
• Features
• Note on manual install
• Part Two: Creating and setting up analytics data connections
• Snowflake connection
• Note on Snowflake authentication
• REST connection to repository
• Note on update compatibility
• Part Three: Set up a Data Alert to notify when a new version of the app is available
• Summary

Part One: 'Qlik Snowflake Monitoring App Installer' template overview

This automation template is a fully guided installer/updater for the Qlik Snowflake Monitor. Leverage this automation template to easily install and update this application. The application itself is community-supported; and it is provided through Qlik’s Open-Source Software GitHub and thus is subject to Qlik’s open-source guidelines & policies.

For more information, refer to the GitHub Repository.

Features

• Easy app installation and updates
• Create new spaces or use existing spaces
• Automatic setup of settings, roles, and permissions
• Always installs the newest version from Qlik's OSS GitHub
• Tags apps to easily track installations and versions
• Works with both user and capacity-based subscriptions
• Compatible with all Snowflake tenant levels

Note on manual install

If the monitoring app was installed manually (i.e. not through the application automation installer), then the app will not be detected as existing. The automation will install new copies side-by-side. Any subsequent executions of the automation will detect the newly installed monitoring application and check their versions. This is because the application is tagged with ‘QCS - QSM - {App Name}’ and ‘QCS – QSM - {Version}’ during the installation process through the automation. Manually installed applications will not have these tags and therefore will not be detected.

Part Two: Creating and setting up analytics data connections

The Qlik Snowflake Monitor requires two connections, one to your Snowflake instance to feed the data for your analytics, and one REST connection to the qlik-oss repository to run a version check on the monitor.

Snowflake connection

You will need to create a custom User, Role and Warehouse on your snowflake tenant. This is to ensure this user and role can see the monitoring details and can be monitored.

1. Update the credentials in the SQL script from GitHub 
2. Run the SQL as ‘ACCOUNTADMIN’ from your Snowflake tenant to create a warehouse to retrieve the relevant data. Make sure you change the password and store this securely.
3. Create a snowflake data connection in the Qlik analytics space into which the application was deployed, using the following details:
   
   
   • Server: The full hostname for the Snowflake account, not prefixed with http:// or https://
   • Port: Leave as default unless it needs changing.
   • Database: SNOWFLAKE
   • Schema: ACCOUNT_USAGE
   • Warehouse: QLIK_MONITORING_WH
   • Role: QLIK_MONITORING_ROLE
     
     

   For Authentication, this setup is defaulted to username & password.

   • Username: QLIK_MONITORING
   • Password: As specified in your configuration above
     
     

   Finally, you need to name the connection as follows:

   • Connection Name: SNOWFLAKE_MONITORING_DATA
     
     
   
   

    

Note on Snowflake authentication

If you wish to use an alternative authentication method, please follow the documentation accordingly on both Snowflake & Qlik.

REST connection to repository

The REST connection is used to fetch version details from the GitHub repository. On reload it will look for the the latest released version in github and check this against the version you have installed. You can later use this in ‘Part Three’ to create an alert when updates to the application are available. To create a REST connection the following information is required:

• URL: https://raw.githubusercontent.com/qlik-oss/qlik-snowflake-monitor/main/manifest.json 
• METHOD: GET
• Allow “WITH CONNECTION”: Enabled
• Connection Name: REST Monitoring Versioning API
  
  
  
  

Once these two connections have been set up, you can reload the application. The application has been created to accommodate Snowflake tenants of all sizes. If you have a small tenant, you will find the initial run of the load script can take around 30 minutes, and for larger tenants this can be over an hour or two. Subsequent runs will utilize cached QVDs that update daily to reduce reload times each subsequent day.

Note on update compatibility

If a new release of the application is made, occasionally a full reload of data is required, but generally, if the data schema is unchanged the existing QVDs will be maintained. This is through the use of versions in the names of the QVDs used to store the data.

Part Three: Set up a Data Alert to notify when a new version of the app is available

The application has the following two variables:

• vLatestVersion: The latest version of the app released on Qlik’s OSS GitHub.
• vIsLatestVersion: A Boolean of whether the app’s current version is equal to the latest version of the app released on Qlik’s OSS GitHub (1 if true, 0 if false).

To create a new Data Alert for updates to the monitoring app, follow these steps:

1. Right-click on a chart in the app, select Alerts and then Create new alert.
2. Clear out the defaults and provide the alert with a name such as Qlik Snowflake Monitor Update Available.
3. Select Add measure and select the fx icon to enter a custom expression. Enter the expression ’$(vLatestVersion)'. Ensure it is wrapped in single quotes, then provide the label for the expression Latest Version.
4. Select Add measure and select the fx icon to enter a custom expression. Enter the expression $(vIsLatestVersion), then provide the label for the expression Is Latest Version.
5. Confirm the Details & data tab resembles the following:
   
   
   
   
6. Navigate to the next tab, Conditions.
7. Set the Measure to Is Latest Version, the Operator to Equal to, and the Value to 0. This way, if the app is not the latest version (0), it will trigger the alert.
8. Confirm that the Conditions tab resembles the following:
   
   

   

6. Navigate to the Distribution tab and confirm that the alert is evaluated When data is refreshed.

Summary

The Qlik Snowflake Monitor can be easily installed by following these steps above. If you wish to find out more, check out this Ometis blog post and this Ometis Webinar to get a run through of the analytics this application can offer.

If you face any issues, please use the GitHub and raise an issue through the repository.

Adding a Qlik Replicate Server to the Qlik Enterprise Manager while having Verify server certificate enabled fails with the following message: 

Could not establish trust relationship for the SSL/TLS secure channel

Disabling the Verify server certificate option allows for the server to be added. 

Resolution

1. Open the Qlik Replicate URL from the Qlik Enterprise Manager server.
   
   
2. View certificate displayed in the Qlik Replicate URL and export the Qlik Replicate certificate
   
   
3. On the Qlik Enterprise Manager server:
   
   
   1. Run mmc.exe to open the Microsoft Management Console.
      
      

   2. From the File menu, select Add/Remove Snap-in. The Add or Remove Snap-ins dialog box opens.

   3. In the left pane, double-click Certificates. The Certificates snap-in wizard opens.

   4. Select Computer account and then click Next.
      
      
   5. In the Select Computer screen, make sure that Local computer is selected and then click Finish.
      
      
   6. Click OK to close the Add or Remove Snap-ins dialog box.
      
      
   7. In the left pane, expand the Certificates folder. Then, right-click the Personal folder and select All Tasks>Import.
      
      
   8. In the File to Import screen, select the certificate file you exported in step 2. 
      
      
   9. Click Next and follow the on screen instructions until the wizard is completed.
      
      
4.  Return to the Qlik Replicate URL from the Qlik Enterprise Manager server and verify that the certificate is now trusted.
   
   
5. Add the Qlik Replicate server

Environment

Qlik Enterprise Manager 6.6 and above
Qlik Replicate 

Related Content

Adding Servers 

The following messages will appear in the engine trace system logs:

QVGeneral: when AAALR(63.312046) is greater than 1.000000, we suggest using new row applicator to improve time and mem effeciency.
QVGeneral: - aggregating on 'RecruiterStats'(%DepartmentID) with Cardinal(87), for Object:  in Doc: ffe8a825-b52e-4ceb-aea2-30de0f2c3306

There has also been reports of end users seeing the message "Internal Engine error" when opening apps when the error above is present.

Also for QlikView see article SE_LOG: when AAALR(1072.471418) is greater than 1.000000, we suggest using new row applicator to improve time and mem efficiency.

Environment:

• Qlik Sense Enterprise on Windows, all versions

"AAALR" is a very low level concept deep in the engine. Generally speaking it means the average length of aggregation array. The longer this array is, the more memory and CPU power are to be used by the Engine to get aggregation results for every hypercube node.
 
When AAALR is greater than 1.0, normally the customer has a large data set and suffers slow responses and high memory usage in their app. In this case, Qlik Sense has a setting called DisableNewRowApplicator (default value is 1).
 
By setting this parameter to “0”, Qlik Sense will use a new algorithm which is optimized for large data set to do the aggregation, and will use much less memory and CPU power.

Changing this setting when they have AAALR warnings, making this change has resulted in drastic performance increases.

Possible setting values for DisableNewRowApplicator:

Resolution:

• Stop all Qlik Sense services from the windows control panel / services
• Locate the “C:\ProgramData\Qlik\Sense\Engine\Settings.ini”
• Add this portion to the Engine Settings.ini file (press <enter> at the end of DisableNewRowApplicator=0 so the cursor rests on a blank line below it)

[Settings 7]
DisableNewRowApplicator=0
         <---- the cursor should be here when saving the file

• Restart all Qlik Sense services
• (If multinode) Complete the above steps on every node.

Related Content:

• How to start investigating a suspected Qlik Sense Performance issue  

As Talend Runtime's services page can be viewed from the following URL in public:

http://RUNTIME_HOST:8040/services

How can this page be hidden so that it doesn't list the services deployed in Talend Runtime?

This article briefly explains how to protect Talend Runtime Service List from exposure.

How TO

• Search the following line in "{runtime-home}/etc/org.apache.cxf.osgi.cfg" file and change the value to "true"

org.apache.cxf.servlet.hide-service-list-page=true

When you change it from false (default value) to true, then your services will be hidden and a page stating " No service was found" will display.

The configuration only supports for hiding Service List, and it is not possible to obtain authorization option by current design

• Use a proxy layer (nginx/load balancer) to wrap the runtime 8040 port per authorization on services URL access

Environment

Talend ESB 

Scripts need to be exported using the Qlik Sense Engine APIs.

Resolution

The Engine API and the Capability API both have a method/function called GetScript() that will allow the app script to be exported. Once the script is accessed, it can be saved to disc, for example, a folder that is perhaps monitored by a change control system. The Engine API can be found here and the Capability API can be found here. The following are examples of both APIs:

Engine API:

IAppIdentifier foundAppIdentifier = _location.AppWithId("....appId....");
IApp app = _location.App(foundAppIdentifier);
string script = app.GetScript();
Console.WriteLine(script);

Capability API:

var app = qlik.openApp("....appId....");
app.getScript().then( function(script){
  console.log(script);
});

Related Content:

Get started with APIs on Windows 
GetScript Method (Engine)
getScript method (Capability API)

Qlik Sense: call Qlik Sense Engine API with Python 
Qlik Sense Repository API/Engine API(Enigma.js) example with JWT authentication: duplicate/scramble/export 
Qlik Sense: Getting started with the .NET SDK 

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)

How to enable "Integrated Windows Authentication" (IWA authentication) in Qlik Sense and QlikView. Windows Integrated Authentication allows a user’s Active Directory credentials to pass through their browser to a web server.

If you would like to do the reverse and always prompt for username and password, then the opposite settings will need to be taken. 

Environment:

QlikView 
Qlik Sense Enterprise on Windows 
Qlik NPrinting 

Internet Explorer:

Step 1, enable IWA: 

1. Go to the Windows Control Panel and select Network and Internet
2. Open Internet Options
   
   
   
   
3. Select the Network Zone you want to configure (Trusted sites or Local intranet) 
4. Open custom level...
   
   
   
   
5. Scroll to the bottom and click Automatic Logon with current username and password
   
   
   
   

If you are looking to configure your Trusted sites and add your Qlik server:

1. Click Trusted Sites
2. Click Sites
   
   
   
   
3. Add your desired domain name.
   
   

Google Chrome:

Google Chrome in Windows will use Internet Explorer settings

Firefox:

1. Navigate to the URL about:config in Firefox. 
2. Proceed by clicking Accept the Risk and Continue
   
   
   
   
3. In the search bar, search for: network.automatic-ntlm-auth.trusted-uris
   
   Add your domain.
   
   
   
   

Save task failed randomly from TAC Job Conductor when importing a zip file (jobdesign.zip)

Log 

2024-05-16 11:02:27 INFO GeneratedProcessHelper - Check the job's signature faild :  /talend/tac/apache-tomcat/temp/jobdesign.zip

2024-05-16 11:02:27 ERROR TalendRemoteServiceServlet - This job was created by an unsupported version of Talend Studio. Talend cannot guarantee the origin of such artifacts which may pose a security risk to your system. Please contact Talend Support for further details about supported versions and our support policies.
org.talend.exception.BusinessException: This job was created by an unsupported version of Talend Studio. Talend cannot guarantee the origin of such artifacts which may pose a security risk to your system. Please contact Talend Support for further details about supported versions and our support policies.
at org.talend.gwtadministrator.server.scheduler.view.ExecutionTaskViewAdapter.checkProjectEE(ExecutionTaskViewAdapter.java:814)
 

Resolution

Use a more recent version of the studio and make sure Job META-INFO folder and content are integrity.

Cause

Get/download the zip that are trying to upload and further check jobInfo.properties/cmdLineVersion and job items file, talend.project/productVersion

• The missing of jobdesign.zip/META-INFO causes compilation error and does not generate and compile job successfully into the zip file.
  
• The jobdesign.zip/META-INFO/MANIFEST.MF/TALENDZI.RSA/TALENDZI.SF like a corruption (empty, incomplete content inside) 
• Job is compiled and published with a lower version studio

Related Content

If you are look for How to avoid missing Item file in Talend Administration Center's Job Conductor, please refer to this Qlik Official article about:

Qlik-Talend-Data-Integration-Save-failed

Environment

• Talend Administration Center 
• Talend Studio 

The AS/400 supports a file concept known as multiple-member files, in which one file (or table) can possess several different members. Each member is a part of the same file or table and shares the same schema, but the members are uniquely named and have unique data. Just like a partition tables in other regular RDBMS eg in Oracle, MySQL etc.

ODBC and OLE DB have no built-in mechanism for accessing multiple members. By default, ODBC always accesses the first member in a multimember file. This is the known limitation in Replicate User Guide: • Multi-member (Partitioned) tables are not supported.

To enable ODBC-based applications such as Data Transformation Services (DTS) to access multiple-member files, we need to use the AS/400's SQL ALIAS statement. The ALIAS statement lets you create an alias for each member we need to access. We use ALIAS to access all members in Qlik Replicate full load stage. More samples Creating and using ALIAS names.

However, all member data changes will be recorded into DB400 Journal file, Qlik Replicate can capture all members changes. No special settings needed in Qlik Replicate CDC stage.

This article describe how to overcome the limitation and setup Replicate task to replicate Multi-Member Tables.

Resolution

1. Create ALIAS for all member of the multi-member table (except the first member) in AS400 Terminal
   
   CREATE ALIAS apsupdb.MMPF_ALIAS2 FOR apsupdb.mmpf(MBR2)
   CREATE ALIAS apsupdb.MMPF_ALIAS3 FOR apsupdb.mmpf(MBR3)
   
   In the above sample, we create 2 alias in library apsupdb , corresponding to 2 members apsupdb.mmpf(MBR2) & apsupdb.mmpf(MBR3).
   
   
2. New a Full Load + CDC task with a few tables. Certainly the task contains the physical table (first member).
3. Press "Export Task" and edit the exported JSON file
4. Add the ALIAS to task JSON file as same as it's a regular table. 
5. Change all STRING data type columns from UTF8 to "ibm-285_P100-1995". In below sample we change
   
   STRING type column "ADDRESS":
   "source_column_data_types": [{
   "column_name": "ADDRESS",
   "converter_name": "ibm-285_P100-1995",
   "data_type": "kAR_DATA_TYPE_STR"
   }],
   
   This is because Replicate cannot get the ALIAS metadata correctly so all STRING data type are regarded as UTF8 which leads garbage data after Full Load done. Other data types (eg number, date etc ) need not to modify.
   
   
6. Import the JSON and run the task, as same as a regular task.

Environment

• Qlik Replicate All supported versions
• IBM DB2 for iSeries All supported versions

Related Content

Support Case: 00049024

When using Qlik REST connector to load data from API sources that return JSON or XML response, sometimes a huge data model with many tables is returned although the response contains only a single table.
For example, the following JSON message is parsed into 5 single-row tables instead of one table with 5 rows:

Environment:

• Qlik REST connector 2.11.0 (default connector in Qlik Sense April 2019)

Cause:

The JSON response has a "nested" structure, i.e each data record is stored as a separated JSON object, which is not member of an array.

Qlik REST connector applies a standard strategy for parsing JSON response:

• Each stand-alone object {..} (not being element of an array) is parsed as a new table
• Each array [..] is parsed as a new table. If the array contains multiple objects, each object will take one line in the resulting table

As a result, nested JSON response is parsed as multiple tables instead of a single flat table. You can find the same strategy in other JSON parsing tool, such as http://json2table.com/.

The same logic applies to nested XML responses.

Resolution:

Qlik REST connector does not yet support loading JSON/ XML responses with nested structure. There is an ongoing improvement request to support this structure in future releases of the connector.
Meanwhile, please consider the following work-around solution:

• For XML: Use a WebFile connection to load the response instead of REST connector. WebFile connection supports reading XML with [XmlSimple, table is...] instruction, which works with nested XML tables. Check the following community discussion for more details: https://community.qlik.com/t5/Qlik-Sense-Data-Connectivity/Can-I-query-nested-XML-data-over-REST-such-that-I-get-a-flat/td-p/1568751
• For both:
  • Develop an external script (such as Python) to download, parse the JSON/ XML response and store the flat table into a local data file. Use EXECUTE command to trigger the external script from Qlik and load data from the file.
  • If you have access to modify the API source, adjust the response format so that each record is stored as a member object of an array. For instance, the example above can be adjusted as follows:

When using Talend Cloud Engine to run your jobs, if the following messages are list in your execution logs, it means there are memory issues occurring.

Execution {Execution number here} failed with code 137 and error Job stopped with errors or unable to run. (check the task execution logs for the error details)

Resolution

• Try to monitor the resource consumption(CPU, Memory, Disk) on Cloud Engine and look at the amount of memory used for the current task to avoid/decrease useless memory consumption.

• For Jobs that require more resources, consider using Talend Remote Engine(RE) or Talend Dynamic Engine (DE) as the target runtime.

Cause

It is a known issue for memory limitations in Talend Cloud Engine and error code 137 happens when system kills the process(out of memory issue)

The following system resource limitations are valid for Jobs running on Cloud Engines

• vCPU: 2
• Disk usage: 200GB
• Memory usage: 8GB
• Throughput: 225GB per hour

Related Content

Please find more details regarding Talend Cloud Engine here: Execution engines in Talend Cloud.

Environment

• Talend Cloud 
• Talend Studio 

When using the tRedshiftOutputBulk or tRedshiftOutputBulkExec component in Talend Studio and setting the "Credential provider" to "Inherit credentials from AWS role", the following error is found in the karaf.log.

java.lang.IllegalArgumentException: Not supported S3 connection credential type: INHERIT_CREDENTIALS. Please use "Static Credentials."

Cause

Redshift does not support “Inherit credentials from AWS role” for "Credential provider".

Resolution

• tRedshiftOutputBulk
• tRedshiftOutputBulkExec

Redshift never supported "Inherit credentials from AWS role". In the UI, it appears that "Inherit credentials from AWS role" can be used, but in reality, it was "Static Credentials" running in the background.

If you were able to run the Job using "Inherit credentials from AWS role" in the past, you may have already set the necessary parameters for "Static Credentials" (Access key and Secret key), which allowed it to run without errors.

Environment

• Talend Data Integration 8.0.1

A Talend EMR spark batch hive task is facing below error message when creating Hive client using classpath

java.lang.ClassNotFoundException: java.lang.NoClassDefFoundError: org/apache/tez/dag/api/SessionNotRunning

Resolution

1. Identify the tez location by  “find / -name *tez*.jar” 
2. Copy with the same folder structure for the tez module from the main node to the edge nodes
    

Cause

Based on EMR hive-site.xml setting,  the Hive engine is configured with tez, but the EMR cluster can not distribute the tez-related jar from the Main node to the Edge nodes. 

<property>
<name>hive.execution.engine</name>
<value>tez</value>
</property>

Environment

Talend Big Data