Yes David, this might be the way to go if you need 24x7 availability
of the table.

However you should be careful that the transaction is not too
big, because otherwise your log files will become full.
So, you might split this transaction into smaller transaction
groups (i.e. committing after 10.000 rows for example).
If you are interested, you can download the 'tx_split' utility
from my website:

http://www.herber-consulting.de/cgi-...ction=IfmxUtil

'tx_split' is an Esql/C program that is able to split big transactions
(delete/update statements that hit a lot of rows) into smaller
transaction groups.
However it is written for the Informix databaseserver, but it should
not be too hard to port it to DB2 UDB Esql/C.
The advantage of the program is that you could use it for abitrary
update- and delete-statements, without writing a new program
every time.

Two tips for your initial question about possible performance
improvements:

1) You might consider using raw devices (DMS tablespaces) if
you do not already have.
This allows you to detach the index from the table in
a separate tablespace.

2) Create these raw devices (logical volumes) as Raid-10
(mirrored/striped).
I know that DB2 does also a kind of striping, but if you align
the stripesizes (DB2_STRIPED_CONTAINERS registry
variable) this might improve the throughput further.

3) Run you delete batch-job locally on the databaseserver.
Otherwise the network might be an additional bottleneck
because with the delete-where-current-of approach a
lot of messages pass from client to server and the other
way round.

4) Test the same job defined as a sql stored procedure.
This eliminates the client-/server traffic and everything
is done in the databaseserver.

5) Thiink about executing the delete in parallel where each
parallel taks processes only a subset of the rows


Best regards

Eric
--
IT-Consulting Herber
WWW: http://www.herber-consulting.de
Email: eric@herber-consulting.de

***********************************************
Download the IFMX Database-Monitor for free at:
http://www.herber-consulting.de/BusyBee
***********************************************