Amazon RDS for DB2 update operations taking longer duration

[PriQlikDBA]

Source : DB2 for zOS v12

Target : AWS RDS for DB2 v11.5.9

Qlik Replicate Server : Windows 2016 (2024.0.11.177)

We have 3 env (Prod,Test,Dev). Prod and Dev handles the update operations like Delete and Insert DML statements, whereas our test env handles the update operations as update dml statement

UPDATE "BRONZE"."ECS_CONTR_TO_ENV"
SET "EMAIL_ADDR"= (
SELECT CASE WHEN "BRONZE"."attrep_changesA871FDC750E1E5C9"."col1" IS NULL THEN "BRONZE"."ECS_CONTR_TO_ENV"."EMAIL_ADDR" WHEN "BRONZE"."attrep_changesA871FDC750E1E5C9"."col1" = '<att_null>' THEN NULL ELSE CAST( "BRONZE"."attrep_changesA871FDC750E1E5C9"."col1" AS VARCHAR(100)) END
FROM "BRONZE"."attrep_changesA871FDC750E1E5C9"
WHERE "BRONZE"."ECS_CONTR_TO_ENV"."CUST_ID"= CAST( "BRONZE"."attrep_changesA871FDC750E1E5C9"."seg1" AS INTEGER) AND "BRONZE"."ECS_CONTR_TO_ENV"."REP_ID"= CAST( "BRONZE"."attrep_changesA871FDC750E1E5C9"."seg2" AS VARCHAR(5)) AND "BRONZE"."ECS_CONTR_TO_ENV"."DATE_OF_BIRTH"= CAST( "BRONZE"."attrep_changesA871FDC750E1E5C9"."seg3" AS DATE) AND "BRONZE"."ECS_CONTR_TO_ENV"."TAX_CNTRY_CODE"= CAST( "BRONZE"."attrep_changesA871FDC750E1E5C9"."seg4" AS VARCHAR(2)) AND "BRONZE"."attrep_changesA871FDC750E1E5C9"."seq" >= ? AND "BRONZE"."attrep_changesA871FDC750E1E5C9"."seq" <= ? ) ,"ADDR1"= (
SELECT CASE WHEN "BRONZE"."attrep_changesA871FDC750E1E5C9"."col2" IS NULL THEN "BRONZE"."ECS_CONTR_TO_ENV"."ADDR1" WHEN "BRONZE"."attrep_changesA871FDC750E1E5C9"."col2" = '<att_null>' THEN NULL ELSE CAST( "BRONZE"."attrep_changesA871FDC750E1E5C9"."col2" AS VARCHAR(50)) END
FROM "BRONZE"."attrep_changesA871FDC750E1E5C9"
WHERE "BRONZE"."ECS_CONTR_TO_ENV"."CUST_ID"= CAST( "BRONZE"."attrep_changesA871FDC750E1E5C9"."seg1" AS INTEGER) AND "BRONZE"."ECS_CONTR_TO_ENV"."REP_ID"= CAST( "BRONZE"."attrep_changesA871FDC750E1E5C9"."seg2" AS VARCHAR(5)) AND "BRONZE"."ECS_CONTR_TO_ENV"."DATE_OF_BIRTH"= CAST( "BRONZE"."attrep_changesA871FDC750E1E5C9"."seg3" AS DATE) AND "BRONZE"."ECS_CONTR_TO_ENV"."TAX_CNTRY_CODE"= CAST( "BRONZE"."attrep_changesA871FDC750E1E5C9"."seg4" AS VARCHAR(2)) AND "BRONZE"."attrep_changesA871FDC750E1E5C9"."seq" >= ? AND "BRONZE"."attrep_changesA871FDC750E1E5C9"."seq" <= ? ) ,"ADDR2"= (
SELECT CASE WHEN "BRONZE"."attrep_changesA871FDC750E1E5C9"."col3" IS NULL THEN "BRONZE"."ECS_CONTR_TO_ENV"."ADDR2" WHEN "BRONZE"."attrep_changesA871FDC750E1E5C9"."col3" = '<att_null>' THEN NULL ELSE CAST( "BRONZE"."attrep_changesA871FDC750E1E5C9"."col3" AS VARCHAR(50)) END

For each update statement, it updates each column based on the seq no. Not sure whether un-touched column is also included. This impacts hugely, it fills the sorter sub-directory of the task directory (this is in the Qlik Replicate server).

We want the test env to function in the same way as Prod and Dev env (like Delete and Insert operations for Update execution). 

Pls let me know how to achieve it. What setting at task or end-point connection level can handle this request.

Thank you,

Raghavan Sampath

[Dana_Baldwin · Accepted solution]

Hi @PriQlikDBA 

Please compare the Apply Changes settings between these environments. If you set "no record found for applying an update" to "insert the missing record", then every update will be handled on the target as a delete and then insert (it will not check for the existence of the record first, for efficiency sake):

Apply Conflicts #Apply Conflicts | Qlik Replicate Help

Thanks,

Dana

[PriQlikDBA]

Thank you team, 

The suggestions helped us in resolving the issue. Now I 'm able to notice Delete and Insert operations for Updates and it addressed our issue. There is no latency observed after the modification.

I sincerely appreciate your support, all the time all your response are truly helpful and beneficial.

Thank you once again for the support.

Have a wonderful day.

Regards,

Raghavan Sampath

[Dana_Baldwin]

@PriQlikDBA Thank you! Glad to hear that. We appreciate your support.

Dana

[Kareem_Primerica]

Hi @Dana_Baldwin , 
Thank you for helping us in resolving the issue , I have one more question , 
Same task is working fine in DEV and PROD even without the suggested settings , why is this issue in Test , 
is there anything we need to look up on , 

your inputs will be Highly Appreciated

[Dana_Baldwin]

Hi @Kareem_Primerica 

My understanding was that you wanted updates applied to the target as delete and insert, and you had already identified this as the root cause, so I thought that was how your environments that are performing well were set. My suggestion was based on this perceived difference.

If this task setting is not in place on your environments that perform well, we would need to look into the root cause of that based on the diagnostics packages for the tasks. Please open a support case and attach those for analysis. Please be advised, if there are no errors or "one-by-one" mode indicated in the logs, we may need to refer you to our Professional Services team (fee based) for assistance tuning or adjusting the tasks for best performance.

Please refer to this article for more details on Support compared to Professional Services: How to contact Qlik Support - Qlik Community - 1837529

Thanks,

Dana