rollback

cosec · Post by **cosec** » Tue May 08, 2007 10:13 pm

I am loading from a sequential file to a target database. If there is an error in one of the rows how do I roll back the previous changes ?

Thanks

chulett · Post by **chulett** » Tue May 08, 2007 10:23 pm

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.

cosec · Post by **cosec** » Wed May 09, 2007 12:09 am

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 ?

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. ...

chulett · Post by **chulett** » Wed May 09, 2007 7:03 am

Your job must abort to roll back transactions - is that happening?

cosec · Post by **cosec** » Wed May 09, 2007 8:36 am

it does abort....
i am trying it with three test rows...the second row has the error.

when i run it the first row commits....the rows per transaction have been set 0.

any suggestions ? thanks for your input

chulett wrote:Your job must abort to roll back transactions - is that happening? ...

ray.wurlod · Post by **ray.wurlod** » Wed May 09, 2007 4:41 pm

Where - exectly - are you setting the rows per transaction property value?

cosec · Post by **cosec** » Wed May 09, 2007 6:40 pm

I am setting it at the ODBC stage

ray.wurlod wrote:Where - exectly - are you setting the rows per transaction property value? ...

chulett · Post by **chulett** » Wed May 09, 2007 6:48 pm

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.

cosec · Post by **cosec** » Wed May 09, 2007 9:20 pm

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 ?

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.

chulett · Post by **chulett** » Wed May 09, 2007 11:00 pm

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.

ray.wurlod · Post by **ray.wurlod** » Thu May 10, 2007 12:37 am

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.

rafik2k · Post by **rafik2k** » Thu May 10, 2007 12:51 am

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)

It seems second row never went into database using odbc.
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