DB2 How to Empty a Table

In data warehouse environment, usually there is a need to clear out the contents of staging tables to prepare for a fresh set of extracted source data. You might also want to housekeep some historical aggregation according to some predefined schedules, which involves copying data from a table to another and subsequently removed all data from the copied table. These are just some of the examples that boiled down to the need of efficiently "truncate" a table.

Microsoft SQL Server and Oracle DBA are definitely enjoying the luxury of built-in table truncation functionality, through command like "TRUNCATE TABLE YourTableName".
Ok fine, but does IBM DB2 UDB, the so-called most scalable and performing RDBMS provide such option?

Before that, let me evaluate some of the options of removing rows from a table.

Option A:

DELETE FROM YourTable

Well, you can delete all rows using this statement. However when involving lot of records, transaction logging causes significant performanc degradation. Still, this option is acceptable if your application requires recovery of deleted rows.


Option B:


(Assume this is within the same transaction)
ALTER TABLE YourTable ACTIVATE NOT LOGGED INITIALLY;
DELETE FROM YourTable;


You managed to escape the bad luck of doing a lot of transaction logging. But wait a minute, constraint checking are still in force (Check yourself by doing explaining a DELETE FROM statement).


Option C:


(Assume this is within the same transaction)
SET INTEGRITY FOR YourTable OFF;
ALTER TABLE YourTable ACTIVATE NOT LOGGED INITIALLY;
DELETE FROM YourTable;


Again, you managed to skip the logging and check constraint and referential constraint checking, datalink integrity checking, and generation of values for generated columns. Primary/Unique Key constraints still enforced.

Option D:


LOAD FROM /dev/null of del REPLACE INTO YourTable


This is by far the most common workaround that I have seen for table truncation. It basically uses the LOAD utility on /dev/null for simulating the loading (replace) of no-data into the designated table. The same concept works for Windows environment.

You can also use similar IMPORT FROM /dev/null of DEL REPLACE INTO YourTable. There are some differences between IMPORT/LOAD.

Option E:

With a little bit of guts, you can drop and recreate the tables. This can be tedious if you got to recreate every constraints/views/etc that dependent on the "new" table.

Option F:


ALTER TABLE YourTable ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE


This is by far my favorite way of doing "TRUNCATE Table" in DB2.

Which ring suits you? You decide.

Tons of POS Transaction File

For the purpose of POC for a retail company's data mining initiative (again), I being trashed with 90k number of small files containing 1 year POS transactional data, nearly double the amount of files/data for previous POC effort. Almost 70% of these files are compressed in Z format. Made a quick study on java.util.zip package provided in Java SDK 1.4.2 (The package is available from SDK 1.1), no luck, the standard facility only supports ZIP and GZIP formats. Ok, fine. I made a search in sourceforge, looking for any open source java implementation. None of the search results are directly useful to my decompression need. Then, I tried Winzip, WinRAR, PowerZip and etc. Emmmm, most of the Windows GUI version of these programs are able to decompress Z archive, however none of them provide batch processing. Darn, I not going to decompress 90k files one by one, am I look like that dumb? Ok, thinking of command line version of Winzip and Winrar. Ooops, unfortunately enough, they don't supports Z format in their command line version.

Decided to do some research on Z archive and found this useful article

Uncompress gz and Z format

It seems to me that Z format and many other compression formats are natively supported by UNIX systems. So sad for Windows users.

Also, read this
Wikipedia: List of archive formats

Uppercase .Z is a different format compared to lowercase .z file. Generally .Z is produced using UNIX's compress command, whereby .z is by UNIX's pack command. Algorithms used for the compression are different too.

Since I only got limited time for this decompression task, I finally settled with GUNZIP program, that's freely available (http://www.gzip.org/) and performed a batch decompression. Proceed to the ETL phase then.

And here is a forum post that I found stating similar decompression requirement. Most probably I will use Runtime.exec to call out external utility such as GUNZIP, rather than trying to find a Java implementation for integration. Anyway, it's depends on the amount of time I have.

Similar Issue