Syntax for Delete Task - MultiLoad

where:

AMPCHECK

The Teradata MultiLoad response to a down AMP condition:

• AMPCHECK NONE allows the Teradata MultiLoad job to start, restart, or continue as long as no more than one AMP is down in a cluster.
• AMPCHECK APPLY inhibits the Teradata MultiLoad job from entering or exiting the application phase when an AMP is down.
• AMPCHECK ALL pauses the Teradata MultiLoad job when an AMP is down.

If an AMPCHECK specification is not entered, the Teradata MultiLoad default is AMPCHECK APPLY.

All of the target tname1 tables in the Teradata MultiLoad job must be fallback tables for the job to start, restart, or continue with a down AMP. The job does not start or restart if any of the target tables are nonfallback.

CHECKPOINT rate

The interval between checkpoint operations during the acquisition phase of a Teradata MultiLoad import task, expressed as either:

• the number of rows read from the client system or sent to the database, or
• an amount of time in minutes.

If a CHECKPOINT rate of 60 or more is specified, a checkpoint operation occurs after each multiple of that number of records is processed.

If a CHECKPOINT rate of less than 60 is specified, a checkpoint operation occurs at the specified frequency, in minutes.

Specifying a CHECKPOINT rate of zero inhibits the checkpoint function. Teradata MultiLoad does not perform any checkpoint operations during the import task.

If the CHECKPOINT rate specification is not used, Teradata MultiLoad performs a checkpoint operation at the default rate – every 15 minutes.

Checkpoints are not set if an FDL-compatible INMOD routine with the FOR, FROM, or THRU options is used. If an FDL-compatible INMOD routine with the FOR, FROM, or THRU options and specify a CHECKPOINT rate other than zero is used, Teradata MultiLoad terminates and an error message appears.

DATAENCRYPTION value

Keyword that enables data encryption for the Teradata MultiLoad job; available on network platforms only The options for value are:

• ON = All sessions will be encrypted.
• OFF = Sessions will not be encrypted. This is the default.

This option will apply only to the BEGIN (DELETE) MLOAD request and the requests after the BEGIN (DELETE) MLOAD command.

Using this option overwrites the data encryption settings specified by both the run-time parameters and the mloadcfg.dat configuration file.

dbname

The database for the target tables, work tables, error tables, and uniqueness violation tables

Use the dbname specification to:

• Specify a qualified database for tname2, tname3, and tname4that is different from the database for the tname1 target table or view
• Create and drop tables without involving the production database.

DELETE

Specifies that a delete task is to be initiated by the following Teradata MultiLoad commands

ERRLIMIT errcount

This number is approximate because Teradata MultiLoad sends multiple rows of data at a time to the database. By the time Teradata Multi load processes the message indicating that the error limit has been exceeded, it may have loaded more records into the error table than the actual number specified in the error limit.

When used with the errpercent parameter, which is an approximation, errcount specifies the number of records that must be sent to the database before the errpercent parameter is applied.

The default is no limit.

Uniqueness violations do not count as rejected records.

For more information, see Error Recording.

ERRORTABLES

The path for error tables

errpercent

Approximate percentage, expressed as an integer, of the total number of records sent to the database so far, that may be rejected during the acquisition phase of the Teradata MultiLoad import task

EXIT name

The keyword phrase that calls a user-defined exit where name is the name of a user-supplied library with a member name of _dynamn.

The exit must be written in C, or in a language with a run-time environment that is compatible with C.

On some versions of UNIX operating systems, ./ prefix characters to the EXIT name specification may be required if the module is in the current directory.

IMPORT

Specifies that an import task is to be initiated by the following Teradata MultiLoad commands

MSG 'string'

A user-supplied string of up to 16 characters that Teradata MultiLoad logs to:

• The operator’s console for mainframe-attached z/OS client systems
• The system log for workstation-attached UNIX and Windows client systems

