Rollback the tables

rafidwh · Post by **rafidwh** » Fri Jan 13, 2006 12:46 am

Hi All,
I need a small idea to my issue, fortunately I am not facing this situation ,but I want to have a clue,if I face this in future.

Suppose I have a flat file and we need to populate the data into three different tables Tab1,Tab2 and TAb3.

Now if any table has got bad data or any error,then the rest of the two tables which had correct data should be rolled back.

The Data should be completely loaded in three tables only if there is no error.
Please suggest me how can we handle this situation

Thanks in Advance

loveojha2 · Post by **loveojha2** » Fri Jan 13, 2006 2:51 am

You would need something like timestamp column (or any other column as an indicator) within your tables which can relate the last entered data into your tables, so that on error in populating any of the table, you can delete the newly entered data using the indicator field, in the other two table.

Andal · Post by **Andal** » Fri Jan 13, 2006 3:09 am

Love

He is asking, if there is any method for Rollback from all the three tables. From ur method we have to delete it Manually.

ray.wurlod · Post by **ray.wurlod** » Fri Jan 13, 2006 3:13 am

Of course. If you've committed, then the rows are in the tables.

IF you're loading the tables via multiple input links to a single ODBC stage, then you can use transaction grouping to hold back any COMMIT until all rows are loaded into all tables. This can lead to huge transaction sizes, which may cause the database to generate a fatal error which will, in turn, cause the job to abort, even though there were no error rows.

Andal · Post by **Andal** » Fri Jan 13, 2006 3:51 am

Ray,

I too have a similar situation but little different. I am having a Master Sequence which will 3 different server jobs. My Question is if any of the server job failed , i have to rollback the records from all the tables.

Now i am deleting the records based on Datetime_field which will be having DSJobStartTimeStamp.

WoMaWil · Post by **WoMaWil** » Fri Jan 13, 2006 4:46 am

in our table world each table has a column load_ID which is a exclusive number for each run job an filled by this job. By this mean we are able, yes as you say manualy, but we have a skript for that, to unload/rollback in a case of problems. We have good expierence with that for now 7 years and are happy with that.

For Update/Delete processes it is also feasible, but you need 2 colums.

Wolfgang

ray.wurlod · Post by **ray.wurlod** » Fri Jan 13, 2006 2:59 pm

If you've committed you can't rollback. Databases 101.

You have to "unwind", by deleting. Based on job start timestamp is OK for rows newly inserted but not for undoing change. That's why type 2 SCD is a good approach; you never update rows (except maybe with expiry information).