DB Alias Tab — SQL Server

Optim™ supports the SQL Server loader. Use the DB Alias tab to define the specific parameters needed to perform the Load Process.

Note: SQL Server forces dates to be in a particular format. Therefore, in order to run the request, the Client language must be the same as the language assigned to the SQL Server User ID.
DB Alias tab for SQL Server selected on the Load Request Editor

Mode

Insert
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 error file (if specified).
Replace
Clears all of the existing rows in the destination tables and replaces with the rows from the Source File.

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.

Note: Since SQL Server does not accept a Replace operand for a load request, you must ensure the tables are empty before you run the BAT file.
Delete files if Successful
Select this check box to delete the data files after the loader completes successfully. If you select this check box, the Fast Load Confirmation feature 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, the Fast Load Confirmation feature is unavailable.
Load when source is Empty
Select this check box to perform the Load if the source file tables are empty. For example, if you need to clear database tables of existing data rows, use empty tables for the Load and select the option Load when source is Empty. If you do not select this check box, any empty tables in the source file will not be loaded.
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

Disable Triggers

Note: Options for disabling database triggers are applicable to SQL Server Version 7.0 or later.
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.

Error File Options

Create
Select this check box to instruct the loader to create a discard file to use during the Load Process.
Max Errors
Enter the number of rows up to 999999999, that the loader can discard because of errors during the Load Process. The process 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.

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 by Optim prior to starting the loader. Refer to SQL Server 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.)

Use NT Authentication

Select this check box to instruct Optim to supply the -T parameter to instruct the loader to use the User ID and Password used to logon to the network. Clear the check box to use a -U and -P parameter (UserID and Password) when starting the loader.

Disable Constraints

Note: Options for disabling referential integrity constraints are applicable to SQL Server Version 7.0 or later.
Never
Select this option if 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 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 that contain 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 the corresponding 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.

Disabling Trigger/Constraint Confirmation dialog

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.

Work path for interim files

Specify a default directory path for storing the temporary loader files.