Cancelling database changes

snassimr · Post by **snassimr** » Sun Aug 21, 2005 1:19 pm

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

ArndW · Post by **ArndW** » Sun Aug 21, 2005 1:31 pm

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.

vmcburney · Post by **vmcburney** » Sun Aug 21, 2005 11:32 pm

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.

snassimr · Post by **snassimr** » Mon Aug 22, 2005 2:39 am

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 ?

elavenil · Post by **elavenil** » Mon Aug 22, 2005 2:45 am

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

snassimr · Post by **snassimr** » Mon Aug 22, 2005 6:10 am

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.

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 ?

Where I can find OCI stage I dont seee it on my pallete ?

elavenil · Post by **elavenil** » Mon Aug 22, 2005 6:20 am

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

snassimr · Post by **snassimr** » Mon Aug 22, 2005 6:24 am

I dont work with oracle .It there somthing like this for sql server?

elavenil · Post by **elavenil** » Mon Aug 22, 2005 6:33 am

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

snassimr · Post by **snassimr** » Mon Aug 22, 2005 6:46 am

There is an option called 'On Fail', you set the appropriate value depends on your req.

Where do you see it ? On wich page ?

elavenil · Post by **elavenil** » Mon Aug 22, 2005 6:51 am

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

chulett · Post by **chulett** » Mon Aug 22, 2005 8:00 am

Transaction Grouping won't help for this as this is not the situation it was meant to be used in. Read the help on the option. Recognize that it forces the Transaction Size to be 1 for it to work. Then you'll see why.

elavenil · Post by **elavenil** » Mon Aug 22, 2005 8:05 am

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

snassimr · Post by **snassimr** » Mon Aug 22, 2005 8:57 am

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

chulett · Post by **chulett** » Mon Aug 22, 2005 9:51 am

snassimr wrote:Yes,elavenil ! You are right

Not sure how that can be right when you made this statement in your very first post:

snassimr wrote:I want to rollback all changes are made to databse if job returning with Status Failed from sequence.

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?