Working with append transformations

You use append transformations to add selected result set columns from different queries or tables to the final result set.

About this task

Append transformations allow you to combine result set columns from different queries or tables from either the same or different data sources to one result set. When you append two columns from two different queries, all of the rows from both of the selected columns are combined in one column in the final query results. The column in the First list determines the name of the final query result set column, and the rows from the column will be displayed first.

To configure an append transformation in the analytical query structure:

Procedure

  1. In the Analytical query structure, select an append transformation node.
    In the Item editor pane, you can view the items of the append transformation. The columns from the source data set in the upper node are displayed in the First list. The columns from the source data set in the lower node are displayed in the Second list.
  2. Optional: If the selected append transformation is not populated yet, add queries or tables into <none> nodes. For more information, see Populating analytical query transformations.
  3. Specify the query result columns that you want to add together.
    1. Select a column from the First list.
    2. Select a column from the Second list.
    3. Click Add Column above the Result columns list.
      The new append condition is added to the Result columns list.
    4. Repeat this procedure for each append condition that you want to add.
  4. If you are populating the append transformation with objects that contain similar names, you can try to automatically create the appropriate append conditions by clicking Automatch.
    If there are any possible auto-matches, they are displayed in the Result columns list.
    Note: Clicking Automatch will erase any append conditions that you previously set.
  5. To include a column without matching it with any column from the other data set, select this column and select <unmatched> from the second column list. Click Add Column above the Result columns list.
  6. Optional: To add another transformation to the current analytical query structure, follow the procedure described in Building analytical query structures.

Results

Whether it is the only transformation in an analytical query or one of many nodes in a complex structure, an append transformation can be run on a database to accelerate the execution of this query. In the Output view, you can see that SQL UNION ALL is applied for such transformations. Generally, append transformations are run on databases if they contain queries with simple SQL statements using only the SELECT operator and retrieving data from one database. Db2 database queries can also apply SQL statements that involve join conditions, sort conditions, row conditions, calculated columns, simple filtering, groups, and categorizations.