473,321 Members | 1,667 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.

Deleting data from table

Hi ,

I have a table that contains 15lakh records.....
I want delete that table....and insert fresh set of record.

when I run the command ...db2 "delete from schema.tabname"
it hangs .......the system it seems hangs...

Is their a better way out to delete the data..

Regards
Bikash

Dec 23 '05 #1
12 18654
bi******@in.ibm.com wrote:
I have a table that contains 15lakh records.....
I want delete that table....and insert fresh set of record.

when I run the command ...db2 "delete from schema.tabname"
it hangs .......the system it seems hangs...


No, it doesn't hang. It just takes a lot of time to delete every single
record. (How many records are "15 lakh"?)

An alternative is to truncate the table by importing an empty file and using
the REPLACE_INTO option for IMPORT/LOAD. Or you could install the
"truncate" procedure and call the procedure: http://tinyurl.com/9gnlo

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Dec 23 '05 #2

"Knut Stolze" <st****@de.ibm.com> wrote in message news:do**********@lc03.rz.uni-jena.de...
bi******@in.ibm.com wrote:
I have a table that contains 15lakh records.....
I want delete that table....and insert fresh set of record.

when I run the command ...db2 "delete from schema.tabname"
it hangs .......the system it seems hangs...


No, it doesn't hang. It just takes a lot of time to delete every single
record. (How many records are "15 lakh"?)


15 lakh = 1.5 million
Dec 23 '05 #3
rkusenet wrote:
15 lakh = 1.5 million


Yeah, deleting those will take a bit time with a simple DELETE
statement. ;-)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Dec 23 '05 #4
Because of checking on each individual record (e.g. FORIEGN KEYs) and
logging, that will take a long time.

I worked around this issue with a LOAD statement that loaded an empty
file. It will wipe the TABLE in almost no time. LOAD is a CLP command,
and may need to be followed by a SET INTEGRITY...IMMEDIATE CHECKED
statement.

If LOAD is not available, DELETEing in chunks based on the PRIMARY KEY
may be faster.

B.

Dec 23 '05 #5

<bi******@in.ibm.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
Hi ,

I have a table that contains 15lakh records.....
I want delete that table....and insert fresh set of record.

when I run the command ...db2 "delete from schema.tabname"
it hangs .......the system it seems hangs...

Is their a better way out to delete the data..

If you're on OS/390 or z/OS you should consider doing a drop of the
tablespace containing the table; I believe that this deletes the rows almost
instantly if the tablespace is of the "segmented" type.

However, be sure to verify this with a test database first; I know this was
possible in some of the earlier versions like Version 3 but I'm not
absolutely positive that it still works that way.

Of course, if you drop a segmented tablespace, you will drop _all_ of the
tables in the tablespace, not just the one you want to delete, so it would
be best if you redesigned your schema to put this large table in a segmented
tablespace of its own. You will also want to consider the impact on any
tables related to your table via referential integrity if you drop the table
(by dropping the tablespace) rather than deleting the rows.

Rhino
Dec 23 '05 #6
The fastest way to delete the rows is with a load. If you know what
you will be inserting (in bulk), then load with that. Otherwise load
with an empty input file. This will work for all UDB.

I'm assuming that with that many rows it is a single table tablespace.

zOS Segmented Tablespac tables have special procssing for DELETE *
(resetting the page in use bits by segment), but you should issue a
LOCK TABLE first.
Remember, DELETE is a logged operation (unless you are on LUW and the
tablespace has a NOT LOGGED INITIALLY enabled and active) and logging
will be the slowest portion of the process (excluding indexes).
R > > Hi ,
R > >
R > > I have a table that contains 15lakh records.....
R > > I want delete that table....and insert fresh set of record.
R > >
R > > when I run the command ...db2 "delete from schema.tabname"
R > > it hangs .......the system it seems hangs...
R > >
R > > Is their a better way out to delete the data..
R > >
R > If you're on OS/390 or z/OS you should consider doing a drop of the
R > tablespace containing the table; I believe that this deletes the rows almost
R > instantly if the tablespace is of the "segmented" type.

R > However, be sure to verify this with a test database first; I know this was
R > possible in some of the earlier versions like Version 3 but I'm not
R > absolutely positive that it still works that way.

