I find subflows quite useful, but specially for breaking big data flows into smaller pieces, rather than reusing the same functionality in different data flows. However, at the end of the day, the second one is probably the most interesting.
There are several transformation patterns common in SQW applications. For instance, check a hierarchy level for null values, duplicates and lookup matching. The problem is that you cannot implement this pattern with subflows, because with the level ID and parent level ID usually come more attributes that can vary from one level to other.
I wonder if subflows inputs could be powered with some kind of "carry over columns" option. I mean, map the required ones, and propagate the rest until the end of the flow.
I understand that performance can be and issue here, but the benefits for the designer can be great.
Many thanks in advanced
Pinned topic Suggestion for SQW Subflow functionality
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2011-08-25T15:31:24Z at 2011-08-25T15:31:24Z by SystemAdmin
JP_Parkin 100000R2WA128 Posts
Re: Suggestion for SQW Subflow functionality2011-08-18T17:42:07ZThis is the accepted answer. This is the accepted answer.Hi - the concept makes a lot of sense and is something that I have been thinking about since I started looking at the subflows. The challenge of course is coming up with a good solution :)
One option that can work for some situations is to join the result of the subflow with the subflow input using a table join. You would create a subflow that takes a identifying key column and whatever data is necessary for the subflow to execute. You then link the operator that was the source to the subflow and the output of the subflow to a Table Join operator. The key columns are needed in order to reconcile the rows from the subflow with the original data.
While this creates what may appear to be a fairly complex join, in many cases the DB2 optimizer will recognize that it doesn't really need to perform a real join since the source tables are the same and the subflow logic will be pushed into the select list.
This is clearly not the nicest visual solution for the Warehouse developer since you need to add the Table Join operator on the palette to bring the columns not used by the subflow back into the flow of data. However, if you have logic that you would like to repeat using a subflow it might be a workable solution until a better solution is available.
Thanks for your feedback
SystemAdmin 110000D4XK203 Posts
Re: Suggestion for SQW Subflow functionality2011-08-25T15:31:24ZThis is the accepted answer. This is the accepted answer.
- JP_Parkin 100000R2WA
I have been using the approach you mention from the very beginning. I agree that is not the nicest solution, specially when the subflow has more than one output (valid, discarded and warning records, for instance), so you have to add several join operators.
Another impacting thing is the fact that subflow input columns cannot be more generic (string, integer, decimal,...), so you don't have to remap the types when you connect to the subflow. An option may be that the subflow takes the type of the input ports when you connect them.
Many thanks for your answer.