How to update the source table in the same job?

dsdoubt · Post by **dsdoubt** » Sat Mar 03, 2007 5:12 am

Hi,
Pls let me know, how to update the source table in the same job based on some condition.

ray.wurlod · Post by **ray.wurlod** » Sat Mar 03, 2007 5:55 am

Why bother with a DataStage job? All you need is an UPDATE statement in the database.

chulett · Post by **chulett** » Sat Mar 03, 2007 7:43 am

As noted, sounds like a job for SQL. If you disagree, please expand on your question. Alot.

kduke · Post by **kduke** » Sat Mar 03, 2007 11:22 am

If you do this then you might lock the records you are trying to update. So the source SQL needs to have the correct options on it.

dsdoubt · Post by **dsdoubt** » Sat Mar 03, 2007 8:00 pm

kduke wrote:If you do this then you might lock the records you are trying to update. So the source SQL needs to have the correct options on it.

This is my concern. We are in desing phase, and dont have access to Datastage. So kindly let me know, if I read some data from a table, and trying to updated the same records with some change, will there be any lock. Can we acheive with read commit and Commit interval of 1 record?

kduke · Post by **kduke** » Sat Mar 03, 2007 11:50 pm

I think you want read uncommitted but I need to look it up. Sometimes you can do it in user defined SQL with a NOLOCK or whatever the database needs based on syntax.

DSguru2B · Post by **DSguru2B** » Sun Mar 04, 2007 10:21 am

Unload your table into a flat file or dataset and then do your transformations and loading.

kumar_s · Post by **kumar_s** » Sun Mar 04, 2007 11:45 pm

DSguru2B wrote:Unload your table into a flat file or dataset and then do your transformations and loading.

But it again require two jobs, or some superfluous flow isolating stages.

DSguru2B · Post by **DSguru2B** » Mon Mar 05, 2007 7:55 am

My direction was towards modularization. More like an advice on not to do it in a single job.

DSGuru79 · Post by **DSGuru79** » Mon Mar 05, 2007 9:35 am

Yes I agree with DSGuru2B. In my project we have this kind of scenario. We always use 2 jobs and it works fine.
1. Dump the tables into dataset for reading (job 1)
2. Update the same table by lookup on the dataset (job 2)

ray.wurlod · Post by **ray.wurlod** » Mon Mar 05, 2007 12:50 pm

So, if we can have 79, is 2B a hex number. And, if so, is 79 a hex number?

DSguru2B · Post by **DSguru2B** » Mon Mar 05, 2007 12:56 pm

Very funny Ray, 2B a bionic number.

ray.wurlod · Post by **ray.wurlod** » Mon Mar 05, 2007 1:00 pm

And here's me assuming it was a blackness grade in pencils...

kduke · Post by **kduke** » Mon Mar 05, 2007 8:48 pm

I think doing this in one job is fine.

dsdoubt · Post by **dsdoubt** » Tue Mar 06, 2007 12:01 am

Thanks Kim.

May I know how to specify the Uncommited read?