Commit on Delete/Insert/UPdate

ketfos · Post by **ketfos** » Fri Sep 03, 2004 11:19 am

Hi
I am deleteing records in Oracle table and writing to a sequential file.

Using Datastage - OCI - Transformer - Seq File

As I understand Datastage does implict commit on INSERT, DELETE, UPDATE.

Is there a way to prevent implict commit?

Ketfos

chulett · Post by **chulett** » Fri Sep 03, 2004 12:25 pm

There's no way to not commit your work.

You do, however, have control over the commit level using the Transaction Handling tab on the OCI stage via the Rows Per Transaction setting. Default is 0 which means only after all rows have been worked successfully and the stage closes the connection.

That being said, how are you accomplishing the deletes in your posted design - before sql?

ketfos · Post by **ketfos** » Fri Sep 03, 2004 12:46 pm

Hi,
I am using a simple sql in input OCI stage
(Select name, address from customer where customer name like 'K%' )

In the Output OCI stage, I Update the customer table with
Udpdate Action - Delete existing row only

SQL shown is - Delete from Customer where customer name:=1

Also I write the deleted records to a Sequential file.

Ketfos

chulett · Post by **chulett** » Fri Sep 03, 2004 1:42 pm

Ok. Was wondering because there wasn't anything that resembled an output OCI stage in your "diagram".

So, did you get your commit question answered?

ketfos · Post by **ketfos** » Fri Sep 03, 2004 2:21 pm

Hi,

Yes and the answer is " There is NO way not to commit."

Ketfos