table access during trying to drop

Posted in 2006

Summary

Deba needed to refresh a few 100k-row tables each weekend while users stayed online: delete-and-reload took 4-5 hours, while drop-and-recreate was faster but user locks on the recreated table blocked index creation. Suggestions were to build a new copy of the table offline (load data, create indexes) and then swap it in with RENAME TABLE (available in IDS 9.3, though a rename can fail if a session is using the table). Art Kagel's final answer: keep dated real table names and point a public synonym at them, dropping and recreating the synonym after each load.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: General Discussion

DEBA mishra — Fri, 18 Aug 2006 02:27:29 — source: IIUG Forums & Mailing Lists

Hi We need suggestion regarding this problem we are facing. We have 2-3 tables in our database whose data needs to be syncronised each weekend. During this time tables are online to users.Whole of the table data gets deleted and then syncronization file gats uploaded. the table contains more than 100000 records. So generally by above process user faces error that record not found if the record gets deleted while user trying to access it during this period. But the problem was it was taking long time 4-5 hrs to complete this deleting and loading. So alternatly what was done was to drop the table and reload again, this was faster but the problem was complicated because the sytem went down because user locked the table when it was recreated. So index creation was not possible. Is their any other way we can do this. Any suggestions are welcome Regards Deba

↪ replying to DEBA mishra

MOHD SHANI SHAFIE — Fri, 18 Aug 2006 03:10:48 — source: IIUG Forums & Mailing Lists

Hi, Maybe you could - 1. Automate the drop/re-create at odd hours. May be at 01:00 on Sunday morning or at suitable time. 2. some IDS has SQL commad - RENAME TABLE Check your version and pls test before putting to Production. Regards. Shani

↪ replying to DEBA mishra

Erhard Eiselen — Fri, 18 Aug 2006 03:15:03 — source: IIUG Forums & Mailing Lists

DEBA mishra wrote: > Sent: Friday, August 18, 2006 04:27 > To: ids@iiug.org > Subject: table access during trying to drop [7290] > > > Hi > > We need suggestion regarding this problem we are facing. > > We have 2-3 tables in our database whose data needs to be > syncronised each > weekend. During this time tables are online to users.Whole of > the table data > gets deleted and then syncronization file gats uploaded. the > table contains > more than 100000 records. > I take it you are using and IDS engine? <snip> > > So alternatly what was done was to drop the table and reload > again, this was > faster but the problem was complicated because the sytem went > down because > user locked the table when it was recreated. So index > creation was not > possible. > > Is their any other way we can do this. > > Any suggestions are welcome > > Regards > Deba > So an option would be is to create an exact copy of the table you want to refresh, but without the data, say table_x_temp (but do not make it a temp table). Then load your data into thus is new table, create the indexes. Then simply rename the old table to table_x_old and rename the "new" table to table_x Of course the rename exercise does not work on older Informix engines. Erhard

↪ replying to Erhard Eiselen

DEBA mishra — Fri, 18 Aug 2006 03:50:42 — source: IIUG Forums & Mailing Lists

Thanks but can anybody suggest which version IDS allows renaming, we are using 9.3now Regards Deba On 8/18/06, Erhard Eiselen <eis@tad.co.za> wrote: > > > DEBA mishra wrote: > > Sent: Friday, August 18, 2006 04:27 > > To: ids@iiug.org > > Subject: table access during trying to drop [7290] > > > > > > Hi > > > > We need suggestion regarding this problem we are facing. > > > > We have 2-3 tables in our database whose data needs to be > > syncronised each > > weekend. During this time tables are online to users.Whole of > > the table data > > gets deleted and then syncronization file gats uploaded. the > > table contains > > more than 100000 records. > > > I take it you are using and IDS engine? > > <snip> > > > > So alternatly what was done was to drop the table and reload > > again, this was > > faster but the problem was complicated because the sytem went > > down because > > user locked the table when it was recreated. So index > > creation was not > > possible. > > > > Is their any other way we can do this. > > > > Any suggestions are welcome > > > > Regards > > Deba > > > > So an option would be is to create an exact copy of the table you > want to refresh, but without the data, say table_x_temp (but do > not make it a temp table). Then load your data into thus is new > table, create the indexes. Then simply rename the old table to > table_x_old and rename the "new" table to table_x > > Of course the rename exercise does not work on older Informix engines. > > Erhard > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

↪ replying to DEBA mishra

Erhard Eiselen — Fri, 18 Aug 2006 04:13:05 — source: IIUG Forums & Mailing Lists

IDS 9.3 should be ok. Here is a simple test. Create a table called abctest with one column Then try the following statement rename table abctest to bcbtest If it tells you the table was renamed (at the bottom) you are good to go! > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On > Behalf Of DEBA mishra > Sent: Friday, August 18, 2006 05:51 > To: ids@iiug.org > Subject: Re: table access during trying to drop [7293] > > > Thanks > but can anybody suggest which version IDS allows renaming, we > are using 9.3now > > Regards > Deba > > On 8/18/06, Erhard Eiselen <eis@tad.co.za> wrote: > > > > > > DEBA mishra wrote: > > > Sent: Friday, August 18, 2006 04:27 > > > To: ids@iiug.org > > > Subject: table access during trying to drop [7290] > > > > > > > > > Hi > > > > > > We need suggestion regarding this problem we are facing. > > > > > > We have 2-3 tables in our database whose data needs to be > > > syncronised each > > > weekend. During this time tables are online to users.Whole of > > > the table data > > > gets deleted and then syncronization file gats uploaded. the > > > table contains > > > more than 100000 records. > > > > > I take it you are using and IDS engine? > > > > <snip> > > > > > > So alternatly what was done was to drop the table and reload > > > again, this was > > > faster but the problem was complicated because the sytem went > > > down because > > > user locked the table when it was recreated. So index > > > creation was not > > > possible. > > > > > > Is their any other way we can do this. > > > > > > Any suggestions are welcome > > > > > > Regards > > > Deba > > > > > > > So an option would be is to create an exact copy of the table you > > want to refresh, but without the data, say table_x_temp (but do > > not make it a temp table). Then load your data into thus is new > > table, create the indexes. Then simply rename the old table to > > table_x_old and rename the "new" table to table_x > > > > Of course the rename exercise does not work on older > Informix engines. > > > > Erhard > > > > > > > > > ************************************************************** > ***************** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. > >

↪ replying to Erhard Eiselen

Robert Roussey\\\\(MIS\\\\) — Fri, 18 Aug 2006 11:33:26 — source: IIUG Forums & Mailing Lists

The rename *may* fail is a user session is accessing the table when you try to rename it. Bob Roussey Unix / Informix Administration Spirit Airlines Robert.Roussey@SpiritAir.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Erhard Eiselen Sent: Friday, August 18, 2006 12:13 AM To: ids@iiug.org Subject: RE: table access during trying to drop [7294] IDS 9.3 should be ok. Here is a simple test. Create a table called abctest with one column Then try the following statement rename table abctest to bcbtest If it tells you the table was renamed (at the bottom) you are good to go! > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On > Behalf Of DEBA mishra > Sent: Friday, August 18, 2006 05:51 > To: ids@iiug.org > Subject: Re: table access during trying to drop [7293] > > > Thanks > but can anybody suggest which version IDS allows renaming, we > are using 9.3now > > Regards > Deba > > On 8/18/06, Erhard Eiselen <eis@tad.co.za> wrote: > > > > > > DEBA mishra wrote: > > > Sent: Friday, August 18, 2006 04:27 > > > To: ids@iiug.org > > > Subject: table access during trying to drop [7290] > > > > > > > > > Hi > > > > > > We need suggestion regarding this problem we are facing. > > > > > > We have 2-3 tables in our database whose data needs to be > > > syncronised each > > > weekend. During this time tables are online to users.Whole of > > > the table data > > > gets deleted and then syncronization file gats uploaded. the > > > table contains > > > more than 100000 records. > > > > > I take it you are using and IDS engine? > > > > <snip> > > > > > > So alternatly what was done was to drop the table and reload > > > again, this was > > > faster but the problem was complicated because the sytem went > > > down because > > > user locked the table when it was recreated. So index > > > creation was not > > > possible. > > > > > > Is their any other way we can do this. > > > > > > Any suggestions are welcome > > > > > > Regards > > > Deba > > > > > > > So an option would be is to create an exact copy of the table you > > want to refresh, but without the data, say table_x_temp (but do > > not make it a temp table). Then load your data into thus is new > > table, create the indexes. Then simply rename the old table to > > table_x_old and rename the "new" table to table_x > > > > Of course the rename exercise does not work on older > Informix engines. > > > > Erhard > > > > > > > > > ************************************************************** > ***************** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. > > ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to DEBA mishra

