Qlik Replicate tasks using Oracle as a Source Endpoint fail after installing the Oracle July 2024 patch. 

All Qlik Replicate versions older than the 2024.5 SP03 release are affected.

Environment

• Qlik Replicate 2024.5 SP02 and below
• Oracle Source Endpoint with Oracle 2024 July patch

Resolution

Upgrade to Qlik Replicate 2024.5 SP03 or later.

Cause

The Oracle July 2024 patch introduced a change to redo events. Qlik has since provided a fix for Qlik Replicate which parses the redo log correctly. 

Internal Investigation ID(s)

RECOB-8698

Making changes to a Qlikview document (.qvw) or Qlik Sense app (.qvf) will sometimes break already existing bookmarks. This is sometimes unavoidable and is a risk developers need to be mindful of when making changes to existing deployments.

Bookmark functionality can be affected by the following changes:

• Removing or renaming fields that are included in the bookmark.
• Changes to the data model or associations between fields.
• Enabling functionality like "Always One Selected" (QlikView) or locked fields, even on fields that are not included in the bookmark!

Making any of these changes can lead to bookmarks that only apply some of the intended selections, or even none at all.

Environment

• QlikView, all versions
• Qlik Sense, all versions, all editions

Resolution

A developer making changes to a document will need to keep these limitations in mind, and test any existing bookmarks, especially after using sensitive functions like "Always One Selected". If a bookmark is discovered to be broken, recreating it after the changes have been made is the best method to get back to a working state.

We in Qlik Support have virtually no scope when it comes to debugging or writing custom security rules for customers. That level of implementation advice needs to be handled by the folks in Professional Services or Presales. That being said, this example is provided for demonstration purposes to explain a specific scenario. No Support or maintenance is implied or provided. Further customization is expected to be necessary and it is the responsibility of the end administrator to test and implement an appropriate rule for their specific use case. For access to more tips and tricks, best practices, and ever-evolving creative solutions, we recommend joining us in our active Qlik Community.

In this scenario, the administrator wants to grant access to the Data Load Editor on a series of apps which the user or set of users already have read rights to.

Setup:

• Name: _DLEUserAccess
• Description / Explanation: This rule will grant update rights to an application based on the inherited Read rights provided elsewhere. Update rights to an app are necessary to see the Data Load Editor Option
• Resource filter(s): App_*
• Action(s): Update
• Conditions: resource.resourcetype = "App" and resource.Stream.HasPrivilege("read") and (user.name="User2")
  • Note: In this example we are using a statically defined user.name value. In a realistic scenario you would want to have a more robust user selection criteria (e.g. user.group="BI Developers" if there is group membership which selects the specific users which you want to target).
  • Note2: This example assumes Authentication Setup is on Stream level -- the user is allowed to access all Apps under a Stream that he/she has "read" access on. Thus resource.Stream.HasPrivilege("read"). In a realistic scenario, depending on the exact Authentication setup, modification on this condition may be required.

• Name: _ScriptUserAccess
• Description / Explanation: This rule will grant read and update rights to specific app objects which scope to the load script of an app based on the inherited Read rights on the app provided elsewhere.
• Resource filter(s): App.Object_*
• Action(s): Read, Update
• Conditions: ((resource.objectType="loadmodel" or resource.objectType="app_appscript")) and resource.app.HasPrivilege("read")
  • Note: In this example we are using a statically defined user.name value. In a realistic scenario you would want to have a more robust user selection criteria (e.g. user.group="BI Developers" if there is group membership which selects the specific users which you want to target).

Sample with Screenshots:

1. Before applying above rules, User 2 has "Read" Access to the Stream where App "12345" is in. "12345" is owned by another user. So User 2 has no access to Data Load Editor:
   
   
2. Create the 1st rule, which grants "update" to User2 on App 12345:
   
   
   
   
   
3. At this point User2 still cannot access Script Editor of App 12345(Even though Data model viewer shows up):
   
   
4. Now create the 2nd rule:
   
   
5. Now, User2 has access to Data Load Editor(and Data Manager) of App 12345:
   
   

When Qlik Replicate runs in CDC mode and CT tables are enabled, primary keys are replicated as null values in the CT tables.

Base tables are not affected by this.

The Oracle patch from the 19th of October 2023 changed the redo log format around how they log before images. Qlik has issued a patch (2023.5 SP5) to read the new format correctly.

Resolution

A fix has been provided in Qlik Replicate 2023.5 SP5 and 2023.11 SP1.

Download and install one of the provided patches:

• Qlik Replicate 2023.5 SP05
  
  Public download link: https://files.qlik.com/url/qr2023050701sp05 (expires 5/31/2024)
  
  Once this download link expires, a patch will be made available on the Qlik download page.
  
  
