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
- Configure the connector to disable constraints before it
bulk loads data and enable constraints after it bulk loads data:
- Set Perform operations before bulk load to Yes.
- Set Disable constraints to Yes.
- Set Perform operations after bulk load to Yes.
- Set Enable constraints to Yes.
- 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.
- 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.
- Configure the connector to disable triggers before it bulk
loads data and enable triggers after it bulk loads data:
- Set Perform operations before bulk load to Yes.
- Set Disable triggers to Yes.
- Set Perform operations after bulk load to Yes.
- Set Enable triggers to Yes.
- To control how to handle table indexes during a bulk load,
set the Index maintenance option property.
- To rebuild indexes after a bulk load:
- Set Perform operations after bulk load to Yes.
- Set Rebuild indexes to Yes.
- 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.
- 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.
- If you plan to bulk load data into tables that contain
DATE or TIMESTAMP columns, enable and configure the date cache:
- Set Use Oracle date cache to Yes.
- Optional: In the Cache size property,
enter the maximum number of entries that the cache stores.
The
default is 1,000.
- 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.
- Set options to control bulk record loading:
- Set Array size to a value 1 -
999,999,999.
The default is 2,000.
- Set Buffer size to a value 4
- 100,240, which represents the buffer size in KB.
The
default is 1,024.
- Set the Allow concurrent load sessions property
depending on your requirement.
- To enable manual mode:
- Set Manual mode to Yes.
- Optional: In the Directory for
data and control files property, specify a directory to
save the control and data files to.
- 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.
- 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.
- 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.