Load Requests

Keywords

The keywords correspond to values that can be specified for a load request.

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.

IDENTITYOV {Y|N} (DB2 for Linux, UNIX, and Windows only)

Action to be taken when a table to be loaded with user-supplied values has an identity column defined as GENERATED ALWAYS. It instructs the load utility to accept non-NULL data for such a column (contrary to the normal rules for these types of identity columns). When this option is used, any rows with no data or NULL data for the identity column will be skipped. Specify:

Y

Perform the load.

N

Do not perform the load.

FILETYPE={A|D|X|I|S|L}

The file type for the file to be loaded:

A

ASCII. For DB2® for Linux®, UNIX, and Windows, use to import data into other DBRM instances or EEE sites.

D

ASCII Delimited. For DB2 for Linux, UNIX, and Windows, use to import data into other DBRM instances or EEE sites. If you select this file type, select a valid delimiter.

X

IXF for DB2 for i. Use to import data into a DB2 for i instance.

I

IXF for DB2 for Linux, UNIX, and 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

DB Alias - Hadoop

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

dbaliasname

Specify the DB Alias for Hive.

LOCAL path

Specifies the workstation path for temporary loader files.

HADOOPURL url

Identifies the http URL of the Hadoop cluster's name node, such as http://somehost:50070.

HADOOPUSER userid

Identifies the name of the Hadoop user.

HADOOPPASS password

Identifies the password for the Hadoop user.

COMMITFREQ n

Identifies the frequency at which you want rows loaded into a CSV file and sent to the Hadoop server to load into Hadoop. For example, if you specify a row count of 1000, each time a thousand rows are loaded into a CSV file, the file is sent to the Hadoop server to load into Hadoop. This process is repeated, in 1000 row increments, until all required rows are processed.

For best performance, specify a commit frequency of 1000 - 1000000 (from one thousand to one million). You can specify a frequency of less than 1000 or more than 1000000, but doing so will adversely affect performance.

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
• INCREMENTAL
• SPECIFICYEAR
• SPECIFICDATE
• TARGETDATES
• 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
• INCREMENTAL
• SPECIFICYEAR
• SPECIFICDATE
• TARGETDATES
• 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.