15.00 - Completion Message - FastLoad

Teradata FastLoad Reference

prodname
FastLoad
vrm_release
15.00
category
Programming Reference
featnum
B035-2411-034K

Completion Message

The Teradata FastLoad completion message is:

Listed below is the syntax of each Teradata FastLoad command.
   Singleline commands may be preceded by a [.] or terminated by a [;].
   Multiline commands must NOT be preceded by a [.] but must be terminated by a [;].
   SQL statements must NOT be preceded by a [.] and MUST be terminated by a [;].
   AXSMOD name [ "<initstring>" ] ;
   BEGIN LOADING [dbname.]tname1
      ERRORFILES [dbname.]errortname1,         [dbname.]errortname2
      [ CHECKPOINT integer ]
      [ INDICATORS         ] ;
   CLEAR ;
              { INTEGERDATE }   
   DATEFORM { ‑‑‑‑‑‑‑‑‑‑‑ } ; 
              { ANSIDATE    } 
   DEF[INE] 
        [ fieldname (data type [,NULLIF [=] value ]) ...
        [,fieldname (data type [,NULLIF [=] value ])] ]
        [ { FILE=filename } ]
        [ {               } ] ;
        [ { INMOD=name    } ]
   END LOADING ;
   ERRLIMIT n ;
     
   HELP ;
   HELP TABLE tname ;
   The INSERT statement has two formats:
   1.  INS[ERT] [INTO] tname.* ;
   2.  INS[ERT] [INTO] tname (cname [... ,cname])
   VALUES (:fieldname [... ,fieldname]) ;
   LOGOFF ;
   LOG[ON] [tdpid/] username,password [ , 'acctid' ] ;
           { OFF    }                                      
           { ‑‑‑    } [ EXIT  [name] [TEXT "string"] ]   
   NOTIFY { LOW    } [ MSG   [text]               ] ;
          { MEDIUM } [ QUEUE [options]            ]     
          { HIGH   }                                    
   OS oscommand ;
   QUIT ;
   RECORD [startrecordnumber] [THRU endrecordnumber] ;
   SESSIONS n|* [ m|* ] ;
               { FORMATTED                               }  
   SET RECORD  { ‑‑‑‑‑‑‑‑‑                               } ;
               { UNFORMATTED                             }  
               { VARTEXT [ c ] [DISPLAY_ERRORS] [NOSTOP] }
                        { 0  255      }   
                        { ASCII        }   
   SET SESSION CHARSET  { ‑‑‑‑‑        } ; 
                        { KanjiEUC_0U  }   
                        { KanjiSJIS_0S } 
   SHOW ;
   SHOW VERSION[S] ;
   SLEEP n ;
   TENACITY n ;
   The following DBS/SQL statements are supported by the
   FastLoad utility:
   CREATE TABLE
   DATABASE dbname ;
   DEL[ETE] FROM tname [ ALL ] ;
   DROP TABLE tname ;

Note: Replace “FILE=” with “DDNAME=” for z/OS.

HELP TABLE

Purpose  

The HELP TABLE command creates a list of field names by querying the Teradata Database and deriving the DEFINE list from the table definition.

Syntax  

where:

 

Syntax Element

Description

tname

Table to be queried.

dbname

Database in which the table resides.

Usage Notes

Table 35 describes the things to consider when using the HELP TABLE command.

 

Table 35: Usage Notes for HELP TABLE 

Topic

Usage Notes

Using DEFINE Commands

If the HELP TABLE command is used to define field names, a DEFINE command must still be used to specify the input data source name or INMOD routine.

UDT column

If the table contains a UDT column, an external representation of the UDT is returned.

For example, if the user defines a USDollar data type as Decimal(13,2) and defines a column as USDollar type in the table, Decimal(13,2) is returned as the data type of this column.

Using a CLEAR Command

When using two HELP TABLE commands in the same Teradata FastLoad job, using a CLEAR command before the second one cancels the first. The following command example produces a list of only the Department table:

HELP TABLE Employee ;
CLEAR ;
HELP TABLE Department ; 

Entering the two HELP TABLE commands without a CLEAR command, as in the following example, produces a list of both the Employee and Department tables:

HELP TABLE Employee ;
HELP TABLE Department ;

Unicode Session Character Set Limitation

Teradata FastLoad uses the number of bytes of storage returned from Teradata Database to construct the USING clause of a load operation. Therefore, when the session character set is UTF‑8 or UTF‑16, the MAX LENGTH returned from the database is not the actual byte count for the Unicode column, meaning that the internally generated USING clause does not properly reflect the structure of the input data stream.

Instead of using HELP TABLE to describe the structure of input data, use the DEFINE command when the session character set is UTF‑8 or UTF‑16.

Example  

