Data integration tutorial: Transform batch data with DataStage

Start with a basic DataStage flow that joins the mortgage applicants and mortgage applications data sets, and then outputs that result to a CSV file in the project. Follow these steps to run the DataStage flow:

1. Start in the Data integration project. If you don't have the project open, follow these steps:

   1. From the Navigation menu , choose Projects > View all projects.

   2. Open the Data integration project.

2. Click the Assets tab to see all of the assets in the project.

3. Click Flows > DataStage flows.

4. Click the Data integration flow in the list to open it. This flow joins the Mortgage Applicants and Mortgage Applications tables that are stored in Db2 Warehouse, filters the data to those records from the State of California, and creates a sequential file in CSV format as the output.

5. Click the zoom in icon and zoom out icon on the toolbar to set your preferred view of the canvas.

6. Double-click MORTGAGE_APPLICATIONS_1 node to view the settings.

   1. Expand the Properties section.

   2. Scroll down, and then click Preview data. This data set includes information that is captured on a mortgage application.

   3. Click Close.

7. Double-click MORTGAGE_APPLICANTS_1 node to view the settings.

   1. Expand the Properties section.

   2. Scroll down, and click Preview data. This data set includes information about mortgage applicants who applied for a loan.

   3. Optional: Visualize the data.

      1. Click the Chart panel.

      2. In the Columns to visualize list, select STATE.

      3. Click Visualize data to see a pie chart showing the distribution of the data by state.

      4. In the Chart type section, click the Treemap icon to see the same data in a treemap chart.

   4. Click Close.

8. Double-click Join_on_ID node to view the settings.

   1. Expand the Properties section.

   2. Note that the join key is the ID column.
      

   3. Click Cancel to close the settings.

9. Click the Logs icon on the toolbar so you can watch the flow's progress.

10. Click Compile, and then click Run. Alternatively, you can click Run which compiles and then runs the DataStage flow. The run can take about one minute to complete.

11. View the logs. You can use the total rows and rows/sec for each step in the flow to visually verify that the filter is working as expected.

12. When the run completes successfully, click Data integration in the navigation trail to return to the project.
    

13. On the Assets tab, click Data > Data assets.

14. Open the MORTGAGE_DATA.CSV file. You can see that this file contains the columns from both the mortgage applicants and mortgage applications data sets.

Now that you joined the mortgage applicant and application data, you are ready to edit the DataStage flow to:

• Task 2: Specify a key column for the Join stage.
• Task 3: Add credit score data from a PostgreSQL database.
• Task 4: Add a Join stage to join the credit score data with the applicant and application data.
• Task 5: Add a Transformer stage to calculate total debt.
• Task 6: Add interest rate data from a MongoDB database.
• Task 7: Add a Lookup stage to look up interest rates for applicants based on their credit scores and Golden Bank's daily interest rate ranges.

Identifying a key column indicates to DataStage that column contains unique values. The Join_on_ID node joins the mortgage applicants and mortgage applications data sets using the ID column for the join key. The next phase is to join the resulting data set with the credit score data. Later, you will join the resulting filtered data with the credit score data set. The second join will use the EMAIL_ADDRESS column as the join key. In this task, you edit the DataStage flow to specify the EMAIL_ADDRESS column as the key column for the resulting data set when it is joined with the credit score data.

Follow these steps to change the Join node settings:

1. Click Data integration in the navigation trail to return to the project.
   

2. On the Assets tab, click Flows > DataStage flows.

3. Open the Data integration flow.

4. Double-click the Join_on_ID node to edit the settings.

5. Click the Output tab, and expand the Columns section to see a list of the columns in the joined data set.

6. Click Edit.

7. For the EMAIL_ADDRESS column name, select Key.

8. Click Apply and return to return to the Join_on_ID node settings.

9. Click Save to save the Join_on_ID node settings.

Follow these steps to add the credit score data that is stored in a PostgreSQL database to the DataStage flow:

1. In the node palette, expand the Connectors section.

2. Drag the Asset browser connector to the canvas beside the MORTGAGE_APPLICANTS_1 node.

3. Locate the asset by selecting Connection > Data Fabric Trial - Databases for PostgreSQL > BANKING > CREDIT_SCORE.

   Note: Click the connection or schema name instead of the checkbox to expand the connection and schema.

   

4. Click the Preview icon to preview the credit score data for each applicant.

5. Click Add.

Follow these steps to add another Join stage to join the filtered mortgage application and mortgage applicant joined data with the credit score data in the DataStage flow:

1. In the node palette, expand the Stages section.

2. Drag the Join stage on to the canvas, and drop the node on the link line between the Filter_State_Code and Sequential_file_1 nodes.

3. Hover over the CREDIT_SCORE_1 connector to see the arrow. Connect the arrow to the Join stage.

