Using Qlik for DB2 - TRUNCATE option

[husainbhavna]

Hello All, 

I am using Qlik replicate to load a table into DB2 using the TRUNCATE option.  When using the TRUNCATE option Qlik runs the following command in the database:

ALTER TABLE "SCHEMA"."TABLENAME" activate not logged initially with empty table

My question is if I am loading 100,000 records and using a commit rate of 10,000 - then only the 1st 10,000 rows will not be logged. Will the remaining 90,000 rows be logged since it's after the 1st commit?

Sincerely,

Husainbhavna

[Bill_Steinagle · Accepted solution]

Hi,

Thank you for the post to the Qlik Forums. The issue here is the newly created Table has not enabled the CAPTURE CHANGES on the Table. Looking over the ODBC Endpoint did not see where you could have post Connection process to run prior to the data being loaded. I would look into a Stored Procedure in DB2 on the Target that could run or the db2 Admin have a script to enable the CAPTURE CHANGES on newly created Tables. I do not see an option in the ODBC Endpoint to this option. 

Regards,

Bill

[john_wang]

Hello @husainbhavna ,copy @Bill_Steinagle ,

Totally agree with Bill. Besides that:

---

When using the TRUNCATE option Qlik runs the following command in the database:

ALTER TABLE "SCHEMA"."TABLENAME" activate not logged initially with empty table

---

You are right. There are different ways to perform TRUNCATE TABLE in DB2 LUW, Qlik Replicate uses above ALTER TABLE to truncate a table, it has the best performance to truncate a huge size table, as similar as below SQL (which was introduced from DB2 LUW v9.7): 

truncate table "SCHEMA"."TABLENAME" drop storage immediate

Please take note that the above SQLs affect the TRUNCATE TABLE operation only (also see below).

---

My question is if I am loading 100,000 records and using a commit rate of 10,000 - then only the 1st 10,000 rows will not be logged. Will the remaining 90,000 rows be logged since it's after the 1st commit?

---

Replicate uses "INSERT" statement to do Full Load (rather than LOAD method which is not supported), so if the table CDC is enabled then the records changes will be logged, otherwise no. To enable the logging:

ALTER TABLE "SCHEMA"."TABLENAME" DATA CAPTURE CHANGES INCLUDE LONGVAR COLUMNS

For your scenario, if logging enabled then all 100,000 records will be logged.

Hope this helps.

Regards,

John.

[husainbhavna]

Thank you john_wang.  But how do I tell Qlik to run the command below?

ALTER TABLE "SCHEMA"."TABLENAME" DATA CAPTURE CHANGES INCLUDE LONGVAR COLUMNS

[husainbhavna]

Hi Bill, 

Thank you for your response. Stored procedures are a good idea. Will look into it

[john_wang]

Hello @husainbhavna ,

You can run it manually as maintenance job as these tables exist before Replicate task running. Or do you want to Replicate execute it automatically ?

I'm not sure if I can manage it being automatically... you are using ODBC target endpoint (and the "Provider syntax" set to "DB2"), right? Let me confirm for you.

Regards,

John.

[john_wang]

Hello @husainbhavna ,

Seems no easy way to call a script in endpoint setting. Stored procedures may help.

BTW, not sure what's the purpose of enabling the logging. If you want to use the DB2 LUW table as both target and source DB, eg in a cascade replication: Oracle source replicate to DB2 LUW target, and the DB2 LUW table will be another replicate source, likes Oracle --> DB2 LUW --> Kafka. Then you can use Replicate task to enable the logging automatically for you, if you enable "Automatically enable Data Capture Changes" in endpoint setting:

Regards,

John.

[husainbhavna]

Hello @john_wang ,

Thank you very much for your assistance.  
The reason is my DB2 has HADR setup. When I use Qlik full load, the 'activate not logged initially' causes the tables in my DR to be in an inconsistent state and cannot be accessed. The solutions to this is to take a backup from prod and restore in DR and then enable HADR again. 

In production environment, it's a risk to have my DR unavailable for a period of time. 

Sincerely,

Husain

[john_wang · Accepted solution]

Hello @husainbhavna ,

Definitely you are correct. the 'activate not logged initially'  has the best performance however it's not the most safe method. it may case the tables in an inconsistent state, or the tablespace status become 0x0020 and a DB BACKUP operation needed. Unfortunately this is the DB2 LUW design.

The GOOD news is, as I said, there are many different methods in DB2 LUW to perform TRUNCATE TABLE operation includes:

1. ALTER TABLE

ALTER TABLE "SCHEMA"."TABLENAME" activate not logged initially with empty table

2. LOAD FROM

LOAD FROM /dev/null OF  DEL REPLACE INTO "SCHEMA"."TABLENAME" NONRECOVERABLE

3. TRUNCATE TABLE

TRUNCATE TABLE "SCHEMA"."TABLENAME" DROP STORAGE IMMEDIATE

Qlik Replicate is using the option 1 by default (it's uniform for different DB2 platforms). We may change it to other options. In below steps we are trying to change the method to use LOAD FROM (it has best performance as well however there is a bit difference depends on the platform where the DB2 LUW Server running).

Add an internal parameter $info.query_syntax.truncate_table in GUI, press enter, and set its value to

if DB2 LUW running on Linux:

CALL SYSPROC.ADMIN_CMD('load from /dev/null of del replace into ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC} NONRECOVERABLE')

if DB2 LUW running on Windows:

CALL SYSPROC.ADMIN_CMD('load from nul of del replace into ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC} NONRECOVERABLE')

Please try it in UAT/QA/DEV/TEST lower env prior to implement it in PROD system.

Best Regards,

John.