Using the insert process with Optim Designer

Use an insert request in an Optim™ interoperability service that will insert data. You can also export the request to an Optim directory.

The insert process copies data from a source file into specified destination tables. Initiate the insert process with an insert request in an Optim interoperability service, which specifies a source file containing the data you want to insert or update and the parameters needed to run the process. The destination can be any database located on any server in your network, including the source database. Because the object definitions are included in the extract file, you can clone the original database, if needed.

Insert Request

Use the New Insert Service wizard to define parameters in an insert request. The wizard allows you to select parameters for processing the insert request, including the following:

If destination tables do not exist, Optim can generate the SQL to create the tables from the object definitions contained in the extract file─either as part of the Insert or as a separate step. In addition to tables, other object definitions can be created. The following process options are available:

Insert
If the primary key value is unique to the destination table, the new row is added to the destination table. If the primary key value is not unique to the destination table (the row already exists), the row is discarded. Optim uses a control file to keep track of discarded rows so that you can review them and possibly reprocess them later.
Update Only
If the primary key of a row in the source data matches the primary key of a row in the destination table, the row is updated. If the primary key of a row in the source data does not match the primary key of a row in the destination table, the row is reported as failed.
Update/Insert
If the primary key value is unique to the destination table, the new row is added to the destination table. If the primary key value is not unique to the destination table (the row already exists), the row in the extract file replaces or updates the existing row.
Mixed
Optim also allows a mix of insertion methods where some tables are inserted and others are updated.

You can choose to run the insert request immediately or schedule the request for later processing.

Control File

A control file is generated by the insert process to record details about the success or failure of processing each row in the extract file. You must specify the name of a control file when you create a request to insert data.

The control file also makes it easy to distinguish among different process requests that use the same extract file. If a path is not specified, control files are stored in the data directory specified in personal options. For details on specifying the data directory and other personal options, see the Common Elements Manual.

You can browse the control file to identify problems with a process, such as rows in the extract file that failed to process or the cause of an unexpected termination. Diagnostics accompany the discarded rows to identify why they were not processed. For details on the Browse Utility, see the Common Elements Manual.

Table Maps

Table maps match source tables to destination tables. Individual tables can be excluded, and tables with different names can be mapped. You can use an existing table map or define the table map along with the other specifications for the insert process.

When the columns in the destination table match the columns in the source table, Optim automatically inserts the data. When the columns do not match, the unmapped data is not inserted unless column maps are specified.

Column Maps

Column maps are used to match source columns to destination columns that have different column names, eliminate columns from the process, and most importantly, allow the specification of values to be used to populate the destination columns.

Values that can be used to populate a destination column include special registers, the NULL value, literals, constants, expressions, and exit routines.

Insert Process Report

An insert process report is generated as part of the insert process. The report contains general information and statistics about the process. The report contains details of what has been inserted─data, object definitions, or both─and what has been discarded.