Faster Approach to delete records......

[satish mullapudi]

Hi All,
I am getting strange situation. These r the steps I have followed:
1. Created an EMPLOYEE table with around 14 fields & 688038 records.
(so a large table indeed).
2. Tried to delete all the rows in the table using the traditional
DELETE FROM EMPLOYEE stmt. It is taking around 53 secs to delete all
the records.
So I have done the below steps to make it fatster:
(i) Create an empty file called No_Data.DEL in the C:\ drive.
(Location & name of file doesn't matter , BUT it shud be an empty
file.)
(ii) Executed the following stmt: IMPORT FROM C:\No_Data.DEL OF DEL
REPLACE INTO Employee.
This stmt took less that 1 sec to delete all the data in the Employee
table.
THIS IS THE FASTER APROACH TO DELETE RECORDS FROM A TABLE.
But I wnat to know ,how did it happen so? Why did the IMPORT stmt take
that much less time to delete all the records , which the traditional
DELETE took a large amount of time.

Thanx in advance....

Awaiting ur replies,

Satish..

[Otto Carl Marte]

On Mar 20, 8:30 am, "satish mullapudi" <[e-mail]>
wrote:

Hi All,
I am getting strange situation. These r the steps I have followed:
1. Created an EMPLOYEE table with around 14 fields & 688038 records.
(so a large table indeed).
2. Tried to delete all the rows in the table using the traditional
DELETE FROM EMPLOYEE stmt. It is taking around 53 secs to delete all
the records.
So I have done the below steps to make it fatster:
(i) Create an empty file called No_Data.DEL in the C:\ drive.
(Location & name of file doesn't matter , BUT it shud be an empty
file.)
(ii) Executed the following stmt: IMPORT FROM C:\No_Data.DEL OF DEL
REPLACE INTO Employee.
This stmt took less that 1 sec to delete all the data in the Employee
table.
THIS IS THE FASTER APROACH TO DELETE RECORDS FROM A TABLE.
But I wnat to know ,how did it happen so? Why did the IMPORT stmt take
that much less time to delete all the records , which the traditional
DELETE took a large amount of time.

Thanx in advance....

Awaiting ur replies,

Satish..

You could do this:
ALTER TABLE EMPLOYEE NOT LOGGED INITIALLY WITH EMPTY TABLE;

[satish mullapudi]

This really works. Here in this cmd u r specifying to replace with an
EMPTY TABLE and that too WITHOUT LOGGING. So, the operation is
performed Faster. But in the one am asking using the IMPORT utility,
can u plz say why it is done faster than the normal DELETE operation.

--
Satish.

Otto Carl Marte wrote:

On Mar 20, 8:30 am, "satish mullapudi" <[e-mail]>
wrote:

Hi All,
I am getting strange situation. These r the steps I have followed:
1. Created an EMPLOYEE table with around 14 fields & 688038 records.
(so a large table indeed).
2. Tried to delete all the rows in the table using the traditional
DELETE FROM EMPLOYEE stmt. It is taking around 53 secs to delete all
the records.
So I have done the below steps to make it fatster:
(i) Create an empty file called No_Data.DEL in the C:\ drive.
(Location & name of file doesn't matter , BUT it shud be an empty
file.)
(ii) Executed the following stmt: IMPORT FROM C:\No_Data.DEL OF DEL
REPLACE INTO Employee.
This stmt took less that 1 sec to delete all the data in the Employee
table.
THIS IS THE FASTER APROACH TO DELETE RECORDS FROM A TABLE.
But I wnat to know ,how did it happen so? Why did the IMPORT stmt take
that much less time to delete all the records , which the traditional
DELETE took a large amount of time.

Thanx in advance....

Awaiting ur replies,

Satish..

You could do this:
ALTER TABLE EMPLOYEE NOT LOGGED INITIALLY WITH EMPTY TABLE;

[Knut Stolze]

satish mullapudi wrote:

Hi All,
I am getting strange situation. These r the steps I have followed:
1. Created an EMPLOYEE table with around 14 fields & 688038 records.
(so a large table indeed).

Not really that large...

2. Tried to delete all the rows in the table using the traditional
DELETE FROM EMPLOYEE stmt. It is taking around 53 secs to delete all
the records.
So I have done the below steps to make it fatster:
(i) Create an empty file called No_Data.DEL in the C:\ drive.
(Location & name of file doesn't matter , BUT it shud be an empty
file.)
(ii) Executed the following stmt: IMPORT FROM C:\No_Data.DEL OF DEL
REPLACE INTO Employee.

It's ever simpler to do:

IMPORT FROM NUL OF DEL REPLACE INTO ...

Then you don't have to create the empty file.

This stmt took less that 1 sec to delete all the data in the Employee
table.
THIS IS THE FASTER APROACH TO DELETE RECORDS FROM A TABLE.
But I wnat to know ,how did it happen so? Why did the IMPORT stmt take
that much less time to delete all the records , which the traditional
DELETE took a large amount of time.

When a row is deleted in a table, DB2 has to log the data change. This is
necessary because you may issue a ROLLBACK at EOT, and then DB2 must be
able to restore the table to the state it was before the DELETE (actually
to the state it was at BOT). So all the data of the deleted rows is needed
somehow. With a lot of rows, you get a lot of log records being written.

The IMPORT/REPLACE does not log each row. It writes a single table
truncation log record, which is much faster.

The alternative to use NOT LOGGED INITIALLY WITH EMPTY TABLE also avoids the
logging and, thus, is much faster than a DELETE statement.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[satish mullapudi]

Thanx Knut for ur explaination.

satish...

Knut Stolze wrote:

satish mullapudi wrote:

Hi All,
I am getting strange situation. These r the steps I have followed:
1. Created an EMPLOYEE table with around 14 fields & 688038 records.
(so a large table indeed).

Not really that large...

2. Tried to delete all the rows in the table using the traditional
DELETE FROM EMPLOYEE stmt. It is taking around 53 secs to delete all
the records.
So I have done the below steps to make it fatster:
(i) Create an empty file called No_Data.DEL in the C:\ drive.
(Location & name of file doesn't matter , BUT it shud be an empty
file.)
(ii) Executed the following stmt: IMPORT FROM C:\No_Data.DEL OF DEL
REPLACE INTO Employee.

It's ever simpler to do:

IMPORT FROM NUL OF DEL REPLACE INTO ...

Then you don't have to create the empty file.

This stmt took less that 1 sec to delete all the data in the Employee
table.
THIS IS THE FASTER APROACH TO DELETE RECORDS FROM A TABLE.
But I wnat to know ,how did it happen so? Why did the IMPORT stmt take
that much less time to delete all the records , which the traditional
DELETE took a large amount of time.

When a row is deleted in a table, DB2 has to log the data change. This is
necessary because you may issue a ROLLBACK at EOT, and then DB2 must be
able to restore the table to the state it was before the DELETE (actually
to the state it was at BOT). So all the data of the deleted rows is needed
somehow. With a lot of rows, you get a lot of log records being written.

The IMPORT/REPLACE does not log each row. It writes a single table
truncation log record, which is much faster.

The alternative to use NOT LOGGED INITIALLY WITH EMPTY TABLE also avoids the
logging and, thus, is much faster than a DELETE statement.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Ian]

Knut Stolze wrote:

>
The alternative to use NOT LOGGED INITIALLY WITH EMPTY TABLE also avoids the
logging and, thus, is much faster than a DELETE statement.

Beware, though, because doing this will affect your ability to recover a
database to a point in time. That is why some people prefer using IMPORT.

[rawu]

On Mar 21, 4:06 am, Ian <[e-mail]:

Knut Stolze wrote:

The alternative to use NOT LOGGED INITIALLY WITH EMPTY TABLE also avoids the
logging and, thus, is much faster than a DELETE statement.

Beware, though, because doing this will affect your ability to recover a
database to a point in time. That is why some people prefer using IMPORT.

when it cames to delete parts of 300 millions records , i can't tell
which approach is better .

1. alter table ... activate not logged initially ; delete ...
(affect recovery)
2. export&truncate&import (need more file spaces and seems ugly)
3. traditional delete ( slow and have to worry about db log space )
4. build a procedure using a cursor to control the commitcount
( slove db log space problem , but slowest)