SEO

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

bikkaran@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

"Knut Stolze" <stolze@de.ibm.com> wrote in message news:doge0g$1ho$1@lc03.rz.uni-jena.de...
> bikkaran@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

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

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.

<bikkaran@in.ibm.com> wrote in message
news:1135326310.783015.228710@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

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
edward.lipson@moondog.com elipson@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 =---

bikkaran@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

Serge Rielau wrote:

> bikkaran@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

Knut Stolze wrote:
> Serge Rielau wrote:
>
>
>>bikkaran@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