By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,658 Members | 1,255 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,658 IT Pros & Developers. It's quick & easy.

Deleting data from table

P: n/a
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
Share this Question
Share on Google+
12 Replies


P: n/a
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

P: n/a

"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

P: n/a
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

P: n/a
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

P: n/a

<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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.