equivalent of savepoint(oracle) in informix

Hi Is there any command in informix where i can roll back committed data similar to oracle save point? Thanks & Regards Debadatta

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 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!! > > >

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? 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 > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > See you at the IIUG Informix 2008 Conference > > The Power Conference for Informix Professionals > > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > > http://www.iiug.org/conf > > Registration Now Open!! > > > > > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!! >

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

Very nice answer. thanks for the hard work.