 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / April 2006Truncate Table
Hi I know we can truncate a table in DB2 by first creating it with NOT LOGGED INITIALLY option. and when we need to truncate it, run the following command alter table <table name> activate not logged initially with empty table My question is: 1. Do we have to connect to the database with auto-commit off every time we have to run this. If yes, then why? thanks > Hi > [quoted text clipped - 7 lines] > 1. Do we have to connect to the database with auto-commit off every > time we have to run this. First, there is no need anymore to define the table as NLI up front. If all you want is to truncate the table then it doesn't matter whether you do commit explicitly or you let the client do auto-commit for you. If, however you want to follow the truncate up with e.g. a mass insert it may make sense to share the transaction between the alter and the inserts. Cheers Serge  SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab > Hi > > I know we can truncate a table in DB2 by first creating it with NOT > LOGGED INITIALLY option. It is not necessary any longer to specify the NLI option at create table time. > and when we need to truncate it, run the > following command [quoted text clipped - 5 lines] > time we have to run this. > If yes, then why? No, you don't. The ALTER TABLE will truncate the table. If this is all you want to do, you can commit right away or let auto-commit do this for you. But if you want to do other things on the table that are not logged, you have to do this in the same transaction as the ALTER TABLE and /then/ you need to ensure that no implicit commit is run, i.e. turn auto-commit off. SignatureKnut Stolze DB2 Information Integration Development IBM Germany Correct me if anything wrong I create a table without NOT LOGGED INITIALLY OPTION. Now when i have to truncate it, then i execute the SQL alter table <table name> activate NOT LOGGED INITIALLY with empty table This will truncate the table. Now my question is: 1. Will it log the tansaction while SQL statement is truncating the table (even if we don't turn off the auto commit). > Correct me if anything wrong > I create a table without NOT LOGGED INITIALLY OPTION. Now when i have [quoted text clipped - 4 lines] > 1. Will it log the tansaction while SQL statement is truncating the > table (even if we don't turn off the auto commit). Not it will not log. Keep in mind that any error between the ALTER and the next COMMIT will cause the table to be places in DROP PENDING. Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab > Correct me if anything wrong > I create a table without NOT LOGGED INITIALLY OPTION. Now when i have [quoted text clipped - 4 lines] > 1. Will it log the tansaction while SQL statement is truncating the > table (even if we don't turn off the auto commit). No it will not log. Keep in mind that any error between the ALTER and the next COMMIT will cause the table to be placed in DROP PENDING. Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab >> Correct me if anything wrong >> I create a table without NOT LOGGED INITIALLY OPTION. Now when i have [quoted text clipped - 11 lines] > Cheers > Serge Also if I am not mistaken a roll forward of the logs through this statement will do the same. Bob |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.