RE: SQL Delete question

I'd address COMPRESS(*NO) on the second CPYF; I think FROMRCD(1)
COMPRESS(*NO) invokes a block copy routine.

-rf

> -----Original Message-----
> From: midrange-l-bounces@midrange.com
[mailto:midrange-l-bounces@midrange.com] On Behalf Of
> Richard B Baird
> Sent: Tuesday, January 21, 2003 1:36 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: SQL Delete question
> 
> 
> Dennis,
> 
> does this file need 24x7 availability?   is there a couple hours you can
> block access to it?
> 
> files with that many deleted records have a performance penalty.  plus,
you
> are still wasting all that space, because the dead records are still part
> of the allocation of the file.
> 
> With a file that size, and with the number of records you want to delete
> (sounds to me like it might be a lot more than what will be left when
done)
> i would suggest doing something like this:
> 
> CPYF FROMFILE(BPCSF/ESN) TOFILE(WRKLIB/WRKFILE) CRTFILE(*YES)
> FROMRCD(1) INCREL(
> (*IF SNDESC *NE 'Billing date: approx. 4/15/02 March 2002 usage')
> (*AND SNDESC *NE 'the other description you are looking for'))
> (*AND SNDESC *NE 'the third description you are looking for').
> 
> then:
> 
> CPYF FROMFILE(WRKLIB/WRKFILE) FROMFILE(BPCSF/ESN) MBROPT(*REPLACE)
> FROMRCD(1)
> 
> I believe the cpyf will run quicker than your sql delete, and even quicker
> because when you specify FROMRCD(1), it reads the file sequentially,
rather
> than by key.
> 
> you will be much happier with the results (no deleted records in file) and
> I truely believe it will be much faster.  BUT it does require that the
file
> be locked down from start to finish.
> 
> hth,
> 
> rick
> 
> _______________________________________________
> 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.cgi/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.