rollback
Moderators: chulett, rschirm, roy
Hi
I tried but it still commits the previous rows.
I set the trasaction isolation to - uncommitted read
and the array size =1 and transaction siz = 0
How do I make it to roll back ?
I tried but it still commits the previous rows.
I set the trasaction isolation to - uncommitted read
and the array size =1 and transaction siz = 0
How do I make it to roll back ?
chulett wrote:Basically by not committing. Transaction Size is a stage property, so for example, setting it to 0 will get you an 'all or nothing' load. ...
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Something is not as you've stated - if the job finishes in an Aborted status, any uncomitted rows will be rolled back. So either it isn't really aborting or you are actually comitting the rows. You need to help us figure out what is actually going on.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Hi
Thanks for the efforts: I am sorry the status was a 'warning'
As stated earlier these are my settings:
I have a sequential file that needs to be loaded in to a target Database(using an ODBC stage).
The sequential file contains three rows. In the second row I created an error( placed a character value to be loaded to a numric column)
The stage setting are as follows:
Transaction handling-
rows per transaction = 0 parameter array size = 1
isolation level = none ( I have tried all the different options and no success)
the update action is "Clear the table and insert rows"
After run:
The target has two rows excluding the row that had the error. Only that row which contained the error rolled back but not the rest.
how do i make it to abort so that I can achieve my desired result that is rollback all changes ?
Thanks for the efforts: I am sorry the status was a 'warning'
As stated earlier these are my settings:
I have a sequential file that needs to be loaded in to a target Database(using an ODBC stage).
The sequential file contains three rows. In the second row I created an error( placed a character value to be loaded to a numric column)
The stage setting are as follows:
Transaction handling-
rows per transaction = 0 parameter array size = 1
isolation level = none ( I have tried all the different options and no success)
the update action is "Clear the table and insert rows"
After run:
The target has two rows excluding the row that had the error. Only that row which contained the error rolled back but not the rest.
how do i make it to abort so that I can achieve my desired result that is rollback all changes ?
chulett wrote:Something is not as you've stated - if the job finishes in an Aborted status, any uncomitted rows will be rolled back. So either it isn't really aborting or you are actually comitting the rows. You need to help us figure out what is actually going on.
Your 'bad' record wasn't 'rolled back', it was never accepted by the database. Rolled back imples it went into the database and then some other error cause all current 'good' but uncomitted work to be undone.
How do you make it abort? If you were using a native stage rather than ODBC, there's usually a 'Treat Warnings as Fatal' option, from what I've seen. In ODBC there is an Errors tab, but it's only there for Stored Procedures or if you've managed to leave the Update Action blank.
Otherwise you need to build a reject link and set it to Abort After X rows go down it - in your case, 1 I would think. Either that or set your Warning Limit at runtime to 1 rather than taking the default of 50 or Unlimited. In either case, the job will Abort once either of those conditions are met.
How do you make it abort? If you were using a native stage rather than ODBC, there's usually a 'Treat Warnings as Fatal' option, from what I've seen. In ODBC there is an Errors tab, but it's only there for Stored Procedures or if you've managed to leave the Update Action blank.
Otherwise you need to build a reject link and set it to Abort After X rows go down it - in your case, 1 I would think. Either that or set your Warning Limit at runtime to 1 rather than taking the default of 50 or Unlimited. In either case, the job will Abort once either of those conditions are met.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Two jobs. The first one checks for errors and loads the result into a staging area (a text file would do). The second loads from there into the target table, but is only executed if the first succeeds (reports no warnings or errors). That control you can institute using a job sequence containing two Job activities with an OK trigger between them.
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.
Code: Select all
The sequential file contains three rows. In the second row I created an error( placed a character value to be loaded to a numric column)
May be DataStage ifself throws that error for second row.
That is why rollback not happening in your case.
What is log information you are getting