473,321 Members | 1,916 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

empty option for delete records

hi, i read from some of the post that if i want to delete a whole
table using sql, i can use empty option to delete all the records but
keep the table definition, how to do that? the reason i want this
because i will get log full error for delete many records before
commit. instead of increase the log file size, i would like to know
other option like it mentioned above.
Nov 12 '05 #1
6 5511
There are two ways to do this in DB2. A fast way is IMPORT REPLACE with
a delimited file that contains no rows:

import from c:\ixf\rep.del of del replace into testvar

IMPORT is usually the best choice, because it does not lock the table
space. If the table has many active pages in the bufferpool, LOAD will
be faster, as IMPORT will flush the bufferpool. LOAD must be used if the
target table has Referential Integrity dependencies or summary tables
defined on it.
xixi wrote:
hi, i read from some of the post that if i want to delete a whole
table using sql, i can use empty option to delete all the records but
keep the table definition, how to do that? the reason i want this
because i will get log full error for delete many records before
commit. instead of increase the log file size, i would like to know
other option like it mentioned above.


Nov 12 '05 #2
There are two ways to do this in DB2. A fast way is IMPORT REPLACE with
a delimited file that contains no rows:

import from c:\ixf\rep.del of del replace into testvar

IMPORT is usually the best choice, because it does not lock the table
space. If the table has many active pages in the bufferpool, LOAD will
be faster, as IMPORT will flush the bufferpool. LOAD must be used if the
target table has Referential Integrity dependencies or summary tables
defined on it.
xixi wrote:
hi, i read from some of the post that if i want to delete a whole
table using sql, i can use empty option to delete all the records but
keep the table definition, how to do that? the reason i want this
because i will get log full error for delete many records before
commit. instead of increase the log file size, i would like to know
other option like it mentioned above.


Nov 12 '05 #3
Blair Adamache wrote:
There are two ways to do this in DB2. A fast way is IMPORT REPLACE with
a delimited file that contains no rows:

import from c:\ixf\rep.del of del replace into testvar

IMPORT is usually the best choice, because it does not lock the table
space. If the table has many active pages in the bufferpool, LOAD will
be faster, as IMPORT will flush the bufferpool. LOAD must be used if the
target table has Referential Integrity dependencies or summary tables
defined on it.


There is also an article that describes how to make the TRUNCATE operation
(via IMPORT ... REPLACE) available in SQL through a stored procedure:

http://www-106.ibm.com/developerwork...ein/index.html

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #4
Blair Adamache wrote:
There are two ways to do this in DB2. A fast way is IMPORT REPLACE with
a delimited file that contains no rows:

import from c:\ixf\rep.del of del replace into testvar

IMPORT is usually the best choice, because it does not lock the table
space. If the table has many active pages in the bufferpool, LOAD will
be faster, as IMPORT will flush the bufferpool. LOAD must be used if the
target table has Referential Integrity dependencies or summary tables
defined on it.


There is also an article that describes how to make the TRUNCATE operation
(via IMPORT ... REPLACE) available in SQL through a stored procedure:

http://www-106.ibm.com/developerwork...ein/index.html

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #5
Ian
xixi wrote:
hi, i read from some of the post that if i want to delete a whole
table using sql, i can use empty option to delete all the records but
keep the table definition, how to do that? the reason i want this
because i will get log full error for delete many records before
commit. instead of increase the log file size, i would like to know
other option like it mentioned above.


You can also :

alter table x not logged initially with empty table

If the table was created with the 'not logged initially' option.
Ian

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Nov 12 '05 #6
Ian
xixi wrote:
hi, i read from some of the post that if i want to delete a whole
table using sql, i can use empty option to delete all the records but
keep the table definition, how to do that? the reason i want this
because i will get log full error for delete many records before
commit. instead of increase the log file size, i would like to know
other option like it mentioned above.


You can also :

alter table x not logged initially with empty table

If the table was created with the 'not logged initially' option.
Ian

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Nov 12 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Wayno | last post by:
My php logs are coming up empty. I have done all I can think of, and all that made sense to me. Can someone take a look at my php.ini please and tell me what you think may be the problem. I...
3
by: tornado | last post by:
Hi all, I am pretty new to PHP. I was reading PHP manual and trying out the example from 2nd chapter (A simple Tutorial). When i try to print the variable as given in the example it returns...
5
by: Adrian Parker | last post by:
Hi. I have a date time picker in my program which uses ADO to read from an Access database. It works perfectly, unless the database is empty (no records) when opened. When you try to open an...
4
by: Larry Rekow | last post by:
In Access I have a macro that, each night, takes a table with a primary key defined in it, and deletes all the rows. Then it imports/appends records from a fixed width text file. In this way,...
0
by: xixi | last post by:
hi, i read from some of the post that if i want to delete a whole table using sql, i can use empty option to delete all the records but keep the table definition, how to do that? the reason i want...
8
by: J-P-W | last post by:
Hi, anyone got any thoughts on this problem? I have sales reps. that remotely send their data to an ftp server. The office downloads all files, the code creates an empty file, then downloads the...
8
by: lmurgas | last post by:
FormA = List of organization records bound to table FormB = Tabbed form with details of organization and all other related entities, such as orders, contacts, invoices, (all as subforms bound to...
2
by: jimwest1995 | last post by:
Hello, I need to remove all the records from my DBF table another word i want to empty the table and this is what i did Delete * from MyTable this work but it does not remove the records...
6
by: kstevens | last post by:
I have tables setup with a main table for information and a subtable that records the "multiple" records for the main record. I have written a query to go in and find Null or "" values to delete...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.