An Alternative to truncate for pre v9.7

So many a times people have to truncate a table in run time, i.e. while processing a batch or a temp tabel or while flushing a table which stores some periodic stats etc... DB2 9.7 has an Amazingly fast command which will help you do that in flash of a blink called truncate... But ooops you are not using db2 v9.7... you had upgraded to db2 9 or db2 9.5 recently and can not take the effort required to port your application to new version and do all the testing again... No sweat... I will be sharing a few such tips which will help you imitate truncate table


1) Which is generic for all platforms...

ALTER TABLE tablename ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
commit;


but I am not very sure if space acquired by table is retained in this case

2) Obviously

ALTER TABLE tablename ACTIVATE NOT LOGGED INITIALLY;
delete from tablename;
commit;


3) This will be specific to a OS platform and I will not suggest using this if you have to code it in your application:

i) *NIX --> import from /dev/null of del replace into tablename
ii) Windows --> import from nul of del replace into tablename

This operation is though faster than normal delete but is logged and hence will not be as performant as first... Some people argue that second option is not recoverable, but we are looking at an alternative for truncate (which is not recoverable either) and hence recovery is not my priority... Moreover I have committed the transaction as soon as I fire "NOT LOGGED" command for "tablename" hence should not be an issue...