DELETE Rows from oracle table

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
Enzopre
Participant
Posts: 57
Joined: Thu Feb 07, 2013 2:04 pm
Location: Italy

DELETE Rows from oracle table

Post by Enzopre »

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.
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Enzopre
Participant
Posts: 57
Joined: Thu Feb 07, 2013 2:04 pm
Location: Italy

Post by Enzopre »

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.
dbhatrai
Participant
Posts: 3
Joined: Thu Jun 18, 2020 11:45 pm
Location: US

Post by dbhatrai »

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.
dbhatrai
Participant
Posts: 3
Joined: Thu Jun 18, 2020 11:45 pm
Location: US

Post by dbhatrai »

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.
Post Reply