Difference between Truncate and Delete

lakshmipriya · Post by **lakshmipriya** » Wed Jan 19, 2005 1:19 am

Hi

What is the difference between the DELETE mode and WRITE - TRUNCATE mode. What will be the impact of the same in database.

Can any one help me out on the same.

Thanks in advance

ray.wurlod · Post by **ray.wurlod** » Wed Jan 19, 2005 1:57 am

Which database? There IS more than one, despite what Oracle bin Larry would have us believe.

As a general rule, truncate is a fast operation that recovers space formerly occupied by data.

Delete deletes individual records, and the operations are logged in the transaction log. It tends to be a rather slower process than truncate.

If this is not the answer to your question, can you please post a more precise question? For example, you may be asking about menu options in a particular stage type. If so, please provide the stage type name and the actual text of the menu options.

You might also profitably consult the documentation and/or on-line help for that particular stage type.

lakshmipriya · Post by **lakshmipriya** » Wed Jan 19, 2005 2:32 am

Hi

Thanks for your great interest.

In DB2/UDBEnterprise stage as of my knowledge both the truncate and delete option send the commond as Delete, if so how does Truncate option will be more faster than the delete option.

How does it internally works on. Can you put some more light on the same.

Thanks

roy · Post by **roy** » Wed Jan 19, 2005 9:06 am

Hi,
I just wanted to point this is a DBA issue rather then DS.
so if no replies simply contact your project's supporting DBA.

IHTH,

ray.wurlod · Post by **ray.wurlod** » Wed Jan 19, 2005 2:52 pm

My earlier reply was generic; consult DB2 documentation (or DBA) for an explanation of the different working of DELETE and TRUNCATE TABLE. You should be able to tie it in with what I wrote to enhance your understanding of the differences.

vmcburney · Post by **vmcburney** » Wed Jan 19, 2005 4:17 pm

Truncate can be dangerous. Triggers on the table will not be fired by a truncate, they will be fired by a delete. Changed data capture modules may have problems identifying a truncate. Dependent foreign key relationships can be bypassed by a truncate leaving you with data integrity problems. These are the type of issues you need to discuss with someone who knows the database structure quite well.

Mike · Post by **Mike** » Wed Jan 19, 2005 5:09 pm

It used to be the case (probably still is) that DB2 doesn't have a TRUNCATE TABLE statement. That explains why DataStage does a DELETE with no WHERE clause under the covers for that option. The closest equivalent to Oracle's TRUNCATE is a LOAD REPLACE with an empty file.

Mike