Configuring bulk loading of data

When you use the Oracle connector to bulk load data to an Oracle database, you can enable or disable constraints and triggers. You can also configure the date cache, manage indexes, set options for bulk record loading, and enable manual mode.

Before you begin

Choose the bulk load write method and specify the table to write to.

About this task

In the Oracle Connector stage, you can set properties that apply only when you use the connector to bulk load data. The values for these properties can affect the load performance and prevent issues that might occur during the bulk load.

For example, during a bulk load, enforcing table constraints and triggers might result in additional I/O overhead and prevent a successful load operation. To avoid these issues, disable Oracle constraints and triggers before a bulk load.

To improve load performance, you can configure the Oracle date cache.

If you do not want the stage to load data directly to the Oracle database, you can enable manual mode. When manual mode is enabled, the connector creates control and data files that can be used to load data to the database by using the Oracle SQL*Loader utility.

Procedure

  1. Configure the connector to disable constraints before it bulk loads data and enable constraints after it bulk loads data:
    1. Set Perform operations before bulk load to Yes.
    2. Set Disable constraints to Yes.
    3. Set Perform operations after bulk load to Yes.
    4. Set Enable constraints to Yes.
    5. In the Exceptions table name field, enter the name of the exceptions table.
      If the exceptions table does not exist, the connector creates it. If the exceptions table already exists, the connector deletes any data that is in the table and then uses it.
    6. Set Process exception rows to Yes.
      When Process exception rows is set to Yes, the connector deletes from the target table the rows that fail the constraint checks. If you defined a reject link for the connector and enabled the SQL error - constraint check reject condition, the connector sends the deleted rows to the reject link. If Process exception rows is set to No and rows fail a constraint check, the job stops.
  2. Configure the connector to disable triggers before it bulk loads data and enable triggers after it bulk loads data:
    1. Set Perform operations before bulk load to Yes.
    2. Set Disable triggers to Yes.
    3. Set Perform operations after bulk load to Yes.
    4. Set Enable triggers to Yes.
  3. To control how to handle table indexes during a bulk load, set the Index maintenance option property.
  4. To rebuild indexes after a bulk load:
    1. Set Perform operations after bulk load to Yes.
    2. Set Rebuild indexes to Yes.
    3. Optional: To enable or disable parallelism and logging to the redo log when the index is rebuilt, specify nondefault values for the Parallel clause and Logging clause properties.
      By default, parallel and logging clauses are not included in the ALTER INDEX statement.
    4. Optional: To stop the job if an index rebuild statement fails, set Fail on error for index rebuilding to Yes.
      If an index rebuild fails, the connector logs a fatal error.
  5. If you plan to bulk load data into tables that contain DATE or TIMESTAMP columns, enable and configure the date cache:
    1. Set Use Oracle date cache to Yes.
    2. Optional: In the Cache size property, enter the maximum number of entries that the cache stores.
      The default is 1,000.
    3. Optional: Set Disable cache when full to Yes.
      When the number of entries in the cache reaches the number that is specified in the Cache size property and the next lookup in the cache results in a miss, the cache is disabled.
  6. Set options to control bulk record loading:
    1. Set Array size to a value 1 - 999,999,999.
      The default is 2,000.
    2. Set Buffer size to a value 4 - 100,240, which represents the buffer size in KB.
      The default is 1,024.
    3. Set the Allow concurrent load sessions property depending on your requirement.
  7. To enable manual mode:
    1. Set Manual mode to Yes.
    2. Optional: In the Directory for data and control files property, specify a directory to save the control and data files to.
    3. Optional: In the Control file name property, specify a name for the control file.
      If you do not specify a value for the control file name, the connector generates the name in the servername_tablename.ctl format, where servername is the value that specified for the Server property and tablename is the value specified in the Table name property.
    4. In the Data file name property, specify the name of the data file.
      If you do not specify a value for the data file name, the connector generates the name in the servername_tablename.dat format.
    5. In the Load options property, specify the bulk load options to include in the control file that the connector generates.
      The value contains parameters that are passed to the Oracle SQL*Loader utility when the utility is invoked to process the control and data files. The default value is OPTIONS(DIRECT=FALSE,PARALLEL=TRUE).

      The DIRECT=FALSE parameter tells the Oracle SQL*Loader to use the conventional path load instead of the direct path load. The PARALLEL=TRUE parameter tells the utility that the data can be loaded in parallel from multiple concurrent sessions. For more information about these options and other load options, see the Oracle product documentation.

      The word OPTIONS and the parentheses must be included in the value that is specified for the property. The connector saves this property value to the control file that is generated and does not check the syntax of the value.