Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  reloading a table atomically

    Posted 03/17/21 09:59 AM

    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


  • 2.  RE: reloading a table atomically

    Posted 03/17/21 10:12 AM

    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

     

     






  • 3.  RE: reloading a table atomically

    Posted 03/18/21 03:11 AM
    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
    ------------------------------



  • 4.  RE: reloading a table atomically

    Posted 03/18/21 03:17 AM
    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
    ------------------------------



  • 5.  RE: reloading a table atomically

    Posted 03/18/21 04:02 AM
    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
    ------------------------------

    Original Message


  • 6.  RE: reloading a table atomically

    Posted 03/22/21 07:37 PM
    Thanks Henri for the suggestion of using synonyms and AUTO_REPREPARE, I will keep it in mind.

    ------------------------------
    Gary McNair
    ------------------------------

    Original Message


  • 7.  RE: reloading a table atomically

    Posted 03/18/21 03:25 AM
    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
    ------------------------------



  • 8.  RE: reloading a table atomically

    Posted 03/22/21 07:33 PM

    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
    ------------------------------

    Original Message


Related Content