Specifying Data Sources

In this section:

How to:

Reference:

Once you have opened a new data flow, you need to identify the data sources you want to use and add them to the flow. Data sources are represented by synonyms in the navigation pane under an application directory folder when the Synonyms button is selected. The options available when you right-click a synonym are described in Navigation Pane: Synonyms. For guidelines on synonyms used as data sources, see Using Synonyms for Data Sources and Data Targets.

When creating a data flow, information about the data source, such as the column names, is required and supplied when the DataMigrator Server is connected.

When creating or editing a data flow, in order to test your flow, data sources referenced in the flow must be available:


Top of page

x
Procedure: How to Determine What is in a Data Source

Before you add a data source to a flow, you can verify that it contains the data that you want (by viewing the columns) and that the data is available.

To view a sample of the data that a synonym will produce, right-click the synonym in the navigation pane and select Sample Data. The sample data will appear in the data flow workspace.

Sample Data

Note: After a synonym has been dragged into the data flow workspace, you can view its columns, sample data, and transformations. Right-click the synonym and select Toggle. You can view the data by selecting either the Columns, Sample Data, or Test Transforms tab.


Top of page

x
Procedure: How to Add a Data Source to a Data Flow
  1. If you have not already created a new data flow, right-click the application directory where you want the new data flow and select New and then Flow.

    An empty data flow appears in the data flow workspace. The Data Flow tab opens by default, with an SQL object in the center of the workspace.

  2. Drag a data source object from the navigation pane into the data flow workspace to the left of the SQL object.

    or

    Right-click in the workspace to the left of the SQL object, and select Add Source.

    The Select Synonym dialog box opens.

  3. Select a synonym and click the Select button.

Top of page

x
Procedure: How to Change a Data Source in a Data Flow

With the data flow open in the workspace:

  1. Right-click the source object that you want to change and select Change Source.

    The Select Synonym dialog box opens.

  2. Select the replacement synonym and click the Select button.

Note: If you change a data source that has source transformations associated with it, the transformations are preserved, but they will appear red if the new source does not contain all of the columns used in the transformation.


Top of page

x
Procedure: How to Use a Relational Stored Procedure as a Data Source

You can use a relational stored procedure as a data source in a flow if you have created a synonym for that stored procedure. For information on creating the synonym, see How to Create a Synonym for a Relational Stored Procedure.

One use for this would be to do a lookup, for example, passing parameter values to the stored procedure and retrieving an answer set based on the supplied parameter.

This is supported for:

If you have created a synonym for a relational stored procedure, it will appear in the navigation pane, along with all other synonyms.

Example of Synonym on the navigation pane

As an example, we are using an Oracle stored procedure that has empno as a parameter and retrieves information associated with that employee number. The Oracle stored procedure goes against the EMP Oracle sample table. We will join the stored procedure to a flat file that contains valid employee numbers.

This is the sample stored procedure:

/* create get_emp routine */
 
CREATE OR REPLACE PACKAGE get_emp AUTHID CURRENT_USER AS
PROCEDURE get_emp_proc (
   pempno     IN  scott.emp.empno%TYPE,
   pename     OUT scott.emp.ename%TYPE,
   pjob       OUT scott.emp.job%TYPE,
   pMgr       OUT scott.emp.mgr%TYPE
);
END get_emp;
/
show err
CREATE OR REPLACE PACKAGE BODY get_emp AS
PROCEDURE get_emp_proc (
   pempno     IN  scott.emp.empno%TYPE,
   pename     OUT scott.emp.ename%TYPE,
   pjob       OUT scott.emp.job%TYPE,
   pMgr       OUT scott.emp.mgr%TYPE
)
IS
 
CURSOR gRec_cur IS
   SELECT  ename,
      job,
        mgr
   FROM scott.emp
   WHERE EMPNO = pempno;
gRec_rec gRec_cur%ROWTYPE;
BEGIN
  OPEN gRec_cur;
 
  FETCH gRec_cur INTO gRec_rec;
 
  IF (gRec_cur%FOUND) THEN
     pename := gRec_rec.ename ;
     pjob   := gRec_rec.job ;
     pmgr   := gRec_rec.mgr ;
  END IF;
  CLOSE gRec_cur;
  EXCEPTION
     WHEN OTHERS THEN
     ROLLBACK ;
     RAISE;
END get_emp_proc;
END get_emp ;
/
show error
 
GRANT EXECUTE ON get_emp TO public ;

To use a relational stored procedure as a data source:

  1. With a data flow open in the workspace, drag the synonym into the workspace to the left of the SQL object.

    Note: The flow we are using joins a flat file named ora_emps with the get_emp_get_emp_proc Oracle stored procedure. The flat file has only one column, empno, which is joined to the stored procedure. For each empno in the flat file, the stored procedure runs and retrieves the name, job, and manager information for that empno.

  2. Right-click the synonym, select Operations and then Sample Data.

    The sample data dialog box opens.

  3. Select parameter check boxes, enter their values, and click Sample Data.

    Note: If the stored procedure can have more than one answer set, you need to select the appropriate one.

    The sample data appears in the workspace.

    Sample data image

Whenever you test sample data, you will need to supply the values for the parameters.


Top of page

x
Reference: Data Source Object Options

After a synonym has been dragged into the workspace, the following options are available when you right-click it:

Toggle

Opens a window that allows you to view the columns, sample data, and transformations of the synonym.

Refresh Columns

Updates the list of columns in a synonym.

Source Transformations

Opens the Source Transformations window. For details, see Creating a Source Transformation in a Data Source Object

Operations
Open

Opens the synonym in the workspace. The file, segments, and columns appear on the left and their attributes and values appear on the right.

Edit as Text

Opens the code of the synonym the workspace where it can be edited.

Sample Data

Shows a sample of the data associated with the synonym.

Data Profiling

Provides the data characteristics for synonym columns.

Change Source

Opens the Select Synonym dialog box where a different source can be selected.

Delete

Deletes the synonym from the data flow.

Properties

Opens the general attributes of the synonym, as shown in the following image.

Data Source Properties

  • Adapter. The name of the adapter.
  • Application Directory. The application directory for the synonym.
  • Synonym Name. The name of the synonym.
  • Description. A description of the selected synonym.
  • Table Name. The name of the source table or data file.
  • Connection. The name of the connection.
  • Tag. The correlation name of the synonym. You can change the default name (T1, T2, and so on) to one that is more descriptive.

Top of page

x
Updating the List of Data Sources

How to:

You can update the list of data sources by refreshing the tree. You can update the list of columns in a data source by refreshing the source.

This is useful when data sources are added to the server or when columns are added to a data source while you are building a data flow, and you want these changes reflected in the data source or column list.



x
Procedure: How to Update the List of Data Sources

In the navigation pane, right-click the server, the Applications Directories folder, or subfolder, and select Refresh.



x
Procedure: How to Update the List of Columns

In the navigation pane, right-click a data source, and select Refresh.

Note: After you drag the data source onto the data flow workspace, you can refresh the column list by right-clicking the data source and selecting Refresh Columns.


iWay Software