15.10 - Geospatial Data Restrictions - MultiLoad

Teradata MultiLoad Reference

prodname
MultiLoad
vrm_release
15.10
category
Programming Reference
featnum
B035-2409-035K

Geospatial Data Restrictions

The following rule applies to geospatial data:

  • Teradata MultiLoad does not support ST_Geometry geospatial data.
  • Purpose  

    The ACCEPT command sets Teradata MultiLoad variables to the value of a specified:

  • External data source and valid character fields
  • Internal environment variable
  • 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:

  • Use charpos1 to ignore only the single specified character.
  • Use charpos1 THRU to ignore all characters from charpos1 through the end of the record.
  • Use THRU charpos2 to ignore all characters from the beginning of the record through charpos2.
  • Use charpos1 THRU charpos2 to ignore all characters from charpos1 through charpos2.
  • 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.

  • In z/OS, this is a DDNAME. (See Table 33.)
  • In UNIX OS and Windows, this is the path name for 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.

     

    Table 33: ACCEPT Command Considerations 

    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:

  • Keyboard in interactive mode
  • Standard input device in batch mode
  • 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 “at” character (@) is allowed as an alphabetic character.
  • The underscore character (_) is not allowed.
  • 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:

  • 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.

    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:

  • the number of rows read from the client system or sent to Teradata 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.

    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:

  • 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 tname4 that 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 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 operator’s console for mainframe-attached z/OS client systems
  • The system log for network-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 Table 34.
  • NOTIFY MEDIUM enables the notify user exit option for the most significant events, as specified by “Yes” in the Medium Notification Level column of Table 34.
  • 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 Table 34.
  • 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 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 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:

  • FastExport sleeps for 35 minutes and then attempts to log onto the Teradata Database.
  • If the first attempt fails, then the SLEEP time is dynamically adjusted to 25 minutes (SLEEP time subtracted from TENACTITY time), so that the total SLEEP time does not exceed the TENACTIY time (60 minutes).
  • 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:

  • Waits for 6 minutes, by default, or for the amount of time specified by the SLEEP run‑time 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 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 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.

    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:

  • 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.

    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:

  • 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.

    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 operator’s console for channel-attached z/OS client systems
  • The system log for network-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.

    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 operator’s console for channel-attached z/OS client systems
  • The system log for network-attached UNIX and Windows client systems
  • 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.

     

    Table 34: Events that Create Notifications  

    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.

     

    Table 35: 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:

  • WT_tname1 for the tname2 work tables
  • ET_tname1 for the tname3 error tables
  • UT_tname1 for the tname4 error tables
  •  

    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:

  • ApplySeq
  • DBCErrorCode
  • DBCErrorField
  • DMLSeq
  • ImportSeq
  • SMTSeq
  • SourceSeq
  • Uniqueness
  • 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:

  • Teradata Database performance and workload
  • Client platform type, performance, and workload
  • Channel performance, for mainframe-attached systems
  • Network topology and performance, for network-attached systems
  • Volume of data to be processed by the application
  • 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:

  • A target table or view not identified in the BEGIN MLOAD command
  • An incompatible view
  • Views as Import Target Objects

    To be a valid target object in a Teradata MultiLoad import task, a view must:

  • Be a single-table view
  • Permit updates to the underlying table
  • Also, a view must not:

  • Specify either a join or self‑join
  • Convert primary index columns
  • 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.

     

    Table 36: DATEFORM Considerations 

    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.

     

    Table 37: DELETE Considerations 

    Topic

    Usage Notes

    Evaluating the Conditional Clause

    The result of evaluating the conditional clause for a row can be either positive, negative, or indeterminate:

  • If positive, then Teradata MultiLoad deletes the row.
  • If negative, then Teradata MultiLoad does nothing.
  • If indeterminate, then Teradata MultiLoad treats the result as an error condition and records the row and error number in the error table.
  • 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:

  • DELETE statements can be applied to either a table or a view, provided that the view does not specify a join.
  • The number of input data records is unlimited.
  • Equality values must be specified for all the primary index columns in the WHERE clause.
  • Values must be specified for the partitioning columns if the target table has PPI.
  • The DELETE statement must be preceded with a DML LABEL command.
  • The OR construct in the WHERE clause of a DELETE statement cannot be used. Instead, use two separate DELETE statements and apply them conditionally with the APPLY clause of the IMPORT command.
  • Using DELETE in Delete Tasks

    If a BEGIN DELETE MLOAD command is used to specify a delete task:

  • The single DELETE statement can be applied to a table only, not to a view.
  • The DELETE operation requires a full file scan.
  • Do not specify Equality values for all the primary index columns in the WHERE clause. However, equality values for a lesser number of columns of a unique primary index can be specified.
  • 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.

  • Do not precede the DELETE statement with a DML LABEL command. The single DELETE statement is always applied.
  • Optionally, provide a single imported data record, properly defined by a LAYOUT command and its associated FIELD commands (and, optionally, FILLER commands), which is then specified in the single IMPORT command. The data record provides values for the WHERE clause of the DELETE statement.
  • 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.

  • In z/OS, this is a DDNAME. (See Table 39 on page 122.)
  • In UNIX OS and Windows, this is the path name for a file. If the path name has embedded white space characters, it must enclose the entire pathname in single or double quotes.
  • 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.

     

    Table 38: DISPLAY Considerations 

    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:

  • Display screen in interactive mode
  • Standard output device in batch mode
  • 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 “at” character (@) is allowed as an alphabetic character.
  • The underscore character (_) is not allowed.
  • 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:

  • ROWS
  • MISSING UPDATE ROWS
  • 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:

  • DUPLICATE
  • DUPLICATE INSERT
  • DUPLICATE UPDATE
  • MISSING
  • MISSING UPDATE
  • MISSING DELETE
  • 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.

     

    Table 39: DML LABEL Considerations 

    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:

  • Update the rows that need to be updated.
  • Insert the rows that need to be inserted.
  • The alternative would be to either:

  • Presort the data for the update and insert operations.
  • First use an UPDATE statement with all of the data, and then use an INSERT statement with the data that failed the update operation.
  • 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:

  • There must be exactly two DML statements in the DML group.
  • The first DML statement must be an UPDATE statement that follows all of the Teradata MultiLoad task rules.
  • The second DML statement must be an INSERT statement, and it must reflect the same primary index specified in the WHERE clause of the UPDATE statement. This is true for both a single-column primary index and a compound primary index.
  • The value for the partitioning column for an update must be specified if the target table has PPI.
  • Both DML statements must refer to the same table.
  • The data length defined for any character fields in the WHERE condition of an UPDATE statement for an upsert must be less than or equal to the corresponding field length. Otherwise, the upsert is not processed. For example:
  •    UPDATE T1 SET c1=:f2 WHERE P1=:f1

    If P1 is char(5) and f1 is char(6), the upsert is canceled.