IDS Forum

Re: equivalent of savepoint(oracle) in informix

Posted By: Art S. Kagel (Oninit)
Date: Monday, 31 March 2008, at 8:02 a.m.

In Response To: Re: equivalent of savepoint(oracle) in informix (debadatta)

debadatta wrote:
> Thanks for confirming the answer, but is there any other way we can
> implement feature of savepoint in informix esql/c or 4gl. for example if i
> have to commit a million record and i am commiting after 5000 rows. how
> can i implement such that i can roll back after committing something like
> 100000 rows?
>

You cannot. You would have to write undo code. I usually invert the
problem to: how do I load the data correctly with a minimal use of
resources and without risking the possibility of loading incorrect data
and with the ability to recover from an unexpected crash of either the
server, server host, or the load job stream itself.

One method I use will be to commit every 10,000 rows and keep track of
where that put me in the input stream/file so that I can pick up the
processing where I left off rather than roll it all back and start
again. This answers the recovery or restartability problem.

Another alternative, one used in my dbcopy utility and Informix's dbload
utility, is to just record any records that fail to load in an editable
format so that they can be corrected and reloaded later. This answers
the need to only allow good data to be loaded without losing the
incorrect data which mahy be correctable.

I know this doesn't answer the problem of when a mass job is REALLY one
that needs to be all or nothing and yet is too big to allow it to be
performed in a single transaction or savepoint. My answer to this would
be to perform the load to an unlogged temp table or a permanent RAW mode
staging table under a table lock. This would prevent the use of locks
and logical log records during this staging step. The staging load will
allow one to clean the data, reject or correct any errors, and make
certain that the data will finally load to its ultimate location cleanly
without error. In this scenario, if there are uncorrectable errors in
the data or the job stream, one can simply drop the temp table or delete
the jobs rows from the staging table or truncate it, thereby rolling
back the entire job. It can then be restarted later when clean data is
available. And of course if a temp table is used, then unlike in
Oracle, the temp table belongs only to the original session, so if there
is a crash at any level, the temp table will be destroyed and the load
can be restarted from scratch. If the load into the staging table (temp
or permanent) completes successfully, one can be confident, assuming
appropriate checks have been performed, that the data will load
correctly and safely from the staging table to its final home and can
perform that stage using partial transactions.

Art S. Kagel
Oninit

> On Mon, Mar 31, 2008 at 4:31 PM, Art S. Kagel (Oninit) <art@oninit.com>
> wrote:
>
>
>> debadatta wrote:
>>
>>> Hi
>>>
>>> Is there any command in informix where i can roll back committed data
>>> similar to oracle save point?
>>>
>>>
>> No. Committed is committed.
>>
>> Anyway, a savepoint is not a COMMIT, it just allows partial rollbacks of
>> anything that comes after the latest savepoint without rolling back the
>> entire transaction not yet committed. If you exit your session without
>> committing then entire transaction is rolled back by Oracle, not just
>> the data since the last savepoint.
>>
>> Art S. Kagel
>> Oninit
>>
>>
>>> Thanks & Regards
>>> Debadatta
>>>
>>>
>>>
>>>

Messages In This Thread

• equivalent of savepoint(oracle) in informix
  debadatta -- Monday, 31 March 2008, at 6:53 a.m.
  • Re: equivalent of savepoint(oracle) in informix
    Art S. Kagel (Oninit) -- Monday, 31 March 2008, at 7:01 a.m.
    • Re: equivalent of savepoint(oracle) in informix
      debadatta -- Monday, 31 March 2008, at 7:08 a.m.
      • Re: equivalent of savepoint(oracle) in informix
        Art S. Kagel (Oninit) -- Monday, 31 March 2008, at 8:02 a.m.
        • Re: equivalent of savepoint(oracle) in informix
          CURTIS CROWSON -- Monday, 31 March 2008, at 10:49 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.