RE: Runtime for deleteing lots of records

[Jeff Bull <Jeff.Bull@xxxxxxxxxxxxxxx>]

... if you know you are going to delete such a high proportion of the total
records, and you have the disk space, it might be quicker to copy the
records you need to keep, then clear the file and cpyf the keep-records.  

I suspect you may have a few access paths over your physicals, changing the
maintenance of them to rebuild; if you could delete the *LFs and recreate
them after the record-delete processing, so much the better.

If it is possible to turn off journalling for the duration, this too can
have a significant effect on performance.

Jeffrey E. Bull
OS400 Software Support Consultant
 
IBM Certified AS/400 System Administrator 

tel.      +44 [0] 149 454 9533               swb.   +44 [0] 149 454 9400
mbl.     +44 [0] 786 750 4961               fax.    +44 [0] 149 454 9454
web.     http://www.itm-group.co.uk
 
ITM Group Ltd, Latimer Square, White Lion Road, Amersham, Buckinghamshire,
HP7 9JQ, United Kingdom


-----Original Message-----
From: oliver.wenzel@cibavision.novartis.com
[mailto:oliver.wenzel@cibavision.novartis.com]
Sent: 10 February 2003 09:35
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.


________________________________________________________________________
This e-mail has been scanned for all viruses by ITM. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, 
email marketing@itm-group.co.uk
ITM - Managing Communication and Information through technology
Company registration number - 3783433
________________________________________________________________________


DISCLAIMER

Any opinions expressed in this email are those of the
individual and not necessarily the Company. This email
and any files transmitted with it, including replies and
forwarded copies (which may contain alterations) 
subsequently transmitted from the Company, are 
confidential and solely for the use of the intended recipient. If you are not 
the intended recipient or the 
person responsible for delivering to the intended recipient, 
be advised that you have received this email in error and
 that any use is strictly prohibited.

If you have received this email in error please notify the IT
manager by telephone on +44 (0)870 871 2233 or via 
email to Administrator@itm-group.co.uk, including a copy
of this message. Please then delete this email and 
destroy any copies of it.

________________________________________________________________________This 
e-mail has been scanned for all viruses by ITM. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, 
email marketing@itm-group.co.uk
ITM - Managing Communication and Information through technology
Company registration number - 
3783433________________________________________________________________________