The following command example builds a list of field names from the table definition for a table named Employee:

HELP TABLE Employee ; 

The SHOW command can be used to verify the field names defined in the HELP TABLE command:

SHOW ;

INSERT

Purpose  

INSERT is a Teradata SQL statement that inserts data records into the rows of the Teradata FastLoad table.

Note: FastLoad also supports temporal syntaxes like CURRENT VALIDTIME, SEQUENCED VALIDTIME, VALIDTIME, NONSEQUENCED VALIDTIME and NONTEMPORAL clauses prefixed in INSERT/INS statement.

Syntax  

where:

 

Syntax Element

Description

tname

Name of the table into which rows are inserted.

cname

Name of column to receive a new row value during the insert operation.

For each cname defined, a corresponding fieldname must be specified.

A list of column names can be defined in any order. The column names do not have to be defined in the same order as they appear in the CREATE TABLE statement.

fieldname

Field name that was defined in a previous DEFINE command.

During the insert operation, Teradata FastLoad inserts the field in the input data record that was assigned to the fieldname into the corresponding column (cname) of the Teradata FastLoad table.

.*

Wildcard specification that all columns in tname that are used to construct the list of field names be used in the insert operation.

When the wildcard specification is used, Teradata FastLoad queries the Teradata Database for all of the column names and uses them to construct a valid INSERT statement.

Usage Notes

The following table describes the things to consider when using the INSERT statement.

Table 36 describes the things to consider when using the INSERT command.

 

Table 36: Usage Notes for INSERT 

Topic

Usage Notes

Required Privileges

To use the INSERT statement, the user ID associated with the Teradata FastLoad job must have INSERT privilege on the specified table.

Inserting Field Values

