ODBC - Transaction Handling

reachmexyz · Post by **reachmexyz** » Mon Jun 16, 2014 11:40 am

Hi,

I am running DataStage 8.7 on Linux environment.
I have a requirement to extract data from Oracle and load into a table named "table1" in SQLServer. But before loading "table1" i am required to delete all the existing records and then insert the new ones.
Tricky thing, is "Delete and insert" should be part of a transaction. i.e.
If "If insert fails, then delete should be rolledback".
How can a make both Delete and Insert as part of single transaction?
Appreciate your responses

chulett · Post by **chulett** » Mon Jun 16, 2014 12:48 pm

What stage are you using? Typically you would get everything in a 'transaction' simply by using a single target stage with multiple input links.

yugee · Post by **yugee** » Mon Jun 16, 2014 12:57 pm

If you need to delete all the records only when all the records are inserted then:
First insert all the records into table2 (create this temp table as before sql statement)
have the after sql statement to truncate table1 and rename table2 to table1

reachmexyz · Post by **reachmexyz** » Mon Jun 16, 2014 2:00 pm

Delete is part of Before SQL and inserts are part of SQL statement. Both are two different transactions. I am using ODBC stage.

chulett · Post by **chulett** » Mon Jun 16, 2014 2:47 pm

Not going to work that way, the deletes will be committed and then your inserts will start. They both need to be 'inside' the stage to be in the same transaction and that requires two links. The delete link needs to be ordered to run first and only needs to send 1 row to trigger the deletes.

Which ODBC stage is this? Seem to recall there being more than one, ODBC Enterprise and ODBC Connector come to mind.

If you have the grants to drop/rename tables and that kind of thing won't freak out your DBA, you can consider what yugee suggested. No need to truncate, however, drop and rename should do it.