reloading a table atomically

Hi Informix Group, We have been using IDS for 15 years or so, but our DB's have always been unlogged/raw. No transactions. We now have a need to reload a table atomically. By "reload" I mean clear all records from the table and load it fresh from a .dat file. By "atomically" I mean the change occuring without other concurrent processes seeing the table in an intermediate state (empty or missing records). Obviously using a transaction would be one way of doing this, but like I said we have never enabled logging on the DB in the past, and would prefer not to have to change a lot of existing INSERT/UPDATE/DELETE code to begin/end transactions. I thought about using an EXCLUSIVE LOCK during the reload, but I believe that with a raw table other processes will ignore the lock when SELECTing, so they will see the table in an intermediate state. I also thought about preparing another table with the new data and then quickly renaming the 2 tables (current to old and then new to current), but there would be a small window of time where there would be no table. Can anybody see a clever way of doing this without transactions? Thanks, Rob ------------------------------ Gary McNair ------------------------------ #Informix

Load into new_table; Rename table to old_table; Rename new_table to table; That as close to instant as you can get, the rename part anyway Don't forget to rename the indices Cheers Paul

Hi Rob, play with synonym ( drop / create ) if it's faster as the two "rename table" you need for the switch to a table copy. Regards Henri ------------------------------ Henri Cujass leolo IT, CTO Germany ------------------------------

Hi, Be carefully, renaming a table with "open cursors" on it will fire "informix errors" : SQL statement error number -710. Table () has been dropped, altered or renamed. Regards ------------------------------ Garcia Benjamin ------------------------------

Hi, depending on the numbers of records and a unique logical key you can maybe load new data on another table a use MERGE to sync the original table ... Regards ------------------------------ Garcia Benjamin ------------------------------

Hi Garcia, it's depend from AUTO_REPREPARE. You can use the open cursor "with reoptimization" in this case. Regards Henri ------------------------------ Henri Cujass leolo IT, CTO Germany ------------------------------

Hi Garcia, I was not aware of this MERGE statement. It looks good for syncing, I will give it a try. Thanks, Rob ------------------------------ Gary McNair ------------------------------

Thanks Henri for the suggestion of using synonyms and AUTO_REPREPARE, I will keep it in mind. ------------------------------ Gary McNair ------------------------------