Working with insert services

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

Use table maps and column maps to map data from the source to the target. You must use a table map to specify the destination for the tables in the source file. You may also use column maps for one or more destination tables. Column maps enable you to specify the source data for each column and, optionally, to transform the data before it is inserted.

The following insert 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.

If tables in the source file do not exist at the target, use the Create utility to create them.

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.



Feedback