Creating and Running a Data Load

A data load allows you to load multiple data entities from a source connection to a target connection.

After you create the data load, you are taken to the Data Load Detail page that displays the details that you need to run a data load. 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.

Note:

Data load is not supported for Oracle Object Storage connections.

Creating a Data Load

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

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:

From the left pane, click the Home tab. Click Load Data.
The Create Data Load wizard appears.
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.
Add a description. This is optional.
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 Working with Projects for more information about projects.
Click Next.
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 Working with Connections for more details about connections.
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.
Click Next.
Similarly, define the target connection.
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,

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.
In the Name field, enter a name for the data load.
Add a description. This is optional.
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 Working 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).
Click Create.
The Data Load Detail page appears listing all the loaded data entities.

Running a Data Load

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

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 Increasing the Memory of ODI Agent before you start to run the data load.

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

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.
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. Click the Validate icon () to validate the selected values.
- 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.
- 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:
  This option is not available for data entities that are loaded using OCI GoldenGate.
- Append – If the table is already present in the target schema, adds rows to the table.
- 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.
Click to save the changes. A green checkmark () in the row indicates that the changes are saved.
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 Viewing 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 Managing 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.

For data loads, the left panel lists the following two folders:

Data Loads - This folder lists all the data loads that you have created in the local Data Transforms instance. When you select a step in the workflow that is a local data load, the Properties Panel available on the right side of the design canvas displays the Type as Data Load and the Linked Object as Home><nameofDataLoad>.
Remote Data Loads - This folder lists all the data loads that you have created in a remote Data Transforms instance. See Creating a Data Transforms Connection for Remote Data Load. When you select a step in the workflow that is a remote data load, the Properties Panel available on the right side of the design canvas displays the Type as Remote Data Load and the Linked Object as Name of the Data Transforms connection > <nameofDataLoad>.

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

Increasing the Memory of ODI Agent

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

If the data that you are loading from the source schema is huge, then you may want to increase the memory of the ODI Agent to avoid OutOfMemory exception errors.

To increase the memory of the ODI Agent:

Edit the /u01/oracle/transforms_home/common/scripts/jettyServer.sh file.
Add the java -Xms1024m -Xmx4096m parameter.

Restart the jetty server. Log in as OPC user and execute the following commands:

ssh -i <path to id_rsa> opc@<Instance IP>
sudo su
systemctl stop|start jettyserver.service
exit