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.
DELETE Rows from oracle table
Moderators: chulett, rschirm, roy
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.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.
Several ways to skin this cat.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
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.