Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Difference between TRUNCATE TABLE and DROP TABLE

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
anant_deshmukh21

New User


Joined: 19 Jun 2007
Posts: 2
Location: pune

PostPosted: Sat Jul 07, 2007 12:11 pm    Post subject: Difference between TRUNCATE TABLE and DROP TABLE
Reply with quote

[size=18]any one plz tell me the difference between TRUNCATE TABLE and DROP TABLE
Back to top
View user's profile Send private message

a027412

New User


Joined: 05 Jul 2007
Posts: 40
Location: Minneapolis, MN

PostPosted: Sat Jul 07, 2007 2:48 pm    Post subject: Re: Difference between TRUNCATE TABLE and DROP TABLE
Reply with quote

to my knowledge, after issuing TRUNCATE command, all the record are deleted and just one record is put in the log. but once the DROP command is issued many records are written to the log. And more over the TRUNCATE command will put the schema in CHK PENDING state and DROP command will not.
Please correct me if am wrong
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Jul 07, 2007 3:02 pm    Post subject:
Reply with quote

Hello,

Please post in the correct forum - this has been moved to DB2, if there is some reason to return it to cobol, please advise.

Also, please use the standard size not
Quote:
[size=18]
fortunately, it wasn't specifiec correctly and did not appear as a shout icon_smile.gif
Back to top
View user's profile Send private message
0d311

Guest





PostPosted: Sat Jul 07, 2007 4:03 pm    Post subject:
Reply with quote

Hi,

As I know, when you do a TRUNCATE TABLE, you only clear up the data and leave the table as is while when you do a DROP TABLE, you not only get rid of the data, but also get rid of the table in the database.

In any case, your boss wouldn't like you doing any of those in production. icon_lol.gif
Back to top
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Sat Jul 07, 2007 7:49 pm    Post subject:
Reply with quote

We do the TRUNCATE in production. It is more efficient than a DELETE FROM table_name. It logs one entry in the transaction log, instead of an entry for each delete.

We have tables that get emptied and reloaded from file provided to us by contractors often.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Jul 08, 2007 5:35 am    Post subject:
Reply with quote

Hello,

Yup, truncate does the housecleaning cheaply and still preserves the structure.

Hopefully, not just everyone has been granted permission to truncate production tables.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Sun Jul 08, 2007 10:25 pm    Post subject:
Reply with quote

We don't even have permission to truncate in Unit. We have to run it through a scheduled job if we are doing that.
Back to top
View user's profile Send private message
salehi

New User


Joined: 30 Sep 2006
Posts: 14
Location: Iran

PostPosted: Mon Jul 09, 2007 5:09 pm    Post subject:
Reply with quote

TRUNCATE quickly removes all rows from a table. It has the same effect as an unqualified DELETE but since it does not actually scan the table it is faster. This is most useful on large tables
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts ISMF Difference between volume count ... upendrasri IBM Tools 2 Tue Dec 05, 2017 12:40 pm
No new posts Finding difference between two PS fil... Vignesh Sid SYNCSORT 5 Mon Oct 30, 2017 1:13 pm
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts Difference in SORT & ICETOOL Mohan Kothakota DFSORT/ICETOOL 5 Fri Sep 22, 2017 4:56 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us