Optim™ supports the
Oracle loader. Use the DB Alias tab to define
the specific parameters needed to perform the Load Process.
Load to Oracle using Named PipeIn a Windows or
Unix environment, load performance may be significantly improved by
using a Named Pipe. When this option is used, the load process runs
in a single phase rather than a 2-stage process, resulting in reduced
elapsed time for load processing. As the row data is immediately loaded
after conversion to a loader format, the space requirements are also
significantly reduced, when compared to the 2-stage load process.
Optim creates the Named Pipe and writes the Archive or Extract File
data to the Named Pipe. Oracle loader utility reads the data from
the Named Pipe and then submits the data to the loader.
Mode
- Insert
- Inserts rows from the Source File into empty destination
tables. If destination tables contain data, the loader returns an
error.
- Replace
- Clears and replaces all of the existing rows in
the destination tables with the rows from the Source File. (Replace might
be significantly more resource-intensive than Truncate since
no logging is performed.)
- Append
- Inserts the rows from the Source File into the destination
tables. If the primary key values match, duplicate rows are discarded
or inserted into the exception table (if specified).
- Truncate
- Truncate is the same as Replace but
the database does not log the rows being deleted, and Truncate requires
that RI constraints are disabled.
Disable Triggers
- Never
- Select
this option if you do not want to disable database triggers for the
loader.
- Always
- Select this option to disable database triggers
during the Load Process and then re-enable the triggers after the
process completes.
- Prompt
- Select this option to display the Disabling
Trigger/Constraint Confirmation dialog. This dialog displays
a list of tables with all associated triggers. You can right-click
to enable or disable triggers during the Load Process for each table.
You can also select whether to enable or disable the triggers after
the Load Process completes.
Load Method
- Conventional Path
- Select
this option when you want to:
- Load a small number of rows into a large table that
has indexes or referential integrity constraints.
- Apply SQL functions to specific data.
- Direct Path
- Select this option when you want to load and index
a large volume of data quickly. The following options are available:
- Parallel Loads
- Select this option to allows multiple load
jobs to execute concurrently. This option is available only if you
select Direct Path and Append mode.
- UnRecoverable Load
- This option disables the writing of the data to the Oracle redo
logs. This option is available for Direct Path loads only.
Note: Direct path load runs faster than the conventional
path, especially when you select the option for Parallel Loads. To
use the direct path, the client and the server must be running on
the same platform. For complete details on which method to use, refer
to the documentation provided by Oracle.
Disable Constraints
- Never
- Select
this option when you do not want to disable referential integrity
constraints for the loader. A Constraint Non-Disablement
Warning message will display when you run the loader.
- Always
- Select this option to disable referential integrity
constraints during the Load Process and then re‑enable the constraints
after the process completes.
- Prompt
- Select this option to display the Disabling
Trigger/Constraint Confirmation dialog. This dialog displays
a list of tables with all associated constraints. You can right-click
to enable or disable constraints during the Load Process for each
table. You can also select whether to enable or disable the constraints
after the Load Process completes.
Note: Options to disable triggers and constraints apply
only when you select the Perform Load option.
The Disabling Trigger/Constraint Confirmation dialog
displays the list of tables in the Load Process and tabs with the
corresponding database triggers and referential integrity constraints
for each table.
The first grid column contains
a Focus Arrow to indicate the table for which triggers and constraints
are listed. To display triggers and constraints for a different table,
click a Focus Arrow grid cell to reposition the arrow, or use the
up/down arrows on your keyboard.
Right-click in the Status During Process column
to select to enable or disable the corresponding trigger or constraint
during the Load Process.
Right-click in the Status
After Process column to select whether to enable or disable
the corresponding trigger or constraint after the Load Process completes.
Options
- Perform Load
- Select this check box to run the loader automatically
after file conversion processing is complete. If you clear this check
box, the Load Process prepares the data in the appropriate format
and creates the BAT file to run the loader, but does not initiate
the loader. To run the loader, edit the BAT file to include the proper
password information and then run the BAT file.
- Use Named Pipe
- Select this check box to use a Named Pipe to load the data. The
data to be loaded is written to the Named Pipe and then submitted
to the loader. This option is available only when Perform
Load is selected. If you select Use Named
Pipe, the options Delete files if Successful and Delete
files if Failure are not available.
- Delete files if Successful
- Select this check box to delete the data files after
the loader completes successfully. If you select this check box, Fast
Load Confirmation is unavailable.
Note: Fast Load Confirmation reduces
processing time when you run a Load Request that has run before. See
Process a Load Request for detailed information.
- Delete files if Failure
- Select this check box to delete the data files if
the loader does not complete successfully. If you select this check
box, Fast Load Confirmation is unavailable.
- Create Exception Tables
- Select
this check box to create exception tables. An exception table contains
copies of rows that violate unique index or primary key rules. Optim ensures that the names of
exception tables do not match names of destination tables specified
in the Load Request. You can modify the exception table names, however,
use care to ensure that the names do not match names of existing database
tables. If the exception table name is left blank, duplicate rows
are discarded.
Click to display
the Exception Table Mapping dialog.
- Inline LOBs
- Select this check box to include LOBs in a data
file (inline with the table data). If this option is not selected,
each LOB is loaded from a separate file that is referenced in the
Oracle loader control file.
- Create Discard File
- Select this check box to instruct the
loader to create a discard file to use during the Load Process.
- Discard Limit
- Enter the number of rows up to 999999999, that the
loader can discard during the Load Process. Processing stops when
the specified number of rows is discarded.
- To end the process if a single row is discarded,
specify 1 as the maximum.
- To set no limit to the number of rows that can be
discarded, specify zero (0) or leave blank.
- Compressed Files
- Select
this check box to create variable length data rows instead of fixed
length data rows. This option can potentially reduce space required
for data conversion, but may slightly increase processing time.
- Delimiter
- Select a column delimiter from the drop-down list.
To avoid a conversion error, do not use a column delimiter that appears
in any of the data being loaded.
- Commit
- Specify
the number of rows after which the loader commits changes to the database,
up to the limit specified in Product Options. Refer to the Installation
and Configuration Guide section on Product Options.
- Load When Source Is Empty
- Select this check box to perform the Load if the source file tables
are empty. If you do not select this check box, the loader will not
be called for any empty source table.
To create a clean test environment:
- For an existing target table, use
- Load When Source Is Empty
- an empty source table
- Replace mode
- For a newly created table, use
- Load When Source Is Empty
- an empty source table,
- Insert mode
Additional Loader Parameters
This
field is provided to allow you to augment the loader arguments created
automatically by Optim with
additional loader parameters, if necessary. The additional parameters
you create append to the list created by Optim, but are not validated prior to starting
the loader. Refer to Oracle documentation for valid operands. If additional
loader parameters are forced from within Product Options,
this field cannot be modified. (See the Installation and Configuration
Guide.)
Work Path
Specify
a default directory path for storing the temporary loader files.