Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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 unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm
No new posts Difference between TWALENG and TWASIZE Arunkumar Chandrasekaran CICS 3 Tue Jan 03, 2017 12:57 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us