Geospatial Data Restrictions
The following rule applies to geospatial data:
Purpose
The ACCEPT command sets Teradata MultiLoad variables to the value of a specified:
The ACCEPT command is a valid command preceding LOGON and LOGTABLE commands.
Syntax
where:
Syntax Element |
Description |
charpos1 and charpos2 |
Start and end character positions of a field in each input record that contain extraneous information For example: |
env_var |
Environment variable that provides the value for the specified utility variables (var) |
fileid |
Data source of the external system The external system DD (or similar) statement specifies a file. If the path name contains embedded white space characters, the entire path name must be enclosed in single or double quotes. If the path name is enclosed by single quotes and there is an embedded single quote, then an escape character needs to precede the embedded single quote. Likewise, if the path name is enclosed by double quotes and there is an embedded double quote, then an escape character needs to precede the embedded double quote. If the path name contains single or double quotation marks, it should be enclosed in either single or double quotation marks. |
var |
Name of the Teradata MultiLoad variable that is to be set with the value accepted from the designated source Character string values appear as quoted strings in the data file. |
Usage Notes
Table 33 describes the things to consider when using the ACCEPT command.
Topic |
Usage Notes |
Coding Multiple Variables |
When multiple variables are coded, each is sequentially assigned input text up to the first space character encountered that is not within a quoted string. |
Delimiting Input Text |
Input text for numeric values must be delimited only by space characters or record boundaries. Character strings must be enclosed in apostrophes. For example, the data record provided to satisfy the following ACCEPT command includes two fields: .Accept age, name from file info;
The following example shows two sample data records, where the first is correct but the second is not: 32 'Tom'
32 Tom
|
Number of Variables |
When the number of variables listed is greater than the number of responses available, unused variables remain undefined (null). If there are not enough variables to hold all responses, Teradata MultiLoad issues a warning message. |
Source File Record Restriction |
A single record, row, or input line is accepted from the designated source. Always make sure that there is only one record in the file from which the ACCEPT command is getting the variables. |
Specifying the System Console/Standard Input Device |
Use the asterisk (*) character as the fileid specification for the system console/standard input (stdin) device. The system console is the: For more information about the keyboard and standard input devices, see “File Requirements” on page 31. |
z/OS fileid Usage Rules |
If a DDNAME is specified, Teradata MultiLoad reads data records from the specified source. A DDNAME must obey the same construction rules as Teradata SQL column names except that: The DDNAME must obey the applicable rules of the external system and may reference a sequential or VSAM data set. If the DDNAME represents a data source on magnetic tape as supported by the operating system, the tape may be either labeled or non-labeled. |
Purpose
The BEGIN MLOAD and BEGIN DELETE MLOAD commands initiate or restart Teradata MultiLoad import or delete tasks.
The syntax for the BEGIN MLOAD command is similar but different for each task.
Syntax for Import Task
Syntax for Delete Task
where:
Syntax Element |
Description |
AMPCHECK |
The Teradata MultiLoad response to a down AMP condition: If an AMPCHECK specification is not entered, the Teradata MultiLoad default is AMPCHECK APPLY. Note: 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: 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. Note: 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: 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: |
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 toTeradata 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 Teradata Database before the errpercent parameter is applied. The default is no limit. Note: Uniqueness violations do not count as rejected records. For more information, see “Error Recording” on page 88. |
ERRORTABLES |
The path for error tables |
errpercent |
Approximate percentage, expressed as an integer, of the total number of records sent to Teradata 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. Note: 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 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: Note: 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. Note: 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 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 specification must be greater than zero. The default minimum, if the SESSIONS option is not used or specifies a min value, is 1. 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 Teradata Database configurations, the default of one session per AMP may be inappropriate. For more information about how to configure sessions, see Table 34 on page 113. |
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 TENACTIY 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 TENACTY command is 1 hour then: |
TABLEWAIT hours |
The TABLEWAIT run‑time 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 Teradata Database indicates that the target table is being loaded (Error 2574, Error 2652), the Teradata MultiLoad utility: 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 Teradata 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. Note: If TENACITY is not specified, hours is 4. If TENACITY is specified without hours, an error message appears. Teradata 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 Teradata 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 Teradata 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 cannot be a view for a delete task.) Specify: 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: 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. Note: 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” on page 41. 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 Each tname3 specification must identify: 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. Note: 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” on page 41. Do not share the acquisition error tables between two or more Teradata MultiLoad jobs. 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: 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. Note: 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” on page 41. 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. Note: 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 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. Note: 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 string specification must be enclosed in single quote characters ('). |
Table 34 lists events that create notifications. Some events create notifications only for import tasks, some only for delete tasks, and some for both.
Event |
Import Task |
Delete Task |
Notification Level |
Signifies |
||
Low |
Medium |
High |
||||
AMPs down |
X |
X |
No |
No |
Yes |
Down AMPs on Teradata Database. |
Checkpoint |
X |
|
No |
No |
Yes |
Checkpoint information has been written to the restart log table. |
CLIv2 error |
X |
X |
Yes |
Yes |
Yes |
A CLIv2 error. |
Delete begin |
|
X |
No |
Yes |
Yes |
DELETE statement about to be sent to Teradata Database. |
Delete end |
|
X |
No |
Yes |
Yes |
Successful delete task processing. |
Delete exit |
|
X |
Yes |
Yes |
Yes |
End of delete task. |
Delete init |
|
X |
Yes |
Yes |
Yes |
BEGIN DELETE MLOAD command processing. |
Error table 1 |
X |
|
No |
No |
Yes |
Successful processing of the SEL COUNT(*) request for the first error table. |
Error table 2 |
X |
X |
No |
No |
Yes |
Successful processing of the SEL COUNT(*) request for the second error table. |
Exit |
X |
|
Yes |
Yes |
Yes |
Teradata MultiLoad is terminating. |
File or INMOD open |
X |
|
No |
No |
Yes |
Successful processing of the IMPORT command. |
Import begin |
X |
|
No |
No |
Yes |
First record about to be read. |
Import end |
X |
|
No |
No |
Yes |
Last record has been read. |
Initialize |
X |
|
Yes |
Yes |
Yes |
Successful processing of the NOTIFY option (BEGIN MLOAD or BEGIN DELETE MLOAD command). |
Phase 1 begin |
X |
|
No |
Yes |
Yes |
Beginning of the acquisition phase. |
Phase 1 end |
X |
|
No |
Yes |
Yes |
Successful processing of the CHECKPOINT LOADING END request after the end of the acquisition phase. |
Phase 2 begin |
X |
X |
No |
Yes |
Yes |
The EXEC MLOAD request is about to be sent to Teradata Database. |
Phase 2 end |
X |
|
No |
Yes |
Yes |
Successful processing of the EXEC MLOAD request. |
Teradata Database restart |
X |
X |
No |
Yes |
Yes |
A crash error from Teradata Database or the CLIv2. |
Teradata Database error |
X |
X |
Yes |
Yes |
Yes |
A Teradata Database error that terminates Teradata MultiLoad. |
Usage Notes
Table 35 describes the things to consider when using the BEGIN MLOAD and BEGIN DELETE MLOAD commands.
Topic |
Usage Notes |
Database and Table Name Specifications |
If the work table or error table names are not specified, Teradata MultiLoad creates them using the target table or view names with a three-character prefix of WT_, ET_, or UV_, as in: |
|
Note: Do not share the work and error tables between two or more Teradata MultiLoad jobs. Do not share work and 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 work and error tables to ensure that the job runs correctly. If a distinct work and error table are not used for each table targeted by a Teradata MultiLoad job, the results are unexpected. One or more of the affected jobs may not restart. |
Dropping Teradata MultiLoad Work and Error Tables |
In the case of a paused Teradata MultiLoad job, do not drop the work tables or the error tables until Teradata MultiLoad completes the import or delete task. For more information, see “Implications of Dropping Required Teradata MultiLoad-Created Tables” on page 41. |
Error Table Column Names Not Allowed in Target Tables |
Teradata MultiLoad posts the faulty client record information. Teradata MultiLoad also posts information that identifies the record and the type of error to the UV_ and ET_ error tables. This additional information is provided in eight columns that precede the faulty records from the client system. To maintain the integrity of the Teradata MultiLoad error table information, target tables must not use any of the eight error table column names: |
Session Configurations |
There is no general method to determine the optimal number of sessions, because this number is dependent on several factors, including, but not limited to: Using too few sessions is likely to unnecessarily limit throughput. On the other hand, using too many sessions can increase session management overhead (and also reduce the number of sessions available to any other applications) and may, in some circumstances, degrade throughput. Regardless of the size of the Teradata Database configuration, for large repetitive production applications, it will usually be appropriate to experiment with several different session configurations to determine the best trade-off between resource utilization and throughput performance. For larger Teradata Database configurations, it is appropriate to establish an installation default for the maximum number of sessions that is less than one session per AMP. This can be done either with the installation configuration file (see “Teradata MultiLoad Configuration File” on page 46) or with a standard run‑time parameter (see “Run‑time Parameters” on page 33). An installation default for number of sessions, if specified in the configuration file, can be overridden in individual Teradata MultiLoad scripts, when necessary. |
Unspecified Target Objects |
During processing after the BEGIN MLOAD command, Teradata MultiLoad terminates with an error message if it encounters a reference to: |
Views as Import Target Objects |
To be a valid target object in a Teradata MultiLoad import task, a view must: Also, a view must not: |
Purpose
The DATEFORM command specifies the form of the DATE data type specifications for the Teradata MultiLoad job.
Syntax
where:
Syntax Element |
Description |
ANSIDATE |
Keyword that specifies ANSI fixed-length CHAR(10) DATE data types for the Teradata MultiLoad job |
INTEGERDATE |
Keyword that specifies integer DATE data types for the Teradata MultiLoad job This is the default Teradata DATE data type specification for Teradata MultiLoad jobs if a DATEFORM command is not entered. |
Usage Notes
Table 36 describes the things to consider when using the DATEFORM command.
Topic |
Usage Notes |
Command Frequency and Placement |
Only one DATEFORM command can be used. Enter the command before the LOGON command. |
Data Type Conversions |
When the ANSIDATE specification is used, convert ANSI/SQL DateTime data types to fixed-length CHAR data types by specifying the column/field names in the Teradata MultiLoad FIELD command. See the command usage notes for “FIELD” on page 127 command description for a description of the fixed-length CHAR representations for each DATE, TIME, TIMESTAMP, and INTERVAL data type specification. |
Release Applicability |
The ANSIDATE specification is valid for Teradata MultiLoad jobs. |
Purpose
DELETE is a Teradata SQL statement that removes rows from a table or view that was previously identified as a target table or view in a BEGIN MLOAD command.
Syntax
where:
Syntax Element |
Description |
dbname |
Explicit qualification of the database for the target table or view The default, if a dbname specification is not entered, is the current default database for the Teradata MultiLoad sessions. |
tname |
Previously identified target table or view |
WHERE condition |
Conditional clause identifying the rows to delete |
Usage Notes
Table 37 describes the things to consider when using the DELETE statement.
Topic |
Usage Notes |
Evaluating the Conditional Clause |
The result of evaluating the conditional clause for a row can be either positive, negative, or indeterminate: Note: Indeterminate results are caused by an abnormal arithmetic condition, such as underflow, overflow, or division by zero. |
Joins |
Teradata MultiLoad operates only on single-table statements. DELETE statements cannot contain joins. |
Required Privilege |
To use the DELETE statement, the DELETE privilege on the specified table or view is required. |
Using DELETE in Import Tasks |
If a BEGIN MLOAD command was used to specify an import task: |
Using DELETE in Delete Tasks |
If a BEGIN DELETE MLOAD command is used to specify a delete task: The columns specified in this clause need not be a part of any index, but can be one or more nonindexed columns. This clause may specify nonequality values for any combination of columns of unique indices, or any values for other columns. In the absence of an IMPORT command, the WHERE clause of the DELETE statement must statically specify the rows to be deleted, without reference to any dynamic data from an imported data record. |
Example
Using DELETE in an Import Task
In the following example, an input data source contains a series of one-field, four-byte records. Each record contains the value (EmpNum) of the primary index column (EmpNo) of a row to be deleted from the Employee table. The example assumes that the Employee table is in the current default database.
.LAYOUT Layoutname;
.FIELD EmpNum 1 INTEGER;
.DML LABEL DMLlabelname;
DELETE FROM Employee WHERE EmpNo = :EmpNum;
Purpose
The DISPLAY command writes messages to a specified destination.
Syntax
where:
Syntax Element |
Description |
fileid |
Data source of the external system The external system DD (or similar) statement specifies a file. If the path name contains single or double quotation marks, it should be enclosed in either single or double quotation marks. |
'text' |
Text to be written to the specified output destination |
Usage Notes
Table 38 describes the things to consider when using the DISPLAY command.
Topic |
Usage Notes |
Conflicting Write Operations on Network-attached Systems |
On network-attached client systems, if the same file to redirect stdout and as the file in a DISPLAY command is specified, the results may be incomplete due to conflicting write operations to the same file. |
Displaying Apostrophes in the Text String |
To display an apostrophe within the text string, use two consecutive apostrophes (single quotes) to distinguish it from both the single quotes enclosing the string and a regular double-quote character. |
Specifying the System Console/Standard Output Device |
The asterisk (*) character can be used as the fileid specification to direct the display messages to the system console/standard output (stdout) device. The system console is the: For more information about the display screen and standard output devices, see “File Requirements” on page 31. |
Utility Variables |
Utility variables are replaced by their values before text is displayed. This is done by preceding the variable name with an ampersand character (&). To display the name of a utility variable, use two ampersand characters (&&) instead of one. |
z/OS fileid Usage Rules |
A DDNAME must obey the same construction rules as Teradata SQL column names except that: The DDNAME must obey the applicable rules of the external system and may reference a sequential or VSAM data set. If the DDNAME represents a data source on magnetic tape, the tape may be either labeled or non‑labeled, as supported by the operating system. |
Purpose
The DML LABEL command defines a label and error-treatment options for one or more immediately following INSERT, UPDATE, and DELETE statements.
Note: When using both UPDATE and INSERT statements, the resulting operation is referred to as an upsert.
Syntax
where:
Syntax Element |
Description |
DO INSERT FOR |
An upsert may be implemented by subsequent UPDATE and INSERT statements for: |
label |
The unique name of the label that is used for the immediately following set of one or more INSERT, UPDATE, or DELETE statements The label name must obey the same construction rules as Teradata SQL column names. The label name in the APPLY clause of an IMPORT command can be referenced. |
MARK or IGNORE |
Teradata MultiLoad either places (MARK) or does not place (IGNORE) rows in the tname4 error table for the type of entry specified: MARK/IGNORE DUPLICATE ROWS has no effect if the table is a multiset table (which allows duplicate rows). IGNORE DUPLICATE ROWS has no effect if the table has a unique primary index. Since a duplicate row implies a uniqueness violation in this case, the row is logged to the uniqueness violation table. In the case of an upsert operation, both the insert and update portions must fail for an error to be recorded. In this case, the mark rows for the missing update operations then have nulls for the target table columns. If either INSERT or UPDATE with DUPLICATE is specified, then the MARK or IGNORE specification applies to both insert and update operations. Similarly, if either UPDATE or DELETE with MISSING is not specified, then the MARK or IGNORE specification applies to both update and delete operations. Note: MARK is the default for all actions except MISSING UPDATE for an upsert operation. |
Usage Notes
Table 39 describes the things to consider when using the DML LABEL command.
Topic |
Usage Notes |
Bypassing the Duplicate Row Check |
Duplicate row checking is not performed if the table is a multiset table (which allows duplicate rows) or if the table has a unique primary index (the uniqueness test takes the place of the duplicate row check). |
DO INSERT FOR ROWS Option |
By following the rules for upsert operations, a number of uses for the DO INSERT ROWS option can be found. With an upsert operation, Teradata MultiLoad needs only one pass of the data to both: The alternative would be to either: |
Import Tasks |
For import tasks, as many as five distinct error treatment options with one DML LABEL command can be specified. For example: .DML LABEL COMPLEX
IGNORE DUPLICATE INSERT ROWS
MARK DUPLICATE UPDATE ROWS
IGNORE MISSING UPDATE ROWS
MARK MISSING DELETE ROWS
DO INSERT FOR MISSING UPDATE ROWS;
|
MARK MISSING UPDATE ROWS Option |
Specifying MARK MISSING UPDATE ROWS while using the DO INSERT ROWS option, causes Teradata MultiLoad to record any update record that fails in the tname4 application error table, along with an error code indicating that the INSERT statement was then executed. Then, if the insert operation fails, the insert record is also recorded in the application error table. The default for an upsert operation, however, is to not mark missing update rows. This is because when the upsert feature is used, it expects the insert operation to occur when the update operation fails. Failure of the update portion of an upsert operation does not, in itself, constitute an error and it is not to be treated as one. |
MARK MISSING DELETE ROWS Option |
The MARK MISSING DELETE ROWS option has no meaning when used with the DO INSERT FOR ROWS option. |
Upsert Feature |
When using the upsert feature: UPDATE T1 SET c1=:f2 WHERE P1=:f1
If P1 is char(5) and f1 is char(6), the upsert is canceled. |