Improving SQL Queries in DB2

Is this a one time thing? If so, pick some slow time and do it.
Another thing to try but you will need a down time. Export the records you
want, drop the table, and import the data.

The best thing to do long term is not to let it get this way. Create an
agent (program) the cleans up on a schedule or an event.

"Melissa Matthews" <melissa_matthews@freddiemac.com> wrote:
>
>Basically I want to delete a large number of records in groups of
>about 100000 so the logs don't over flow. The only way I have been
>able to get something equivalent to a row ID is the following:
>
>DELETE FROM UDBADM.whole_loan_master_detailed_lnlv
>WHERE (LOAN_SEQ_NBR,LOAN_NBR) IN
>(SELECT LOAN_SEQ_NBR,LOAN_NBR
> FROM (SELECT ROW_NUMBER() OVER(ORDER BY LOAN_SEQ_NBR) as ROWID,

>
> LOAN_SEQ_NBR,LOAN_NBR FROM UDBADM.whole_loan_master_detailed_lnlv) A

>
> WHERE ROWID < 1000)
>
>This works, but it looks like it would take about six hours to delete a
million
>records if we let it run all the way through.
>
>Is there some easier, i.e faster way to do this?

>
>"Erin Gannon" <egannon@dev-archive.com> wrote:
>>
>>Do you have some SQL you want to improve, but you're not sure how?
>>
>>Erin Gannon
>>Editorial Assistant
>>
>