ART KAGEL, BLOOMBERG/ — Fri, 18 Aug 2006 12:56:35 — source: IIUG Forums & Mailing Lists

All Informix versions from the beginning of Online 4.01 have had the RENAME TABLE command. Art S. Kagel ----- Original Message ----- From: Deba Mishra <ids@iiug.org> At: 8/17 23:56:21 Thanks but can anybody suggest which version IDS allows renaming, we are using 9.3now Regards Deba On 8/18/06, Erhard Eiselen <eis@tad.co.za> wrote: > > > DEBA mishra wrote: > > Sent: Friday, August 18, 2006 04:27 > > To: ids@iiug.org > > Subject: table access during trying to drop [7290] > > > > > > Hi > > > > We need suggestion regarding this problem we are facing. > > > > We have 2-3 tables in our database whose data needs to be > > syncronised each > > weekend. During this time tables are online to users.Whole of > > the table data > > gets deleted and then syncronization file gats uploaded. the > > table contains > > more than 100000 records. > > > I take it you are using and IDS engine? > > <snip> > > > > So alternatly what was done was to drop the table and reload > > again, this was > > faster but the problem was complicated because the sytem went > > down because > > user locked the table when it was recreated. So index > > creation was not > > possible. > > > > Is their any other way we can do this. > > > > Any suggestions are welcome > > > > Regards > > Deba > > > > So an option would be is to create an exact copy of the table you > want to refresh, but without the data, say table_x_temp (but do > not make it a temp table). Then load your data into thus is new > table, create the indexes. Then simply rename the old table to > table_x_old and rename the "new" table to table_x > > Of course the rename exercise does not work on older Informix engines. > > Erhard > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to DEBA mishra

ART KAGEL, BLOOMBERG/ — Fri, 18 Aug 2006 13:34:33 — source: IIUG Forums & Mailing Lists

OK, here's the solution: RENAME The table permanently but set a synonym to the name used in the applications to point to the real table. Then to load the new day's data create a new table, load the data, destroy the synonym pointing to yesterday's table and recreate it pointing to the new table. Then you can keep a day or two of historical data and just drop the table when you are through with it. So, for example, if the current apps call the table 'daily_data' rename the daily data table to be say daily_data_20060818 and create a synonym: CREATE PUBLIC SYNONYM daily_data FOR daily_data_20060818; Then at resynch time: CREATE TABLE daily_data_20060819 (.........); LOAD FROM .... INSERT INTO daily_data_20060819; { Or however you load the data into the new table } DROP SYNONYM daily_data;CREATE PUBLIC SYNONYM daily_data FOR daily_data_20060819; Art S. Kagel ----- Original Message ----- From: Deba Mishra <ids@iiug.org> At: 8/17 22:33:00 Hi We need suggestion regarding this problem we are facing. We have 2-3 tables in our database whose data needs to be syncronised each weekend. During this time tables are online to users.Whole of the table data gets deleted and then syncronization file gats uploaded. the table contains more than 100000 records. So generally by above process user faces error that record not found if the record gets deleted while user trying to access it during this period. But the problem was it was taking long time 4-5 hrs to complete this deleting and loading. So alternatly what was done was to drop the table and reload again, this was faster but the problem was complicated because the sytem went down because user locked the table when it was recreated. So index creation was not possible. Is their any other way we can do this. Any suggestions are welcome Regards Deba ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.