Troubleshooting
Problem
TRUNCATE TABLE processing in Db2 LUW HADR environment might cause an increased log gap or a commit slowdown, depending on the DB2_HADR_BUF_SIZE and SYNCMODE.
Cause
TRUNCATE TABLE processing or an equivalent:
IMPORT FROM /dev/null OF DEL REPLACE INTO <tab_name>
in Db2 HADR environment involves a replay of special log records, related to the removal of extents belonging to the truncated object. Replay of each such a record requires a modification in EMP page for the truncated object, which must be synchronously written to the disk. As the result, depending on HADR synchronization mode, one can observe:
a) the TRUNCATE being much slower in SYNC mode than without the HADR
b) an increased log gap for NEARSYNC, ASYNC, SUPERASYNC modes when one of the tables is being truncated
a) the TRUNCATE being much slower in SYNC mode than without the HADR
b) an increased log gap for NEARSYNC, ASYNC, SUPERASYNC modes when one of the tables is being truncated
Diagnosing The Problem
In both cases one could observe a spike in the number of physical IO operations on the standby database done by redo EDUs (db2redow), with sqlbRedoUpdateEMP function on the stack:
pwrite64
sqloseekwrite64
sqloWriteBlocks
sqlbWritePageToDisk
sqlbWritePage
sqlbufix
sqlbRedoUpdateEMP
sqlbRedo
sqldmrdo
sqlpRecDbRedo
sqlpPRecProcLog
sqlpParallelRecovery
Resolving The Problem
If table is truncated temporarily and later is populated with the data again the preferred approach is to use TRUNCATE with "RESUE STORAGE" clause ("DROP STORAGE" being the default), which empties the table but keeps pages assigned to the table object. Runtime difference might be significant, see an example for SYNC mode and a table with 2000000 pages:
For NEARSYNC, ASYNC, SUPERASYNC modes the replay of extent removal records blocks replay of other records affecting given tablespace, so in cases when "REUSE STORAGE" cannot be used one should consider:
- high DB2_HADR_BUF_SIZE or log spooling if replay of TRUNCATE records causes slower commits on primary
- moving truncated objects to a separate tablespaces, in order to avoid replay of records for other objects (UPDATE/DELETE/INSERT) being required to wait for TRUNCATE to be replayed.
Problem might be more severe for releases older than 9.7 FP7, where the replay of TRUNCATE operation was blocking replay of all other log records for the database.
$ time db2 "truncate table big_table immediate"
DB20000I The SQL command completed successfully.
real 0m27.94s
user 0m0.01s
sys 0m0.01s
$ time db2 "truncate table big_table immediate reuse storage"
DB20000I The SQL command completed successfully.
real 0m0.37s
user 0m0.01s
sys 0m0.01s
For NEARSYNC, ASYNC, SUPERASYNC modes the replay of extent removal records blocks replay of other records affecting given tablespace, so in cases when "REUSE STORAGE" cannot be used one should consider:
- high DB2_HADR_BUF_SIZE or log spooling if replay of TRUNCATE records causes slower commits on primary
- moving truncated objects to a separate tablespaces, in order to avoid replay of records for other objects (UPDATE/DELETE/INSERT) being required to wait for TRUNCATE to be replayed.
Problem might be more severe for releases older than 9.7 FP7, where the replay of TRUNCATE operation was blocking replay of all other log records for the database.
[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PlvAAE","label":"HADR->Log shipping and Replay"}],"ARM Case Number":"","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"All Versions"}]
Was this topic helpful?
Document Information
Modified date:
30 September 2021
UID
swg21991146