Example: Using a variable to create a data flow condition

You can create valid SQL expressions in the SQL Expression Builder and valid SQL conditions in the SQL Condition Builder by using a combination of operations, keywords, and variables. If you use a variable in a data flow condition, you can specify different data types and values for the variable at different phases of the data flow design process.

Before you begin

Prerequisites
  • Be sure that you can access the data warehousing in Db2® Tutorial sample data on which this example is based. From the sample data, you need the DWESAMP database and the sqlwSample data ing project.
  • Connect to the DWESAMP database.
  • Reverse engineer the PRODUCT table from the MARTS schema of the DWESAMP database in the Data Source Explorer to the DWESAMP database model of the sqlwSample data ing project in the Database Project Explorer.
  • Using the Manage Variables wizard, create a variable group named sample_variable_group. In that group, create a variable named Change_product_dept_name, with the type of String and a current value of PRODUCT DEPARTMENT. In the Final Phase for Value Changes field, select EXECUTION_INSTANCE.
  • Create the c:\Reports folder on your computer. You can remove this folder and the contents after you complete the example and verify the results.

About this task

This example explains how to create a data flow that generates a text file when you run the data flow. The text file lists all of the products for a selected department. The data flow condition uses a variable that allows you to change the department name for a product at run time to specify which items are listed in the text file. By using one variable, you can create a text file that lists all of the items for any product department.

Assume that you are using the sqlwSample data ing project to model inventory reporting data for a chain of retail clothing stores. The ing project includes a Product table with a column that records the product department name of each item in your inventory. Dress Formal is the product department name for one of your retail clothing lines.

You want to create a text file that lists all of the inventoried items with the Dress Formal department name. You plan to import this file to the spreadsheet program in which you are designing your inventory reports. You can create the following data flow to generate this text file.

Figure shows the data flow that uses a table source operator, a where condition operator, and a file export operator.

To create a data flow that generates a text file that lists all items in the Dress Formal department:

Procedure

  1. In the Design Studio, create a new data flow and name it sample_data_flow.
  2. Specify PRODUCT as the table source. Drag a table source operator onto the canvas. In the General tab, browse for the PRODUCT source database table, expand the DWESAMP database model, expand the MARTS schema, and then select the PRODUCT table.
  3. Create a where condition that uses a variable for the product department name. Drag a where condition operator onto the canvas, and connect the output port of the PRODUCT table source operator to the input port of the where condition operator. Set the condition properties by clicking the Filter condition ellipsis button (...) to open the SQL Condition Builder. Build the following condition:
    1. For the input, double-click the PD_DEPT_NM (Product Department Name) column to start building the condition in the SQL Text area.
    2. For the operation, select the equals sign (=).
    3. In the variables area, double-click the Change_product_dept_name variable, and click OK to finish the condition. The condition should look like this: 
    INPUT_02.PD_DEPT_NM = '${sample_variable_group/Change_product_dept_name}'
  4. Drag a file export operator onto the canvas to export a text file that captures the data flow execution results. Connect the results port of the where condition operator to the input port of the file export operator.
    1. On the General page, specify that you want to use a variable for the file name by selecting Use Variable at the File Name field.
    2. In the File Name field, specify the path, name, and extension as follows: type c:\Reports\ and browse to append the Change_product_dept_name variable as the file name. Then, type the .txt file extension. In the SQL text area, the file path and name should look like this: 
    c:\Reports\Change_product_dept_name\.txt
  5. Save the data flow, validate it, and then run it.
    1. Select Data Flow > Execute.
    2. In the Flow Execution window, accept the default settings on the General, Diagnostics, and Resources pages.
    3. Select the Variables page, and change the Current Value field of the Change_product_dept_name variable to DRESS FORMAL. In the Variable Information window that opens when you edit the variable, notice that you can change the data type and the current value of the variable. You can also specify the final design phase in which the current value can be changed.
  6. Open the c:\Reports folder and the DRESS FORMAL.txt file that is in it.

Results

The file lists all of the items that are inventoried in the Dress Formal department. You can easily create a formatted report of this data by exporting it to a spreadsheet program.