The string specification must be enclosed in single quote characters (').

NODROP

NODROP will tell MultiLoad not to drop error tables even if they are empty at the end of the job.

NOTIFY

The Teradata MultiLoad implementation of the notify user exit option:

• NOTIFY OFF suppresses the notify user exit option.
• NOTIFY LOW enables the notify user exit option for those events signified by “Yes” in the Low Notification Level column of the following table.
• NOTIFY MEDIUM enables the notify user exit option for the most significant events, as specified by “Yes” in the Medium Notification Level column of the following table.
• NOTIFY HIGH enables the notify user exit option for every Teradata MultiLoad event that involves an operational decision point, as specified by “Yes” in the High Notification Level column of the following table.

MultiLoad terminates with a fatal error if any of NOTIFY error conditions occur.

QUEUE option

The keyword phrase that specifies queue management options (option) on mainframe-attached z/OS client systems.

This option is available only on z/OS, and only for import tasks with a low notification specification.

This option invokes an ENQ when the BEGIN MLOAD command is processed, followed by a DEQ when the significant event occurs.

The option specification is one of the following:

RNAME

A parameter containing a quoted string of up to 255 characters.

The default is TDUSER.

SCOPE

A parameter that is one of the following:

• JOB—Specifies that the QUEUE is local to the job, including all of the job steps.
• SYSTEM—Specifies that the QUEUE is global to the computer running it.
• SYSTEMS—Specifies that the QUEUE is global to all computers in the complex.

The default is SYSTEMS.

NOBLOCK

A parameter specifying that if the ENQ blocks for any reason, it must return an error instead. This is a fatal error for the job.

The default, an implied BLOCK (there is no BLOCK keyword), means that the ENQ will wait for the QUEUE.

SESSIONS

Maximum number of Teradata MultiLoad sessions that are logged on when a LOGON command is entered and, optionally, the minimum number of sessions required to run the job:

• The max parameter specifies the number of sessions to log on.

  The max specification must be within the range of 1 to 32767, but if a SESSIONS max value is specified that is larger than the number of available AMPs, Teradata MultiLoad limits the sessions to one per working AMP.

  The default maximum, if the SESSIONS option is not used, is one session for each AMP.

• The min parameter specifies the minimum number of sessions required for the job to continue.

  The min specification must be greater than zero.

  The default minimum, if the SESSIONS option is not used or specifies a min value, is 1.

• The asterisk (*) parameter specifies the maximum and minimum number of sessions.

  Using the asterisk character as the max specification logs on for the maximum number of sessions, one for each AMP.

  Using the asterisk character as the min specification logs on for at least one session, but less than or equal to the max specification.

  Specifying SESSIONS * * has the same effect as not using the SESSIONS option at all.

On large to very large database system configurations, the default of one session per AMP may be inappropriate. For more information about how to configure sessions, see the table at the end of this section.

SLEEP minutes

The SLEEP runtime option in which minutes is the length of time that Teradata MultiLoad waits before retrying a logon operation.

The SLEEP specification must be greater than zero. If zero is specified, Teradata MultiLoad responds with an error message and uses the default value. The default value is 6 minutes.

MultiLoad uses the SLEEP specification in conjunction with TENACITY specification. If the amount of time specified with the SLEEP command exceeds that of the TENACITY command, then the sleep interval is reset and equated to the amount of time specified by the TENACITY command. For example, if the time specified with TENACITY command is 1 hour then the SLEEP time is reset to 60 minutes so that the SLEEP time does not exceed the TENACITY time.

The sleep interval specified by the SLEEP command is dynamically adjusted so that the total sleep time does not exceed the amount of time specified by the TENACITY command. For example, if the time specified with the SLEEP command is 35 minutes and the time specified with the TENACITY command is 1 hour then:

• Teradata FastExport sleeps for 35 minutes and then attempts to log on to the database.
• If the first attempt fails, then the SLEEP time is dynamically adjusted to 25 minutes (SLEEP time subtracted from TENACITY time), so that the total SLEEP time does not exceed the TENACITY time (60 minutes).

TABLEWAIT hours

The TABLEWAIT runtime option in which hours is the number of hours that Teradata MultiLoad continues trying to start Teradata MultiLoad when one of the target tables is being loaded by some other job (MultiLoad/FastLoad).

When the Teradata MultiLoad utility tries to start Teradata MultiLoad, and the database indicates that the target table is being loaded (Error 2574, Error 2652), the Teradata MultiLoad utility:

• Waits for 6 minutes, by default, or for the amount of time specified by the SLEEP runtime option.
• Then tries to start Teradata MultiLoad again.

Teradata MultiLoad repeats this process until it has either acquired the table or exceeded the TABLEWAIT (hours) time period.

TENACITY hours

The TENACITY run-time option in which hours is the number of hours that Teradata MultiLoad continues trying to log on when the maximum number of load jobs is already running on the database.

The default tenacity value is 4 hours.

For information on how the TENACITY command interacts with the SLEEP command, see the SLEEP minutes entry in this table.

If TENACITY is not specified, hours is 4. If TENACITY is specified without hours, an error message appears.

The database either allows Teradata MultiLoad to log on for all of the required data loading sessions, or rejects the logon because load capacity is currently completely consumed.

When Teradata MultiLoad tries to log on for a new task, and the database indicates that the maximum number of load sessions is already running, Teradata MultiLoad:

1. Waits for 6 minutes, by default, or for the amount of time specified by the SLEEP run-time option.
2. Tries again to log on to the database.

Teradata MultiLoad repeats this process until it has either logged on for the required number of sessions or exceeded the TENACITY hours time period.

TEXT 'string'

A user-supplied string of up to 80 characters that Teradata MultiLoad passes to the named exit routine

The string specification must be enclosed in single quote characters (').

tname1

• The target table or view for an import task
• The target table for a delete task

  (The target cannot be a view for a delete task.)

Specify:

• Up to five target tables or views for an import task
• One target table for a delete task

Each tname1 specification must identify an existing target table or view.

If the database is not specified, Teradata MultiLoad uses the default database to qualify the table or view name.

tname2

The work table for each tname1 table or view.

Work tables are special un-hashed tables that Teradata MultiLoad uses when executing both import and delete tasks.

Each tname2 specification must identify:

• A new (nonexisting) table for a non-restart task
• An existing table for a restart task

Otherwise, Teradata MultiLoad terminates the task with an error message.

If the WORKTABLES specification is not used, Teradata MultiLoad creates the tname2 work tables using a default name of WT_tname1 for each tname1 table or view which is specified.

Teradata MultiLoad automatically drops the work tables after completing the import or delete task.

In the case of a paused Teradata MultiLoad job, do not drop the work tables or error tables until Teradata MultiLoad completes the import or delete task.

For more information about dropping the work tables or error tables, see Implications of Dropping Required Teradata MultiLoad-Created Tables.

Do not share the work tables between two or more Teradata MultiLoad jobs. Do not share work tables between two or more target tables in a single Teradata MultiLoad job. Each table targeted by a Teradata MultiLoad job must have its own work table to ensure that the job runs correctly.

If a distinct work table for each table targeted by a Teradata MultiLoad job, the results are unexpected. able to restart one or more of the affected jobs.

tname3

The fallback error table for each tname1 table or view that receives information about errors detected during the acquisition phase of the Teradata MultiLoad import task. Do not share the acquisition error tables between two or more Teradata MultiLoad jobs.

Each tname3 specification must identify:

• A new (nonexisting) table for a non-restart task
• An existing table for a restart task

Otherwise, Teradata MultiLoad terminates the task with an error message.

If the ERRORTABLES specification is not used, Teradata MultiLoad creates the tname3 error tables using a default name of ET_tname1 for each target tname1 table or view which is specified.

In the case of a paused Teradata MultiLoad job, do not drop the error tables or the work tables until Teradata MultiLoad completes the import or delete task. For more information about dropping the work tables or error tables, see Implications of Dropping Required Teradata MultiLoad-Created Tables.

Do not share acquisition error tables between two or more target tables in a single Teradata MultiLoad job. Each table targeted by a Teradata MultiLoad job must have its own acquisition error table to ensure that the job runs correctly.

If a distinct acquisition error table for each table targeted by a Teradata MultiLoad job is not used, the results are unexpected. One or more of the affected jobs may not be able to restart.

tname4

The fallback error table for each tname1 table or view that receives information about errors detected during the application phase of the Teradata MultiLoad import or delete task.

Each tname4 specification must identify:

• A new (nonexisting) table for a non-restart task
• An existing table for a restart task

Otherwise, Teradata MultiLoad terminates the task with an error message.

If the ERRORTABLES specification is not used, Teradata MultiLoad creates the tname4 error tables using a default name of UV_tname1 for each target tname1 table or view specified.

In the case of a paused Teradata MultiLoad job, do not drop the error tables or the work tables until Teradata MultiLoad completes the import or delete task. For more information about dropping the work tables or error tables, see Implications of Dropping Required Teradata MultiLoad-Created Tables.

Do not share the application error tables between two or more Teradata MultiLoad jobs. Do not share application error tables between two or more target tables in a single Teradata MultiLoad job. Each table targeted by a Teradata MultiLoad job must have its own application error table to ensure that the job runs correctly.

If a distinct application error table for each table targeted by a Teradata MultiLoad job, is not used, the results are unexpected. One or more of the affected jobs may not be able to restart.

WORKTABLES

Specifies the path for the work tables

EXITEON name

The keyword phrase that calls a user-defined exit routine which supports extended object names where name is the name of a user-supplied library with a member name of _dynamn.

The exit must be written in C, or in a language with a run-time environment that is compatible with C.

On some versions of UNIX operating systems, ./ prefix characters to the EXITEON name specification may be required if the module is in the current directory.

MSGEON 'string'

MSGEON supports extended object names. A user-supplied string of up to 16 characters that Teradata MultiLoad logs to:

• The operator’s console for mainframe-attached z/OS client systems
• The system log for workstation-attached UNIX and Windows client systems

The string specification must be enclosed in single quote characters (').

EXIT64 name

The keyword phrase that calls a user-defined exit routine which supports 8-byte activity count where name is the name of a user-supplied library with a member name of _dynamn.

The exit must be written in C, or in a language with a run-time environment that is compatible with C.

On some versions of UNIX operating systems, ./ prefix characters to the EXITEON name specification may be required if the module is in the current directory.

MSG64 'string'

MSG64 supports 8-byte activity count. A user-supplied string of up to 16 characters that Teradata MultiLoad logs to:

• The operator’s console for mainframe-attached z/OS client systems
• The system log for workstation-attached UNIX and Windows client systems

The string specification must be enclosed in single quote characters (').

The following table lists events that create notifications. Some events create notifications only for import tasks, some only for delete tasks, and some for both.