Avoid use of logical logs during mass DELETE
Joerg wanted to run a daily mass DELETE without filling logical logs. Switching the table to RAW avoids logging, but inserts arriving during the delete would then be unlogged too. Art Kagel confirmed logging is all-or-nothing per table/database. Martin suggested fragmenting by the delete criteria and detaching fragments (not licensed for Joerg); Luis proposed simulating this with per-day tables plus a union view and insert trigger; John Miller suggested splitting deletes by ROWID ranges. Joerg's own working approach: an insert trigger diverting new rows to a logged table B, set A to RAW, delete, then restore A to STANDARD, remove duplicates and copy B back.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi there, May be this question seems a bit strange... I'm looking for an idea how to avoid the use of logical logs for a daily mass DELETE task. I now that I can temporary use ALTER TABLE TYPE (RAW) - but during the mass DELETE it is possbile that some other data out of the DELETE WHERE clause could be INSERT'ed. I would like to have the INSERT's done as transcation during the mass DELETE. Does anyone have an idea how to realize this? Thanks is advance Joerg
Hi, one idea that comes to my mind (and surely needs some elaboration befor a final implementation...) : you could fragment the table, using the criteria (hopefully it is rather simple) of your daily deletes as the fragmentation expression. That way every day you would have the rows to be deleted all together in a single fragment ... that you simply 'throw away' (I think 'detatch' is the correct term). So you do not have to do an actual "delete" at all. Of course you'd have to provide a new fragment (also daily, I guess) for the rows to be inserted during the current day. This would be 'attach fragment', I think. TIA, Martin -- Martin Fuerderer IBM Informix Development Munich, Germany Information Management Read about the Informix Warehouse Accelerator: http://tinyurl.com/the-iwa-blog IBM Deutschland Research & Development GmbH Chairman of the Supervisory Board: Martin Jetter Board of Management: Dirk Wittkopp Corporate Seat: Boeblingen, Germany Reg.-Gericht: Amtsgericht Stuttgart, HRB 243294 ids-bounces@iiug.org wrote on 07/08/2011 09:52:14 AM: > Hi there, > > May be this question seems a bit strange... > > I'm looking for an idea how to avoid the use of logical logs for a daily mass > DELETE task. I now that I can temporary use ALTER TABLE TYPE (RAW) - but > during the mass DELETE it is possbile that some other data out of the DELETE > WHERE clause could be INSERT'ed. I would like to have the INSERT's done as > transcation during the mass DELETE. > > Does anyone have an idea how to realize this? > > Thanks is advance > > Joerg > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Thanks for your thoughts - but as we don't have license for table
fragmentation I cannot go that way.
I'm just "fighting" with the following idea:
I would like to create a trigger that will move all inserts to a logged table
B.
This trigger on table A will be activated if mass delete is starting.
Any dataset inserted will then be copied to another table. After mass delete
is finshed the table is set back from RAW and STANDARD and the trigger can be
disabled.
Then I can copy back the data to the origin table A to have them safe (logged)
in case of a desaster.
Of course at that time they are also in the triggered table A (just not
logged) so I have to delete them first before I leave the RAW mode and copy
them back from table B.
How could a trigger look like that the "inserts" are *moved* - not only copied
- to table B during the mass delete?
Or in other words:
I only want the insert's in table B - not in table A
Here's my current schema:
create trigger copy_to_logged_table_Binsert on A
referencing new as newrow
(insert into B(col1,col2, ...)
values (newrow.col1, newrow.col2, ... ));
Logging is all or nothing at the table or database level. You cannot turn logging off for the deletes and still have inserts to the same table be logged. No. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Jul 8, 2011 at 3:52 AM, JOERG REDEMANN <joerg.redemann@sabre.com>wrote: > Hi there, > > May be this question seems a bit strange... > > I'm looking for an idea how to avoid the use of logical logs for a daily > mass > DELETE task. I now that I can temporary use ALTER TABLE TYPE (RAW) - but > during the mass DELETE it is possbile that some other data out of the > DELETE > WHERE clause could be INSERT'ed. I would like to have the INSERT's done as > transcation during the mass DELETE. > > Does anyone have an idea how to realize this? > > Thanks is advance > > Joerg > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf3071cc0419afbc04a78e38da
Well...
The solution decsribed above works fine for me:
(table B is in standard mode)
create trigger copy_to_logged_table_Binsert on A
referencing new as newrow
(insert into B(col1,col2, ...)
values (newrow.col1, newrow.col2, ... ));
1. enable this trigger
2. alter table A to raw
3. start mass delete on table A
4. disable this trigger
5. alter table A to standard
5. delete doubles from table A which have been copied by trigger to B during
mass delete
6. copy all data back from B to A
7. truncate B
So at any time in case of a disaster all inserts are tracked in logical logs.
Joerg
Hi,
You can simulate a fragmentation per day as follows:
1) create many tables A, with the name of A_<date>
2) create a view named A, with a 'union' of "select * from A_<date>".
3) Create a trigger on the view of insert that put the row in a table
which have the corresponding date.
The mass delete is only the re-creation of the view and the triggers,
to use the new table (of the new day) and making free the las table day.
After that, drop the released table.
I hope this help you.
Best Regards,
Quique Feoli
At 10:11 a.m. 08/07/2011, JOERG REDEMANN wrote:
>Well...
>
>The solution decsribed above works fine for me:
>
>(table B is in standard mode)
>
>create trigger copy_to_logged_table_B>insert on A
>referencing new as newrow
>(insert into B(col1,col2, ...)
>values (newrow.col1, newrow.col2, ... ));
>
>1. enable this trigger
>2. alter table A to raw
>3. start mass delete on table A
>4. disable this trigger
>5. alter table A to standard
>5. delete doubles from table A which have been copied by trigger to B during
>mass delete
>6. copy all data back from B to A
>7. truncate B
>
>So at any time in case of a disaster all inserts are tracked in logical logs.
>
>Joerg
>
>
>*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
Since you are not using fragmentation you have a much simpler way of
breaking up the deletes. You can delete by the pseudo column called
ROWID.
ROWID will allow you to break up the data by pages.
delete from table_1 where rowid < '0x10000' ------- DELETE FROMFIRST 256 pages of the table
delete from table_1 where rowid >= '0x10000' and rowid ----- DELETE
FROM 256 page to 512 page
delete from table_1 where rowid > = '0x20000' ----- DELETE FROMPAGE 512 and greater
rowid = page number * 256
Hope this helps.
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 07/08/2011 01:50:11 PM:
> From:
>
> "Luis Enrique Feoli" <quifeoli@montevideo.com.uy>
>
> To:
>
> ids@iiug.org
>
> Date:
>
> 07/08/2011 01:50 PM
>
> Subject:
>
> Re: Avoid use of logical logs during mass DELETE [24284]
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Hi,
>
> You can simulate a fragmentation per day as follows:
>
> 1) create many tables A, with the name of A_<date>
> 2) create a view named A, with a 'union' of "select * from A_<date>".
> 3) Create a trigger on the view of insert that put the row in a table
> which have the corresponding date.
>
> The mass delete is only the re-creation of the view and the triggers,
> to use the new table (of the new day) and making free the las table day.
> After that, drop the released table.
>
> I hope this help you.
> Best Regards,
> Quique Feoli
>
> At 10:11 a.m. 08/07/2011, JOERG REDEMANN wrote:
> >Well...
> >
> >The solution decsribed above works fine for me:
> >
> >(table B is in standard mode)
> >
> >create trigger copy_to_logged_table_B> >insert on A
> >referencing new as newrow
> >(insert into B(col1,col2, ...)
> >values (newrow.col1, newrow.col2, ... ));
> >
> >1. enable this trigger
> >2. alter table A to raw
> >3. start mass delete on table A
> >4. disable this trigger
> >5. alter table A to standard
> >5. delete doubles from table A which have been copied by trigger toB
during
> >mass delete
> >6. copy all data back from B to A
> >7. truncate B
> >
> >So at any time in case of a disaster all inserts are tracked in
> logical logs.
> >
> >Joerg
> >
> >
>
>
>*******************************************************************************
> >
> > Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>