IBM Support

DB2 TRUNCATE IMMEDIATE SQL Statement and Log Records Created

Question & Answer


Question

The understanding was that a TRUNCATE IMMEDIATE SQL statement will not do any logging and so therefore it should be equivalent to a LOAD REPLACE LOG(NO) utility in terms of DB2 logging. However, it can seen that logging is indeed happening for the TRUNCATE IMMEDIATE. Please let it be known whether TRUNCATE IMMEDIATE really should/does create logs.

Answer

Yes, it is true that the TRUNCATE IMMEDIATE SQL statement does write some DB2 logs. This means that TRUNCATE IMMEDIATE is more equivalent to a mass delete operation, than a LOAD REPLACE LOG(NO) and therefore the LOAD REPLACE LOG(NO) *will* perform better than it. With TRUNCATE IMMEDIATE, DB2 will write a few extra log records that will prevent the TRUNCATE from being undone in case of a rollback. The log records are written, but they are only used for RECOVER , and not ROLLBACK.

For more info on the TRUNCATE SQL statement, please review the two redbooks listed below under Related Information.

[{"Product":{"code":"SWG90","label":"z\/OS"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Not Applicable","Platform":[{"code":"","label":"MVS\/ESA"},{"code":"","label":"OS\/390"}],"Version":"1.8;1.9","Edition":"","Line of Business":{"code":"LOB56","label":"Z HW"}},{"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"RDS","Platform":[{"code":"PF035","label":"z\/OS"}],"Version":"10.0;10.1","Edition":"All Editions","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
03 September 2021

UID

isg3T1021634

Page Feedback