Load Requests

This syntax is used for each exported Load Request definition:

CREATE LOAD identifier.name
   [DESC //description//]
   XF xfilname CF cfilname {LOCALTM (tmdef) | TM tmname}
   FORCEEDITTM {Y|N} STOPONERROR {Y|N}
   STOPONFIRSTCONVERTERROR {Y|N} MODE {S|P}
   ALWAYSCALLCREATE {Y|N} SHOWCURRENCY {Y|N}
   SHOWAGE {Y|N} PROCESS_FILEATTACH {F|C}
   [DB2CS ( TYPE {I|R} LOAD {Y|N} DELOK {Y|N}
      DELFAIL {Y|N} [FILETYPE={A|D|X|I|S|L}]
      EXCPFAIL {Y|N} EXCPCNST {Y|N} [EXCPCIID cid]
      [LOCAL path | REMOTE path]
      COPY {I IMAGE imagepath | A SESS numsession | N}
      [STATS {S|O|I|D}] [COMMITFREQ n]
      [DISCARDLIMIT n] )]
   [ORACLE (TYPE{I|R|A|T} LOAD{Y|N}
      DELOK{Y|N} DELFAIL{Y|N}
      DSCFILE {Y|N} [USEDIRECTPATH {Y|N} ]
      STARTVIO {Y|N} DISCARDLIMIT n
  [LOCAL path | REMOTE path]
      [COMMITFREQ n] TRIGMODE {A|P|N}
       CONSMODE {A|P|N} )]
   [SYBASE ( TYPE {I|R} LOAD {Y|N} DELOK {Y|N}
      DELFAIL {Y|N}
      DSCFILE {Y|N} DISCARDLIMIT n
      [LOCAL path | REMOTE path]
      TRIGMODE {A|P|N} CONSMODE {A|P|N} )]
      [PARTS (table_partition_mapping)]
   [SQLSERVER (TYPE{I|R} LOAD{Y|N} DELOK{Y|N}
      DELFAIL{Y|N}
      DSCFILE{Y|N}[DISCARDLIMIT n]
      [LOCAL path | REMOTE path] TRIGMODE {A|P|N}
      CONSMODE {A|P|N} WINAUTH {Y|N} )]
   [INFORMIX (TYPE{I|R} LOAD{Y|N} DELOK{Y|N}
      DELFAIL{Y|N}
      STARTVIO {Y|N} DELETEVIO {Y|N} DSCFILE {Y|N}
      [DISCARDLIMIT n]
      [LOCAL path | REMOTE path] TRIGMODE {A|P|N}
      CONSMODE {A|P|N} )]
   [FUNCTION_AGING (AGETYPE {NONE | INCREMENTAL |
      SPECIFICYEAR | SPECIFICDATE | TARGETDATES |
      RULEBASED} [YEARS nn] [MONTHS nn]
      [WEEKS nn] [DAYS nn] [SPECIFICYEAR nnnn]
      [SPECIFICDATE mm/dd/yyyy]
      [TARGETSTART mm/dd/yyyy TARGETEND mm/dd/yyyy]
      [MULTIPLE nn] [RULE rulename]
      [CALENDAR calendarname]
      [PIVOT nn] [INVALIDDATES {Y|N}]
      [SKIPPEDDATES {Y|N}])]
   [GLOBAL_AGING (AGETYPE {NONE | INCREMENTAL |
      SPECIFICYEAR | SPECIFICDATE | TARGETDATES |
      RULEBASED} [YEARS nn] [MONTHS nn]
      [WEEKS nn] [DAYS nn] [SPECIFICYEAR yyyy]
      [SPECIFICDATE mm/dd/yyyy]
      [TARGETSTART mm/dd/yyyy TARGETEND mm/dd/yyyy]
      [MULTIPLE nn] [RULE rulename]
      [CALENDAR calendarname]
      [PIVOT nn] [INVALIDDATES {Y|N}]
      [SKIPPEDDATES {Y|N}])];
   [REPORT_OPTION (RPTERROR {T|F} [MAXTBLERR nnn]
      [MAXRUNERR nnn] [RPTSUMMARY{T|F}]
      [RPTINVALID{T|F}]
      [RPTSKIPPED {T|F}])]
   [CURRENCY_OPTION (DEFAULT currencytablename
      [GLOBAL currencytablename] [FROM currencytype]
      [TO currencytype] [TRIANG {T|F}])]
   [EMAILNOTIFY ({A|S|F} emailaddress)];

Keywords

The keywords correspond to values that can be specified for a Load Request. For additional information, see the Move User Manual or Archive User Manual .

LOAD identifier.name
The name of the Load Request, specified in two parts (identifier.name) is required following the CREATE LOAD keyword.
DESC //description//
A description of the Load Request, delimited by double slashes.

Editor Options

XF xfilname
The fully qualified name of the Extract File or Archive File used as input for the Load Process.
CF cfilname
The fully qualified name of the Control File for the Load Process.

Table Map Keywords

A Table Map parameter must be included. The Table Map can be Local or Named. If the keyword is LOCALTM, the full definition of the Table Map follows in parentheses. If the keyword is TM, the name follows.

LOCALTM (tmdef)
A full definition of the Local Table Map being used, contained in parentheses, follows this keyword. The syntax and parameters for the definition of a Local Table Map are identical to those for a standalone Table Map.
TM tmname
The full two-part name of the Named Table Map being used for the Load Request.

General

FORCEEDITTM {Y|N}
(Always View Table Map) indicates whether the Table Map Editor opens automatically when processing a Load Request.
Y
The Table Map Editor opens automatically.
N
The Table Map Editor does not open automatically.
STOPONERROR {Y|N}
This keyword is only valid if MODE is set to In Sequence. Indicates whether processing continues in another DBMS loader after an
Y
Processing halts.
N
Processing continues.
STOPONFIRSTCONVERTERROR {Y|N}
This keyword is only valid if MODE is set to In Sequence. Indicates whether processing continues if an error occurs in the Convert Process.
Y
Processing halts.
N
Processing continues.
MODE {S|P}
Indicates the mode for processing in multiple databases.
S
In Sequence
P
In Parallel
ALWAYSCALLCREATE {Y|N}
Indicates whether the Create dialog displays each time.
Y
Create dialog displays.
N
Create dialog displays when needed.
SHOWCURRENCY {Y|N}
Indicates whether the Currency tab displays.
Y
Currency tab displays.
N
Currency tab does not display.
SHOWAGE {Y|N}
Indicates whether the Aging tabs display.
Y
Age Function and Global Aging tabs display.
N
Age Function and Global Aging tabs do not display.
PROCESS_FILEATTACH {F|C}
If the source file contains file attachment pseudocolumns, indicate how the Load Process should proceed.
F
Fail the process.
C
Process pseudocolumns as normal table columns. If matching columns do not exist in the table, the pseudocolumns are ignored.

DB Alias - DB2CS

DB2CS keywords correspond to values that can be specified for a Load Request using the DB Alias tab of the Load Request Editor.

TYPE {I|R}
Indicates the type of Load that is performed.
I
Insert
R
Replace
LOAD {Y|N}
Indicates whether the Load is processed, or only prepared for processing.
Y
The load is performed.
N
The load is not performed.
DELOK {Y|N}
Indicates whether the .sql, .ixf, and .msg files are deleted when the Load is successful.
Y
The files are deleted.
N
The files are not deleted.
DELFAIL {Y|N}
Indicates whether the .sql, .ixf, and .msg files are deleted when the Load is not successful.
Y
The files are deleted.
N
The files are not deleted.
FILETYPE={A|D|X|I|S|L}
The file type for the file to be loaded:
A
ASCII. For DB2 Linux, UNIX or Windows, use to import data into other DBRM instances or EEE sites.
D
ASCII Delimited. For DB2 Linux, UNIX or Windows, use to import data into other DBRM instances or EEE sites. If you select this file type, select a valid delimiter.
X
iSeries® IXF. For iSeries, use to import data into an iSeries instance.
I
IXF. For DB2 Linux, UNIX or Windows, use as the preferred import file type for expedient processing.
S
Teradata ASCII. For a Teradata loader, use to import data into a Teradata instance.
L
Teradata Delimited. For a Teradata loader, use to import data into a Teradata instance.
EXCPFAIL {Y|N}
Indicates whether rows that violate unique index or primary key rules are stored in the exception tables.
Y
Rows in violation are stored in the exception tables.
N
Rows in violation are not stored in the exception tables.
EXCPCNST {Y|N}
Indicates whether rows that violate referential integrity or table check constraints are stored in the exception tables.
Y
Rows in violation are stored in the exception tables.
N
Rows in violation are not stored in the exception tables.
EXCPCIID cid
The Creator ID for creating the exception tables.
LOCAL path
Specifies the workstation path for temporary loader files.
REMOTE path
Specifies the server path for temporary loader files.
COMMITFREQ n
Indicates the frequency (every nth row) at which commits are issued.
DISCARDLIMIT n
Indicates the maximum number of rows (n) that can be discarded.

Copy Keywords

These keywords reflect values selected on the Select Copy Option dialog.

COPY {I|A|N}
Indicates which Copy option is selected:
I
Copy image to a directory (IMAGE must follow)
A
Copy image using ADSM (SESS must follow)
N
Do not copy
IMAGE imagepath
(Path Name) indicates the path and directory (imagepath) for the image copy.
SESS numsession
(I/O Sessions) indicates the number of I/O sessions (numsession) to be used with ADSM.

Statistics Keywords

These keywords reflect values selected on the Select Statistics dialog.

STATS {S|O|I|D}
Indicates which Select Statistics options are selected:
S
Table and distribution is selected.
O
Indexes only is selected.
I
Table and Indexes is selected.
D
Extended stats for Indexes is selected

DB Alias - ORACLE

Oracle keywords correspond to values that can be specified for a Load Request using the DB Alias tab of the Load Request Editor.

TYPE {I|R|A|T}
Indicates the type of Load that is performed.
I
Insert
R
Replace
A
Append
T
Truncate
LOAD {Y|N}
Indicates whether the Load is processed, or only prepared for processing.
Y
The load is performed.
N
The load is not performed.
DELOK {Y|N}
Indicates whether the .sql, .ixf, and .msg files are deleted when the Load is successful.
Y
The files are deleted.
N
The files are not deleted.
DELFAIL {Y|N}
Indicates whether the .sql, .ixf, and .msg files are deleted when the Load is not successful.
Y
The files are deleted.
N
The files are not deleted.
DSCFILE {Y|N}
Indicates whether discarded rows are written to a Discard File.
Y
The rows are written.
N
The rows are not written.
USEDIRECTPATH {Y|N}
Indicates the path as either Direct or Conventional.
Y
The path is direct. Select this option when you want to load and index a large volume of data quickly.
N
The path is conventional.
STARTVIO {Y|N}
Indicates whether violation tables are created.
Y
The violation tables are created.
N
The violation tables are not created.
DISCARDLIMIT n
Indicates the maximum number of rows (n) that can be discarded.
LOCAL path
Specifies the workstation path for temporary loader files.
REMOTE path
Specifies the server path for temporary loader files.
COMMITFREQ n
Indicates the frequency (every nth row) at which commits are issued.
TRIGMODE {A|P|N}
Indicates whether triggers are disabled.
A
Always
P
Prompt for specific triggers
N
Never
CONSMODE {A|P|N}
Indicates whether constraints are disabled.
A
Always
P
Prompt for specific constraints
N
Never

DB Alias - SYBASE

Sybase ASE keywords correspond to values that can be specified for a Load Request using the DB Alias tab of the Load Request Editor.

TYPE {I|R}
Indicates the type of Load that is performed.
I
Insert
R
Replace
LOAD {Y|N}
Indicates whether the Load is processed, or only prepared for processing.
Y
The load is performed.
N
The load is not performed.
DELOK {Y|N}
Indicates whether the .sql, .ixf, and .msg files are deleted when the Load is successful.
Y
The files are deleted.
N
The files are not deleted.
DELFAIL {Y|N}
Indicates whether the .sql, .ixf, and .msg files are deleted when the Load is not successful.
Y
The files are deleted.
N
The files are not deleted.
DSCFILE {Y|N}
Indicates whether discarded rows are written to a Discard File.
Y
The rows are written.
N
The rows are not written.
DISCARDLIMIT n
Indicates the maximum number of rows (n) that can be discarded.
LOCAL path
Specifies the workstation path for temporary loader files.
REMOTE path
Specifies the server path for temporary loader files.
TRIGMODE {A|P|N}
Indicates whether triggers are disabled.
A
Always
P
Prompt for specific triggers
N
Never
CONSMODE {A|P|N}
Indicates whether constraints are disabled.
A
Always
P
Prompt for specific constraints
N
Never
PARTS identifier.tablename = partition name
If you want to store extracted data in a specific partition, you must pass the appropriate partition name when you run the Load process. You can specify one partition name only for each table, such as: qadba.my_publishers = west. In this example, qadba.my_publishers identifies the destination table within the table map, and west is the Sybase partition name for that table.

DB Alias – SQL Server

SQL Server keywords correspond to values that can be specified for a Load Request using the DB Alias tab of the Load Request Editor.

TYPE {I|R}
Indicates the type of Load that is performed.
I
Insert
R
Replace
LOAD {Y|N}
Indicates whether the Load is processed, or only prepared for processing.
Y
The load is performed.
N
The load is not performed.
DELOK {Y|N}
Indicates whether the .sql, .ixf, and .msg files are deleted when the Load is successful.
Y
The files are deleted.
N
The files are not deleted.
DELFAIL {Y|N}
Indicates whether the .sql, .ixf, and .msg files are deleted when the Load is not successful.
Y
The files are deleted.
N
The files are not deleted.
DSCFILE {Y|N}
Indicates whether discarded rows are written to a Discard File.
Y
The rows are written.
N
The rows are not written.
DISCARDLIMIT n
Indicates the maximum number of rows (n) that can be discarded.
LOCAL path
Specifies the workstation path for temporary loader files.
REMOTE path
Specifies the server path for temporary loader files.
TRIGMODE {A|P|N}
Indicates whether triggers are disabled.
A
Always
P
Prompt for specific triggers
N
Never
CONSMODE {A|P|N}
Indicates whether constraints are disabled.
A
Always
P
Prompt for specific constraints
N
Never
WINAUTH {Y|N}
Indicates whether NT authentication is used.
Y
NT authentication is used.
N
NT authentication is not used.

DB Alias - Informix

Informix® keywords correspond to values that can be specified for a Load Request using the DB Alias tab of the Load Request Editor.

TYPE {I|R}
Indicates the type of Load that is performed.
I
Insert
R
Replace
LOAD {Y|N}
Indicates whether the Load is processed, or only prepared for processing.
Y
The load is performed.
N
The load is not performed.
DELOK {Y|N}
Indicates whether the .sql, .ixf, and .msg files are deleted when the Load is successful.
Y
The files are deleted.
N
The files are not deleted.
DELFAIL {Y|N}
Indicates whether the .sql, .ixf, and .msg files are deleted when the Load is not successful.
Y
The files are deleted.
N
The files are not deleted.
STARTVIO {Y|N}
Indicates whether violation tables are created.
Y
The violation tables are created.
N
The violation tables are not created.
DELETEVIO {Y|N}
Indicates whether to delete all rows in existing violation tables before the Load begins.
Y
Delete rows from violation tables.
N
Do not delete rows from violation tables.
DSCFILE {Y|N}
Indicates whether discarded rows are written to a Discard File.
Y
The rows are written.
N
The rows are not written.
DISCARDLIMIT n
Indicates the maximum number of rows (n) that can be discarded.
LOCAL path
Specifies the workstation path for temporary loader files.
REMOTE path
Specifies the server path for temporary loader files.
TRIGMODE {A|P|N}
Indicates whether triggers are disabled.
A
Always
P
Prompt for specific triggers
N
Never
CONSMODE {A|P|N}
Indicates whether constraints are disabled.
A
Always
P
Prompt for specific constraints
N
Never

Age Function

If options for function aging are specified, they follow the FUNCTION_AGING keyword in parentheses.

FUNCTION_AGING
Indicates function date aging is used for the Load.
AGETYPE type
The function type specified as one of the following:
NONE SPECIFICDATE
INCREMENTAL TARGETDATES
SPECIFICYEAR RULEBASED
YEARS nn
The positive or negative numeric value (nn) to age the date by years.
MONTHS nn
The positive or negative numeric value (nn) to age the date by months.
WEEKS nn
The positive or negative numeric value (nn) to age the date by weeks.
DAYS nn
The positive or negative numeric value (nn) to age the date by days.
SPECIFICYEAR yyyy
An explicit year (yyyy) to which Load should age.
SPECIFICDATE mm/dd/yyyy
The date (mm/dd/yyyy) to which Load should age.
TARGETSTART mm/dd/yyyy
The date (mm/dd/yyyy) Load uses as the starting date when calculating the amount to age using relative aging.
TARGETEND mm/dd/yyyy
The date (mm/dd/yyyy) Load should use as the target date when calculating the amount to age using relative aging.
MULTIPLE nn
The number of times (nn) to apply a rule to determine the aging amount.
RULE rulename
The name of the rule (rulename) being used.
CALENDAR calendarname
The name of the calendar (calendarname) being used.
PIVOT nn
The pivot year (nn).
INVALIDDATES {Y|N}
Indicates whether rows containing dates that are invalid are used.
SKIPPEDDATES {Y|N}
Indicates whether rows containing dates that should be skipped are used.

Global Aging

If options for function aging are specified, they follow the GLOBAL_AGING keyword in parentheses.

GLOBAL_AGING
Indicates global date aging is used for the Load.
AGETYPE type
The function type specified as one of the following:
NONE SPECIFICDATE
INCREMENTAL TARGETDATES
SPECIFICYEAR RULEBASED
YEARS nn
The positive or negative numeric value (nn) to age the date by years.
MONTHS nn
The positive or negative numeric value (nn) to age the date by months.
WEEKS nn
The positive or negative numeric value (nn) to age the date by weeks.
DAYS nn
The positive or negative numeric value (nn) to age the date by days.
SPECIFICYEAR yyyy
An explicit year (yyyy) to which Load should age.
SPECIFICDATE mm/dd/yyyy
The date (mm/dd/yyyy) to which Load should age.
TARGETSTART mm/dd/yyyy
The date (mm/dd/yyyy) Load should use as the starting date when calculating the amount to age using relative aging.
TARGETEND mm/dd/yyyy
The date (mm/dd/yyyy) Load should use as the target date when calculating the amount to age using relative aging.
MULTIPLE nn
The number of times (nn) to apply a rule to determine the aging amount.
RULE rulename
The name of the rule (rulename) being used.
CALENDAR calendarname
The name of the calendar (calendarname) being used.
PIVOT nn
The pivot year (nn).
INVALIDDATES {Y|N}
Indicates whether rows containing dates that are invalid are processed.
SKIPPEDDATES {Y|N}
Indicates whether rows containing dates that should be skipped are processed.

Report Options

If Report options are specified, they follow the REPORT_OPTION keyword in parentheses.

REPORT_OPTION
Indicates reporting options are used in the Load.
RPTERROR {T|F}
Indicates whether to report errors (True or False).
MAXTBLERR nnn
The maximum number (nnn) of errors per table to report.
MAXRUNERR nnn
The maximum number (nnn) of errors per run to report.
RPTSUMMARY {T|F}
Indicates whether to report the Aging summary (True or False).
RPTINVALID {T|F}
Indicates whether to report invalid dates (True or False).
RPTSKIPPED {T|F}
Indicates whether to report skipped dates (True or False).

Currency

If options for currency conversion are specified, they follow the CURRENCY_OPTION keyword in parentheses.

CURRENCY_OPTION
Indicates options for currency conversion are used for the Load.
DEFAULT currencytablename
The name of the default Currency Table (currencytablename) being used.
GLOBAL currencytablename
The name of the global Currency Table (currencytablename) being used.
FROM currencytype
The three character code for the source currency type (currencytype).
TO currencytype
The three character code for the target currency type (currencytype).
TRIANG {T|F}
Indicates whether to convert currency via the euro dollar (True or False).

Notify

This parameter relates to the ability to send notification via email about the outcome of the process.

EMAILNOTIFY {A|S|F} emailaddress
Indicates the process outcome under which to send notification to the corresponding email address.
A
Always send notification.
S
Send notification when process succeeds.
F
Send notification when process fails.