insert data into table that is being populated

datastgnewbie · Post by **datastgnewbie** » Mon Apr 17, 2006 10:02 am

Hi everyone,
I have a column X that is being populated, in the transformer, after that value has been populated i need to take column X and compare it to another reference table, to populate another column. I tried this so far and i kept crashing datastage. After column x was done i sent that to a hash file and than to a transformer where that was compared to the reference table, than sent back to another hash file where i sent it to the final table but it kept on crashing because the table was already open. If anyone has any suggestion it is greatly appriacated. THANKS

diamondabhi · Post by **diamondabhi** » Mon Apr 17, 2006 11:11 am

Can you post the exact error that you are getting?

DSguru2B · Post by **DSguru2B** » Mon Apr 17, 2006 1:52 pm

When you say Column X populated in transformer would make us assume that you are populating it through datastage(not from the table).
Post your design.

kris_daredevil · Post by **kris_daredevil** » Mon Apr 17, 2006 1:58 pm

load the x column into file instead of loading into table and make look up with reference column and then finnaly load into table.

ray.wurlod · Post by **ray.wurlod** » Mon Apr 17, 2006 3:30 pm

It's unhelpful not having the exact error message. Chances are that you're trying to do too much with two few stages, or even too few jobs, and perhaps getting locking issues in the database.

Staging the intermediate data, perhaps into a file as the previous poster suggested, is one way, but you can get by simply by streaming the result of the first lookup into a second Transformer stage in order to perform the second lookup.

If there may be repeats in the source data, use a hashed file to contain a copy of current rows from the target table. The hashed file can be updated without needing to touch the target table. There are plenty of descriptions of this technique on this forum. A Search will find them.

kris_daredevil · Post by **kris_daredevil** » Mon Apr 17, 2006 5:04 pm

ray.wurlod wrote:It's unhelpful not having the exact error message. Chances are that you're trying to do too much with two few stages, or even too few jobs, and perhaps getting locking issues in the database.

Stag ...

ray
i think he is loading the same table in the etl twice which is causing the etl job to hang!!!