INSERT

All options that may be specified using the online INSERT panel are also available using the batch INSERT processing statement. For an extract file on tape, you must use batch execution for an insert process.

INSERT
  EXTRACT_FILE ( { USEDD | explicitfilename  } )
  CONTROL_FILE ( File Allocation Parameters )
  { TABLE_MAP_DEFINE (parameters) ; | TABLE_MAP mapid.name
     [ PROCESS_MODE {INS | UPD | BOTH} ]
     [ DELETE_BEFORE_INSERT {TABLE | END} ]
     [ TMDEFCID defcreatorid ] }
  [ LOCK_TABLES {YES | NO} ]
  [ COMMIT_COUNT n ]
  [ COMMIT_MINUTES n ]
  [ DISCARD_COUNT n ]
  [ UNKNOWN {ALLOW | FAIL} ]
  [ AGING (parameters) ]
  [ REPORT_LEVEL {DETAIL | SUMMARY} ]
  [ RESTART {YES | NO} ]
  [ EMPTY_TABLE_FOUND_RC4 { YES | NO } ]

EXTRACT_FILE

Name of the Extract File to be used in the INSERT process.

USEDD

Use the file name specified in the PSDFEXTR control card statement.

explicitfilename

The fully qualified name of the Extract File.

CONTROL_FILE

Control File to be used in the INSERT process. Use the keywords shown in File Allocation Parameters to name and allocate the Control File.

TABLE_MAP

The name of the Table Map to be used. TABLE_MAP is required.

mapid.name

The fully qualified name of the Table Map.

TABLE_MAP_DEFINE

The Optim™ online process generates this keyword when it creates an INSERT job for batch execution.

When you create an INSERT job outside of the Optim online process, the best practice is to use the TABLE_MAP keyword to refer to a named Table Map in the Optim Directory.

Place the TABLE_MAP_DEFINE parameters within parentheses. A semicolon must follow the close parenthesis. (See TABLE_MAP_DEFINE Parameters for the allowable parameters.)

PROCESS_MODE

The process mode for any tables for which process mode is not specified in the Table Map.

INS

Data rows are inserted.

UPD

Data rows are updated.

BOTH

Data rows are both inserted and updated (default).

DELETE_BEFORE_INSERT

Delete Before Insert option for tables for which delete before insert is not required by the Table Map. This keyword applies only to tables processed with Insert Mode. This keyword is valid only if the Site Option Target Rows Del Before Isrt is Y or I.

TABLE

Delete rows and commit after each table.

END

Delete rows and commit at end.

LOCK_TABLES

Indicate whether to lock tables during processing. This keyword is relevant only if site options allow a user to lock tables.

YES

Lock tables.

NO

Do not lock tables (default).

COMMIT_COUNT

Number of rows processed between commit operations. If this keyword is omitted and the COMMIT_MINUTES keyword is omitted, the site limit is used.

n

Specify a value from 1 to the site limit.

COMMIT_MINUTES

The number of minutes between commit operations. This keyword overrides a value specified with COMMIT_COUNT.

n

Specify a number from 1 to 1440.

DISCARD_COUNT

The maximum number of discarded rows allowed. If this limit is met, the process is terminated. If you omit this keyword, there is no limit.

n

Specify a value from 1 to 4,294,967,295.

UNKNOWN

Processing for tables referenced in the Table Map that are unknown.

ALLOW

Bypass table.

FAIL

Terminate the INSERT (default).

TMDEFCID

Override for the default Creator ID specified in the Table Map.

defcreatorid

The Creator ID to be used.

AGING

This keyword indicates that date values in the source columns are to be aged. It provides parameters to be used in the aging process. See AGING Keyword Parameters for detailed information on using this keyword.

REPORT_LEVEL

The level of detail in the process report.

DETAIL

Produce a detailed report (default).

SUMMARY

Produce a summary report.

RESTART

Indicate whether this is a restart or retry attempt for an Insert process that failed earlier. Optim automatically determines whether to perform a restart or a retry.

YES

Restart or retry processing.

NO

Do not attempt to restart or retry processing (default).

EMPTY_TABLE_FOUND_RC4

Indicate the action taken and return code (RC) setting when empty tables have been detected during Insert batch processing.

YES

Empty tables will be processed and a warning message will be issued with RC=4.

NO

Empty tables will cause termination with an error and RC=12 (default).

Example

The following is an example of INSERT batch statement usage.

To insert the contents of the Extract File PSTUSER.DISC, use INSERT ONLY processing, and delete all rows from the tables before inserting the new rows, specify:

INSERT EXTRACT_FILE PSTUSER.DISC
   CONTROL_FILE  (DSNAME PSTUSER.INSCTRL)
   TABLE_MAP  PSTUSER.MAP9
   PROCESS_MODE  INS
   DELETE_BEFORE_INSERT  TABLE