Hi
I work with db2 7.2 AIX, I need to delete some rows from a large table with 20 mil records I use
delete from TAB-A where co11= 'A';
I have index on col1 but it takes long about 1/2 hours that is not acceptable, Some body suggested me I deaclare cursor and use "where current cursor cursor-name" in my delete sql statement. any idea and help will be appreciated.
Thanks

Too elementary ... but,
Have you done a runstats on the table including the indexes ?

Cheers

Sathyaram

Thank you for your answer , Yes I did runstats and bound the packages.
I imagin if we have a table with 200 mil records (that I will have in future in our system) what is the best solution for deletition a part of table.
Thank you in advanace for your co-operation

200 million rows is a lot. You need to check the
limit for a single table in DB2 which depends on
the configured pagesize and ranges from 64 GB
to 512 GB for single-partition-tables (no EEE).

You might consider partitioning your database across
several partitions using DB2 EEE or in DB2 ESE (Version 8.1),
this might improve performance, because the delete will
run in parallel mode.

From my point of view you need some kind of fragmentation
technology like the one Informix offers.
With Informix you can fragment your table using for example
range fragmentation into several dbspaces. This allows you
to detach a single fragment without any resource consuming
delete process, it takes only a second and your data is
"deleted".

But this is not possible with DB2 (at least not yet, but the
IFMX guys will work on a future DB2 version, hopefully).
You might consider using several stand-alone-tables and
combining them thru a union-view.
I read something that in Version 8.1 also inserts/updates/deletes
against union views are possible. So that might help.


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
***********************************************

Here is one solution:

>> export the table without the data you want to delete using where clause

>>import an empty file in the table; this is the fastest way to delete data from table without using much of log activity

>>load the file exported in first step which will contain only the data you wanted to keep

You can automate this whole process to better use it.

Hope this helps

dollar

Thank you for your suggestion but if I use LOAD command it will lock the tablespace by the end of load process . am I right?

In V7.x the tablespace is locked during the LOAD, in V8.1 only the table itself is locked.

But this might also not be desirable in a 24x7 OLTP system.


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
***********************************************

Thank you eric,
so I can not use Load because delete command is used in application and we can not lock the tablespace (in that time we have read write in so many tables in tablespace. How about if I use dectare cursor and use delete command with "with current cursor <corsur-name>?

Hi,

In DB2 vesion 8.1 exist multi-dimensional clustering (index type)

Multi-Dimensional Clustering - Benefits:
- Faster Query speed
- Access only the data pages necessary - no searching
- Get all the data in a few accesses versus 100s of page reads
- Reduces CPU & disk I/O use for other users to leverage
- Simple & Intuitive --Multidimensional keys and Star Schema friendly
- Perfect for OLAP style hierarchical analysis
- Executive reports, Summaries, etc.
- Reduces index size --Saves disk, faster queries
- One index entry covers entire data page, not one per row
- Reduces DBA reorg's --row clustering managed by DB2
- FASTER DELETES --JUST DROP A FEW DATA PAGES
- Faster Inserts --store the record, but rarely insert an index entry

Grofaty

Beside repeating the IBM marketing messages,
did you ever verify this ?

I doubt that MDC indexing is that useful in OLTP
environments. Also I doubt that a EEE or in
V8.1 a ESE with multiple partitions is really
useful in HighEnd OLTP environments.
Informix might be (at least today) the better choice.


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
***********************************************

You probably have just verified this...however....
If your table is partitioned deletind so many rows....are you sure that it doesn't make a lock escalation?
I think so...so....using a load replace in this case is faster!

Hi,

Yes you are probably right. MDC indexing is not sutible for OLTP systems. But it can be useful in warehouse environments.

I haven't tested it yet, but it seem to be very promising...

Grofaty

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
***********************************************

Hi,

If you just have to avoid locking of tablespace and continue deleteing records then better do this using a procedure where you can select records in a cursor with one record (primary key) at a time and delete that record.
This way there won't be any deadlock and locking also.

Cheers,

If you want, you can use QMF for generating the needed delete statement in the way decripted in the last post.
You select the keys and make punctual delete statements. It avoids lock.
Remember to use commit after every delete statement!!!!!!!!!
In this way if something goes wrong rollback takes just few second (to rollback the last update!).
Is it enough clear?