DB Alias Tab — Oracle

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 Pipe

In 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.

DB Alias tab for Oracle selected on the Load Request Editor

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.
Constraint Non-Disablement Warning
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 Tools > Edit Exception Table Map to display the Exception Table Mapping dialog.

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.