DSXchange

Hi Experts,

I am using Delete statement in Before SQL option to delete few records and write mode=insert in Oracle connector stage.
Job completed successfully.
But Only it inserting the records. Deletions are not taking place.

1)Why did it fail to delete records?
2)Any alternate methods to delete some records from table then insert new records?

---------------------------------------------------
Thanks in advance,
Phani

What error/warning messages appeared in the job log?

Hi Ray,

Thanks for the reply.

Log is not providing any warnings/information regarding Delete statement.

---------------------------
Thanks,
Phani

I've just encountered something similar and it was a rather frustrating discovery process as our BEFORE and AFTER SQL statements were being parsed but seemingly not executed; this occurred after a fixpack install that we ended up rolling back because we could figure out what the problem was (and were under time pressure, as usual).

The cause in our case was the "Autocommit" setting, defaulting to "off" in our SQL-Server connectors. The version we developed on would let this "off" setting still commit the before- and after-sql stages but the fixpack update, and also 9.1, doesn't. One needs to specify "on" or explicitly put a COMMIT into.

Note that we had this in our stages where we read data, I haven't tested this on the write stages but assume that the commit frequency and array size would take care of that when writing.

Thanks Arndw,

AutoCommit is off in my db.
Thus I took After Sql=commit and run the Job.

Eventhough records are not deleting.Only inserting.

Where shoud i need to take Commit statement?Please specify.

----------------------------------------
Thanks,
Phani

I am afraid I did not quite understand your last post.

In your ODBC connector stage specify the autocommit as follows:



and see if that changes things for you.

Thanks Arndw,

I am using Oracle Connector stage.

-----------------------------------------------
Thanks,
Phani

Ok, so we are dealing with a different problem.

If you intentionally make a syntax error in your DELETE before-SQL, does the job produce an error? If yes, then the before-sql is being parsed. Add ";COMMIT" to the before-SQL and see if any messages or warnings are in the log if the rows aren't being deleted. You can turn on as much logging as possible in your parameters to add to runtime output. I think that either OSH_DUMP or OSH_SCORE will echo the actual SQL commands used at runtime so you can manually run them to see if they work as you expect.

I believe Arnd has put the right solution forward. My understanding is that DataStage connectors have completely separate sessions established for the Before, After and Extraction phases. This means a "COMMIT" in the After SQL wouldn't have any effect on the Before SQL execution.