INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Tips and Tricks

'Truncating' a Table by sathyarams
Posted: 30 Dec 02 (Edited 13 Feb 04)

The following discussion holds good for V7 ...

1. Using DELETE

DELETE FROM <tablename>

This causes all the rows in the table to be deleted. Log records are written. If the table is large(in terms of millions of records), a very large active log space is required. DELETE Triggers are fired.

2. Using IMPORT/LOAD

IMPORT from /dev/null of del
         replace into <tablename>
LOAD from /dev/null of del
         replace into <tablename> NONRECOVERABLE

More privelages are required for these tasks. IMPORT .. REPLACE requires CONTROL on the table and LOAD requires LOAD Authority on the table. Good thing about this is that there is minimal logging.
The tables may be left in check pending  state. DELETE Triggers are not fired.

From the database recovery point of view, it is advisable to use IMPORT

3. Using NLI

ALTER TABLE <tablename> NOT LOGGED INITIALLY WITH EMPLY TABLE

The table should have been created with the NLI Option. No logging is done. DELETE triggers are not fired.







Back to IBM: DB2 FAQ Index
Back to IBM: DB2 Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close