Cancelling database changes
Moderators: chulett, rschirm, roy
Cancelling database changes
Hi !
I want to rollback all changes are made to databse if job returning with Status Failed from sequence.
If there is anyway to do so ?
Thanks in advance
I want to rollback all changes are made to databse if job returning with Status Failed from sequence.
If there is anyway to do so ?
Thanks in advance
Your commit size is the deciding factor here. If the job fails before doing a commit any database changes will be rolled back. This is a great feature, but when doing very large inserts into database one is often forced to specify a commit size so that the rollback segment(s) in the DB aren't filled.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
It depends on your restart plan. If you have a file full of updates and it fails you can just run it again, updating the same rows in the database wont do any harm. A load of inserts is harder, you could try rolling them back manually with a delete statement using a date field or a process identification field. A target table of summarised or aggregated data cannot really be rolled back, you've got to recover the entire table from a backup or export taken directly before the load was attempted. Or else, as Arnd suggests, put the entire load into a single transaction.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Thank you all !
I set the commit size to 0 and still new rows was inserted . MAy be I need set Isolation level another from NONE ?
I use two different ODBC stages one for insert and one for update with transaction size 0 the job hung up during run. And only option set to transaction size to 1 is working.
What is option in Job sequence "Add checkpoints so sequence is restartable on failure"
Can it help ?
I set the commit size to 0 and still new rows was inserted . MAy be I need set Isolation level another from NONE ?
I use two different ODBC stages one for insert and one for update with transaction size 0 the job hung up during run. And only option set to transaction size to 1 is working.
What is option in Job sequence "Add checkpoints so sequence is restartable on failure"
Can it help ?
Add checkpoint option is to restart the job in the Job sequence from where it failed after the fixing the problem. This would not help rolling back the data in the database.
If you want to commit the changes only when insert and update are successful, use OCI stage and enable transaction grouping. This would commit the changes when both operations are successful.
HTWH.
Regards
Saravanan
If you want to commit the changes only when insert and update are successful, use OCI stage and enable transaction grouping. This would commit the changes when both operations are successful.
HTWH.
Regards
Saravanan
It means if I restart the job it restarts from job was failed ? Can I compile the sequence and stiil job restart from this failed job ?Add checkpoint option is to restart the job in the Job sequence from where it failed after the fixing the problem. This would not help rolling back the data in the database.
Where I can find OCI stage I dont seee it on my pallete ?
The sequence restarts the job, fromt the job which failed in the last run and restart method would work till the job sequence is compiled. The job sequence will start from the 1st job after the job sequence is compiled.
Oracle OCI can be found in 'Database' category in the palette.
Would suggest you to separate these two different topics into two separate posts so that future searching would be fast.
HTWH.
Regards
Saravanan
Oracle OCI can be found in 'Database' category in the palette.
Would suggest you to separate these two different topics into two separate posts so that future searching would be fast.
HTWH.
Regards
Saravanan
Sorry for my assumption that i made you are using Oracle DB. But pls mention that which database you use while asking DB related question, which would help us to get ideas/suggesstions/recommendations fast.
I am not so sure that this option is available in any of the SQL server stages. But this option is available in ODBC stage itself. ODBC stage can be used to accomplish DB insert and update. There is an option called 'On Fail', you set the appropriate value depends on your req.
HTWH.
Regards
Saravanan
I am not so sure that this option is available in any of the SQL server stages. But this option is available in ODBC stage itself. ODBC stage can be used to accomplish DB insert and update. There is an option called 'On Fail', you set the appropriate value depends on your req.
HTWH.
Regards
Saravanan
There is a tabpage called 'Transaction Handling' in Inputs tab of ODBC stage. And there is an option called 'Enable transaction grouping' and select that option then one line for each link would be shown in the same page. There you mention rollback/continue under On Fail option. This option would be available if you have more than one link to ODBC stage.
Regards
Saravanan
Regards
Saravanan
Hi Craig,
What i understood from the post is, he is trying to insert and update into Tables and he wants to issue commit if both are successful otherwise both should be rolled back. If this is the case, i think transaction grouping would help. Pls correct me if i am wrong.
Snassimr,
Pls correct me if my understanding is wrong.
Regards
Saravanan
What i understood from the post is, he is trying to insert and update into Tables and he wants to issue commit if both are successful otherwise both should be rolled back. If this is the case, i think transaction grouping would help. Pls correct me if i am wrong.
Snassimr,
Pls correct me if my understanding is wrong.
Regards
Saravanan
Yes,elavenil ! You are right
I try to find out how if I use different links to different ODBC stages on the same table the job hung up with parameters another from 1,1 for transactional size and array size.
On other side i need to use 0 on transaction size to do abort and it seems it wont do any updates on table ?
I want to solve it . I'll try grouping
I try to find out how if I use different links to different ODBC stages on the same table the job hung up with parameters another from 1,1 for transactional size and array size.
On other side i need to use 0 on transaction size to do abort and it seems it wont do any updates on table ?
I want to solve it . I'll try grouping
Not sure how that can be right when you made this statement in your very first post:snassimr wrote:Yes,elavenil ! You are right
Transaction Grouping is used to roll back a single transaction when any of the links involved in that transaction fail. So, which of these two distinctly different problems are you trying to solve?snassimr wrote:I want to rollback all changes are made to databse if job returning with Status Failed from sequence.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers