Roll back if job fails
Moderators: chulett, rschirm, roy
Roll back if job fails
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?
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?
Rejith R
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
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.
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.
Re: Roll back if job fails
Same way you would do it in any other version.rejith.ry wrote: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?
![Wink :wink:](./images/smilies/icon_wink.gif)
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
[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?rejith.ry wrote:My requirement is slightly different. I dont want to delete all records. I want to delete some records based on some criteria.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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]
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]
Rejith R
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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:
as the generated sql and - as an added bonus - it should actually work for you! Give that a shot.
![Laughing :lol:](./images/smilies/icon_lol.gif)
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
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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...
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers