RE: Runtime for deleteing lots of records

You are basically 'purging' all the records date stamped from 1/1/2000 thru
9/1/2002

Maybe you have a lot of logicals built over the file.  Maybe there are
records locked.  Do you have triggers?  Are you journaling or running a
mirrored HA application?  There could be a lot of reasons why it's taking
soooo long.  In my (biased) opinion, using SQL to delete massive amounts of
data as shown in your example is very dangerous.  SELECTing 16 million
records is biting off a lot at once.  It at least makes you worry (as you
are now).  You'd be better off using a more controlled approach.  The SQL
could have spent a lot of time figuring out which records to include, and
could have killed the system with logical file access path maintenance once
it started deleting stuff.  In some cases, I've seen the As/400 decide to
just rebuild access paths rather than do things *IMMED if you're chopping
off too much stuff.

The reason I said 'biased' opinion is because I authored a software product
to do what you are attempting, but in a very controlled manner.  You can
specify your criteria and then an RPG program will be created for you to do
the purge in the 'right way' - one record at a time.  You can also
'throttle' the purge to slow it down in case all the access path maintenance
is killing other work on the system.  If you change your mind, you can stop
the job in a controlled manner - not just 'killing' the job.  Since this is
an RPG program, you can watch it run and see the record counts ticking away.
You don't have to worry why it's chewing up so much CPU and not appearing to
get anyplace...

But that's just my humble (and biased) opinion.

BTW - Novartis Canada (now Patheon) is running this tool.  Let me know if
you would like more information.

++++++++++++++++++++++++++++++++++++++++++++
Go... FASTER!  Without an upgrade!  With ARCTOOLS/400(tm)
http://www.arctools.com
info@arctools.com 
DCSoftware, Inc.
Ph: (508) 435-8243
Fax: (508) 435-4498
++++++++++++++++++++++++++++++++++++++++++++
 
-------Original Message-------
 
From: Midrange Systems Technical Discussion
Date: Monday, February 10, 2003 04:52:31
To: midrange-l@midrange.com
Subject: Runtime for deleteing lots of records
 
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...
 
Regards,
 
Oliver

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.