Instead of getting a bit of a sleep in and then making my way out of bed to make my wife “Mother’s Day Breakfast in Bed” (i.e. An absurd of bacon on toast with an egg perched on top) – I get a call from our first responders saying that after Windows patching he could not get back onto Qlik Enterprise manager to restart our Qlik tasks.
As I grumbled out of bed, I was hoping it was something simple like a Windows Defender firewall getting turned back on.
Little did I know that I ended up working on the problem most of the day; meaning my wife missed her breakfast in bed, brunch in bed, lunch in bed and wine in bed.
Starting from the start
Our Midrange team washed their hands of the issue and it was over to us to get Qlik Enterprise Manager
Logging into my PC and going to the usual QEM address; I got greeted with the Chrome error of:
ERR_CONNECTION_CLOSED
Rightio.
To rule out the usual suspects of browser or VPN; I tried (sigh) Microsoft Edge and also Chrome from a virtual machine that was located in the internal network.
Again returned that ERR_CONNECTION_CLOSED error message
I next tried logging on to the QEM server itself. The server has Chrome installed on it – useful for testing for firewall or DNS problems.
I tried:
Using the DNS name
Using the IP address
Using localhost
This time I got a different error message:
ERR_CONNECTION_RESET
Off to Qlik for help
I raised a case with Qlik Support for help. They speculated it was a DNS issue – which didn’t make sense to me. If it was a DNS issue – I would have expected the IP address to work.
I still had to answer a lot of questions and run lots of nslookup commands. No – the DNS name hasn’t changed. No – the IP address hasn’t change
Nothing really pointed to where the issue was originated from.
SkyNet to the rescue
While waiting for Qlik to get back to me and since we have access to SkyNet Google Gemini Enterprise – curiously I asked the Gen AI.
In a browser on the server myqemaddress; I am trying access Qlik Enterprise manager on address https://myqemaddress/attunityenterprisemanager/ but getting an error ERR_CONNECTION_RESET.
What could be the reason I am getting this error message?
It returned the two usual suspects:
Service is down (Checked – no errors in the Enterprise manager logs)
Proxy issue (We don’t have a proxy between QEM and the clients)
But the third suggestion was interesting:
3. Verify Port Binding and SSL
Enterprise Manager enforces HTTPS (defaulting to port 443).
If the SSL certificate is not correctly bound to the port, the connection will reset.
Check if the port is listening: Open a command prompt as Administrator and run: netstat -ano | findstr :443 If you don’t see a listener, the service hasn’t claimed the port.
Verify SSL Binding: Run: netsh http show sslcert | findstr :443 If no certificate is listed, the HTTPS setup is incomplete.
netsh http show sslcert | findstr :443
IP:port : [::]:443
I ran the same command on our DEV Enterprise manager server that was working and got:
So prod was missing the wildcard address of 0.0.0.0:443
I added it back in using the guide that we have for updating certificates and it worked. Thank you SkyNet Google Gemini Enterprise! You saved the rest of Mother’s day
Why was the SSL binding deleted?
Well that is the question that I’d like to know.
I could see in the logs that the certificate was deleted when patching was happening:
I sent an incident ticket off to Midrange. So far I haven’t heard back from them.
But at least QEM is up and running and the issue is documented so that if you come to this page in desperation; you can get QEM up and running as well and enjoy Mother’s day.
Watching my usual favourite recipe channels on YouTube; I was suggested a video “Why Restaurant Ragu Tastes So Much Better”. I usually avoid clickbaity type titles, but with Bolognese/Ragu been one of my favourite dishes I curiously clicked on it.
And wow.
It was a complete eyeopener with new ingredients and techniques that I had to try.
Racing down to the store next day – I brought the ingredients that I was missing and cooked it that afternoon.
And it was going so well – every key point I was tasting and been blown away on how rich and meaty the ragu was tasting.
Until adding the “Gastrique.”
It was a vinegar and sugar slurry that you add right at the end. I should have trusted my instinct and used just a little touch; but my wife goes, “Follow the recipe and add it all in.”
The Gastrique overpowered the dish. It was still tasty; but it was so much better before I added it in.
But I learnt so much and I will never look at spag bol the same again.
Original recipe
My version
This is my working version that I have modified from the original version. I will hone in the ingredient and steps on subsequent attempts.
Bold is something I have added from the original recipe Strikethrough is something I have changed from the original recipe
Ingredients
1 kg beef mince (50% shin, 50% chuck)
400 g pork belly, diced
100 g pancetta, diced
200 g onion, finely diced
150 g carrots, finely diced
150 g celery, finely diced
100 g Mushrooms, finely diced
1–2 garlic cloves, finely grated
Extra virgin olive oil
Neutral oil
80 g tomato purée one can of crushed tomatoes reduced down to a purée
400 g passata
200 ml red wine
150 ml Whole milk (added in two stages)
~500 ml chicken or beef stock
1 Parmesan rind
2 bay leaves
Pinch of nutmeg
Salt, to taste
Pepper, to taste
To finish
Cold butter
Extra milk
Gastrique: 100 g balsamic vinegar + 50 g sugar 50 g balsamic vinegar + 25 g sugar
Method
On low heat, add olive oil and pancetta to a heavy pot and slowly render the fat.
Add onion, carrot, mushroom and celery with a pinch of salt.
Sweat gently until soft, pale and sweet, no colour.
Grate in the garlic for the final 1–2 minutes, then remove the soffrito and set aside.
Return the pot to high heat with a little neutral oil.
Add the beef mince, breaking it up and cooking hot to drive off moisture.
Once dry, lower the heat slightly and caramelise the beef slowly until deeply browned, scraping the fond regularly.
Add the pork belly and gently warm through — no heavy browning.
Stir in the tomato purée and cook until it darkens and caramelises.
Lower the heat and add the first portion of milk. Let it fully absorb into the meat.
Deglaze with red wine and reduce by about half, scraping the pan clean.
Return the soffrito to the pot, then add passata, stock, Parmesan rind, bay leaves and a pinch of nutmeg.
Bring to a simmer, cover, and cook in a low oven at 120°C for 4½ hours, stirring occasionally.
While cooking, make a gastrique by boiling the vinegar and sugar briefly until lightly thickened.
Once cooked, remove the bay leaves and Parmesan rind.
Finish with a splash of milk, cold butter for gloss, and the gastrique added gradually to balance acidity.
It is a new day and a new target endpoint in Qlik Replicate for us.
What is different; it is a new target endpoint – Big Query; which we have not used before.
Usually for our Date Lake, the steps are Source System -> Qlik Replicate -> JSON files -> GCS -> DBT -> Big query.
But this use case was different, a more tactical solution to get some tables off a legacy Data Warehouse straight into Big Query and bypassing the GCS and DBT combination.
Sharing Credentials.
In our organisation credentials are stored in their own project and are separated from other GCP resources. This is so that the credentials are centrally located in one project and we are not duplicating credentials across multiple projects.
The Cloudies created a Service Account for us in project_x and pointed to a “scratchpad” dataset in project_y for Qlik Replicate to create the tables and copy across the data. We can then use the generated table schema to generate tables subsequent environments where Qlik doesn’t have create permissions.
But the problem was that the credentials were in project_x; but I could not force Qlik Replicate to write to project_y. Initially I thought I needed to prefix the schema with the project; like how it appears in big query:
SELECT * FROM `project_y.scratchpad.my_table`
This didn’t work and looking at the log file with verbose turned up; I could still see it was trying to write to project_x.
While I was waiting for replies on the forum and trying a couple of suggestions; I had a look at the credential json that I downloaded from Secrets manager:
We did an initial run in development as a POC and to shake out any permission issue. So far; no issues and the destination tables were created and written to in Big Query. Once we are a bit more stable; I will do a couple of rough benchmarks between writing to Big Query vs to GCS with json.
The Cloudies and Data Lake developers are now trying to work out what is the best way to manage the permissions by their infrastructure code in higher environments. In the meantime, I am modifying our QR task migration pipeline to have a variable destination table schema as the schema (which relates to dataset in Big Query) will change moving from environment to environment. It should be easy to do since we have python wrapper classes over the top of the Qlik Replicate json code. It will just be a new method to change table owners to a passed in variable.
Just as a safeguard I also did a google search and a forum search to see if anything else turned up.
Again nothing.
I pinged back to my manager.
“QR should capture deletes simply fine. Where did you hear that QR had problems with MongoDB deletes from?”
“Oh. The project manager heard from a Mongo guy that Qlik does not work.”
I bit my lip. To me that seemed the same as getting health advice off a random guy at the pub who saw something on Telegram.
But I realised now that even though I had official Qlik documentation backing me up; the burden of proof was back on me.
Back into the world of containers
Qlik Replicate in a docker container has been an extremely useful tool on my Linux development machine. It enables me to quickly test various aspects of QR without interrupting the official dev environment; or fighting with the cloud team to get a proof-of-concept source system set up.
First, I had to set up a MongoDB docker container. Specifically, the MongoDB had to have a “replica”; a standalone wouldn’t work. Since this was a POC; I didn’t need a fully fledge – multi replica – ultra secure cluster; just something simple and disposable.
The great thing is that we don’t have to mess around installing extra ODBC drivers that can make the Qlik Replicate docker image complex.
So with the MongoDB and the Qlik Replicate docker containers up and running; we can how create a new endpoint with the following settings:
Field
Variable
MongoDB Deployment
Standard
Hosts
host.docker.internal
Authentication Method
SCRAM-SHA-256
Username
root
Password
root
Authentication database name
admin
A test connection confirmed that the connection was working as expected.
Proving that deletes are working
Getting close to resolving the burden of proof. I got our GenAI to create a simple python script to add, update, and delete some data from the Mongo Database and set it running:
import pymongo
import random
import string
import time
def generate_random_string(length=10):
"""Generate a random string of fixed length."""
letters = string.ascii_lowercase
return ''.join(random.choice(letters) for i in range(length))
def add_random_data(collection):
"""Adds a new document with random data to the collection."""
new_document = {
"name": generate_random_string(),
"age": random.randint(18, 99),
"email": f"{generate_random_string(5)}@example.com"
}
result = collection.insert_one(new_document)
print(f"Added: {result.inserted_id}")
def update_random_data(collection):
"""Updates a random document in the collection."""
if collection.count_documents({}) > 0:
# Get a random document using the $sample aggregation operator
try:
random_doc_cursor = collection.aggregate([{"$sample": {"size": 1}}])
random_doc = next(random_doc_cursor)
new_age = random.randint(18, 99)
collection.update_one(
{"_id": random_doc["_id"]},
{"$set": {"age": new_age}}
)
print(f"Updated: {random_doc['_id']} with new age {new_age}")
except StopIteration:
print("Could not find a document to update.")
else:
print("No documents to update.")
def delete_random_data(collection):
"""Deletes a random document from the collection."""
if collection.count_documents({}) > 0:
# Get a random document using the $sample aggregation operator
try:
random_doc_cursor = collection.aggregate([{"$sample": {"size": 1}}])
random_doc = next(random_doc_cursor)
collection.delete_one({"_id": random_doc["_id"]})
print(f"Deleted: {random_doc['_id']}")
except StopIteration:
print("Could not find a document to delete.")
else:
print("No documents to delete.")
if __name__ == "__main__":
print("Starting script... Press Ctrl+C to stop.")
try:
client = pymongo.MongoClient("mongodb://root:root@localhost:27017/?authSource=admin")
db = client["random_data_db"]
collection = db["my_collection"]
# The ismaster command is cheap and does not require auth.
client.admin.command('ismaster')
print("MongoDB connection successful.")
except pymongo.errors.ConnectionFailure as e:
print(f"Could not connect to MongoDB: {e}")
exit()
while True:
try:
add_random_data(collection)
add_random_data(collection)
add_random_data(collection)
update_random_data(collection)
delete_random_data(collection)
# Wait for five seconds before the next cycle
time.sleep(5)
except KeyboardInterrupt:
print("\nScript stopped by user.")
break
except Exception as e:
print(f"An error occurred: {e}")
break
# Close the connection
client.close()
print("MongoDB connection closed.")
With the script running and adding, updating and deleting data to the MongoDB; a QR task can be created to read from the database “random_data_db” and collection “my_collection”
And voilà. Data is flowing through Qlik Replicate from the MongoDB.
And more importantly deletes are getting picked up as expected.
And voilà. Data is flowing through Qlik Replicate from the MongoDB.
And more importantly; deletes are getting picked up as expected.
If you have come to my humble website after googling “Can Qlik Replicate replicate deletes from a MongoDB?” well, the answer is “Yes – Yes it can.”
I can only speculate where that rumour came from. Maybe it was from an older version of QR or MongoDB that the person in question was referring to? Or some very abnormal setup of a MongoDB cluster?
Or some complete misunderstanding.
Anyway, I have meeting in the next hour or two with the project team; let us find out.
With the post festive system haze still on – I spent a full day trying to work out another encoding problem that came up in our exception table.
Let me take you on a journey.
It starts with MS-SQL.
We have Qlik Replicate replicating data from a DB2/zOS system into a MS-SQL system. I was investigating why there were inserts missing from the target table and exploring the dbo.attrep_apply_exceptions table I notice a bunch of INSERTS from another table in the exception table:
/*
CREATE TABLE dbo.destination
(
add_dte numeric(5, 0) NOT NULL,
chg_dte numeric(5, 0) NOT NULL,
chg_tme varchar(6) NOT NULL,
argmt varbinary(10) NOT NULL,
CONSTRAINT pk_destination PRIMARY KEY CLUSTERED
(
argmt
) WITH (DATA_COMPRESSION = ROW) ON [PRIMARY]
) ON [PRIMARY]
*/
INSERT INTO [dbo].[destination] ( [add_dte],[chg_dte],[chg_tme],[argmt]) VALUES (46021,46021,'151721', 0x35333538C3A302070000);
I thought it would be a good place to start by substituting the failed insert into a temp table and using the temp table to investigate downstream data flows.
So:
DROP TABLE IF EXISTS ##temp_destination;
SELECT *
INTO ##temp_destination
FROM [dbo].[destination]
WHERE 1 = 0;
INSERT INTO ##temp_destination ( [add_dte],[chg_dte],[chg_tme],[argmt]) VALUES (46021,46021,'151721', 0x35333538C3A302070000);
But as I was substituting the temp table into objects downstream – I was just getting logical errors. Comparing the temp table against other data that already existed dbo.destination; it looked not right.
Working Upstream
I thought it would be a good place to start by substituting the failed insert into a temp table and using the temp table to investigate downstream data flows.
Since we just merged a system into our DB2/zOS system; my initial thought it was a corrupted record coming from a merge process. I contacted the upstream system owners and asked for help.
They came back and said the record was fine and the issue must be in the QR system converting the data wrong. This did not make sense – if there was a issue in the QR system; it would have been picked up in testing. Also, there would be lots of exceptions in dbo.attrep_apply_exceptions. I had confidence that QR was handling it right.
Not trusting what the DB2/zOS team had told me; I created a temp Qlik task to dump out that table into a test database.
Using the other values in the record; I could look the record up in the table and compare the two.
For the field argmt it came back as:
0xF5F3F5F846022F00000F
This looked nothing like what was in the exception table.
EBCDICy epiphany
Of course – it took a while; but it finally struck what was going on.
The field argmt had sections encoded in EBCDIC. This was getting brought across as bytes to MS-SQL. There was a downstream trigger that was failing in the MS-SQL database. But when it was getting written out into the exception table; it was getting written as ASCII (or possibly Windows-Latin) encoding.
(Only showing start of bytes as this is what we use downstream)
With me just copying the insert statement and using it as is; I was treating ASCII bytes as EBCDIC; causing all sorts of logical errors downstream.
Where to from here?
I’m going to raise this abnormality with Qlik. I am not sure if it is a “bug” or a “feature”; but good to get clarification on that point.
I also will need to educate our users on how to interpret the values in dbo.attrep_apply_exceptions so not to stumble into mistakes that I did.
As for fixing the data itself – since we have the full dump of data from the table and the data is slow moving; I can substitute the values from the data dump into the problem INSERT statement.
To make our lives easier migrating Qlik Replicate tasks from one environment to another; I am looking how to migrate Qlik Replicate schedules.
So far by GitLab pipelines we can migrate tasks and endpoint; but schedules we must do manually. Quite often we forget to manually create the schedules as they are not as obvious component compared to tasks and endpoints. If you move to a new environment and your task is not there, well that is a no brainer. If your endpoint is not there; your task will not migrate.
But schedules are something that lurks in the background; inconsistently created when we rush to put them in when we realise the Qlik task did not run when it was meant to.
Plan
Using the python API, we can export all the items from a Qlik Replicate server with the export_all method. In the returned Json file will be the schedule details like this:
The idea is to modify the Json for the schedule to match the environment we are migrating to; put the schedule into a blank server Json template and use the import_all
method to upload the new schedules to the destination server.
The Cron does not look right…
Looking over the exported Json; the cron syntax did not look right. It had six fields instead of the expected five.
"schedule": "0 1 * * * *"
This confused me for a while as I haven’t come across a cron like this before.
After coming up with no results searching Qlik’s documentation; I created some test schedules to try and determine what the sixth field is used for.
Ahh. After a short while I found the answer.
If the schedule is a “Once off” run; the sixth field is used for “Year”.
I think as Qlik Replicate developers; we have all been here before.
The testers berating you through MS Teams saying, “We have made a change, and we cannot see it downstream! It is all Qlik Replicate’s fault!”
Opening the task and checking the monitoring screen – I can see the change against the table. What are they going on about? Qlik has picked up something; why can’t they see it? (Sixty percent of the time; something downstream has failed, twenty percent of the time something upstream has failed, nineteen percent of the time they are looking in the wrong spot and the remaining one percent of the time – well we won’t mention that one percent.)
But looking at the monitoring screen – what does those numbers mean? Also, if you look in the analytics database; what does those figures mean?
Hopefully, this article will help you understand the monitoring and analytics numbers with some simple examples.
Filters – why does it have to be filters?
We have two main types of Qlik Replicate tasks. One type grabs all changes from a particular table and sends it to our Data Lake in micro batches of fifteen minutes.
The other type are our speed tasks; only grabbing changes on specific columns on a table. To limit QR to only picking up specific changes; we have filters like this:
($AR_H_OPERATION != 'UPDATE' AND $AR_H_OPERATION != 'DELETE') OR
(
($AR_H_OPERATION == 'UPDATE') AND
(
( $BI__FIELD_1 != $FIELD_1 ) OR ( $BI__FIELD_1 IS NULL AND $FIELD_1 IS NOT NULL ) OR ( $BI__FIELD_1 IS NOT NULL AND $FIELD_1 IS NULL ) OR
( $BI__FIELD_2 != $FIELD_2 ) OR ( $BI__FIELD_2 IS NULL AND $FIELD_2 IS NOT NULL ) OR ( $BI__FIELD_2 IS NOT NULL AND $FIELD_2 IS NULL ) OR
( $BI__FIELD_3 != $FIELD_3 ) OR ( $BI__FIELD_3 IS NULL AND $FIELD_3 IS NOT NULL ) OR ( $BI__FIELD_3 IS NOT NULL AND $FIELD_3 IS NULL )
)
)
So, the question is – if we made an update to $FIELD_4 meaning that QR will send the change downstream; what would the monitoring tab on the task display?
Let is create an example to find out.
An example
Here is a simple table -pre-populated with six record:
CREATE TABLE dbo.CDC_ANALYTICS_EXAMPLE
(
RECORD_ID INT IDENTITY(1,1) PRIMARY KEY,
DATA_INT INT,
DATA_VARCHAR VARCHAR(100),
INCLUDE_RECORD CHAR(1)
);
GO
INSERT INTO dbo.CDC_ANALYTICS_EXAMPLE VALUES(1, 'Record 1', 'Y');
INSERT INTO dbo.CDC_ANALYTICS_EXAMPLE VALUES(2, 'Record 2', 'Y');
INSERT INTO dbo.CDC_ANALYTICS_EXAMPLE VALUES(3, 'Record 3', 'Y');
INSERT INTO dbo.CDC_ANALYTICS_EXAMPLE VALUES(4, 'Record 4', 'N');
INSERT INTO dbo.CDC_ANALYTICS_EXAMPLE VALUES(5, 'Record 5', 'N');
INSERT INTO dbo.CDC_ANALYTICS_EXAMPLE VALUES(6, 'Record 6', 'N');
SELECT *
FROM dbo.CDC_ANALYTICS_EXAMPLE WITH (NOLOCK);
And we create a simple QR task with the following filters:
The first filter is the Fullload Passthru Filter. When the full load initially runs; only three of the six records will be brought across.
The second filter Record Selection Condition; should filter any data changes on when $INCLUDE_RECORD has a value of ‘Y’.
Results after running the QR task
Full load
After running the full load; qlik shows the following on the monitoring screen:
Three records – that’s what we expected out of the six.
Interesting under the “Total Completion” section; the distinction of the filter is quite clear. Three records brought across and three remaining.
Change Processing
Let’s make some changes to the data in our test table:
-- This will be captured
BEGIN TRANSACTION
DECLARE @IDENTITY INT;
INSERT INTO dbo.CDC_ANALYTICS_EXAMPLE VALUES(null, 'Record x', 'Y');
SET @IDENTITY = @@IDENTITY;
UPDATE dbo.CDC_ANALYTICS_EXAMPLE
SET DATA_INT = @IDENTITY,
DATA_VARCHAR = 'Record ' + CAST(@IDENTITY AS varchar)
WHERE RECORD_ID = @IDENTITY;
DELETE dbo.CDC_ANALYTICS_EXAMPLE WHERE RECORD_ID = @IDENTITY;
COMMIT;
GO
-- This will be excluded
BEGIN TRANSACTION
DECLARE @IDENTITY INT;
INSERT INTO dbo.CDC_ANALYTICS_EXAMPLE VALUES(null, 'Record x', 'N');
SET @IDENTITY = @@IDENTITY;
UPDATE dbo.CDC_ANALYTICS_EXAMPLE
SET DATA_INT = @IDENTITY,
DATA_VARCHAR = 'Record ' + CAST(@IDENTITY AS varchar)
WHERE RECORD_ID = @IDENTITY;
DELETE dbo.CDC_ANALYTICS_EXAMPLE WHERE RECORD_ID = @IDENTITY;
COMMIT;
This is what the monitoring screen shows:
Looking at the Recent activity screen; hmm – this could lead to some confusion.
The metrics shows the two blocks of changes; even though one block was filtered out. So this is indicating that this gives the results when there is a change in the table; not matter if it was subsequently filtered out.
If those testers in the opening paragraph created a record that was filtered out in QR; we could give them false direction if we said “it should be downstream”
If we look at the Aggregates screen; it paints a true picture of what was detected and transferred to the target:
We can see the three records in the “Total Changes Applied” column.
Analytics database
From the analytic database:
SELECT
*
FROM public.aem_taskv t
WHERE
t.task_name IN ('TEST_ANALYTICS')
ORDER BY
t.server_name, t.task_name, t.retrieval_time
Field
Value
id
110876201
retrieval_time
2025-12-03 12:02
server_id
5
task_name_id
914
task_state_id
3
task_stop_reason_id
1
task_profile_id
1
cdc_evt_applied_insert_count
1
cdc_evt_applied_update_count
1
cdc_evt_applied_delete_count
1
cdc_evt_applied_ddl_count
0
full_load_tables_completed_count
1
full_load_tables_loading_count
0
full_load_tables_queued_count
0
full_load_tables_with_error_count
0
full_load_total_records_transferred
3
full_load_est_records_count_for_all_tables
6
full_load_completed
1
full_load_start
2025-12-03 10:57
full_load_finish
2025-12-03 10:57
full_load_thrput_src_thrput_records_count
0
full_load_thrput_src_thrput_volume
0
full_load_thrput_trg_thrput_records_count
0
full_load_thrput_trg_thrput_volume
0
cdc_thrput_src_thrput_records_count
0
cdc_thrput_src_thrput_volume
0
cdc_thrput_trg_thrput_records_count
0
cdc_thrput_trg_thrput_volume
0
cdc_trans_read_rollback_count
0
cdc_trans_read_records_rollback_count
0
cdc_trans_rollback_change_volume
0
cdc_trans_applied_transactions_in_progress_count
0
cdc_trans_applied_records_in_progress_count
0
cdc_trans_applied_comitted_transaction_count
2
cdc_trans_applied_records_comitted_count
6
cdc_trans_applied_volume_committed
0
cdc_trans_read_memory_events_count
0
cdc_trans_read_swapped_events_count
0
cdc_trans_applied_memory_events_count
0
cdc_trans_applied_swap_events_count
0
cdc_source_latency
0
cdc_apply_latency
0
memory_usage_kb
0
disk_usage_kb
0
cpu_percentage
0
data_error_count
0
task_option_full_load_enabled
1
task_option_apply_changes_enabled
0
task_option_store_changes_enabled
1
task_option_audit_changes_enabled
0
task_option_recovery_enabled
0
cdc_trans_read_in_progress
2
server_cpu_percentage
0
machine_cpu_percentage
16
tasks_cpu_percentage
0
server_name
xxxx
task_name
TEST_ANALYTICS
server_with_task_name
xxxx::::TEST_ANALYTICS
A couple of take aways from the results:
Unfortunately the records in the the analytic database is grouped up at a task level; not at an individual table level. This makes it harder to determine if a change from a particular table was applied
cdc_trans_applied_records_comitted_count is a count of all the records read; but not necessarily applied
If you want the number of records flowing down to downstream; add together the fields cdc_evt_applied_insert_count, cdc_evt_applied_update_count and cdc_evt_applied_delete_count together
Final thoughts
The monitoring and analytics screen is useful tools for diagnosing QR tasks – and general ASMR; watching the numbers bounce up as transactions flow through the system.
Understanding the figures of what they translate to – whether changes getting detected or applied downstream is fundamental in diagnosing problems.
Nothing is better than getting access to the source and target systems and checking yourself to confirm if things are working correctly; but the monitoring screen is a good place to start investigating.
The formation of a Lutheran congregation at Castlemaine in Victoria, Australia (1861 – 1940)
The history of the Lutheran congregation in Castlemaine is strongly linked to the history of the Bendigo congregation. In 1856 a congregation of Lutherans was formed in Bendigo in communion with the Evangelical Lutheran Church of Victoria.
The beginnings of the Lutheran congregation in Castlemaine must have dated to a similar time, as in 1861 a Lutheran Church was constructed in Castlemaine at the corner of Hargraves and Parker Street. A notice in the Mount Alexander Mail, on 19th July 1861, recorded the laying of the foundation stone for the building.
Today no sign of the original church building remains, with residential dwellings located in the area where it once stood. It is understood that the building was destroyed by fire in 1919. The Castlemaine Art Museum records the existence of a photo of the church in its archives, and I have requested a digital copy of the photo. Considering the time period, it is likely that the building was a fairly modest one.
A news article, from the Mount Alexander Mail, dated 29th May 1875, indicates a manse was located at the same site. The original dwelling, although altered and fully renovated, still exists today at 99 Hargreaves St.
In 1862 Pastor Friedrich Munzel arrived in Australia and took up the role of Pastor for Castlemaine, Maldon and Yandoit. In the same year Pastor Munzel, who resided in Castlemaine, was requested to include Bendigo into his sphere of labour.
He preached every second week in Bendigo. It was recorded that because he was not living in Bendigo, Pastor Munzel was unable to make as much an impression on them as would a Pastor who was living amongst them. It appears that the Bendigonians were a tough crowd.
In 1864 (2 years from his arrival) a serious dissension between Pastor Munzel and the employed schoolteacher at Bendigo caused a storm and eventually lead to a split in the Bendigo congregation. With these events having taken a toll on his heath, Pastor Munzel resigned in 1868, completing a term of 6 years.
The congregations of Bendigo, Castlemaine and Maldon sent a call to Germany for a new minister, and the call was accepted by Pastor Friedrich Leypoldt, who took charge in 1869, and commenced work to heal the rifts. In those days a good many of the members were gold miners, living in tents, scattered over the hills, flats and back gullies, and it was often no easy matter to find their dwellings.
Pastor Leypoldt conducted services once a month in Castlemaine.
An article in the Mount Alexander Mail, on 11th June 1907, gave notice for a general meeting of the congregation to be held on the 12th of June at the German Lutheran Church in Barker St.
I have not been able to identify where this Church building, in Barker St, was located. One has to wonder if this was a typo error and meant to refer to Parker St.
Outbreak of World War I (1914 – 1918).
1914 saw the outbreak of World War I.
German Lutherans in Australia faced intense anti-German sentiment, leading to the closure of Lutheran schools, the banning of the German language in services and publications, and widespread social prejudice. German Lutherans, many of whom were Australian born, were subjected to harassment and negative attitudes from their fellow citizens.
In 1922, 4 years after the end of the war, Pastor Leypoldt moved to Sandringham and interestingly, the LCA archives (Sept 2023) record Castlemaine ceasing to be a preaching place in 1922. This is despite the fact that Pastor Leypoldt apparently travelled back to deliver services once a month in Castlemaine until 1928 and in Bendigo until 1930. This was an incredible term of service, spanning 61 years and demonstrated incredible dedication to his flock.
In 1929 the Victorian Synod asked Pastor Prove, of Grovedale, to take over from Pastor Leypoldt.
There was a steady decline in attendances during this period, however, it was noted that during the 1930’s some members of the Evangelical Lutheran Church of Australia (ELCA) moved to Bendigo.
During this extended period of vacancy, Pastor Prove visited the area on a monthly basis up until 1937 (a substantial period of 7 years).
In 1936 the Home Mission Board issued a number of calls for a pastor to move to the area. This was successful in 1937 when Pastor Peter Strelan arrived and commenced services in Bendigo and Echuca. At various times he also conducted services at neighbouring regional locations including Castlemaine, Maldon and Maryborough.
The outbreak of World War II (1939 – 1945)
Following the outbreak of war for a second time, German Lutherans in Australia faced a continuation of intense anti-German sentiment. Some Lutheran pastors and members were arrested and interned by the Australian government as enemy aliens.
LCA records show that one of the locations served by Pastor Strelan included the Tatura Internment Camp (spanning a period of 1941-1946).
The transition to English services (1910 – 1945)
German continued to be the language of many Lutheran homes for up to three or four generations. Similarly, German was the language of the Lutheran Church in its worship and its business. In the early 1900s, moves were made to introduce English, and this was hastened by the outbreak of World War I. There was a transition period in the 1920s and 1930s, and after World War II, only English was used.
A New Beginning (from 1952)
The LCA archives (Sept 2023) record Castlemaine as an Evangelical Lutheran Church preaching place from 1952 to the present day.
With the world wars behind them, by 1952, Lutheran services at Castlemaine had presumably become a regular feature on the calendar.
Pastor Strelan accepted a call to Unley, SA, in 1953 having served for a not inconsiderable 16 years.
Over the next 71 years (spanning from 1954 – 2025) a total of 13 different Pastors would faithfully serve the members of Castlemaine and its surrounding districts.
One notable Pastor was Stanley Mibus who served from 1954 – 1958 (4 years) and then missed the place so much he returned in 1961 for another 5 year stint. I guess there is just something special about this area.
Castlemaine’s membership during the 1960’s varied from the mid 30’s to the mid 40’s, but the average worship attendance was below 10. This was a time of significant immigration from the UK and Europe, with the government easing racial restrictions in 1966.
In 1967 the then serving Pastor, Pastor Harms reported “because of several transfers from the Castlemaine area, the attendances at the services there (two a month) have dropped back to less than half a dozen per service”.
The Chewton Era (~1978 – 2004)
Pastor Paul Stolz arrived in 1977 and around this time (perhaps 1978) worship services commenced at St Johns Anglican Church, 18 Fryers Rd, Chewton.
An unloved feature of the church property was the corrugated iron toilet structure, inhabited by spiders. After making do for 26 years, this shortcoming eventually led to the decision to find an alternative place of worship. The final service at Chewton is recorded as being held on the 18th of January 2004.
Castlemaine membership during the 1970’s hovered around the mid 30’s but declined in the 1980’s and 1990’s, dropping back to the mid 20’s. Average attendance was okay at around 15.
The Recent Era (2004 – 2025)
In 2004 worship services commenced at the Seventh Day Adventist Church, 252 Barker St, Castlemaine. The first worship service there is recorded as being held on the 1st of February 2004 and would have been conducted by Pastor Greg Lockwood.
This information was read by Craig Linke at the final Lutheran worship service in Castlemaine on Sunday the 5th of October 2025.
Records
Baptised membership at Castlemaine –
1963 – 33
1965 – 46
1978 – 35
1986 – 23
1983 – 23
1991 – 25
Average attendance at Castlemaine –
1967 – 4
1969 – 6
1975 – 20
1992 – 14
Figures obtained from the statistics held at Bendigo Church (2025).
We have been doing a bit of “Stress and Volume” testing in Qlik Replicate over the past few days; investigating how my latency is introduced to a MS SQL server task if we run it through a log stream.
If you’re not aware – you can get minute latecy from Qlik Replicate by turning the “Performance” logs up to “Trace” or higher:
This will result in messages getting produced in the task’s log file like:
I created a simple python script to parse a folder of log files and output it in a excel. Since the data ends up in a panda data frame; it would be easy to manipulate the data and output it in a specific way:
import os
import pandas as pd
from datetime import datetime
def strip_seconds(inString):
index = inString.find(" ")
returnString = float(inString[0:index])
return(returnString)
def format_timestamp(in_timestamp):
# Capture dates like "2025-08-01T10:42:36" and add a microsecond section
if len(in_timestamp) == 19:
in_timestamp = in_timestamp + ":000000"
date_format = "%Y-%m-%dT%H:%M:%S:%f"
# Converts date string to a date object
date_obj = datetime.strptime(in_timestamp, date_format)
return date_obj
def process_file(in_file_path):
return_array = []
with open(in_file_path, "r") as in_file:
for line in in_file:
upper_case = line.upper().strip()
if upper_case.find('[PERFORMANCE ]') >= 0:
timestamp_temp = upper_case[10:37]
timestamp = timestamp_temp.split(" ")[0]
split_string = upper_case.split("LATENCY ")
if len(split_string) == 4:
source_latency = strip_seconds(split_string[1])
target_latency = strip_seconds(split_string[2])
handling_latency = strip_seconds(split_string[3])
# Makes the date compatible with Excel
date_obj = format_timestamp(timestamp)
excel_datetime = date_obj.strftime("%Y-%m-%d %H:%M:%S")
# If you're outputting to standard out
#print(f"{in_file_path}\t{time_stamp}\t{source_latency}\t{target_latency}\t{handling_latency}\n")
return_array.append([in_file_path, timestamp, excel_datetime, source_latency, target_latency, handling_latency])
return return_array
if __name__ == '__main__':
log_folder = "/path/to/logfile/dir"
out_excel = "OutLatency.xlsx"
latency_data = []
# Loops through files in log_folder
for file_name in os.listdir(log_folder):
focus_file = os.path.join(log_folder, file_name )
if os.path.isfile(focus_file):
filename, file_extension = os.path.splitext(focus_file)
if file_extension.upper().endswith("LOG"):
print(f"Processing file: {focus_file}")
return_array = process_file(focus_file)
latency_data += return_array
df = pd.DataFrame(latency_data, columns=["File Name", "Time Stamp", "Excel Timestamp", "Source Latency", "Target Latency", "Handling Latency"])
df.info()
# Dump file to Excel; but you can dump to other formats like text etc
df.to_excel(out_excel)
And voilà – we have an output to Excel to quickly create statistics on latency for a given task(s).
What statistics to use?
Latency is something you can analyse in different ways, depending on what you’re trying to answer. It is also important to pair latency statistics with the change volume that is coming through.
Has the latency jumped at a specific time because the source database is processing a daily batch? Is there a spike of latency around Christmas time where there are more financial transactions compared to a benign day in February?
Generally, if the testers are processing the latency data they provide back:
Average target latency
90th percentile target latency
95th percentile target latency
Min target latency
Max target latency
This is what we use to compare two runs to each other when the source load is consistent and we’re changing a Qlik Replicate task
I am just writing a brief post about a conversation I was asked to join.
The question paraphrased:
For dates in an Microsoft SQL Server, when they get passed through Qlik Replicate to Kafka Avro – how are they interpreted? Epoch to utc? Or to the current time zone?
I didn’t know the answer so I created the following simple test:
Websites store cookies to enhance functionality and personalise your experience. You can manage your preferences, but blocking some cookies may impact site performance and services.
Essential cookies enable basic functions and are necessary for the proper function of the website.
Name
Description
Duration
Cookie Preferences
This cookie is used to store the user's cookie consent preferences.
30 days
These cookies are needed for adding comments on this website.
Name
Description
Duration
comment_author
Used to track the user across multiple sessions.
Session
comment_author_email
Used to track the user across multiple sessions.
Session
comment_author_url
Used to track the user across multiple sessions.
Session
Statistics cookies collect information anonymously. This information helps us understand how visitors use our website.
Google Analytics is a powerful tool that tracks and analyzes website traffic for informed marketing decisions.
Contains information related to marketing campaigns of the user. These are shared with Google AdWords / Google Ads when the Google Ads and Google Analytics accounts are linked together.
90 days
__utma
ID used to identify users and sessions
2 years after last activity
__utmt
Used to monitor number of Google Analytics server requests
10 minutes
__utmb
Used to distinguish new sessions and visits. This cookie is set when the GA.js javascript library is loaded and there is no existing __utmb cookie. The cookie is updated every time data is sent to the Google Analytics server.
30 minutes after last activity
__utmc
Used only with old Urchin versions of Google Analytics and not with GA.js. Was used to distinguish between new sessions and visits at the end of a session.
End of session (browser)
__utmz
Contains information about the traffic source or campaign that directed user to the website. The cookie is set when the GA.js javascript is loaded and updated when data is sent to the Google Anaytics server
6 months after last activity
__utmv
Contains custom information set by the web developer via the _setCustomVar method in Google Analytics. This cookie is updated every time new data is sent to the Google Analytics server.
2 years after last activity
__utmx
Used to determine whether a user is included in an A / B or Multivariate test.
18 months
_ga
ID used to identify users
2 years
_gali
Used by Google Analytics to determine which links on a page are being clicked
30 seconds
_ga_
ID used to identify users
2 years
_gid
ID used to identify users for 24 hours after last activity
24 hours
_gat
Used to monitor number of Google Analytics server requests when using Google Tag Manager
