Delete on a Database

Pavan_Yelugula · Post by **Pavan_Yelugula** » Tue Mar 13, 2007 4:16 pm

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

narasimha · Post by **narasimha** » Tue Mar 13, 2007 4:21 pm

Pavan,

I would write a script which will delete all the 4 tables and call it as a before job script.

chulett · Post by **chulett** » Tue Mar 13, 2007 4:25 pm

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.

kumar_s · Post by **kumar_s** » Tue Mar 13, 2007 4:38 pm

Optionally you can have many Delete commands sperated by ";".

Pavan_Yelugula · Post by **Pavan_Yelugula** » Tue Mar 13, 2007 4:51 pm

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

kumar_s · Post by **kumar_s** » Tue Mar 13, 2007 4:58 pm

...Basic code script for which i need to buy a licencse or from database stages provided by oracle...

Not necessarily. AS long as you have ODBC settings done, you can call the script which inturns call the required script.

...transfomers i need four input links right...

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.

If you attained you solution, you can make mark the topic as resolved.

narasimha · Post by **narasimha** » Tue Mar 13, 2007 5:00 pm

A combination of Craig and Kumar's suggestion.
Change your job design to -

Code: Select all

Transformer -> OraOcI Stage

- Add constraint @OUTROWNUM=1
- Add a dummy Stage Variable
- Use custom sql, seperate the 4 delete statements with a ';'.

It should work fine.

chulett · Post by **chulett** » Tue Mar 13, 2007 5:02 pm

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.

chulett · Post by **chulett** » Tue Mar 13, 2007 5:05 pm

narasimha wrote:A combination of Craig and Kumar's suggestion.
Change your job design to <snip>

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

Use four links. Easy Peasy.

narasimha · Post by **narasimha** » Tue Mar 13, 2007 5:26 pm

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

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

Pavan, you have a generic solution from Craig.
Incase if you want to delete everything from the tables then this will work.

kumar_s · Post by **kumar_s** » Tue Mar 13, 2007 5:35 pm

Just a note, all the table should be in same database.

Pavan_Yelugula · Post by **Pavan_Yelugula** » Tue Mar 13, 2007 5:57 pm

Thanks a lot guys for all the suggestions...

Thanks
Pavan