Hi,
Can you suggest methods to do bulk delete.? The table contain 7 million rows in that.

Regards
Mohan

IMPORT FROM /dev/null of del replace into tablename

or

ALTER TABLE tablename NOT LOGGED INITIALLY WITH EMPTY TABLE

The first one is a command and cannot be issued from a SQL Interface like JDBC.

The second one is SQL but requires higher privileges on the table.

Sathyaram

Hi, this command deletes all rows from table.

On Windows there is:
import from nul: of del replace into tablename.

Hope this helps,
Grofaty

Also on z/OS this is indeed the fastest way to bulk delete:
- "LOAD REPLACE" from an empty dataset.
or
- REORG ... DISCARD FROM table WHEN (0 = 0)

As can be expected, REORG ... DISCARD WHEN is also the most efficient way to bulk delete only part of the table rows: use the condition which you would put in "DELETE ... WHERE" as the "REORG ... DISCARD ... WHEN" condition. (stage-1 / sargable only)

Hi,
I want to delete a praticular(eg:empid) 10 lakh rows .The total rows in that table contain 35 lakh rows. Can I use the Load command or is there any specific command is there ..?

You can either:

1. send one (or more) DELETE statements, or
2. you export the data you want to keep, empty/truncate the table and then import/load the previously exported data.

Maybe it is an option for you to use NOT LOGGED INITIALLY when doing the DELETE. That avoids logging, but has same side effects that may not be an option in your environment.

What are "lakh rows" ?

It is a different(????) number(!!) system where 10 lakhs = 1 million ...
So, the OP is talking about 1 m and 3.5 m records

Sathyaram

On z/OS, use "REORG ts DISCARD FROM table WHEN (EMPID = value)"
See http://publibz.boulder.ibm.com/cgi-b...20040209165609