TRUNCATE Query in DB2 to avoid filling upof TrasactionLogs

In DB2, if the dataset is very huge in a table then deleting all the data will be a nightmare. When we delete a huge dataset directly using a DELETE statement the transaction log will get filled in and no more operations will be permitted.

Workaround to TransactionLog Full issue
If you came across a TransactionLog is full, then it is because the allotted space for your transaction gets filled in before the commit of your DML. Once you got this error, you can simply execute following statement in your query window,

COMMIT;

It will release your transaction log and rolls back the data, since it couldn't perform the execution of previous statement successfully.


Upto DB2 version 9.5
We have to use the DELETE statement to perform this huge dataset deletion.
Simple way to delete the huge dataset would be to DELETE with a subquery.

For example,

DELETE FROM Table_To_Delete WHERE Primary_Key IN (SELECT Primary_Key FROM Table_To_Delete FETCH FIRST 1000 ROWS ONLY);

Execute this repeatedly to until you get 0 rows deleted Result. Instead of giving the range of primary keys to delete, we can go for this shortcut.

From DB2 Version 9.7
From this version TRUNCATE statement is introduced which helps in deleting all the records of a table without any trail in transaction log.

Following can be a syntax of TRUNCATE,

TRUNCATE TABLE Table_To_Delete
IGNORE DELETE TRIGGERS
DROP STORAGE
CONTINUE IDENTITY
IMMEDIATE

Full syntax explanation is given here in DB2 Reference

Hope this is useful.

How to get the version of DB2 database using query?

Its very easy to get the version of DB2 database using a query. It works in most of the DB2 version.

We can execute the following query just as like any other queries and get the result.

SELECT service_level FROM TABLE(ENV_GET_INST_INFO()) AS TEMP;

Once you execute this query you will get a result set like this,



Hope this is useful!