• Qlik Enterprise Manager 2023.5 SP05
  
  Included if you are using Qlik Enterprise Manager to match the version with Qlik Replicate.
  
  Public download link: https://files.qlik.com/url/qem2023050614sp05 (expires 5/31/2024)
  
  Once this download link expires, a patch will be made available on the Qlik download page.

Environment

Qlik Replicate May, version 2023.5.0.516 and earlier.

Qlik Replicate 2023.11.0.159 (GA)

Scenario: Qlik Replicate is using a DB2 Z/OS Source Endpoint. During an IPL, tasks fail while connecting to the DB2 instance in the Data Sharing Group.

The errors in the log are:

00124988: 2024-06-20T12:11:18 [TASK_MANAGER ]I: Task error notification received from subtask 0, thread 0, status 1020101 (replicationtask.c:3519) 00124988: 2024-06-20T12:11:18 [TASK_MANAGER ]W: Task 'ECS_GRP2_LS_IC0A_ECS_03' encountered a fatal error (repository.c:6064) 00125214: 2024-06-20T12:11:18 [SOURCE_CAPTURE ]E: Reading IFI packet encountered a fatal error upon UDTF execution. Aborting... [1020101] (db2z_ifi_api.c:405)our prod Logstream and Aurora tasks encountered multiple errors failing with DB2 communication error. Attaching the required logs and diagnostic package. Some of the tasks were able to recover, but two tasks tried to recover and finally errored out. When we tried to resume with resume option they threw an error and were able to resume with locally stored checkpoint option.:: this is what the error looks on simple resume: 00125214: 2024-06-20T12:11:18 [SOURCE_CAPTURE ]E: R4DB2: Instance state has been recreated (r4z2util.c:1294 [20014] (db2z_ifi_api.c:995) 00125214: 2024-06-20T12:11:18 [SOURCE_CAPTURE ]I: IFI306 Skew is 3927874278216977 micro seconds (db2z_endpoint_util.c:510) 00125214: 2024-06-20T12:11:18 [SOURCE_CAPTURE ]I: IFI306 is initialized with buffer size of 262144 bytes (db2z_ifi_cdc.c:113) 00124988: 2024-06-20T12:11:18 [TASK_MANAGER ]I: Task error notification received from subtask 0, thread 0, status 1020101 (replicationtask.c:3519) 00124988: 2024-06-20T12:11:18 [TASK_MANAGER ]W: Task 'ECS_GRP2_LS_IC0A_ECS_03' encountered a fatal error (repository.c:6064) 00125214: 2024-06-20T12:11:18 [SOURCE_CAPTURE ]E: Reading IFI packet encountered a fatal error upon UDTF execution. Aborting... [1020101] (db2z_ifi_api.c:405) 00125214: 2024-06-20T12:11:18 [SOURCE_CAPTURE ]E: db2z_capture_start: Error connecting to IFI306 [1020101] (db2z_endpoint_capture.c:3109) 00125214: 2024-06-20T12:11:18 [SOURCE_CAPTURE ]E: Error executing command [1020101] (streamcomponent.c:2052) 00125214: 2024-06-20T12:11:18 [TASK_MANAGER ]E: Stream component failed at subtask 0, component st_0_cadm_db2_DDFIC0A_0523 [1020101] (subtask.c:1431) 00125214: 2024-06-20T12:11:18 [SOURCE_CAPTURE ]E: Stream component 'st_0_cadm_db2_DDFIC0A_0523' terminated [1020101] (subtask.c:1602)

Resolution

A DB2 configuration parameter (enableAcr) can be added to the db2dsdriver.cfg configuration file, which maintains the connection to DB2 during an IPL when the instance is in a Data Sharing Group.

For more information about enableAcr, see Element in the <acr> section of the db2dsdriver configuration file (Source: IBM.com).

Specifies whether automatic client reroute is in effect. The default is true. If the server accessed is Db2 for z/OS, the enableAcr parameter should be enabled only when the enableWLB parameter is in effect.

Cause

Environment

• Qlik Replicate 
• DB2 Z/OS Source Endpoint

Qlik Data Gateway erroring out with "Invalid metadata response (DirectAccess-2201) " message. The error could be due to a different issues. 

Resolution

• Verify Privileges on the space where the Gateway was created and the Space where user is attempting to create the connection, our documentation describes the details  here
• If privileges are correct, try to restart Qlik Data Gateway services on the Server where it resides.
• Verify if the issue is specific to a connection, if one user was able to successfully create a connection, attempt to create a new connection with the user that is getting the error.
• If nobody is able to create a connection for an specific Data Source, attempt to configure a DSN ODBC connection from the Data Gateway Server to verify it succeeds. 

Cause

The cause of the issue is usually configuration, if the suggestions above didn't fix the issue it will be necessary to report the issue for further analysis. 

Related Content

Qlik Data Gateway: Direct Access Data Gateway failing with Internal Error 

Environment

• Qlik Cloud 
• Qlik Data Gateway 

Qlik Sense upgrade process was completed successfully. However, upon attempting to run Qlik Sense services, the CPU usage spiked to 99% to 100%, rendering the Qlik server unresponsive.

Restarting the Qlik Sense services or rebooting the Qlik Sense Windows machine does not resolve the issue.

The Windows Task Manager on the Qlik Sense Windows machine reveals the Qlik Dispatcher service causes the CPU spike.

This article provides a solution for one possible root cause leading to a high CPU load. Other root causes may exist (such as an unoptimized Qlik Sense document) and will require additional troubleshooting.

Resolution

To resolve this particular root cause:

1. Navigate to C:\Program Files\Qlik\Sense\ServiceDispatcher\ on the Qlik Sense Windows machine
2. Open the services.conf file
3. Verify f you can locate duplicate entries, such as:
   
   
   
   
4. If duplicates are found, delete them.

Note: In a multi-node environment, this verification must be performed on all nodes.

Cause

A duplicated command in the configuration file was leading to multiple instances of the service being triggered, creating an infinite loop. This misconfiguration resulted in an excessive number of threads and consumed all available CPU resources.

Environment

• Qlik Sense Enterprise on Windows 

This article documents the use case scenario of using CVENT Core.

This customization is provided as is. Qlik Support cannot provide continued support of the solution. For assistance, reach out to our Professional Services or engage in our active Integrations forum.

Qlik has used CVENT Core to organize sessions for Qlik Connect, allowing Qlik to extract data hourly into a Qlik Sense app.

Cvent Core uses 2-legged oAuth2. Qlik Application Automation's oAuth2 connectors currently only support 3-legged oAuth2, so I had to work around by connecting straight to Qlik Sense REST Connector, where we could do 2-legged. Here's how we did this. 

Setup

1. Get your credentials, which you need to make the first token call. Make sure you can login to your workspace and create application to get a client_id and client_secret. Have these open and ready to copy. Here's a walkthrough if you're having trouble finding them: Developer quickstart. And send this to your CVENT manager if you can't access: Inviting your developers.
   
   
2. Create the token refresh connector in Qlik Sense and get your first access token. Create new connection > REST API. Use the following details:
   
   
   • URL: https://api-platform.cvent.com/ea/oauth2/token
   • Method: POST (empty body)
   • Check: Add 'Expect: 100-continue' header
   • Authentication Schema: Anonymous
   • Query Parameters:
     • grant_type: client_credentials
     • client_id: {your client id}
   • Query headers:
     • Content-Type: application/x-www-form-urlencoded
     • Authorization: Basic {your client secret}
       
       
3. No need for WITH CONNECTION or Pagination. Test this connection and save it, with a name like CVENT_TOKENREFRESH. Then click to select data on the connector and click the root table.
   
   This lists your first bearer token, which will expire in 60 min, but will be valid long enough to set up your session or attendee connectors. If your token expires, simply "select data" again to regenerate another. We don't need this quite yet, so click cancel for now.
   
   
4.  Add script to regenerate token on app reload to the beginning of your data load script. Add the following to the beginning of your load script, or just before your CVENT data. This script will use the connector you just made to generate the token and save it as a variable "vAuthToken".
   
   

   // Get access token using Basic Auth
   LIB CONNECT TO 'QlikWorld:CVENT_TOKENREFRESH';
   TokenRaw:
   SQL SELECT 
   "access_token"
   FROM JSON (wrap on) "root";
   [Token]:
   LOAD [access_token]
   RESIDENT TokenRaw;
   DROP TABLE TokenRaw;
    let vAuthToken=peek('access_token',-1);
    drop table Token;
   //Trace $(vAuthToken);

5.  Create your CVENT session data connector (or any other connector you need from the APIs, reference here) using the original token. First, click "select data" on your TOKENREFRESH connector to pull up the screenshot above, and click the token to copy it to your clipboard. Then, click Create new connection > REST API and use the following details. For this example I'll create a connector to List Sessions. Many details like the url, method, and pagination can be found in the documentation. 
   • URL: https://api-platform.cvent.com/ea/sessions
   • Method: GET
   • Query parameters: N/A
   • Query headers: 
     • Authorization: Bearer {your first token, copied to clipboard}
     • Accept: application/json
   • CHECK: Allow "WITH CONNECTION"
   • Pagination: 
     • Pagination type: Next token
     • 'Next token' parameter name: token
     • 'Next token' path: root/paging/nextToken
     • Do NOT check "pass via header" or "look in header"
       
       
6. Name, test, and save your connector. Then, select data from the connector and add all your tables.
   
   
7. Adjust connector script to utilize new access token instead of original. Since the session connector was set up with the access token we generated, it will only work for 60 minutes! However, since we checked off "Allow "WITH CONNECTION", we can tell the connector to use a different value for the header / authorization token each time (aka, our vAuthToken variable). Go to your sql script and add the following bolded script, right before the final semicolon (do not add the italic script, that should already be there):
   
   

   FROM JSON (wrap on) "root" PK "__KEY_root"
   WITH CONNECTION (
   HTTPHEADER "Authorization" "Bearer $(vAuthToken)"
   );

8. Load your app and test that this is working as designed. Use this same process of steps 4 and 5 for any other event call you need to make, such as Enrollment, Surveys, and more. 

Limitations

• If you need to add new fields to the {session} connector, you will need to generate another token by "selecting data" from the token refresh connector, copy it, edit the {session} connector to add the new token, saving it, and selecting data from the {session} connector, and add the fields. This makes it challenging to collaborate. Better to just add every field at the beginning when you do setup and drop what you don't need.
• Security issues - if you are using your credentials to generate the token, make sure the connector is in a secure shared space and not somewhere that can be accessed by anyone in your tenant, and mask the variable or do what you need to do there. 
• Writeback: not sure how I will do this since it requires app automation which can't do 2-legged. But I'll update here if we figure it out next year. 

Related Content

• CVENT API Reference
• Qlik Sense REST Pagination
• CVENT pagination post
• CVENT Ouath2

In Replicate Oracle source endpoint there was a limitation:

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

Cause 

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

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

Resolution

1- Error messages of long table name

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

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

2- Error messages of long column name

      There are different messages if the column name exceeds 30 characters

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

Or

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

Or (incomplete WHERE clause)

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

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

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

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

alter system set ENABLE_GOLDENGATE_REPLICATION=true;

    Take notes this is supported when the user has GoldenGate license, and Oracle routinely audits licenses. Consult with the user DBA before alter the system settings.

Environment

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

Related Content 

Internal support case ID: # 00045265.

Qlik Replicate  

Is it possible to disable the Qlik Sense Dev Hub for end-users?

Environment:

Qlik Sense Enterprise on Windows 

Resolution:

It is not possible to disable the Qlik Sense Dev Hub for end users.  This is working as designed. 

The default security rules will restrict access for standards users to read-only. Customization is available using Security Rules in the Qlik Sense Management.

Please refer to the Qlik Sense Online Help for more details. 

How to Connect NPrinting to Single or Multiple Qlik Sense servers:

• You may use this article to connect to NPrinting to a single or multiple QS servers
• In some cases you may wish to connect your NPrinting server to multiple Qlik Sense server environments. The process described in this article will describe how to enable this feature and how to manage possible errors encountered.
• Note that all additional QS servers must be in the same domain as the original QS and NP servers.
• If having issues connecting to multiple Qlik Sense servers, see Qlik NPrinting will not read Qlik Sense certificates

Environments:

• This feature is ONLY available in NPrinting June 2019 release and newer versions.
• NOTE: This feature is not supported in NPrinting April 2019 and earlier versions.

Implement the solution: 

• Go to Qlik Sense QMC>Start>Certificates
• Export the Qlik Sense server certificates from each additional Qlik Sense server.
• Use the NPrinting server computer name  or the Friendly Url Alias of the NPrinting Server as the "Machine Name" that is used to log into the NPrinting Web Console with.
• Export a certificate for both if both addresses below are used to access the NP web console.
  
  • Computer name:  nprintingserverPROD1
  • Friendly URL/Alias address:  Internal.nprintingserver.domain.com

• Select include secret key and
• do NOT include a password when exporting the certificates. See Exporting certificates through the QMC on the Qlik Sense Online Help for details. 

• Navigate to the exported certificates location on the QS server and rename the exported file 'client.pfx' with a suitable name. ie: if your Qlik Sense server name is QS1, prepend the file client certificate file name as follows: QS1client.pfx (Naming of this file should ideally reflect the Qlik Sense server that the file was exported from. You may use however, any name that you wish)
• Copy this file to the NPrinting server path:
  

"C:\Program Files\NPrintingServer\Settings\SenseCertificates"

• Restart all NPrinting services
• If newly installing NPrinting May 2023 IR or later, please check this article if you are having NPrinting Qlik Sense connection failures: NPrinting Qlik Sense Connections Fail with Fresh Install of May 2023 NPrinting and Higher Version

NOTE: Reminder that the NPrinting Engine service domain user account MUST be ROOTADMIN on each Qlik Sense server which NPrinting is connecting to.

Test  access to the additional Qlik Sense server.

• Open the NPrinting Web Console
• Create a new NP App. ie: "NP_App_QSserver-2"
• Create a new NP connection and use the Virtual Proxy Address for the new target Qlik Sense server
• Verify your connection
• Save your connection to load the metadata for the first time.
• Create a test report and preview
• If having issues connecting to multiple Qlik Sense servers, see Qlik NPrinting will not read Qlik Sense certificates

"C:\Program Files\NPrintingServer\Settings\SenseCertificates" 

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

• NPrinting Qlik Sense Connections Fail with Fresh Install of May 2023 NPrinting and Higher Version
• https://help.qlik.com/en-US/nprinting/Content/NPrinting/DeployingQVNprinting/NPrinting-with-Sense.htm
• https://help.qlik.com/en-US/nprinting/February2021/Content/NPrinting/DeployingQVNprinting/NPrinting-with-Sense.htm#anchor-1

A user with a Root Admin role should be able to access all resources and functions in the Qlik Sense Management Console (QMC). In rare instances, the Root Admin will notice some options being unavailable (greyed out) such as Duplicate or Export buttons being disabled.

This applies to Root Admins without professional or analyzer licenses allocated and is seen in environments which were upgraded from previous versions of Qlik Sense. 

Example:

Resolution

1. Log in to Qlik Sense Management Console (QMC). The user must be a Root Admin.
2. Navigate to License management 
3. Open the Analyzer Capacity Rule tab
4. Locate the rule called Security rule for AnalyzerTimeAccessType
   

   This rule is a legacy rule which will only exist if you have upgraded your environment or still are on an older version of Qlik Sense.

5. Modify the rule to exclude Root Admin and apply the change.
   e.g.
   

   ((user.roles!="RootAdmin"))​

Cause 

If the Root Admin user is not allocated professional or analyzer license, analyzer capacity will be applied automatically by "Security rule for AnalyzerTimeAccessType" in old versions of Qlik Sense or if a legacy rule still exists. 

Environment

• Qlik Sense Enterprise on Windows all versions

Inside Qlik Application Automation, the Amazon S3 functionality is split into two connectors: the native Cloud Storage connector and the specific Amazon S3 connector. To create, update, and delete files, it’s highly recommended to use the native Cloud Storage connector. To get the information and metadata of regions & buckets, use the Amazon S3 connector.

The following is an example of automation using the Amazon S3 connector to output a paginated list of regions and buckets in each region (not covered in this article).

Environment:

Qlik Application Automation 

This article focuses on the available blocks in the native Cloud Storage connector in Qlik Application Automation to work with files stored in S3 buckets. It will provide some examples of basic operations such as listing files in a bucket, opening a file, reading from an existing file, creating a new file in a bucket, and writing lines to an existing file.

The Cloud Storage connector supports additional building blocks to copy files, move files, and check if a file already exists in a bucket, which can help with additional use cases. The Amazon S3 connection also supports advanced use cases such as generating a URL that grants temporary access to an S3 object, or downloading a file from a public URL and uploading this to Amazon S3.

Let’s get started.

Authentication for this connector is based on tokens or keys.

Log in to the AWS console with an IAM user to generate the access key ID and secret access key required to authenticate.

Now let's go over the basic use cases and the supporting building blocks in the Cloud Storage connector to work with Amazon S3:

1. How to list files from an existing S3 bucket
   
   
   1. Create an Automation.
   2. From the left menu, select the Cloud Storage connector.
      
      
      
      
   3. Search for the List Files block from the available list of blocks.
      
      
      
      
   4. Drag and drop the block into the automation and connect it to the Start block.
      
      
      
      
   5. The ‘Path’ parameter of this block allows to list the contents of a specific directory from a Dropbox account. In this example, ‘./’ indicates the root directory of your bucket.
   6. Drag and drop the Output block into the automation and connect it to the List Files on Amazon S3 block.
      
      
      
      
   7. Run the automation. If it was not previously saved, a 'Save automation' popup will appear. This will output a paginated list of files available in the root directory of a S3 bucket.
      
      
2. How to open an existing file and read from it
   
   
   1. The first two steps are similar to those described before.
   2. Now use the Open File block from the list.
   3. Drag and drop the block into the automation, link it to the Start block, and fill in the required parameters, ie. Path, Region, and Bucket. You can use ‘do look up’ to search across your S3 account. Add the file directory, filename, and file extension under ‘Path’, e.g. ./4bxH6V4ac9zoAxZU.csv
      
      
      
   4. Drag and drop the Read Data From File block and link it to the previous block. Use the output from the previous block as input.
      
      
      
      
   5. Drag and drop the Output block into the automation before running the automation. This will output a paginated table with the data stored in the file.
      
      
      
      
      
      
3. How to create a new file (if it doesn’t exist and delete it if it does) in the S3 bucket, write lines of data, save, and close the opened file
   
   
   1. The first two steps are similar to the two previous use cases.
   2. Now select the Check If File Exists block, drag and drop it into the automation, and link it to the Start block.
      
      
      
   3. The previous block will return ‘True’ if the file exists, and ‘False’ if it doesn’t. Now search for the Condition block to drag and drop it into the automation. Link it to the previous block and add the following condition:
      
      
      
      
   4. First, let’s focus on the ‘YES’ part of the condition. Search the Delete File block and drag and drop it into the automation. If it already exists, this will delete the specified file inside the S3 bucket.
   5. Hide the ‘NO’ part of the condition and continue building this automation at the loose end of the Condition block (this part executes regardless of the result of evaluating the condition). First, search the Create File block, add it to the canvas, and connect it to the previous block.
   6. Next, search the Write Line to File block and connect it to the Create File on Amazon S3 block. Fill in the required input parameters. Select CSV as ‘Mode’ and specify the ‘Column names’ (i.e. headers) of the file.
      
      
      
      
   7. This example shows how to define ‘Column names’ manually but this operation can also be automated using the Get Keys formula and reading files stored in S3 buckets, lists or objects defined as variable. The same applies to the ‘Data’ input parameter. In this example, one single line of data has been added manually, but we could read data from other data sources (e.g. tables, flat files, etc.) or loop through a list of items, and write each item as a line in the CSV file. It requires additional data transformations though. Check the ‘Csv’ function from the ‘Other Functions’ link.
   8. Finally, search the Save and Close block, and link it to the Write Line to File on Amazon S3 block. Optionally, add the Output block into the automation. This will show the path where the file has been saved and closed on the S3 bucket in Amazon.
      
      
      
      
   9. Optionally, you can add the following building blocks as a continuation of your automation to check the content of the newly created file in the S3 bucket. If the file has been successfully created and written, then this will output its content as rows, otherwise, it’ll output ‘File Not Found’.
      
      

Server Side Encryption update:

The Amazon S3 connector in Qlik Application Automation now supports adding the SSE header value for creating new files. This header is available on the Create File and Copy File blocks. It's possible to choose the default behavior which is AES256 encryption. As an alternative, it's possible to choose aws:kms encryption and provide a valid KMS Key ID.

Attached example file: create_and_write_files_amazon_s3.json

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.

For the Oracle database that integrates with the Avaloq Banking Suite, Qlik Replicate retrieves zero records from the source table during full load (without a warning or error messages). This occurs because the following PL/SQL procedure must be executed to obtain the data:

exec k.session#.open_session();

No mechanism exists to instruct Qlik Replicate to execute the above procedure before sending the query statement to the database.

Resolution

On the Oracle Database, create a trigger that fires after logon and embeds the PL/SQL procedure within it. For example, 

CREATE OR REPLACE TRIGGER AFTER_LOGON_TRIGGER_REPLICATE
　 AFTER LOGON ON DATABASE WHEN (USER = 'QR_ACCOUNT')
　 BEGIN
　　 execute immediate 'call k.session#.open_session()';
　 COMMIT;
END;

Where QR_ACCOUNT is the account name used to access the Oracle database.

Environment

Qlik Replicate all versions

It is possible to export the list of tenatnt users to a .json file using the "user ls" command from the Qlik Command Line Interface (qlik-cli).

Environment

• Qlik Cloud 

Resolution

The scripts provided in this article are provided as they are and they are for guidance only.   

As a tenant admin, download and configure the Qlik-cli 

1. run
   
   

   qlik user ls --limit 1000 > tenantusers.json​

   
   
   (please note: the qlik-cli is able to paginate over the limit of 100 users that concerns the API it uses. So, setting a higher limit will produce the full list of users). 
   
   in a command prompt or UNIX shell. The resulting tenantusers.json file will be saved in the current path for the prompt/shell  and will be in this format, with the roles defined on top and the user details under them.
   
   

   [
     {
       "assignedGroups": [],
       "assignedRoles": [
         {
           "id": "608050f7634644db3678b1a2",
           "level": "user",
           "name": "Developer",
           "type": "default"
         },
         {
           "id": "608050f7634644db3678b17f",
           "level": "admin",
           "name": "TenantAdmin",
           "type": "default"
         },
         {
           "id": "605a1c2151382ffc836af862",
           "level": "user",
           "name": "SharedSpaceCreator",
           "type": "default"
         },
         {
           "id": "605a1c2151382ffc836af866",
           "level": "user",
           "name": "ManagedSpaceCreator",
           "type": "default"
         },
         {
           "id": "605a1c2151382ffc836af86b",
           "level": "user",
           "name": "DataSpaceCreator",
           "type": "default"
         },
         {
           "id": "605a1c2151382ffc836af85d",
           "level": "admin",
           "name": "AnalyticsAdmin",
           "type": "default"
         },
         {
           "id": "605a1c2151382ffc836af85f",
           "level": "admin",
           "name": "DataAdmin",
           "type": "default"
         },
         {
           "id": "63580b8d5cf9728f19217be0",
           "level": "user",
           "name": "PrivateAnalyticsContentCreator",
           "type": "default"
         },
         {
           "id": "6356f0425cf9728f1962b942",
           "level": "user",
           "name": "DataServicesContributor",
           "type": "default"
         }
       ],
       "created": "2020-05-18T09:38:29.214Z",
       "createdAt": "2020-05-18T09:38:29.214Z",
       "email": "martina.testoni@dkdaklaldkdaklladaaddddl.com",
       "id": "USERID1",
       "lastUpdated": "2023-04-04T07:32:00.756Z",
       "lastUpdatedAt": "2023-04-04T07:32:00.756Z",
       "name": "Martina Testoni",
       "picture": "https://s.gravatar.com/avatar/gravatarimage=pg\u0026d=https%3A%2F%2Fcdn.auth0.com%2Favatars%2Fdp.png",
       "preferredLocale": "",
       "preferredZoneinfo": "Europe/Copenhagen",
       "roles": [
         "Developer",
         "TenantAdmin",
         "SharedSpaceCreator",
         "ManagedSpaceCreator",
         "DataSpaceCreator",
         "AnalyticsAdmin",
         "DataAdmin",
         "PrivateAnalyticsContentCreator",
         "DataServicesContributor"
       ],
       "status": "active",
       "subject": "auth0|SUBJECTID2",
       "tenantId": "TENANTID"
     },
     {
       "assignedGroups": [],
       "assignedRoles": [
         {
           "id": "608050f7634644db3678b17f",
           "level": "admin",
           "name": "TenantAdmin",
           "type": "default"
         },
         {
           "id": "605a1c2151382ffc836af86b",
           "level": "user",
           "name": "DataSpaceCreator",
           "type": "default"
         },
         {
           "id": "608050f7634644db3678b1a2",
           "level": "user",
           "name": "Developer",
           "type": "default"
         },
         {
           "id": "605a1c2151382ffc836af866",
           "level": "user",
           "name": "ManagedSpaceCreator",
           "type": "default"
         },
         {
           "id": "63580b8d5cf9728f19217be0",
           "level": "user",
           "name": "PrivateAnalyticsContentCreator",
           "type": "default"
         },
         {
           "id": "605a1c2151382ffc836af862",
           "level": "user",
           "name": "SharedSpaceCreator",
           "type": "default"
         },
         {
           "id": "6356f0425cf9728f1962b95c",
           "level": "user",
           "name": "Steward",
           "type": "default"
         },
         {
           "id": "605a1c2151382ffc836af85d",
           "level": "admin",
           "name": "AnalyticsAdmin",
           "type": "default"
         },
         {
           "id": "62bb165356d1879582c1b468",
           "level": "admin",
           "name": "AuditAdmin",
           "type": "default"
         },
         {
           "id": "605a1c2151382ffc836af85f",
           "level": "admin",
           "name": "DataAdmin",
           "type": "default"
         }
       ],
       "created": "2023-03-31T08:44:37.332Z",
       "createdAt": "2023-03-31T08:44:37.332Z",
       "email": "Gentile.Faccenda@dkdaklaldkdaklladaaddddl.com",
       "id": "USERID2",
       "lastUpdated": "2023-04-03T11:24:35.037Z",
       "lastUpdatedAt": "2023-04-03T11:24:35.037Z",
       "name": "Gentile Faccenda",
       "picture": "https://s.gravatar.com/avatar/randomurl=https%3A%2F%2Fcdn.auth0.com%2Favatars%2Fdp.png",
       "roles": [
         "TenantAdmin",
         "DataSpaceCreator",
         "Developer",
         "ManagedSpaceCreator",
         "PrivateAnalyticsContentCreator",
         "SharedSpaceCreator",
         "Steward",
         "AnalyticsAdmin",
         "AuditAdmin",
         "DataAdmin"
       ],
       "status": "active",
       "subject": "auth0|IDPSUBJECT2",
       "tenantId": "TENANTID"
     }
   ]
   

    

2. Use any tool you like (online or not) to convert the json to csv, xlsx or other format. 
   
   In a PowerShell you can generate the users natively in csv format by using:
   
   

   qlik user ls --limit 1000 | ConvertFrom-Json | ConvertTo-Csv > tenantusers.csv​

Related Content

This article documents how to convert a Direct Task into a Log Stream Task without reloading the target.

Resolution

1. Stop the Direct task (Oracle-SQLServer)
   
   
2. Create a new Log Stream (Parent Task)
   
   In our example, the task is called Logstream_new
   
   In Parent Task (a), add same source (b) and same table(s) (c) as the ones used it in Direct Task and run the task
   
   
   
   
3. Create a Dummy Replication (Child) task 
   
   In our example, the task is called Child_Task_new
   
   
   
   
4. Open Qlik Enterprise Manager 
   
   
5. Select the Direct Task (Oracle-SQL Server)
   
   
6. Click Export Task > With Endpoints Export
   
   This exports the Direct Task JSON
   
   
   
   
7. Repeat the export for the Child Task, exporting the JDSON With Endpoints
   
   
   
   
8. Open the Child Task JSON and copy all instances of the Source name 
   
   In our example, this is Duplicate_Oracle_Src
   
   
   
   
   
9. Open the Direct task (Oracle-SQLServer) JSON and replace all instances of the source name with child task source name 
   
   In our example, this is Duplicate_Oracle_Src
   
   
   
   
10. Import the Direct Task JSON (Oracle-SQLServer).
    
    
11. Go to Qlik Replicate Server and Open the Oracle-Sqlserver Task and click on Source endpoint 
    
    
12. Enable Read changes from log stream staging folder and select your Staging directory in Log stream staging task
    
    

    Re-enter the Password

    
    
    
13. Start the task using the Advanced Run (a) option and select (b) Source change position: 0 
    
    
    

Environment

• Qlik Replicate 
• Qlik Enterprise Manager 

While working with Google Cloud BigQuery as the target database, the task reports errors during the target apply stage.

The messages in task log file:

2024-08-01T11:22:33:395316 [TARGET_APPLY    ]E:  RetCode: SQL_ERROR  SqlState: 42000 NativeError: 70 Message: [Simba][BigQuery] (70) Invalid query: Unrecognized name: JW_L0 at [1:2853] [1022502]  (ar_odbc_stmt.c:3974)

2024-08-01T11:22:33:433379 [TARGET_APPLY    ]E:  Failed to prepare statement 'UPDATE `JW_L0`.`ACCOUNTS` SET ...
2024-08-01T11:22:33:762878 [TARGET_APPLY    ]E:  Failed to get bulk update statement for table 'JW_L0'.'ACCOUNTS' [1022517]  (odbc_util.c:4424)

Environment:

• Qlik Replicate All versions
• Google Cloud BigQuery All versions

Resolution

Enable the Apply changes using SQL MERGE tuning option:

1. Open Task Settings 
2. Go to Change Processing 
3. Go to Change Processing Tuning 
4. Find Miscellaneous Tuning and enable Apply changes using SQL MERGE 

For more information and details regarding this tuning option, see Apply changes using SQL MERGE.

Qlik has observed the following error while working with Databricks Delta Lakehouse endpoint in Replicate 2023.05 or above. This Technical Advisory article aims to create awareness of the issue and provides a workaround. 

[DEFAULT_FILE_NOT_FOUND] It is possible the underlying files have been updated. You can explicitly invalidate the cache in Spark by running 'REFRESH TABLE tableName' command in SQL or by recreating the Dataset/DataFrame involved. If disk cache is stale or the underlying files have been removed, you can invalidate disk cache manually by restarting the cluster.

This causes the table to be suspended and requires a reload.

Environment:

• Qlik Replicate 2023.05 and later

Resolution

Fix Version:

A fix is currently being worked on and patches will be made available at the earliest.

Workaround:

1. Go to the Databricks Endpoint
2. Open the Advanced tab
3. Open Internal Paramters
4. Add the parameter: additionalConnectionProperties
5. Set the value toForceSynchronousExec=1
   
   
   
   
6. Save the Endpoint
7. Stop and Resume the task

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

Cause

Product Defect ID: QB-28166

This video will demonstrate how to install and configure Qlik-CLI for SaaS editions of Qlik Sense.

Content:

• Requirements
• Installation method One
• Installation Method Two
• Enable the Completion Feature (optional, but useful)
• Use Qlik-CLI
• Related Content
• Transcript

Requirements

• The user has access to a Qlik Cloud tenant
• The user has a professional license assigned in the tenant
• The user has the developer role assigned (required for API access)
• An API key can be obtained

Installation method One

1. Download Qlik-CLI from GitHub
2. Copy the executable to a local directory
3. Add the Qlik-CLI as an Environment Variable Path. 
   1. Open your Windows Control Panel
   2. Navigate to System 
   3. Open Advanced system settings in the leftmost menu
   4. Click Environment Variables 
   5. Locate Path in the System variables 
   6. Click Edit
   7. Click New
   8. Add the path to Qlik-CLI for example C:\Tools\Qlik-QLI\
   9. Confirm with OK until all windows are closed
4. Confirm the Qlik-CLI location path to verify it the installation completed successfully 
   1. Open PowerShell
   2. Execute:
      

      get-command qlik​

   3. Closer PowerShell

Installation Method Two

1. Download and install Chocolatey as documented on chocolatey.org.
2. Open PowerShell
3. Execute:
   

   choco install qlik-cli

4. This completes the installation.

Enable the Completion Feature (optional, but useful)

1. Verify if a PowerShell profile exists or create a new one
   1. Open PowerShell
   2. Execute:
      

      if ( -not (Test-Path $PROFILE) ) {
         echo "" > $PROFILE
      }​

2. Open the profile with the command notepad $PROFILE:
   

   qlik completion ps > "./qlik_completion.ps1" # Create a file containing the powershell completion.
   . ./qlik_completion.ps1                      # Source the completion.​

3. Save it. 
4. Restart PowerShell

Use Qlik-CLI

Advanced and additional instructions as seen in the video can be found at Qlik-CLI on Qlik.Dev. Begin with Get Started.