R > Of course, if you drop a segmented tablespace, you will drop _all_ of the
R > tables in the tablespace, not just the one you want to delete, so it would
R > be best if you redesigned your schema to put this large table in a segmented
R > tablespace of its own. You will also want to consider the impact on any
R > tables related to your table via referential integrity if you drop the table
R > (by dropping the tablespace) rather than deleting the rows.

R > Rhino

Edward Lipson via Relaynet.org Moondog
ed***********@moondog.com el*****@bankofny.com
---
þ MM 1.1 #0361 þ
----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---
Dec 24 '05 #7
bi******@in.ibm.com wrote:
Hi ,

I have a table that contains 15lakh records.....
I want delete that table....and insert fresh set of record.

when I run the command ...db2 "delete from schema.tabname"
it hangs .......the system it seems hangs...

Is their a better way out to delete the data..

Regards
Bikash

ALTER TABLE T ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
COMMIT;

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Dec 24 '05 #8
Serge Rielau wrote:
bi******@in.ibm.com wrote:
I have a table that contains 15lakh records.....
I want delete that table....and insert fresh set of record.

when I run the command ...db2 "delete from schema.tabname"
it hangs .......the system it seems hangs...

Is their a better way out to delete the data..
ALTER TABLE T ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
COMMIT;


Now that is a cool way to do the truncation. ;-)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Dec 27 '05 #9
Knut Stolze wrote:
Serge Rielau wrote:

bi******@in.ibm.com wrote:
I have a table that contains 15lakh records.....
I want delete that table....and insert fresh set of record.

when I run the command ...db2 "delete from schema.tabname"
it hangs .......the system it seems hangs...

Is their a better way out to delete the data..


ALTER TABLE T ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
COMMIT;

Now that is a cool way to do the truncation. ;-)

Indeed. Widely unknown for some uncomprehensible reason.
Folks keep yacking about TRUNCATE TABLE and 90% of what it does is
right there....

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Dec 28 '05 #10
ALTER TABLE T ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
COMMIT;

Indeed. Widely unknown for some uncomprehensible reason.


Perhaps because it's uncomprehensible ?
Dec 28 '05 #11
Mark Townsend wrote:

ALTER TABLE T ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
COMMIT;

Indeed. Widely unknown for some uncomprehensible reason.

Perhaps because it's uncomprehensible ?

No less so, than LOAD REPLACE with an empty file.

Mark,

I thought we had some agreement that we either post in competitive
newgroups to clarify misconceptions or to be constructive.
Did the rules change?
Your noise to data ratio is quite high these days..
I don't have to play nice ein c.d.oracle.* either...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Dec 28 '05 #12
Serge Rielau wrote:
Your noise to data ratio is quite high these days..


Really ? - and here I am thinking I've been good. Dang.
Dec 28 '05 #13

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

Similar topics

3
by: Savas Ates | last post by:
i cant delete data from excell which i use as a database with asp .. i encounter an error report about isam. how can i delete data from excell help meee!
4
by: harish | last post by:
DELETING 100 million from a table weekly SQl SERVER 2000 Hi All We have a table in SQL SERVER 2000 which has about 250 million records and this will be growing by 100 million every week. At a...
4
by: Fughal | last post by:
Hi, I have a big DB2 database and I need this database without any Data in it for testing something. I have made a backup of these db and restore it on a testing system. My Problem is now how...
1
by: Mark | last post by:
This question refers to a main form with a continuous form subform. After an error occurs after entering several records in the subform, how can I delete all the data in the main form and all the...
3
by: Nathan Bloom | last post by:
Hi, I have a data entry form (access 2000) that also allows the user to add, update, and delete records from the form. The Delete action is carried out in an event procedure and has the...
1
by: KC | last post by:
Hello, I am using Access 2002. WinXP, Template from MS called Orders Mgmt DB. I have tweaked this DB to work for our small co. It has worked pretty well up until I made the mistake of deleting...
11
by: shriil | last post by:
Hi I have this database that calculates and stores the incentive amount earned by employees of a particular department. Each record is entered by entering the Date, Shift (morn, eve, or night)...
4
by: MiziaQ | last post by:
Hey, I'm using the following code to write entries to a data file and then read them in an msflexgrid. I now would like to add code under a delete button to use the table(grid) to delete rows from...
4
by: sphinney | last post by:
I'm not exactly sure how to start this post. My question is pretty simple, but it will take a little bit of context before I can state it. (And thanks in advance for taking the time to read this!) ...
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...
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...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
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.