Roll back if job fails

[rejith.ry]

My requirement is ,
1. Want to delete all records in a table.
2. Insert new records from a flat file.
3. If the insert fails rool back to the previous state. (Table with old records)

I am using an oracle stage for inserting new rows. In the before tab of SQL in OCI stage, I am specifying the delete statement. If the delete happens & insert failes the data in the table gets lost. i.e, the delete is getting commited. I want the data to be comminted, only if the job is successful. If the job fails, want to roll back. How to do it in Datastage 7.5.1?

[keshav0307]

you have a very odd type of requirement.
if you want to delete all the rows, why do you want to rollback to old status. you already have the data in the file which you are going to load...

There are many solution for this. but a simple one is that you can load all the data this table into a temporary table before deleting it, and when you job fails, load the table from this temp table.

[ArndW]

Or you could add a "MarkedForDeletion" flag column to your table and update it as necessary and perform a delete based in its value as well.

[rejith.ry]

My requirement is slightly different. I dont want to delete all records. I want to delete some records based on some criteria.

[chulett]

My requirement is ,
1. Want to delete all records in a table.
2. Insert new records from a flat file.
3. If the insert fails rool back to the previous state. (Table with old records)

I am using an oracle stage for inserting new rows. In the before tab of SQL in OCI stage, I am specifying the delete statement. If the delete happens & insert failes the data in the table gets lost. i.e, the delete is getting commited. I want the data to be comminted, only if the job is successful. If the job fails, want to roll back. How to do it in Datastage 7.5.1?

Same way you would do it in any other version.

First - get the delete statement out of the 'Before SQL' tab, that's what is causing all your grief. Use it for more appropriate things like altering settings in the current setting, not for stuff like this.

Second - change your Update Action to Clear table then insert rows which is the equivalent of what you were trying to do - a transactional delete of all records in the table and then inserts of all records on the link. Make sure you also use a Transaction Size of zero for this all to work as you've specified.

Third - don't send questions like this to my email. Email or Private Messages are for exactly that - private or personal stuff. I usually don't reply as that exposes my personal email address - and all I would have said was 'Post your question in the Forums'. That's how you get answers from anyone and others benefit from seeing those same answers.

[chulett]

My requirement is slightly different. I dont want to delete all records. I want to delete some records based on some criteria.

[sigh] Ok... that would have been good to note the first time. What kind of 'criteria'? Something you can encapsulate in a single sql statement it would seem, yes?

Then you need two links to the same OCI stage. The first one does the delete and the second ones does the inserts. Ensure the delete link is in fact the first link in the Link Ordering and make its constraint @OUTROWNUM=1 so it only fires once and does all deletes needed.

Your second link can remain as you have it now.

[rejith.ry]

I tried the above method, ie 2 links to the same Oracle stage. But it is givin illegal variable name/number error. CopyOfupdatetest..Oracle_OCI_0: ORA-01036: illegal variable name/number.
Any help regarding this will be appreciated.

[chulett]

That would imply you are using User-defined sql and it isn't setup correctly. Post your sql and any information we'd need to understand it. Either that or switch to generated sql.

[rejith.ry]

The delete query I am using is delete from tablename where column1=20
insert query is
INSERT INTO Tablename(column1,column2,column3,column4,column5) VALUES (:1,:2,:3,:4,:5)
I am using autogenerated query using "Inser rows without clearing"

Craig can I sedn the job snapshot to your personal mail ID?[/img]

[ray.wurlod]

You can post links to hosted images. Search the forum for a "how to".

[chulett]

Um... let me think... No.

Your problem is the delete sql, it has no bind variables and you need one for every column in the link. Make sure the link has one column, put the '20' in its derivation, switch the update action to 'Delete existing rows only' and mark the field as a Key field. You should end up with:

Code: Select all

delete from tablename where column1 = :1

as the generated sql and - as an added bonus - it should actually work for you! Give that a shot.

[rejith.ry]

Hey Craig,
Noe the job is running. But I think it is in a deadlock. It doesn't get finished. Delete is happening, But it is stopping at the INSERT statement. (From director)

[chulett]

Have you verified the deadlock? If so, then you'll probably need to set the Transaction Size of the delete link to 1 so it gets commited and is out of the way of the inserts. Problem is the fact that they won't be there if you need to rollback... hmmm...

[rejith.ry]

Hi Craig,
The issue is solved :D . The deadlock was due to some other issue. Thank you very much!!!!!!!!!!!!!! :D

[chulett]

Excellent. :D