Rollback of previous data on job failure during full load

[Former Member]

Hi All,

When I do a full load and use 'delete table table before loading' option if my job , if my job fails abruptly is it possible to automatically rollback in BODS ?

Can anyone please explain me what exactly happens?

Or is there a particular mechanism I have to use during full loads to restore my previous data in target as before at the point of failure ?

Please help me with the solution.

Thanks,

Deepa

[former_member187605 · Accepted solution]

If you check delete table table before loading, your table will be truncated before the data flow starts. A SQL truncate cannot be rolled back. If your job fails, you cannot recover your original data.

As a workaround, include a script to saveguard your table before starting the dataflow. Use a try-catch structure to intercept any errors. If your data flow runs successfully, drop the copy of the table; if it runs into an error, put the original contents back.

[Former Member · Accepted solution]

I slightly disagree. SQL truncate can be rolled back if you use transaction in your code.

[former_member187605 · Accepted solution]

In many databases truncate table is a DDL statement that cannot be rolled back. Check http://blogs.lessthandot.com/index.php/datamgmt/dbadmin/mssqlserveradmin/truncate-rollback-differenc... for a difference in behaviour between MS SQL Server and Oracle.

[Former Member · Accepted solution]

Truncate command logs though differently from Delete. While coding the truncate statement should be within a transaction, like below (referring to SQL server)

Begin Transaction

truncate table

Rollback transaction

[former_member198401 · Accepted solution]

Hi Arun,

I  agree with you... It works in SQL Server.

drop table ##TEMP

/** Create a temporary table **/

select * into ##TEMP from TEST1

/**Insert a Value in Table**/

insert into ##TEMP values(1)

select * from ##TEMP

/**Begin a Transaction**/

begin tran T1

truncate table ##TEMP

/** Rollback the transaction**/

rollback tran T1

select * from ##TEMP

Hope this helps Deepa

Rgeards

Arun Sasi

[former_member187605 · Accepted solution]

In general, the truncate table statement is a DDL statement that cannot be rolled back. To my knowledge, that's the case in HANA, SQL Anywhere, Sybase ASE, Sybase IQ, DB2, MySQL, Oracle, Teradata... I only know about 2 exceptions to this rule: Informix and... SQL Server.

[former_member198401 · Accepted solution]

Hi Dirk,

Its also possible to rollback in Oracle only if the transaction contains marked savepoints, ROLLBACK TO SAVEPOINT [name] can be used to rollback the transaction upto the specified savepoint only. As a result, all the data changes upto the specified savepoint will be discarded.

I am sure it will be possible in other databases as well until you specify a transaction...

Regards

Arun Sasi

[former_member187605 · Accepted solution]

Because the truncate statement is a DDL statement in Oracle, it issues an autocommit before execution. All prior savepoints become useless. No rollback possible.

[Former Member · Accepted solution]

Do you mean, we can not rollback the data in BODS ?

Then Is there any alternative way to restore the data that was there before the job run, If the job fails abruptly during the full load ?

Or I should store the backup of the target data and rerun the job to load the backup table\file data in to the target?

Here HANA is our target system.

[former_member187605 · Accepted solution]

It's not a problem of DS, it's a feature of the underlying database. In HANA you cannot.

But no worries, I have given you a workaround already:

As a workaround, include a script to saveguard your table before starting the dataflow. Use a try-catch structure to intercept any errors. If your data flow runs successfully, drop the copy of the table; if it runs into an error, put the original contents back.

[Former Member · Accepted solution]

Check this for a work around in HANA. Not sure how much of help this would be  -

[Former Member · Accepted solution]

Thanks Drik.

Yeah,I will use this work around.

Thanks,

Deepa

[former_member699496 · Accepted solution]

Hi:

I have a Oracle target table , and set "delete table before loading", it's success perfectly. I wondering about the Oracle account I use which don't have authority of truncating table, only grant select/insert/update/delete, how can it be works with "truncate"?

[Former Member]

Thanks All. I have got the solution. Thread can be closed.

Thanks,

Deepa

[former_member187605]

That's something you as OP will have to do yourself .

[Former Member]

Yeah actually I have come up with a similar solution as an alternative, if there is no option to do automatic Rollback with DS options. So I double checked whether Rollback is not possible on delete.

[former_member187605]

Deepa Angadi wrote:

Thread can be closed.

What I actually wanted to say is that you're the only one who can do that, i.e close the thread . Nobody will (be able to) do that on your behalf.

[Former Member]

If you find Dirk's solution helpful, you should assign points to him. That's the way of thanking for his time and free consulting.

When you say you came up with a "similar solution", would you mind sharing in the forum so all the people are benefitted?

[former_member187605]

and Julie's reply in

[Former Member]

Yeah Arun, Sure.

But it was almost same as dirk suggested.

In my job I am saving copy of target table as a backup in flat file. So when the job fails I will using the previous day's back up file as source to load data to the table in the catch block.

[Former Member]

Thanks once again dirk. New to SCN

[Former Member]

Hi,

In the target table option, turn on the transaction control option. Set the value to zero.

Arun

[Former Member]

Hi Arun,

Thanks for the reply. Can you please let me know what exactly transaction control option does.

I saw that in few documents but did not find what it does.

Thanks,

Deepa

[Former Member]

Hi Deepa,

Check this, page 182 you will find a very detailed explanation

http://help.sap.com/businessobject/product_guides/sbods42/en/ds_42_reference_en.pdf

Arun

[Former Member]

Arun's suggestion makes sense. If we set transaction order to 0 , then truncate data before loading will be disabled. Only downside of this arrangement is that DS will keep all the data in memory before single commit. This might not work when a dataflow needs to process large amount of data.

[former_member187605]

I am sorry to say but that does not make sense at all. When you set Include in transaction to Yes, Delete data from table before loading is automatically disabled. That means that the original wanted behaviour of the data flow is lost, isn't it?

What happens when you do a full load, have the option set and your job succeeds? Because the truncate has not been executed (you disabled it), the original data is stil there; And that's not according to plan .

[Former Member]

To avoid duplicate records, Auto correct load can be enabled even if transaction control is set to yes

[former_member187605]

Sure. And what if the new run contains new records (only)? Autocorrect load won't help at all. The original records will still be there.

[Former Member]

Agree, assuming that scenario what Deepa is looking for. Transaction controls have limits and by far which is the only straight forward DS way where you can do some thing equivalent to a Roll back of DB servers.