Informix

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

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

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

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

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

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

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