15.00 - Error Message - FastLoad

Teradata FastLoad Reference

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

Error Message

If an invalid value is specified, Teradata FastLoad responds with the following error message:

    FDL4867 Invalid number of sessions requested
    FastLoad will log on as many sessions as possible.

SET RECORD

Purpose  

The SET RECORD command specifies the format of the input data for Network-Attached platform as:

  • Formatted
  • Unformatted
  • Binary
  • Text
  • Variable‑length text
  • The SET RECORD command specifies the format of the input data for Mainframe-Attached platform as:

  • Variable‑length text
  • The SET RECORD command:

  • Can be specified only one time per Teradata FastLoad job script
  • When specified, must be appear before the DEFINE command
  • Syntax  

    Figure 1: For Mainframe‑Attached Client Systems
    Figure 2: For Network‑Attached Client Systems

    where:

     

    Syntax Element

    Description

    FORMATTED

    Keyword specification that the input data source is in Teradata 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 Teradata 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 2byte 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 endofrecord marker, which is a:

  • Line feed (x’0A) on UNIX platforms
  • Carriagereturn/line feed pair (X’0D0A’) on Windows platforms
  • Note: TEXT data requires all CHAR or ANSIDATE data types.

    VARTEXT

    Keyword specification that the input data source is in variablelength text record format, with each field separated by a delimiter character.

    c

     

    Optional specification of the delimiter that separates fields in the variablelength text records of the input data source

    The delimiter can be a single or multicharacter 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.

    Note: 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 variablelength text records. If it's specified, it must be specified after the DISPLAY_ERRORS keyword.

    If not specified, erroneous variablelength text records will be displayed on stderr.

    NOSTOP

    Optional keyword specification that inhibits the Teradata FastLoad termination in response to an error condition associated with a variablelength text record.

    TRIM

    Optional keyword. It is used to specify whether field values in variablelength 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 variablelength text records of the input data source. It is specified after the keyword LEADING, TRAILING or BOTH.

    Rules for a trim character are:

  • The trim character must be a single character, but may be either a singlebyte or multibyte 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 zerolength 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 variablelength 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 variablelength text records of the input data source. See 'r' for more information.

    'r'

    Optional specification of the closing quoted character in field values of variablelength text records of the input data source.

    Rules for opening and closing quoted characters are:

  • The quote character, either opening or closing quote, must be a single character, but may be either a singlebyte or multibyte 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.
  • Usage Notes

    The following are the things to consider when using the SET RECORD command.

  • Data Formats
  • The input source data can be either in text or binary format, where:

  • Text format is a data source containing characters for display on an ASCII terminal.
  • Binary format is numbers in hexadecimal
  • Unformatted Records
  • When UNFORMATTED is specified, Teradata FastLoad assumes nothing concerning the structure of the data, end‑of‑record delimiters, special characters and field length indicators. The input data can be either text or binary:

  • Use both an INSERT statement and a DEFINE command to define the fields
  • For binary data, manually insert the indicator bytes preceding each record
  • Teradata FastLoad then uses the DEFINE clause as a guide to calculate the actual length of each record.

    Data that is extraneous and not intended for use can be defined as CHAR.

    Note: For ASCII data, line ending characters can differ from platform to platform. For example, some systems might only use a carriage return character, while others might use both a carriage return and a line feed character to end a line. Always consider the platform‑dependent characteristics when reading ASCII data from a text file.

  • VARTEXT Records
  • When VARTEXT is specified, Teradata FastLoad assumes that the input data is variable‑length text fields separated by a field delimiter character. The utility parses each input data record on a field‑by‑field basis, and creates a VARCHAR field for each input text field.

  • Data Type Specifications
  • When using the VARTEXT specification, VARCHAR and VARBYTE are the only valid data type specifications which can be used in the Teradata FastLoad DEFINE command.

  • Null Fields
  • Two consecutive delimiter characters direct Teradata FastLoad to null the field corresponding to the one right after the first delimiter character.

    Also, if the last character in a record is a delimiter character, and yet there was at least one more field to be processed, Teradata FastLoad nulls the field corresponding to the next one to be processed, as defined in the DEFINE command.

  • Input Record Requirements
  • The total number of fields in each input record must be equal to or greater than the number of fields described in the Teradata FastLoad DEFINE command. If the total number is less, Teradata FastLoad generates an error message. If the total number is more, the Teradata Database ignores the extra fields.

  • Error Record Handling
  • When Teradata FastLoad encounters an error condition in an input record, it normally discards the record and terminates. When loading variable‑length text records, either or both of these functions can be inhibited by specifying the error‑handling options:

  • DISPLAY_ERRORS
  • NOSTOP
  • By specifying both options and redirecting STDERR to a file location instead of the terminal screen, the Teradata FastLoad job will run to completion and save all the error records. They can then be manually modified using another utility such as BTEQ or MultiLoad to load them into the table.

  • Variable‑length Fields
  • When using variable‑length fields in either formatted or unformatted records, either:

  • Include a two‑byte binary integer indicator immediately preceding each variable‑length field. Teradata FastLoad uses this indicator to determine the exact length of the field.
  • Pad each variable‑length field with blanks to produce fixed‑length fields
  • In either case, the maximum field length as shown in the table definition cannot be exceeded.

  • DEFINE and INSERT Specifications
  • Use VARCHAR specifications in the DEFINE command and INSERT statements for variablelength data:

    User.Table Definition
    Name       Type         Size
    Co1001     Integer      4 bytes
    Co1002     Varchar(8)   up to 8 bytes
    Co1003     Date         4 bytes
    define      Co1001 (integer),
                Co1002 (Varchar(8)),
                Co1003 (date)
    file = file_path ;
    insert into User.Table
             values ( :Co1001,
                      :Co1002,
                      :Co1003 ) ;

    To pad a variable‑length field to the maximum used in the table definition (in this case eight bytes) define column 2 as Char(8) with the table definition remaining Varchar(8).

    The following table (User.Table) contains three columns of fixedlength data types. Each record has four bytes as an integer, followed by eight bytes of characters, and then four bytes of a date in integer format:

    User.Table Definition
    Name        Type        Size
    Co1001      Integer     4 bytes
    Co1002      Char(8)     8 bytes
    Co1003      Date        4 bytes

    Assuming that the fields in the record correspond exactly to the table columns, the DEFINE command and INSERT statement specifications would be:

    define      Co1001 (integer),
                Co1002 (char(8)),
                Co1003 (date)
    file = file_path ;
    insert into User.Table
             values ( :Co1001,
                      :Co1002,
                      :Co1003 ) ;
     Co1001  Co1002  Co1003
    |00030506|4549474854202020|000CFD1F  

    The DEFINE and INSERT specifications to define undesirable data (such as special control characters or carriage returns using HEX 0A as end‑of‑record delimiters) would be:

    defineDummy(char(8)),
       Co1001       (integer),
       Co1002       (char(8)),
       Co1003       (date),
       Newline(char(1))
       file = file_path ;
    insert into User.Table
             values ( :Co1001,
                      :Co1002,
                      :Co1003 ) ;
    Control Char  Co1001  Co1002  Co1003
    0FCA037CB86BFF8A|00030506|4549474854202020|000CFD1F|0A
      New line 

    Note: Some systems might require a Newline(char(2)) specification instead of Newline(char(1)).

    Example  

    The following command example sets records to unformatted mode:

    set record unformatted ;