Topic
2 replies Latest Post - ‏2011-08-25T15:31:24Z by SystemAdmin
SystemAdmin
SystemAdmin
203 Posts
ACCEPTED ANSWER

Pinned topic Suggestion for SQW Subflow functionality

‏2011-07-29T15:23:01Z |
Hello

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.

Make sense?

Many thanks in advanced
Updated on 2011-08-25T15:31:24Z at 2011-08-25T15:31:24Z by SystemAdmin
  • JP_Parkin
    JP_Parkin
    128 Posts
    ACCEPTED ANSWER

    Re: Suggestion for SQW Subflow functionality

    ‏2011-08-18T17:42:07Z  in response to SystemAdmin
    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

    Best regards,
    JP
    • SystemAdmin
      SystemAdmin
      203 Posts
      ACCEPTED ANSWER

      Re: Suggestion for SQW Subflow functionality

      ‏2011-08-25T15:31:24Z  in response to JP_Parkin
      Hi

      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.