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
Rollback the tables
Moderators: chulett, rschirm, roy
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.
Success consists of getting up just one more time than you fall.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.
Rgds
Anand
Anand
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
For Update/Delete processes it is also feasible, but you need 2 colums.
Wolfgang
Wolfgang Hürter
Amsterdam
Amsterdam
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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).
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).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.