Home > DB2 UDB > DB2 UDB – Truncate Tables

DB2 UDB – Truncate Tables

DB2 – Equivalent to SQL Server/Oracle TRUNCATE command

Problem
This technote describes how to delete the contents of a table with minimal logging overhead

Solution
Use the LOAD or IMPORT command in REPLACE mode, and import an input file with no rows. This will cause the table to be truncated with very little or no logging overhead.

There are two ways to do this in DB2® Universal Database™ (DB2 UDB). A fast way is perform an IMPORT REPLACE with a delimited file that contains no rows, for example:

import from c:\ixf\rep.del of del replace into testvar

IMPORT is usually the best choice, because it does not lock the table space. If the table has many active pages in the bufferpool, LOAD will be faster, as IMPORT will flush the bufferpool. LOAD must be used if the target table has referential integrity dependencies or summary tables defined on it. In DB2 UDB Version 7 and earlier, LOAD requires exclusive access to the table space. If you plan to use LOAD a lot, put each table that gets LOADed (or uses LOAD to be emptied) in its own table space, or use IMPORT REPLACE.

Note that on UNIX® and Linux® platforms, you can also IMPORT or LOAD from /dev/null

Advertisements
Categories: DB2 UDB
  1. Anonymous
    26-November-2009 at 9:24 AM

    kamleshrao.blogspot.com is very informative. The article is very professionally written. I enjoy reading kamleshrao.blogspot.com every day.payday loans edmonton payday loans online

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: