DSXchange

hi to all,

please can any one tell me how to delete record from oracle table in datastage parallel? the only operation to be executed is delete record from table which date field value is greater than another date.

In particular I must exec this SQL statement (on a oracle table) in datastage:

DELETE FROM ORACLE_TABLE WHERE DATE_FIELD > somedate

Can any one suggest me how to do this?

thanks.

The database stage allow you to do Before-SQL, After-SQL and SQL commands.

Depending upon whether you want this DELETE to be executed once per run or once per input data row you would choose between the options.

Use a dummy SQL statement (i.e. "SELECT sysdate FROM DUAL;") and put your DELETE as an After-SQL.

You could also generate a row with 'somedate' in it and then use that in your target stage to do the delete. Or generate the row and slip your 'somedate' job parameter into it before the target and issue the delete.

Several ways to skin this cat.

Hi to all, I resolved the problem. The solution that I have implemented is the following:

Oracle Connector -------> Peek Stage (oracle connector as input stage and Peek stage linked its output link).

In the oracle connector "Properties page" i set "Read Mode" field to Select and in the "Table name" field to Dual table as Dummy Query.

In the "Run before and after SQL Statement" field I have inserted my DELETE Statement.

In the "Column page" I have inserted as column name "dummy" .

In the peek stage i have not done any configuration.

My DELETE Statement runs but it does not commit to the database. This is a parallel job that executed with status = OK

Oracle_Connector_12: The connector will use the following DELETE statement at runtime: DELETE FROM TABLE WHERE BATCH_SID=ORCHESTRATE.BatchID AND PROCESS_ID=ORCHESTRATE.ProcessID.


Oracle_Connector_12,3: Number of rows deleted on the current node: 0.

Does Oracle connector has any auto commit feature that I need to enable? What am I missing? Please advise.

After some struggle, I found that every column including the ones that I ORCHESTRATED and the dummy had to be selected as Key.

This resolved my issue.