Need help with delete

I need to put together some esql code that will delete N-rows of data from a table when the data has aged 4 days. The rub is I can't hit the system hard enough so that the customer notices - i.e.: lock table for 5 min. (input "maybe" 1000 rows/minute) Here is the plan I've put together: About every 10 minutes - ----------------------------------- create temp table T1 create temp table T2dirty mode insert into T1 select rowid from data where date > 4 days old while (not done){ move (next) ? 500 ? (1000) from T1 to T2 delete from data where in (select from T2) (possible sleep of 2 seconds)} ----------------------------------- I can't suck up a million locks I can't lock the table for any length of time I can't run one big job at 2am I don't care how slow it runs as long its done within the ? 10 minutes. There's my plan - bummer of a birthmark Ralph - take your best shot - give me a better idea. Thanks Chuck Lidderdale liderbug@rmi.net

create a descending index on the date field and then use 4gl or a stored procedure so you can delete the oldest data first: (Check syntax and fill in the blanks yourself): create index tbl_date_idx on table (date_field desc) let p_date=today-4 let sql_str= "select 1 from table where date_field>? ", "order by date_field desc" prepare sel_id from sql_str declare sel_cur cursor for sel_str let sql_str= "delete from table where current of sel_cur" # You might need to use the 'cursor_name() function here # eg "delete from table where current of ", cursor_name(sel_cur) prepare del_id from sql str open sel_cur using p_date let i=1 foreach sel_cur execute del_id let i=i+1 if i>1000 then exit foreach end if end foreach Hope that helps, Douglas Wilson On Fri, 16 Apr 1999 17:08:41 GMT, Chuck Lidderdale <chuck.lidderdale@wcom.com> wrote: >I need to put together some esql code that will delete >N-rows of data from a table when the data has aged >4 days. The rub is I can't hit the system hard enough >so that the customer notices - i.e.: lock table for 5 min. >(input "maybe" 1000 rows/minute) > >Here is the plan I've put together: >About every 10 minutes - >----------------------------------- >create temp table T1 >create temp table T2>dirty mode >insert into T1 select rowid from data where date > 4 days old >while (not done)>{ > move (next) ? 500 ? (1000) from T1 to T2 > delete from data where in (select from T2) > (possible sleep of 2 seconds)>} >----------------------------------- >I can't suck up a million locks >I can't lock the table for any length of time >I can't run one big job at 2am >I don't care how slow it runs as long its done >within the ? 10 minutes. > >There's my plan - bummer of a birthmark Ralph - take your >best shot - give me a better idea. > >Thanks >Chuck Lidderdale >liderbug@rmi.net > >

Hmm, there's a couple of problems with the solution below, like you'd need a 'for update' cursor in order to use the 'where current of' clause, but you're not allowed to do an 'order by' on an update cursor. But I'll probably make it worse if I try to fix it this late at night, so I'll just say that if you select primary key or unique index fields (or as a last resort, rowid, keeping in mind what the problems with that might be), then you can delete based on the selected fields. Then you don't need an update cursor or a 'delete where current of' statement. Late at night, some moron sed: >create a descending index on the date field and then >use 4gl or a stored procedure so you can delete the oldest data first: >(Check syntax and fill in the blanks yourself): >create index tbl_date_idx on table (date_field desc) >let p_date=today-4 >let sql_str= >"select 1 from table where date_field>? ", >"order by date_field desc" >prepare sel_id from sql_str >declare sel_cur cursor for sel_str >let sql_str= >"delete from table where current of sel_cur" ># You might need to use the 'cursor_name() function here ># eg "delete from table where current of ", cursor_name(sel_cur) >prepare del_id from sql str >open sel_cur using p_date >let i=1 >foreach sel_cur > execute del_id > let i=i+1 > if i>1000 then exit foreach end if >end foreach >Hope that helps, >Douglas Wilson >On Fri, 16 Apr 1999 17:08:41 GMT, Chuck Lidderdale <chuck.lidderdale@wcom.com> >wrote: >>I need to put together some esql code that will delete >>N-rows of data from a table when the data has aged >>4 days. The rub is I can't hit the system hard enough >>so that the customer notices - i.e.: lock table for 5 min. >>(input "maybe" 1000 rows/minute) >> >>Here is the plan I've put together: >>About every 10 minutes - >>----------------------------------- >>create temp table T1 >>create temp table T2>>dirty mode >>insert into T1 select rowid from data where date > 4 days old >>while (not done)>>{ >> move (next) ? 500 ? (1000) from T1 to T2 >> delete from data where in (select from T2) >> (possible sleep of 2 seconds)>>} >>----------------------------------- >>I can't suck up a million locks >>I can't lock the table for any length of time >>I can't run one big job at 2am >>I don't care how slow it runs as long its done >>within the ? 10 minutes. >> >>There's my plan - bummer of a birthmark Ralph - take your >>best shot - give me a better idea. >> >>Thanks >>Chuck Lidderdale >>liderbug@rmi.net >> >>