Re: SQL Delete question

["Richard B Baird" <rbaird@xxxxxxxxxxxxxxxxxxxxx>]

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