Completion Message
The Teradata FastLoad completion message is:
Listed below is the syntax of each Teradata FastLoad command.
Single‑line commands may be preceded by a [.] or terminated by a [;].
Multi‑line 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 [ "<init‑string>" ] ;
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.
|
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.
|
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 fixed‑length 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: 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 (B035‑1100). 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.
|
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: |
Example
The following command example ends Teradata FastLoad and exits the Teradata Database:
LOGOFF ;