Massive delete - any ideas to do it nondisruptively?

I'm looking for some clever soul out there to help me with what should be a well-solved problem by now. (If not, it's our chance to make history!) The situation is fairly classic: I have several large tables, one parent and five children, tied together by a serial number. The parent table, but not the children, has a timestamp. These are active tables, averaging about 100,000 rows per day in the parent table and more in the children. When the data gets old enough, the rows get deleted. Question: How can I purge old data, based on the timestamp, from these tables NONDISRUPTIVELY? I know I can use HPL to unload the data I want to keep, drop the table, and reload it. That works but it's disruptive. Obviously, I can just delete the relevant information, either in a single cascading delete or by a joined delete for the children and a ranged one from the parents. That works, too, but it's slow. I could copy the data I want to keep to new tables, drop the originals and rename the copies. The problems there are that it's still a little disruptive (though tolerably so) and the data in the tables being copied is being updated during the copy, so keeping synchronization would be hard. The fatal flaw in this plan, though, is I don't have enough disk space to copy the tables. The idea has been advanced of fragmenting the parent table based on the date and time, detaching the fragment with the data to be purged, then dropping the resultant new table. That works great for the parent (maybe, if indexes don't have to be rebuilt), but how do I do the children? I have no control over the serial numbers being used. So......... anyone out there have any great ideas for running these massive deletes as a lights-out, nondisruptive process? (IDS 7.31.FC6, HP/UX 10.20) -Don Wolford, Verizon Data Services _______________________________________________________ The FREE service that prevents junk email http://www.mailshell.com

What development tools do you have access to? -----Original Message----- From: iiug@dcw.mailshell.com [mailto:iiug@dcw.mailshell.com] Sent: Wednesday, June 18, 2003 10:52 AM To: ids@iiug.org Subject: Massive delete - any ideas to do it nondisruptively? [1386] I'm looking for some clever soul out there to help me with what should be a well-solved problem by now. (If not, it's our chance to make history!) The situation is fairly classic: I have several large tables, one parent and five children, tied together by a serial number. The parent table, but not the children, has a timestamp. These are active tables, averaging about 100,000 rows per day in the parent table and more in the children. When the data gets old enough, the rows get deleted. Question: How can I purge old data, based on the timestamp, from these tables NONDISRUPTIVELY? I know I can use HPL to unload the data I want to keep, drop the table, and reload it. That works but it's disruptive. Obviously, I can just delete the relevant information, either in a single cascading delete or by a joined delete for the children and a ranged one from the parents. That works, too, but it's slow. I could copy the data I want to keep to new tables, drop the originals and rename the copies. The problems there are that it's still a little disruptive (though tolerably so) and the data in the tables being copied is being updated during the copy, so keeping synchronization would be hard. The fatal flaw in this plan, though, is I don't have enough disk space to copy the tables. The idea has been advanced of fragmenting the parent table based on the date and time, detaching the fragment with the data to be purged, then dropping the resultant new table. That works great for the parent (maybe, if indexes don't have to be rebuilt), but how do I do the children? I have no control over the serial numbers being used. So......... anyone out there have any great ideas for running these massive deletes as a lights-out, nondisruptive process? (IDS 7.31.FC6, HP/UX 10.20) -Don Wolford, Verizon Data Services _______________________________________________________ The FREE service that prevents junk email http://www.mailshell.com "CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel Retail. This email message and all attachments may contain legally privileged and confidential information intended solely for the use of the addressee. If you are not the intended recipient, you should immediately stop reading this message and delete it from the system. Any unauthorized reading, distribution, copying, or other use of this message or its attachments is strictly prohibited. All personal messages express solely the sender's views and not those of WHSmith USA Travel Retail. This message may not be copied or distributed without this disclaimer."

Clever soul? Anyway, have you considered fragmenting the tables on a timestamp range, then detaching the oldest fragment and dropping the detached table? It's very fast and minimally disruptive. Plus, it may even improve performance overall... :o) -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche >From: iiug@dcw.mailshell.com >To: ids@iiug.org >Subject: Massive delete - any ideas to do it nondisruptively? [1386] Date: >Wed, 18 Jun 2003 10:52:13 -0400 (EDT) > >I'm looking for some clever soul out there to help me with what should be a >well-solved problem by now. (If not, it's our chance to make history!) > >The situation is fairly classic: I have several large tables, one parent >and five children, tied together by a serial number. The parent table, but >not the children, has a timestamp. These are active tables, averaging >about 100,000 rows per day in the parent table and more in the children. >When the data gets old enough, the rows get deleted. > >Question: How can I purge old data, based on the timestamp, from these >tables NONDISRUPTIVELY? > >I know I can use HPL to unload the data I want to keep, drop the table, and >reload it. That works but it's disruptive. > >Obviously, I can just delete the relevant information, either in a single >cascading delete or by a joined delete for the children and a ranged one >from the parents. That works, too, but it's slow. > >I could copy the data I want to keep to new tables, drop the originals and >rename the copies. The problems there are that it's still a little >disruptive (though tolerably so) and the data in the tables being copied is >being updated during the copy, so keeping synchronization would be hard. >The fatal flaw in this plan, though, is I don't have enough disk space to >copy the tables. > >The idea has been advanced of fragmenting the parent table based on the >date and time, detaching the fragment with the data to be purged, then >dropping the resultant new table. That works great for the parent (maybe, >if indexes don't have to be rebuilt), but how do I do the children? I have >no control over the serial numbers being used. > >So......... anyone out there have any great ideas for running these massive >deletes as a lights-out, nondisruptive process? (IDS 7.31.FC6, HP/UX >10.20) > >-Don Wolford, Verizon Data Services > > >_______________________________________________________ >The FREE service that prevents junk email http://www.mailshell.com > _________________________________________________________________ Express yourself with cool emoticons - download MSN Messenger today! http://www.msn.co.uk/messenger

How abt a trigger on delete of master table which will delete from children and then using detach fragment? I dont know if informix will allow that, but you can try Rgds Preetinder "Obnoxio The...." wrote: > Clever soul? Anyway, have you considered fragmenting the tables on a > timestamp range, then detaching the oldest fragment and dropping the > detached table? > > It's very fast and minimally disruptive. Plus, it may even improve > performance overall... :o) > > -- > Bye now, > Obnoxio > > "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" > - Coluche > > >From: iiug@dcw.mailshell.com > >To: ids@iiug.org > >Subject: Massive delete - any ideas to do it nondisruptively? [1386] Date: > >Wed, 18 Jun 2003 10:52:13 -0400 (EDT) > > > >I'm looking for some clever soul out there to help me with what should be a > >well-solved problem by now. (If not, it's our chance to make history!) > > > >The situation is fairly classic: I have several large tables, one parent > >and five children, tied together by a serial number. The parent table, but > >not the children, has a timestamp. These are active tables, averaging > >about 100,000 rows per day in the parent table and more in the children. > >When the data gets old enough, the rows get deleted. > > > >Question: How can I purge old data, based on the timestamp, from these > >tables NONDISRUPTIVELY? > > > >I know I can use HPL to unload the data I want to keep, drop the table, and > >reload it. That works but it's disruptive. > > > >Obviously, I can just delete the relevant information, either in a single > >cascading delete or by a joined delete for the children and a ranged one > >from the parents. That works, too, but it's slow. > > > >I could copy the data I want to keep to new tables, drop the originals and > >rename the copies. The problems there are that it's still a little > >disruptive (though tolerably so) and the data in the tables being copied is > >being updated during the copy, so keeping synchronization would be hard. > >The fatal flaw in this plan, though, is I don't have enough disk space to > >copy the tables. > > > >The idea has been advanced of fragmenting the parent table based on the > >date and time, detaching the fragment with the data to be purged, then > >dropping the resultant new table. That works great for the parent (maybe, > >if indexes don't have to be rebuilt), but how do I do the children? I have > >no control over the serial numbers being used. > > > >So......... anyone out there have any great ideas for running these massive > >deletes as a lights-out, nondisruptive process? (IDS 7.31.FC6, HP/UX > >10.20) > > > >-Don Wolford, Verizon Data Services > > > > > >_______________________________________________________ > >The FREE service that prevents junk email http://www.mailshell.com > > > > _________________________________________________________________ > Express yourself with cool emoticons - download MSN Messenger today! > http://www.msn.co.uk/messenger