You can create a data load in either of the following ways:

• Using the Create Data Load wizard.
• Using the Projects page.

The newly created data load is displayed in the Data Loads page of the associated project. Click the Actions icon () next to the selected data load to edit, rename, start, export, or delete it.

Creating a Data Load from the Home Page

This section describes the generic steps to create a data load. If you plan to load and transform data using OCI GoldenGate, create the data load using the Projects page. See Creating a Data Load from the Projects Page.

To create a data load from the Home page:

1. From the left pane, click the Home tab. Click Load Data.

   The Create Data Load wizard appears.

2. In the Name field, enter a name for the data load. The field is pre-populated with a default name. You can edit this value.
3. Add a description. This is optional.
4. Select a project name from the drop-down. If this your first time here, click the + icon to create a project. If you have logged in as SUPERVISOR, the default project name is Home. For other users, the default project name is in the format <username>_Home. You can edit the default value. See Work with Projects for more information about projects.
5. Click Next.
6. To define your source connection, from the Connection drop-down, select the required connection from which you wish to add the data entities. Alternatively, click the + icon to create a new connection. See Work with Connections for more details about connections.
7. In the Schema drop-down, all schema corresponding to the selected connection are listed in two groups:
   • Existing Schema (ones that you've imported into Oracle Data Transforms) and
   • New Database Schema (ones that you've not yet imported).
   Select the schema that you want to use from the drop down.

   Note
   
   If there is missing information such as user name or password not specified, wallet missing, and so on, the list may fail to populate with a “This connection has missing information.” error. Click the Edit icon () to open the Update Connection page where you can fill in the missing details.
8. Click Next.
9. Similarly, define the target connection.
10. Click Save.

    The Data Load Detail page appears listing all the loaded data entities.

Creating a Data Load from the Projects Page

To create a data load from the Projects page,

1. On the Home page, click Projects, and then the required project title. In the left pane, click Data Loads, and then click Create Data Load.

   The Create Data Load wizard appears.

2. In the Name field, enter a name for the data load.
3. Add a description. This is optional.
4. Select the source and target schemas.
   Note
   
   Make sure that you have created connections before you plan to create a data load using the Projects page. See Work with Connections for more details about connections.
   • To use the OCI GoldenGate Deployment Console to load data entities:
     1. Select the Use GoldenGate checkbox.

        The Create Data Load page now shows fields that are specific to OCI GoldenGate.

     2. Select the GoldenGate connection from the drop-down.
     3. To select the source connection, select the Registered Database and the Schema.
     4. Similarly, define the target connection.
   • To use all other connection types to load entities:
     1. To define your source connection, select the connection from which you wish to add the data entities from the Connection drop-down.
     2. From the Schema drop-down select the schema that you want to use. All schema corresponding to the selected connection are listed in two groups
        • Existing Schema (ones that you've imported into Oracle Data Transforms) and
        • New Database Schema (ones that you've not yet imported).
5. Click Create.

   The Data Load Detail page appears listing all the loaded data entities.

It includes the details of the source schema, the data entities that are loaded from the source schema, and the details of the target schema. You can choose the action that you want to apply on each data entity – recreate, truncate, append - and load tables in bulk to the target schema. When you run a data load, multiple jobs run in the background to complete the request.

You can run a data load in either of the following ways:

• Using the Data Load Detail Page.
• Running a Data Load from the Workflow Details Page.

Running a Data Load from the Data Load Detail Page

The Data Load Detail page displays the information that you need to run a data load. You can apply different actions - incremental merge, incremental append, recreate, truncate, append - on the data entities before loading it to the target schema.

Note

APPLIES TO: Data Transforms that is available as a separate listing on Marketplace called Data Integrator: Web Edition.

If the data load is huge, you might want to increase the memory of the ODI Agent to avoid any issues. Follow the instructions in Increase the Memory of ODI Agent before you start to run the data load.

To run a data load from the Data Load Detail Page:

1. In the Data Load Detail page, select the data entities that you want to move to the target schema.
   To filter the list, you can do one of the following:

   • Enter the name or part of the name in the Name text box. This search returns data entities that include the specified string in the name. For example, the search string AD returns results such as ADMIN, ADDRESS, BADGE, UPGRADE, WORKLOAD, and so on.
   • Turn on the Use Regular Expression toggle to use pattern matching to search for particular strings of characters. For example, the search string CO.* returns results such as CONTACT, COUNT, COUNTRY and so on.
   You can select multiple data entities and load them to the target schema. You can also sort the displayed list using the following options:

   • All - Displays all the data entities that match the search criteria.
   • Selected - Displays all the rows that you selected.
   • Unselected - Displays all the unselected rows.
   • Invalid – Displays all the invalid rows.

   Note
   
   These options display the list of data entities based on the search criteria. To view the list of all data entities, clear any applied filters.
2. Click on the required icon to choose any of the following actions:
   • Incremental Merge - Updates the data in the selected column by comparing the source table with the target table based on the specified merge key. To use this option, select the column that you want to merge and then select the merge key. To use this option, select the column that you want to merge and then select the merge key. Click the Validate icon () to validate the selected values.
     Note
     
     

     • The last update values for the Incremental Column are stored in the Data Load. If you create a new data load with the same table, this option acts like Append, Truncate, and Recreate the first time you run it.
     • If the target table is not truncated, do not start the Data Load using the Append, Truncate, or Recreate mode and then switch the action to Incremental Merge. This will cause every existing row in the target table to be updated. Oracle recommends that you start the Data Load using the Incremental Merge action.
   • Incremental Append - Updates data in the selected column in the target schema. To use this option, select the column that you want to update and click the Validate icon () to validate the selection.

     The first time you run the Data Load, this option works in the same way as the Append, Truncate, and Recreate options, meaning all rows are inserted into the target. On all subsequent Data Load runs, the Incremental Column is used to filter only new rows to append data to avoid the same rows from being loaded more than once.

     Note
     
     

     • The last update values for the Incremental Column are stored in the Data Load. If you create a new data load with the same table, this option acts like Append, Truncate, and Recreate the first time you run it.
     • If the target table is not truncated, do not start the Data Load using the Append, Truncate, or Recreate mode and then switch the action to Incremental Append. Oracle recommends that you start the Data Load using the Incremental Append action.
   • Recreate – If the table is already present in the target schema, drops the existing table and recreates it.
     Note
     
     This option is not available for data entities that are loaded using OCI GoldenGate.
   • Truncate – If the table is already present in the target schema, deletes all the data from the selected table. Nothing is dropped.
     Note
     
     For Delta Share data loads the Data Load Detail page only includes the option. You cannot apply different actions - incremental merge, incremental append, recreate, truncate, append - on the data entities before loading it to the target schema. This is to make sure that the data is consistent between the Delta Sharing server and the target schema.
   • Append – Inserts all rows in the Dataset into the target. If the Dataset contains records that already exist in the target, there will be duplicate records. Use Append if the source dataset is expected to have only new records or duplicate rows in the target are not a concern.
   • Do Not Load – Skips the selected data entity from the data load job. After you click Save, these data entities are no longer available for future data load jobs.

   You can select multiple data entities and apply different actions. The unsaved rows are highlighted in bold.

   Note
   
   These options are not available for Delta Share connections.
3. To specify how you want to store the source column names in the target tables, click Advanced Options, which is on the right side of the Data Load Detail page. Choose one of the following:
   • Retain original names by enclosing all names with delimiters - Creates column names with the same names as is from the source tables in the target table.
   • Use no delimiters - This is the default selection. Converts all the column names to upper case and replaces spaces and special characters with underscores.

     The following options are applicable to reserved words such as Date, Timestamp, Start, and so on.

     • Enclose with delimiters - This is the default selection. Encloses column names that are reserved words with delimiters (not all column names).
     • Use a prefix - Adds the specified prefix to column names that are reserved words (not all column names).

     For column names that have the same name after conversion, the names are suffixed with a numeric value to maintain uniqueness. For example column names

     Date, date, DATE, Item_@Code, Item$$Code, Item%%Code

     are created in the target table as

     DATE, DATE_0, DATE_1, ITEM__CODE, ITEM__CODE_0, ITEM__CODE_1.

   Note
   
   Once the data load is run, the selected options are applied and retained for all subsequent runs. You cannot change the configuration.
4. Click to save the changes. A green checkmark () in the row indicates that the changes are saved.
5. To start the data load,
   • Click .
   • For GoldenGate data loads, click .

   A confirmation prompt appears when the data load starts successfully.

To check the status of the data load, see the Status panel on the right below the Target Schema details. For details about the Status panel, see Monitor Status of Data Loads, Data Flows, and Workflows. This panel shows links to the jobs that execute to run this data load. Click the link to monitor the progress on the Job Details page. For more information about jobs, see Create and Manage Jobs.

Running a Data Load from the Workflow Details Page

You can add multiple data loads to a workflow along with data flows or workflows and run them as separate steps. The left panel of the Workflow Details page lists the data flows, workflows, and data loads that are available for use.

After you have added the data loads to the workflow, click to execute them.