Deleting from source

alhowarth · Post by **alhowarth** » Tue Oct 17, 2006 2:43 pm

Hello. I am new to DataStage, so hopefully I ask the question correctly.

I would like to create a job that when selecting a row from the source (DB2 UDB), will perform a row-level lock. After copying the data to the destination, deletes the source row.

Thanks,
Al

samsuf2002 · Post by **samsuf2002** » Tue Oct 17, 2006 2:51 pm

what is ur destination if u are using same table as source u can just replace the existing row with the new one .

alhowarth · Post by **alhowarth** » Wed Oct 18, 2006 7:28 am

The destination is another DB2 database.

Thanks though

Krazykoolrohit · Post by **Krazykoolrohit** » Wed Oct 18, 2006 7:39 am

I am not sure if you can delete the source rows.

You can try the following.

select user-defined SQL in the stage you use to read the data. In the sql, write the one for deleting. I am not sure if the stage can accept two sqls.

If the above doesnt work. try diong this in another job.

meena · Post by **meena** » Wed Oct 18, 2006 9:05 am

Hi,
The source and destination are db2 that fine but are they same table.

If not:
1. You can do it in one job by using one Db2 stage, but need to check with the execution order. Else better to design a second job.

If yes:
You can use user defined query option one to load in generated tab and in after tab use the delete statement.

DeepakCorning · Post by **DeepakCorning** » Wed Oct 18, 2006 9:17 am

I am not sure if you can do this in the same job , can not think of a way to dothis in the same job. Another approach to do this is to pull all the data in a flat file and then delete statement in one job and then useing the file as the source.

sb_akarmarkar · Post by **sb_akarmarkar** » Thu Oct 19, 2006 3:31 am

Hi,

If there are few numbers of rows you are selecting then you can delete rows from source while inserting into target... becsuse will lock be for select and in one shot you will get all required row and lock will be released after...so you can delete data from source while inserting into target
but If there there is more number of rows then better populate key column from source into flat file while inserting into target and link that file to source and write user defined query to delete.

Source------->Transformer--------->Target
|
|
----------------->Flatfile ------->Source

__________

Anupam[/img]

kumar_s · Post by **kumar_s** » Thu Oct 19, 2006 3:37 am

But you can try to have a ORDER BY class or a Aggregator in the job to check if the lock be released, so that you can delete in the same job.