Mulitple update to the table using DataStage

[g_rkrish]

Hi,

Actually i have table that need to be populated.Each of the column in the table has different business logic but with the same column in the source.

I thought of doing that by populating primary keys first then updating each column one by one.Is any way in datastage that we can do that with single shot.Or do we need to do the Update the table in a sequence.

Can any one help me with this?

thanks,
RK

[ogmios]

Your mail has some "holes" in it... but let's try.

You need to populate a table with fields from a source, the updates have the same primary key but the logic for the calculation is different. And you can't do it in a single SQL, e.g. your groupings are different.

2 SQLs as input one table as output.

Personally I would write 2 separate jobs to update your table and execute these in sequence.

Ogmios

[g_rkrish]

Your mail has some "holes" in it... but let's try.

You need to populate a table with fields from a source, the updates have the same primary key but the logic for the calculation is different. And you can't do it in a single SQL, e.g. your groupings are different.

2 SQLs as input one table as output.

Personally I would write 2 separate jobs to update your table and execute these in sequence.

Ogmios

Yes your correct.I want to do that business logic where the business logic depends on the same column but with different values on that column.

i can do the way you said but the problem is we have like 300 odd columns need to be populated.so is any way we can do it.

thanks,
RK

[ogmios]

No... no way that I know of.

[kcbland]

Best practice: Build your target row within DS and then either insert or update it once in the target. DB i/o is a performance killer.

[ogmios]

I have been in a similar situation the original poster is.

They probably want to have kind of a summary table. They can calculate the primary key and each column separately, but they can't combine all columns in one go.
E.g. imagine you compare the primary key to multiple groupings, you can determine the primary key and 1 grouping very easily, but it's very hard to get the groupings for the same primary key together on 1 row. SQL Pivoting would do it for small amounts of data.

I have a project running 40 jobs in sequence like that just filling additional (40) columns...

Ogmios

[g_rkrish]

Best practice: Build your target row within DS and then either insert or update it once in the target. DB i/o is a performance killer. ...

The way am doing that is


Source Target

1)
Column 1:L1 Column1:'Y'
Column2:'XXXXX'

2)

Column 1:L3 Column1:'Y'
Column2:'YYYYYY'


etc Like that we are doing is any possible way that we do it in DS level.

Thanks,
RK

[kcbland]

This works well in DS, because if you compute each column separately as a separate job, staging to a hashed file for each column, then you just need to stream your driving keys and reference lookup each column. Since each column would be an equi-join, you need to zero/default fill the target column if no reference value is found. You merge all the columns and create the final row once. Now you just check to see if it's an insert or update and appropriately load each row once, instead of insert, updatex40.

[g_rkrish]

Best practice: Build your target row within DS and then either insert or update it once in the target. DB i/o is a performance killer. ...

The way am doing that is


Source Target

1)
Column 1:L1 Column1:'Y'
Column2:'XXXXX'

2)

Column 1:L3 Column1:'Y'
Column2:'YYYYYY'


etc Like that we are doing is any possible way that we do it in DS level.

Thanks,
RK

I think I messed up the format .


1)

Source:
Column 1:L1 Column2:'XXXXX'

Target:

Column1:'Y'


2)

Source:
Column 1:L3
Column2:'YYYYYY'

Target:
Column1:'Y'


Thanks,
RK