Deleting a preidentified set of records from database

gotosarath · Post by **gotosarath** » Sun Dec 05, 2004 9:10 am

Hi,

I am new to Ascential Data Stage. I have a task to accomplish through DataStage job.

Delete from mytab Where mycol = #Param#

Right now I am accomplishing it by using a dummy select from an ODBC stage and passing that through a Transformer and then using Final ODBC to delete what I wanted. But I believe this is a messy way of doing.

Can somebody (I know somebody will

) suggest a better way of accomplishing this?

Thanks & Regards,
Sarath

chulett · Post by **chulett** » Sun Dec 05, 2004 10:16 am

Well, if you really need to do this in a DataStage job...

Drop the first ODBC stage and simply start with a Transformer. It looks a little weird but works great for generating test data or in your case - a single row.

You'll need to define a Stage Variable in it so that the job will compile, but other than that you don't need to use it. Put a constraint on the link like so:

Code: Select all

@OUTROWNUM=1

and all you'll get is one row out of the Transformer. Use your Job Parameter as the value of the single field to pass to the ODBC stage doing the delete and you are good to go.

Heck, you may be able to drop the Transformer and just use a single ODBC stage with the Job Parameter coded into Custom SQL there.

davidnemirovsky · Post by **davidnemirovsky** » Sun Dec 05, 2004 9:44 pm

If your deletion criterea is pre-defined and will not change then just use a parameter driven custom SQL statement as Craig suggests. That's the way I would do it.

gotosarath · Post by **gotosarath** » Mon Dec 06, 2004 7:14 am

Thanks a lot for the reply. I will try that.