DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
Enzopre
Participant



Joined: 07 Feb 2013
Posts: 57
Location: Italy
Points: 1045

Post Posted: Fri Jan 16, 2015 1:05 am Reply with quote    Back to top    

DataStage® Release: 8x
Job Type: Parallel
OS: Windows
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

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup

Joined: 16 Nov 2004
Posts: 16318
Location: Germany
Points: 92566

Post Posted: Fri Jan 16, 2015 3:23 am Reply with quote    Back to top    

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.

_________________

Image
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43085
Location: Denver, CO
Points: 222463

Post Posted: Fri Jan 16, 2015 8:30 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
Enzopre
Participant



Joined: 07 Feb 2013
Posts: 57
Location: Italy
Points: 1045

Post Posted: Sat Jan 17, 2015 5:53 pm Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
dbhatrai
Participant



Joined: 18 Jun 2020
Posts: 3
Location: US
Points: 18

Post Posted: Fri Jun 19, 2020 8:20 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
dbhatrai
Participant



Joined: 18 Jun 2020
Posts: 3
Location: US
Points: 18

Post Posted: Fri Jun 19, 2020 10:34 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours