Using the insert service with Optim Designer

Use an insert service to insert data stored in a file data store into a destination database.

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 file data store, you can clone the original database, if needed.

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.

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.

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

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 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 file data store. The report contains general information and statistics about the insert process. The report contains details of what has been inserted – data, object definitions, or both – and what has been discarded.