4. Double-click the CREDIT_SCORE_1 node to edit the settings.

   1. Click the Output tab, and expand the Columns section to see a list of the columns in the joined data set.

   2. Click Edit.

   3. For the EMAIL_ADDRESS and CREDIT_SCORE column names, select Key.

   4. Click Apply and return to return to the CREDIT_SCORE_1 node settings.

   5. Click Save to save the CREDIT_SCORE_1 node settings.

5. Double-click the Join_1 node to edit the settings.

   1. Expand the Properties section.

   2. Click Add key.

      1. Click Add key again.

      2. Select EMAIL_ADDRESS from the list of possible keys.

      3. Click Apply.

   3. Click Apply and return to return to the Join_1 node settings.

   4. Change the Join_1 node name to Join_on_email.

   5. Click Save to save the Join_1 node settings.

Follow these steps to add a Transformer stage that creates a new column by summing the LOAN_AMOUNT and CREDITCARD_DEBT columns:

1. In the Stages section, drag the Transformer stage on to the canvas, and drop the node on the link line between the Join_on_email and Sequential_file_1 nodes.

2. Double-click the Transformer node to edit the settings.

3. Click the Output tab.

   1. Click Add column.

   2. Scroll down in the list of columns to see the new column.

   3. Name the column TOTAL_DEBT.

   4. Click the Edit icon in the row’s Derivation column.

   5. Click the Calculator icon in the Derivation column to open the expression builder.

   6. Search for LOAN_AMOUNT, and double-click the column name to add it to the expression. Note that the link number is appended to the column name.

   7. Type a plus sign +.

   8. Search for CREDITCARD_DEBT, and then double-click the column name to add it to the expression. Note that the link number is appended to the column name.

   9. Verify that the final expression is Link_7.LOAN_AMOUNT + Link_7.CREDITCARD_DEBT.

      Note: Your link number may be different.

   10. Click Apply and return to return to the Transformer page.

   11. For the CREDIT_SCORE column name, select Key.

4. Click the Stage tab.

   1. Select the Advanced page.

   2. Change the Execution mode to Sequential.

5. Click Save and return to return to the canvas.

Follow these steps to include the interest rates in the flow by adding a data asset connector to a MongoDB database:

1. In the node palette, expand the Connectors section.

2. Drag the Asset browser connector on to the canvas beside the CREDIT_SCORE_1 node.

3. Locate the asset by selecting Connection > Data Fabric Trial - Mongo DB > DOCUMENT > DS_INTEREST_RATES.

4. Click the Preview icon to preview interest rates for each credit score range.
   
   You can use the values in the STARTING_LIMIT and ENDING_LIMIT columns to look up the appropriate interest rate based on the applicant's credit score. The ID column is not needed, so you will delete that column in the next step.

5. Click Add.

Based on each applicant's credit score, you want to look up the appropriate interest rate. Follow these steps to add a Lookup stage and specify the range for starting and ending credit score limits for each interest rate:

1. In the Stages section, drag the Lookup stage on to the canvas, and drop the node on the link line between the Transformer_1 and Sequential_file_1 nodes.

2. Connect the DS_INTEREST_RATES_1 connector to the Lookup_1 stage.

3. Double-click the DS_INTEREST_RATES_1 node to edit the settings.

4. Click the Output tab.

   1. Expand the Columns section, and click Edit.

   2. Select the _ID column.

   3. Click the Delete icon to delete the _ID column.

   4. Click Apply and return to return to the DS_INTEREST_RATES_1 node settings.

   5. Click Save to save the changes to the DS_INTEREST_RATES_1 node.

5. Double-click the Lookup_1 node to edit the settings.

6. Expand the Properties section.

   1. For the Apply range to columns field, select CREDIT_SCORE. The Reference Links, Operator, and Range column fields display.

   2. For the Reference Links, select Link_9.

      Note: Your link number may be different.

   3. For the first Operator, select <=.

   4. For the first Range column, select ENDING_LIMIT.

   5. For the second Operator, select >=.

   6. For the second Range column, select STARTING_LIMIT.

7. Click the Output tab.

   1. Expand the Columns section, and click Edit.

   2. Select the STARTING_LIMIT and ENDING_LIMIT columns.

   3. Click the Delete icon to delete these unnecessary STARTING_LIMIT and ENDING_LIMIT columns.

   4. Click Apply and return to return to the Lookup_1 node settings.

   5. Click Save to save the changes to the Lookup_1 node.

Follow these steps to edit the Sequential file node to create a final output file as a data asset in the project, and then compile and run the DataStage flow:

1. Double-click the Sequential_file_1 node to edit the settings.

2. Click the Input tab.

3. Expand the Properties section.

4. For the Target File, copy and paste MORTGAGE_APPLICANTS_INTEREST_RATES.CSV for the file name.

5. Select Create data asset.

6. For the First line is column names field, select True.

7. Click Save.

8. Click Run which compiles and then runs the DataStage flow. The job takes about 1 minute to complete.

9. Click Logs on the toolbar to watch the flow's progress. It is normal to see warnings during the run, and then you see that the flow ran successfully.