Delete on a Database
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 133
- Joined: Tue Nov 23, 2004 11:24 pm
- Location: India
Delete on a Database
Hi All
I needed to delete data from 4 tables..i am trying to achieve this in a single job.
i have a job design
which is as follows
Oracle Stage----->Dummy File
Firstly i don't think i can rite one single SQL to delete data from all the four tables at a go with where clause....
This leaves me with writing 4 delete statements...my oracle stage is taking one delete in before and one in after...i rote a cutom SQL to satisy the dummy link. do i need to have another Oracle stage for the other 2 delete statements or can i acheive them in this single oracle stage
Thanks
Pavan
I needed to delete data from 4 tables..i am trying to achieve this in a single job.
i have a job design
which is as follows
Oracle Stage----->Dummy File
Firstly i don't think i can rite one single SQL to delete data from all the four tables at a go with where clause....
This leaves me with writing 4 delete statements...my oracle stage is taking one delete in before and one in after...i rote a cutom SQL to satisy the dummy link. do i need to have another Oracle stage for the other 2 delete statements or can i acheive them in this single oracle stage
Thanks
Pavan
One Oracle stage feed by four Transformers, each providing the keys for the delete statements. All kinds of discussions here on how to 'source' from a Transformer, you pretty much just need a stage variable and a constraint so it does not feed in rows forever.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 133
- Joined: Tue Nov 23, 2004 11:24 pm
- Location: India
Kumar
It is working with ';'
i remember trying this before may be i made a mistake in writing the SQL then..
And Narsimha sorry if i am sounding a bit naive but i understand i can work with SQL from Basic code script for which i need to buy a licencse or from database stages provided by oracle...The Script you are sugesting is it like a stored proc i need to write on oracle and call it from DS or is the Script a DOS Script as i have my datastage on Windows...
Chulett
The idea looks pretty cool to have four Transformers pool links on to Oracle but for these four transfomers i need four input links right which will be four dummy files if i understand it right... i am just wondering that it will be easy for me with a oracle stage on to a dummy file with a dummy link and delete the dummy file at the end of the job...
Thanks
Pavan
It is working with ';'
i remember trying this before may be i made a mistake in writing the SQL then..
And Narsimha sorry if i am sounding a bit naive but i understand i can work with SQL from Basic code script for which i need to buy a licencse or from database stages provided by oracle...The Script you are sugesting is it like a stored proc i need to write on oracle and call it from DS or is the Script a DOS Script as i have my datastage on Windows...
Chulett
The idea looks pretty cool to have four Transformers pool links on to Oracle but for these four transfomers i need four input links right which will be four dummy files if i understand it right... i am just wondering that it will be easy for me with a oracle stage on to a dummy file with a dummy link and delete the dummy file at the end of the job...
Thanks
Pavan
Not necessarily. AS long as you have ODBC settings done, you can call the script which inturns call the required script....Basic code script for which i need to buy a licencse or from database stages provided by oracle...
Transformer cannot have 4 input links. Other that reference stream. It can only have many output stream. What he was suggestion is to give one input to Transformer and extend 4 links to 4 different stage which does the job for you....transfomers i need four input links right...
If you attained you solution, you can make mark the topic as resolved.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
A combination of Craig and Kumar's suggestion.
Change your job design to -
- Add constraint @OUTROWNUM=1
- Add a dummy Stage Variable
- Use custom sql, seperate the 4 delete statements with a ';'.
It should work fine.
Change your job design to -
Code: Select all
Transformer -> OraOcI Stage
- Add a dummy Stage Variable
- Use custom sql, seperate the 4 delete statements with a ';'.
It should work fine.
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.
There's no files, dummy or otherwise, involved with the 'source from a Transformer' method. Just literally four Transformers stages all linked to one OCI stage. There are no input links on the Transformers, only one output link apiece to the same OCI stage.
Create a bogus stage variable in each transformer so the job will compile. Add a constraint to each link like @OUTROWNUM=1 so that only one 'record' goes to the OCI stage. Then 'hard code' your key values in the transformer.
For example, to delete all records where fieldx = 10, create fieldx in the transformer, set its value to 10, mark it as a key and set the Update Action to 'Delete existings rows only'. Done.
Adjust as needed. More than one record can be generated and sent, like a series of generated dates or whatever is needed. The constraint is the most important thing, without it the job will run 'forever'.
This way the deletes will be done in parallel, rather than the sql solution which is a serial one. And it's just plain cooler.
Create a bogus stage variable in each transformer so the job will compile. Add a constraint to each link like @OUTROWNUM=1 so that only one 'record' goes to the OCI stage. Then 'hard code' your key values in the transformer.
For example, to delete all records where fieldx = 10, create fieldx in the transformer, set its value to 10, mark it as a key and set the Update Action to 'Delete existings rows only'. Done.
Adjust as needed. More than one record can be generated and sent, like a series of generated dates or whatever is needed. The constraint is the most important thing, without it the job will run 'forever'.
This way the deletes will be done in parallel, rather than the sql solution which is a serial one. And it's just plain cooler.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Can't work that way, all the metadata would have to be the same for the keys in each table. Unless you mean four sql statements in the 'before' tab and something bogus in the main sql area. Yeesh...narasimha wrote:A combination of Craig and Kumar's suggestion.
Change your job design to <snip>
Use four links. Easy Peasy.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
It is a little bogus.
I gave the value for the dummy output from the transformer as 1.
I used User defined sql and used the below query. It did the job
Pavan, you have a generic solution from Craig.
Incase if you want to delete everything from the tables then this will work.
I gave the value for the dummy output from the transformer as 1.
I used User defined sql and used the below query. It did the job
Code: Select all
DELETE FROM TABLE1 WHERE 1 =:1; DELETE FROM TABLE2 WHERE 1 =:1; DELETE FROM TABLE3 WHERE 1 =:1; DELETE FROM TABLE4 WHERE 1 =:1
Incase if you want to delete everything from the tables then this will work.
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.
-
- Premium Member
- Posts: 133
- Joined: Tue Nov 23, 2004 11:24 pm
- Location: India