SET RECORD | Syntax | Teradata FastLoad - 17.10 - Syntax - FastLoad

Teradata® FastLoad Reference

Product
FastLoad
Release Number
17.10
Published
June 2021
Last Update
2021-07-01
Content Type
Programming Reference
Publication ID
B035-2411-061K
Language
English (United States)

SET RECORD syntax (mainframe-attached systems)

SET RECORD syntax (workstation-attached systems)

where the following is true:

FORMATTED
Keyword specification that the input data source is in database standard format.
This is the default specification, if the SET RECORD command is not used in the Teradata FastLoad job script.
UNFORMATTED
Keyword specification that the input data source deviates from database standard format.
Unformatted records are any data file, such as a text file, that does not have various properties such as a consistent structure with regard to record length and order of data elements.
BINARY
Keyword specification that the input data source is in binary format.
The format must be a 2-byte integer, n, followed by n bytes of data.
TEXT
Keyword specification that the input data source is in text format.
The format must be an arbitrary number of bytes, followed by an end-of-record marker, which is one of the following:
  • Line feed (x’0A) on UNIX platforms
  • Carriage-return/line feed pair (X’0D0A’) on Windows platforms
TEXT data requires all CHAR or ANSIDATE data types.
VARTEXT
Keyword specification that the input data source is in variable-length text record format, with each field separated by a delimiter character.
'c'
Optional specification of the delimiter that separates fields in the variable-length text records of the input data source
The delimiter can be a single or multi-character sequence (or string).
If the delimiter is not specified, the default is the character sequence consists of a single pipe character (|).
If the script character set is different from the client session character set, the delimiter is converted from the script character set to the client session character set before it is passed to Data Connector.
Any character sequence that appears in the data cannot be used as a delimiter. No control character other than a tab character can be used in a delimiter.
DISPLAY_ERRORS
Optional keyword specification that writes input data records that produce errors to the standard error file.
'efilename'
Optional specification of a regular file name used to store erroneous variable-length text records. If it's specified, it must be specified after the DISPLAY_ERRORS keyword.
If not specified, erroneous variable-length text records will be displayed on stderr for network platforms, and on SYSOUT for channel platforms.
NOSTOP
Optional keyword specification that inhibits the Teradata FastLoad termination in response to an error condition associated with a variable-length text record.
TRIM
Optional keyword. It is used to specify whether field values in variable-length text record could be trimmed. It must be followed by one of the following keywords: NONE, LEADING, TRAILING or BOTH.
NONE
Can follow the keyword TRIM. It is used to specify that field values are not to be trimmed. TRIM NONE is the default behavior of the trim processing, which is the same as not specifying the TRIM at all.
LEADING
Can follow the keyword TRIM. It is used to specify the leading characters of field values must be trimmed. See 'p' below for trim character specification.
TRAILING
Can follow keyword TRIM. It is used to specify that the trailing characters of field values must be trimmed. See 'p' below for trim character specification.
BOTH
Can follow keyword TRIM. It is used to specify that the leading and trailing characters of field values must be trimmed. See 'p' below for trim character specification.
'p'
Optional specification of the trim character in field values of variable-length text records of the input data source. It is specified after the keyword LEADING, TRAILING or BOTH.
Rules for a trim character are as follows:
  • The trim character must be a single character, but may be either a single-byte or multi-byte character. It is expressed in the client session character set.
  • By default, if 'p' is not specified, the trim character is the blank (space) character.
  • Trimming can be performed on either unquoted or quoted field values.
  • If a field consists solely of one or more trim characters, it will be a zero-length VARCHAR after trimming. It can be set to NULL using NULLIF option on the DEFINE command.
QUOTE
Optional keyword. It is used to specify whether field values in variable-length text record will never be quoted (if it is followed by keyword NO), optionally be quoted (if it is followed by keyword OPTIONAL) or always be quoted (if it is followed by keyword YES). It must be followed by one of the following keywords: NO, OPTIONAL or YES.
NO
Can follow keyword QUOTE. It is used to specify that field values will never be quoted. It is the default behavior.
OPTIONAL
Can follow keyword QUOTE. It is used to specify that field values will optionally be quoted.
YES
Can follow keyword QUOTE. It is used to specify that field values will always be quoted.
'q'
Optional specification of the opening quoted character in field values of variable-length text records of the input data source. See 'r' for more information.
'r'
Optional specification of the closing quoted character in field values of variable-length text records of the input data source.
Rules for opening and closing quoted characters are as follows:
  • The quote character, either opening or closing quote, must be a single character, but may be either a single-byte or multi-byte character. It is expressed in the client session character set.
  • The opening and closing quote characters can be different.
  • If only 'q' is specified, it's used for both opening and closing quotes.
  • By default, if 'q' or 'r' are not specified, the opening quote or the closing quote is the '"' character.