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
Mulitple update to the table using DataStage
Moderators: chulett, rschirm, roy
Re: Mulitple update to the table using DataStage
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
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
In theory there's no difference between theory and practice. In practice there is.
Re: Mulitple update to the table using DataStage
ogmios wrote: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
RK
Re: Mulitple update to the table using DataStage
No... no way that I know of.
In theory there's no difference between theory and practice. In practice there is.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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
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
In theory there's no difference between theory and practice. In practice there is.
kcbland wrote: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
RK
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
g_rkrish wrote:kcbland wrote: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
RK