Re: Runtime for deleteing lots of records

[Martin Rowe <martin@xxxxxxxxxx>]

On Mon, 2003-02-10 at 09:35, oliver.wenzel@cibavision.novartis.com
wrote:
> Hi,
> 
> we had run a job this weekend that deleted lots of old records from a file
> with about 22 million records. Some
> 16 million records were deleted with RUNSQLSTM below:
> 
> delete from vroirspl where vlcind>000101 AND VLCIND<020901
> 
> This has run for about 9hours 15min. Is that okay on an 820 with 210GB DASD
> (mirrored 6718) and 3GB ram?
> It seems to be quite a long time...

Had a similar exercise last week, albeit on a smaller file. 800,000
deleted out of 1.4 million which took around 30 mins on a four way 730
(250Gb DASD,6Gb RAM). There are a couple of things that can slow it
down. If the field isn't a primary key it can take longer to select the
correct records to delete. Deleting by RRN (assuming the old records are
blocked in the file) may be quicker for this sort of delete. Also if
you've a lot of access paths over your file that are on MAINT(*IMMED)
then they have to be updated in real time for all those 16 million
records. I would guess this may be your time culprit. 

Regards, Martin
-- 
martin@dbg400.net  AIM/Gaim: DBG400dotNet  http://www.dbg400.net       /"\
DBG/400 - DataBase Generation utilities - AS/400 / iSeries Open        \ /
Source free test environment tools and others (file/spool/misc)         X 
[this space for hire]  ASCII Ribbon Campaign against HTML mail & news  / \