During the insert operation, field values are inserted in the table in the order in which the columns are listed in the CREATE TABLE statement. If field values in the input data are stored in the same order as columns are defined in the CREATE TABLE statement for the Teradata FastLoad table, a list of column names does not need to be specified in the INSERT statement (for instance, INSERT INTO table1 VALUES (:f1, :f2).

When the second format of the INSERT statement is used, a list of field names is constructed from the definition of the table. During the insert operation, field names and their data types are taken from the CREATE TABLE statement and used to define the table.

The field name definitions are established in the order in which columns are defined in the CREATE TABLE statement. So, the fields in each data record must be in the same order as the columns in the definition of the table.

Using DEFINE Commands

When using the second form of the INSERT statement, use the DEFINE command to specify the name of the input data source or INMOD routine used in the Teradata FastLoad job.

If a DEFINE command that defines one or more fields before the INSERT statement is entered, Teradata FastLoad appends the field definitions to the definitions constructed from the INSERT statement.

Note: The colon character preceding the input field name descriptions (:fieldname) indicates that a corresponding DEFINE field must exist. If the INSERT statement does not include: fieldname expressions, then Teradata FastLoad transmits the command to the Teradata Database intact, without linking it with a previous DEFINE command.

Using SHOW TABLE Statements

If a Teradata SQL SHOW TABLE statement is used to display the exact definition of a table, you must do so from BTEQ or another application. Teradata FastLoad does not support this statement.

ANSI/SQL DateTime Specifications

The ANSI/SQL DATE, TIME, TIMESTAMP, and INTERVAL DateTime data types in Teradata SQL CREATE TABLE statements can be used. They can be specified as column/field modifiers in INSERT statements. They must be converted to fixedlength CHAR data types when specifying the column/field names in the Teradata FastLoad DEFINE command.

Using Unicode Data

Note: Do not use the tname.* version of an INSERT statement when using Unicode data from any of the following:

  • A KATAKANAEBCDIC session
  • A session with a character set name ending with _0I
  • Any session with a character set that does not support multibyte characters (for example, ASCII or EBCDIC)
  • In addition to the field names from the referenced tables, these functions return byte/character counts that Teradata FastLoad uses internally to construct the USING clause for the subsequent load operation. Because of the byte and character count conversions that take place when importing and exporting CHAR and VARCHAR data between a client system and the Teradata Database, the internally generated USING clause does not properly reflect the structure of the input data stream.

    Unicode Session Character Set Limitation

    For information, see Table 35 on page 123.

    Example  

    The following command example defines the EmpRecs input data source and the fields in each record (Emp_Number and Emp_Name):

    DEFINE Emp_Number (INTEGER), Emp_Name (VARCHAR(30)),
       FILE=EmpRecs ;
    INSERT INTO Employee (EmpNo, Name) VALUES
       (:Emp_Number, :Emp_Name) ; 

    The INSERT statement defines the table and columns to receive new data values.

    For each data record, the value in the first field (Emp_Number) is inserted in the EmpNum column and the value in the second field (Emp_Name) is inserted in the Name column.

    Example  

    The following command example establishes a list of field names from the definition of the Teradata FastLoad table:

    DEFINE FILE=InFile;
    INSERT OldTable.* ; 

    The DEFINE command specifies the input data source (InFile) and defines each field to be inserted in the Teradata FastLoad table (OldTable).

    Example  

    Sometimes the records in an input data source contain data that does not belong in the Teradata FastLoad table. If, for example, each record contains 100 bytes of extra data, a dummy field can be defined in the DEFINE command that is not referenced in the INSERT statement.

    The following command example constructs a list of field names that match the current definition of NewTable appended to the definition of the extra data item:

    DEFINE ExtraData (CHAR (100)) FILE = InFile;
    INSERT NewTable.*;

    When extra data is used in the input data source with the INSERT TABLE .* feature, the extra data must be located at the beginning of each record.

    This feature cannot be used if the extra data occurs in the middle or at the end of the records. In this case, explicitly define each data item in the data source and each item in the values clause of the INSERT statement.

    LOGDATA

    Purpose  

    Supplies parameters to the LOGMECH command beyond those needed by the logon mechanism, such as user ID and password, to successfully authenticate the user. The LOGDATA command is optional. Whether or not parameters are supplied and the values and types of parameters depend on the selected logon method.

    LOGDATA is only available on network‑based platforms.

    Syntax

    where:

     

    Syntax Element

    Description

    logdata_string

    Parameters for the logon mechanism specified using “LOGMECH” on page 129

    For information about the logon parameters for supported mechanisms, see Security Administration (B0351100).

    The string is limited to 64 KB and must be in the session character set.

    Note: Make sure that the string ends with a semicolon.

    Usage Notes  

    For more information about logon security, see Security Administration (B035‑1100).

    Example  

    If used, the LOGDATA and LOGMECH commands must precede the LOGON command. The commands themselves may occur in any order.

    The following example demonstrates using the LOGDATA, LOGMECH, and LOGON commands in combination to specify the Kerberos logon authentication method and associated parameters:

    .logmech KRB5;
    .logdata joe@domain1@@mypassword;
    .logon cs4400s3;

    LOGMECH

    Purpose  

    Identifies the appropriate logon mechanism by name. If the mechanism specified requires parameters other than user ID and password for authentication, the LOGDATA command provides these parameters. The LOGMECH command is optional and available only on network‑attached systems.

    Syntax  

    where:

     

    Syntax Element

    Description

    logmech_name

    Defines the logon mechanism

    For a discussion of supported logon mechanisms, see Security Administration (B035‑1100).

    The name is limited to 8 bytes; it is not case sensitive.

    Usage Notes  

    Every session to be connected requires a mechanism name. If none is supplied, a default mechanism can be used instead, as defined on either the server or client system in an XML‑based configuration file.

    For more information about logon security, see Security Administration (B035‑1100).

    Example  

    If used, the LOGDATA and LOGMECH commands must precede the LOGON command. The commands themselves may occur in any order.

    The following example demonstrates using the LOGDATA, LOGMECH, and LOGON commands in combination to specify the Windows logon authentication method and associated parameters:

    .logmech NTLM;
    .logdata joe@domain1@@mypassword;
    .logon cs4400s3;

    LOGOFF

    Purpose  

    The LOGOFF command ends Teradata FastLoad sessions and exits from the Teradata Database.

    The LOGOFF and QUIT commands may be used interchangeably.

    Syntax  

    Usage Notes

    Table 37 describes the things to consider when using the LOGOFF command.

     

    Table 37: LOGOFF Command Usage Notes 

    Topic

    Usage Notes

    Pausing Teradata FastLoad

    If the LOGOFF command is entered after a BEGIN LOADING command, but before the END LOADING command, the Teradata FastLoad job pauses and can be restarted later.

    Locked Tables

    When a Teradata FastLoad job pauses during the loading phase, the Teradata Database locks the tables named in the BEGIN LOADING command. The tables remain locked until an END LOADING command is entered.

    Termination Return Codes

    When a Teradata FastLoad job terminates, the utility returns a code indicating the way the job completed:

  • Code 0—Normal completion. The job completed successfully and according to the specified plan.
  • Code 4—Warning. A warning condition occurred; for example, a job deviated from normal or from the specified plan, but still completed successfully. A warning may indicate deviation from the plan; for example, the number of sessions specified were not actually used, or a part of the job did not run. Warning conditions do not terminate the job.
  • Code 8—User error. A user error, such as a syntax error in the job script, terminated the job.
  • Code 12—Severe error. A fatal error terminated the job. A fatal error is any error other than a user error.
  • Example  

    The following command example ends Teradata FastLoad and exits the Teradata Database:

    LOGOFF ;