I believe conditional lookups are planned for future releases of Datastage.
There are some alternatives:
- Split your data streams to let the minimum number of rows advance to each DBLookup tranformer. Those rows that are successfully lookup up are outputted to a file, the rest are passed on to the next transform:
[HashFile] [DBLookup1] [DBLookup2] |
[FlatFile]->[Transform]->[Transform]->[Transform]->[FlatFile]
------------->[FlatFile]
------------------------->[FlatFile]
Merge the three files with an after job command. This is much faster since you are not carrying the bulk of your data through your 2nd and 3rd transforms as extra baggage.
- You could try to build a hash file lookup to replace the dblookup1 and dblookup2 and populate it with every possible combination of key and computed value. This really depends on your data and lookup SQL.
regards
Vincent
Constraints on the Left hand side of the Transform
Moderators: chulett, rschirm, roy
Thanks Vincent,
I had already thought of splitting the output so that the data to another file and then merging the files. The problem is that the next stage or processing needs the data in the original order otherwise we introduce a HEAP of extra processing (which we were able to remove this processing by assuring the data was in the correct order).
So to get the data in the correct order we would need to bulk load all three record sets into a table and then select them out in the correct order. Thanks for the suggestion, its good to see somebody else was thinking along the same lines
I would also love to change the DB Lookups into Hash files, but the the logic required by the lookup cannot be done by Hash files(the SQL contains a MAX, a BETWEEN and , GROUP BY and HAVING statements).
Any idea when conditional lookups are planned? I assume that they are not part of DS6?
Thanks
Starg.
PS. We are on DS 5.2.1
I had already thought of splitting the output so that the data to another file and then merging the files. The problem is that the next stage or processing needs the data in the original order otherwise we introduce a HEAP of extra processing (which we were able to remove this processing by assuring the data was in the correct order).
So to get the data in the correct order we would need to bulk load all three record sets into a table and then select them out in the correct order. Thanks for the suggestion, its good to see somebody else was thinking along the same lines
I would also love to change the DB Lookups into Hash files, but the the logic required by the lookup cannot be done by Hash files(the SQL contains a MAX, a BETWEEN and , GROUP BY and HAVING statements).
Any idea when conditional lookups are planned? I assume that they are not part of DS6?
Thanks
Starg.
PS. We are on DS 5.2.1
Hello Starg,
the condition lookup is ownly avaible in DS6/390. But you could solve your problem with the link partitioner/collector in DS 6 by using the modulo function.
Generate an additional field "need_lookup" with the values 0 = not lookup, 1 = lookup. Then you are able to use the link partitioner to splitt your data stream into to different ways.
But it is more elegant to have a condition lookup functionality in DS. I'm confident that we will get this with on of the next release, or not!?
Regards,
Udo
the condition lookup is ownly avaible in DS6/390. But you could solve your problem with the link partitioner/collector in DS 6 by using the modulo function.
Generate an additional field "need_lookup" with the values 0 = not lookup, 1 = lookup. Then you are able to use the link partitioner to splitt your data stream into to different ways.
But it is more elegant to have a condition lookup functionality in DS. I'm confident that we will get this with on of the next release, or not!?
Regards,
Udo