LOBs Data Restrictions - FastExport

Teradata FastExport Reference

Product
FastExport
Release Number
15.00
Language
English (United States)
Last Update
2018-09-28
dita:id
B035-2410
lifecycle
previous
Product Category
Teradata Tools and Utilities

LOBs Data Restrictions

Teradata FastExport does not support LOBs data type.

ACCEPT

Purpose  

The ACCEPT command sets FastExport utility 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 contains 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, the fileid is a DDNAME. (See Table 24 on page 73.)
  • In UNIX OS and Windows, fileid is the path name for a file. If the path name has embedded white space characters, enclose the entire path name in single or double quotation marks.
  • If the path name is enclosed by single quotation marks and there is an embedded single quotation mark, then an escape character (single quotation mark) needs to precede the embedded single quotation mark. Likewise, if the path name is enclosed by double quotation marks and there is an embedded double quotation mark, then an escape character (double quotation mark) needs to precede the embedded double quotation mark.
  • 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 FastExport utility 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 24 describes the things to consider when using the ACCEPT command.

     

    Table 24: ACCEPT Command Usage Notes 

    Topic

    Usage Notes

    Specifying the System Console/Standard Input Device

    The asterisk (*) character can be used 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 25.

    z/OS fileid Usage Rules

    If a DDNAME is specified, FastExport 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, the tape may be either labeled or unlabeled, as supported by the operating system.

    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.

    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.

    Input text for character strings must be enclosed in apostrophes. For example, the data record provided to satisfy the following ACCEPT command should include 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, FastExport issues a warning message.

    BEGIN EXPORT

    Purpose  

    The BEGIN EXPORT command signifies the beginning of an export task and sets the specifications for the task sessions with Teradata Database.

    Syntax  

    where:

     

    Syntax Element

    Description

    SESSIONS…

    Maximum number of FastExport sessions that is logged on when a LOGON command is entered and, optionally, the minimum number of sessions required to run the job

  • The max value specifies the maximum number of sessions to log on. The max specification must be greater than zero. If a SESSIONS max value is specified that is larger than the number of available AMPs, FastExport limits the sessions to one per working AMP. The default maximum, if the SESSIONS option is not used, is 4.
  •  

  • The min value 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 a min value is specified, is 1. If the min value is specified as 0 which is invalid, FastExport will display an error message and terminate with the user error, RC=8"
  • The * value 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 always less than or equal to the max specification.
  • For more information about setting number of sessions, see Table 26 on page 80.

    TENACITY hours

    Number of hours that FastExport tries to log on to Teradata Database

    When FastExport tries to log on for a new task, and Teradata Database indicates that the maximum number of utility import/export sessions are already running, FastExport:

    1 Waits for six minutes, by default, or for the amount of time specified by the SLEEP option.

    2 Then it tries to log on to Teradata Database again.

    FastExport repeats this process until it has either logged on for the required number of sessions or exceeded the TENACITY hours time period.

    The default value is 4.

    For more information about the maximum number of load utility tasks that can run, see “Concurrent Load Utility Tasks” on page 50.

    For information on how the TENACITY command interacts with the SLEEP command, see the SLEEP minutes entry in this table.

    SLEEP minutes

    Number of minutes that FastExport waits between logon attempts

    Default value is 6.

    FastExport uses the SLEEP specification in conjunction with the 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 SLEEP command is 65 minutes and 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).
  • NOTIFY…

    FastExport 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 25.
  • NOTIFY MEDIUM enables the notify user exit option for those events signified by “Yes” in the Medium Notification Level column of Table 25.
  • NOTIFY HIGH enables the notify user exit option for those events signified by “Yes” in the High Notification Level column of Table 25.
  • EXIT name

    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 programming language with a runtime environment that is compatible with C.

    For an example, see “Sample Notify Exit Routine” on page 191.

    Note: On some versions of UNIX operating systems, ./ prefix characters may have to be added to the EXIT name specification if the module is in the current directory.

    A new EXIT64 keyword is added if the user intends to see large count values more than 4000, like 4294967300. It works the same way as EXIT keyword, and is used when the Notify value is higher than LOW.

    EXITEON keyword is added if the user intends to view Extended Object Names of 128 characters. It works the same as EXIT keyword when Extended Object names are used in script.

    TEXT 'string'

    A user‑supplied string of up to 80 characters that FastExport passes to the named user exit routine

    The string specification must be enclosed in single quote characters (').

    MSG 'string'

    A user‑supplied string of up to 16 characters that FastExport logs on to:

  • The operator’s console (mainframe‑attached z/OS client systems)
  • The system log (network‑attached UNIX and Windows client systems)
  • The string specification must be enclosed in single quote characters (').

    QUEUE option

    Queue management option on mainframe‑attached z/OS client systems

    Note: This option is available only on z/OS, and only for tasks with a low notification specification.

    This option invokes an ENQ when the BEGIN EXPORT 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 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.

    DATAENCRYPTION

    Keyword that enables data encryption for the FastExport job

    Valid options are:

  • ON = All the requests between BEGIN EXPORT and END EXPORT commands will be encrypted.
  • OFF = The requests between BEGIN EXPORT and END EXPORT commands will not be encrypted. This is the default.
  • This option will apply only to the requests between BEGIN EXPORT and END EXPORT commands.

    Using this option overwrites the data encryption settings specified by both the runtime parameters and in the fexpcfg.dat configuration file.

    DECIMALDIGITS

    A user‑supplied maximum number of digits in the DECIMAL data type that can be exported. Starting from V2R6.2, the maximum number of digits in the DECIMAL data type increased from 18 to 38. Note that if a user doesn’t set the limit, the default maximum number of digits is 18.

    When the client is a mainframe, the user can set the limit to 31 to request automatic CAST to avoid n>31 results.

    Using this option overwrites the max_decimal_returned value specified in the clispb.dat file for network‑attached systems or the HSHSPB parameter for mainframe‑attached systems.

    NOSTOP

    If the NOSTOP option is specified and Teradata or CLIv2 does not support Large Decimal, if the user specifies a valid value for the decimaldigits parameter, FastExport does the following:

  • Displays a message that Teradata Database or Teradata CLIv2 does not support Large Decimal
  • Displays a warning that the decimaldigits setting is ignored
  • Continues with the Teradata FastExport job
  • Exits with an exit code of 4, unless there is another error with a higher exit code
  •  

    If the NOSTOP option is not specified and Teradata or CLIv2 does not support Large Decimal, if the user specifies a valid value for the decimaldigits parameter, FastExport maintains the current behavior and does the following:

  • Displays a message that Teradata Database or Teradata CLIv2 does not support Large Decimal
  • Terminates the FastExport job
  • Exits with an exit code of 8
  • Note: If the user specifies a valid value for the max_decimal_returned parameter in clispb.dat, FastExport maintains the current behavior, regardless of NOSTOP option.

    SPOOL

    Tells FastExport to spool the answer set. This is the default.

    NOSPOOL

    Tells FastExport to try to use the NoSpool method. If the NoSpool method is not supported, FastExport issues a warning and then uses the Spool method

    NOSPOOLONLY

    Tells FastExport to use the NoSpool method only. If the NoSpool method is not supported, then terminate the job with an error.

    Table 25 lists the events which create notifications.

     

    Table 25: Events That Create Notifications 

    Event

    Notification Level

    Signifies

    Low

    Medium

    High

    Initialize

    Yes

    Yes

    Yes

    Successful processing of the BEGIN EXPORT command

    File or INMOD open

    No

    No

    Yes

    Successful processing of the IMPORT command

    Teradata Database Restart

    No

    Yes

    Yes

    A crash error from Teradata Database or CLIv2

    CLIv2 error

    Yes

    Yes

    Yes

    A CLIv2 error

    Teradata Database error

    Yes

    Yes

    Yes

    A Teradata Database error that terminates FastLoad

    Exit

    Yes

    Yes

    Yes

    FastExport is terminating

    Export begin

    No

    Yes

    Yes

    Opening the export file

    Request submit begin

    No

    Yes

    Yes

    Submitting the SELECT request

    Request submit end

    No

    Yes

    Yes

    Received SELECT request response

    Request fetch begin

    No

    Yes

    Yes

    Fetching SELECT request results

    File or OUTMOD open

    No

    No

    Yes

    Opening output file or OUTMOD

    Statement fetch begin

    No

    No

    Yes

    Fetching current statement

    Statement fetch end

    No

    No

    Yes

    Last record fetched for current statement

    Request fetch end

    No

    Yes

    Yes

    Last record fetched for current request

    Export end

    No

    Yes

    Yes

    Export task completed

    Block Count

    No

    Yes

    Yes

    The total number of blocks when the no spool is in effect

    Usage Notes  

    Table 26 describes the things to consider when using the BEGIN EXPORT command.

     

    Table 26: BEGIN EXPORT Usage Notes 

    Topic

    Usage Notes

    Command Placement and Frequency

    The BEGIN EXPORT command must be the first command in a group of FastExport utility commands that specify an export task.

    Multiple BEGIN EXPORT commands can be used in a FastExport job script, but each export task specification must begin with a BEGIN EXPORT command and end with an END EXPORT command.

    Sessions Limit Specification

    The number of sessions that you should specify depends on the connections to Teradata Database and the amount of data to be returned.

    In addition to the sessions that are used for the export task, FastExport uses two additional sessions to:

  • Maintain the restart log table
  • Submit Teradata SQL statements
  • There is no general method to determine the optimal number of sessions, because it 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
  • When specifying the session limit, always consider the load that the export task is placing on the channel or network connection.

    For example, four sessions on a mainframe‑attached system, each on a different interface processor (IFP) on a channel, and all concurrently returning data can saturate a single channel.

    In such a case, define the maximum number of sessions as four times the number of channels that are controlled by the Teradata Director Program (TDP) that connects the sessions.

    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.

    If the minimum number of FastExport sessions are not logged, FastExport will terminate.

    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 greater than four sessions, but less than one session per AMP. This can be done using an installation configuration file (see “FastExport Configuration File” on page 36) or a standard runtime parameter (see “Runtime Parameters” on page 28).

    An installation default for number of sessions, if specified in the configuration file, can be overridden in individual FastExport job scripts, when necessary.

    On large to very large Teradata Database configurations, the limit of one session per AMP when * is specified may be inappropriately large.

    Spool/NoSpool Modes

    The NoSpool mode exports the contents of a table as fast as possible without reading the table into a spool file or distributing the file to all AMPs before extracting it. Three options for spooling are:

  • (Default) SPOOL the data.
  • Use the NOSPOOLONLY mode, but return an error if NOSPOOL is not supported.
  • Use the NOSPOOL mode when possible; otherwise spool the data in Teradata Database.
  • Possible scenarios for taking advantage of the NOSPOOL mode are as follows:

  • Copy a table to another system in preparation for replication or to recover from a synchronization fault in a dual system environment.
  • Copy a table to another system during a system migration (for example, replace NPARC, efficiency approaching ARC).
  • Copy a table or a subset of a table to a test/development system where a data sample is most useful.
  • Copy (possibly a subset of) a table to populate a Data Mart or other data store (for example, out to SAS, MOLAP tools, etc.).
  • Limitations and functionality:

  • NOSPOOL mode applies only to simple SELECT statements. The following are not supported:
  • Access to nondata tables, such as SELECT DATE or SELECT USER
  • USING modifier; instead, define restraint parameters by using a FastExport IMPORT command with supporting FIELD and FILLER commands
  • Contains a SORT (ORDER BY), HAVING, or WITH clauses
  • Joins
  • Aggregations (Explain shows SUM step)
  • TABLE functions
  • Ordered‑analytic (OLAP) functions
  • Multiple SELECT statements or multistatement requests
  • Statements with zero or more than one, retrieve or sampling step
  • NOSPOOL mode only retrieves data from a single table, but the SELECT statement can be selective about which columns are exported and can constrain the job to a subset of rows.
  • Scalar expressions/functions are allowed.
  • The Sample and partition eliminating constraints are supported.
  • Spool/NoSpool (continued...)

  • The Activity Count returned for a regular spooled job indicates the number of affected blocks; however, for non‑spooled jobs, the number of blocks is unknown, so the response message contains ActivityType (instead of Activity Count) to indicate the NOSPOOL process.
  • Disadvantages of the NOSPOOL mode:

  • Locks are maintained during the entire export process.
  • Data conversion errors previously detected during the spooling phase will not be detected until the block is read, which could occur during any time during the export.
  • Row order (because of the absence of the ORDER BY clause) may or may not be consistent between runs; therefore, NOSPOOL mode offers no guarantee of consistency.
  •  

    DATEFORM

    Purpose  

    The DATEFORM command specifies the form of the DATE data type specifications for the FastExport job.

    Syntax  

    where:

     

    Syntax Element

    Description

    ANSIDATE

    Keyword that specifies ANSI fixed‑length CHAR(10) DATE data types for the FastExport job

    INTEGERDATE

    Keyword that specifies integer DATE data types for the FastExport job

    This is the default specification for FastExport jobs if a DATEFORM command is not entered.

    Usage Notes  

    Table 27 describes the things to consider when using the DATEFORM command.

     

    Table 27: DATEFORM Command Usage Notes 

    Topic

    Usage Notes

    Command Frequency and Placement

    Only one DATEFORM command can be used.

    The command must be entered before the LOGON command.

    Data Type Conversions

    When the ANSIDATE specification is used, ANSI/SQL DateTime data types must be converted to fixed‑length CHAR data types when specifying the column/field names in the FIELD command.

    For each DATE, TIME, TIMESTAMP, and INTERVAL data type specification, see “FIELD” on page 97 for a description of the fixed‑length CHAR representations.

    DISPLAY

    Purpose  

    The DISPLAY command writes messages to a specified destination.

    Syntax  

    where:

     

    Syntax Element

    Description

    text

    Text to be written to the specified output destination

    fileid

    Data source of the external system

    The external system DD (or similar) statement specifies a file:

  • In z/OS, the fileid is a DDName.
  • In UNIX OS and Windows, fileid is the path name for a file.
  • If the path name contains single or double quotation marks, it should be enclosed in either single or double quotation marks.

    Usage Notes  

    Table 28 describes the things to consider when using the DISPLAY command.

     

    Table 28: DISPLAY Command Usage Notes 

    Topic

    Usage Notes

    Conflicting Write Operations on Network‑attached Systems

    On network‑attached client systems, if the same file is specified to redirect stdout as the file in a DISPLAY command, 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 25.

    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, code 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 nonlabeled, as supported by the operating system.

    END EXPORT

    Purpose  

    The END EXPORT command signifies the end of an export task and initiates processing by Teradata Database.

    Syntax  

    Usage Notes  

    Table 29 describes the things to consider when using the END EXPORT command.

     

    Table 29: END EXPORT Command Usage Notes 

    Topic

    Usage Notes

    Command Placement and Frequency

    The END EXPORT command must be the last command in a group of FastExport utility commands that specify an export task.

    Multiple END EXPORT commands can be used in a FastExport job script, but each export task specification that begins with a BEGIN EXPORT command must end with an END EXPORT command.

    Command Processing

    In response to the END EXPORT command, the FastExport utility sends a SELECT statement to Teradata Database that:

  • Places the resource locks on the Teradata Database tables
  • Prepares the export data for return to the client system
  • EOC

    Purpose  

    The EOC (End Of Console) command, which is primarily for developer use, indicates “end of console” when FastExport script is input from the console. Do not use EOC command in FastExport batch mode, this will cause FastExport job to terminate.

    Syntax  

     

    EXPORT

    Purpose  

    The EXPORT command provides the client system destination and file format specifications for the export data retrieved from Teradata Database and, optionally, generates a MultiLoad script file that can be used to reload the export data.

    Syntax  

    where:

     

    Syntax Element

    Description

    OUTFILE fileid

    Data destination file on the client system.

    The client system DD or equivalent statement specifies a file:

  • In UNIX OS and Windows, the fileid is the path name for a file.
  • If the path name has embedded white space characters, the entire path name must be enclosed in single or double quotes.

    If the path name is enclosed by single quotation marks and there is an embedded single quotation mark, then an escape character (single quotation mark) needs to precede the embedded single quotation mark. Likewise, if the path name is enclosed by double quotation marks and there is an embedded double quotation mark, then an escape character (double quotation mark) needs to precede the embedded double quotation mark.

  • In z/OS, the fileid is a DDNAME.
  • AXSMOD name

    Name of the access module file to be used to export data. These access modules include:

  • OLE DB Access Module (oledb_axsmod.dll on Windows platforms)
  • Named Pipes Access Module
  • Teradata WebSphere® MQ Access Module (client version)
  • Teradata WebSphere®MQ Access Module (server version)
  • See the Teradata Tools and Utilities Access Module Reference (B035‑2425) for the name of the access module file for each platform.

    A personal shared library file name can be used if custom access module is used.

    The AXSMOD option is not required for importing disk files on either network‑attached or mainframe‑attached client systems, or magnetic tape files on mainframe‑attached client systems. It is required for importing magnetic tape and other types of files on network‑attached client systems.

    To specify the OLE DB Access Module, Named Pipes Access Module, or the WebSphere MQ Access Module for specific platforms, see Teradata Tools and Utilities Access Module Reference.

    ’initstring’

    [Optional] initialization string for the access module

    OUTMOD modulename

    [Optional] user‑written routine for processing the export data.

    In z/OS, modulename is the name of a load module. On UNIX and Windows platforms, it is the path name of the OUTMOD executable code file.

    FastExport provides six parameters to the named procedure, as described in “FastExport/OUTMOD Routine Interface” on page 57.

    Note: On some versions of UNIX operating systems, ./ prefix characters may have to be added to the OUTMOD modulename specification if the module is in the current directory.

    MODE…

    Format mode of the export data returned to the client system:

  • INDICATOR
  • RECORD
  • The default, if a MODE option is not specified, is INDICATOR mode.

    Note: FastExport does not support field mode. To export field mode data, use the appropriate format clauses in the SELECT statements to enable Teradata Database to convert response data to character format.

    FORMAT…

    Record format of the export file on network‑attached systems where:

  • FASTLOAD specifies that each record is a two‑byte integer, n, followed by n bytes of data, followed by an end‑of‑record marker, either X'0A' or X'0D'.
  • BINARY specifies that each record is a two‑byte integer, n, followed by n bytes of data.
  • TEXT specifies that each record consists of an arbitrary number of characters in the client session character set, followed by an end‑of‑record marker, which is:
  • On UNIX platforms, the newline character (identified in Unicode as LINE FEED U+000A)
  • On Windows platforms, the two‑character sequence carriage return followed by line feed (identified in Unicode as CARRIAGE RETURN U+000D and LINE FEED U+000A, respectively)
  • For client session character sets other than UTF16, the end‑of‑record marker byte sequence is:

  • On UNIX platforms, X'0A'
  • On Windows platforms, X'0D0A'
  • For the UTF16 client session character set (in which each character is encoded in two bytes), the end‑of‑record marker byte sequence is:

  • On big endian UNIX platforms, X'000A'
  • On little endian UNIX platforms, X'0A00'
  • On Windows platforms, X'0D000A00'
  • Note: TEXT format should only be specified for character data. Do not specify TEXT format for binary data, such as, INTEGER, BYTEINT, PERIOD, and other binary data. Depending on the actual byte values of the binary data, unexpected results may occur.

  • UNFORMAT specifies that each record is exported as it is received from CLIv2 without any client modifications.
  • Note: The FORMAT options apply only to UNIX and Windows platforms.

    The default, if a FORMAT option is not specified, is FASTLOAD.

    Note: INDICATOR mode is not recommended when using TEXT record format. Please use UNFORMATTED record format instead.

    Note: TEXT data requires all CHAR or ANSIDATE data types.

    BLOCKSIZE integer

    Maximum block size that should be used when returning data to the client

    The default block size is 64K bytes, which is the maximum supported by Teradata Database.

    Note: The BLOCKSIZE specification for a FastExport EXPORT command cannot be larger than the row size supported by Teradata Database.

    OUTLIMIT records

    Maximum number of response records that should be written to the output client file

    When this number is reached, the utility writes the following message to the print output file and stops processing response data:

       Output limit of n exceeded. 

    MLSCRIPT fileid

    Destination file of the generated MultiLoad script file

    When the MLSCRIPT option is specified, FastExport generates a MultiLoad script file that can later be used to reload the export data back into Teradata Database.

    The client system DD or equivalent statement specifies a file:

  • In UNIX OS and Windows, the fileid is the pathname for a file
  • In z/OS, the fileid is a DDNAME.
  • By default, if the MLSCRIPT option is not specified, then the FastExport utility does not generate a MultiLoad script file.

    Note: If the specified fileid already exists, it will be overwritten.

    Note: FastExport generates the MultiLoad script layout based on field sizes and types received from the Teradata Database. Type DATE must be modified to CHAR () type as in the case of using ANSIDATE in the DATEFORM command. They must then be converted to the fixed length CHAR data type as though writing a script to load the data.

    If Extended Object Name is used in Teradata FastExport, the resulting MultiLoad script file generated will not have column names in U& syntax. The user should add the U& syntax for multiload job to run successfully.
    For example, Teradata FastExport uses a column as U&"c1#FF43#FF11"UESCAPE'#',
    The resulting multiload script is:

    In order for Teradata MultiLoad to work properly, you must change c1c1 to U&"c1#FF43#FF11"UESCAPE'#'.

    			.LAYOUT DATAIN_LAYOUT INDICATORS;
    			.FIELD c1c1 1 INTEGER;
    			.FIELD c2 5 CHAR(20);
     
    			.DML LABEL INSERT_DML;
    			INSERT INTO &DBASE_TARGETTABLE..&TARGETTABLE (
    			 c1c1 = :c1c1
    			,c2 = :c2
    			);

    PAD

    The padding feature is only available on the Z/OS platform.

    PAD can have two values, NULLS and BLANKS. Records are padded with NULLS When NULLS is specified. Records are padded with BLANKS when BLANKS is specified.

    The default value is BLANKS.

    Usage Notes  

    Table 30 describes the things to consider when using the EXPORT command.

     

    Table 30: EXPORT Command Usage Notes 

    Topic

    Usage Notes

    Attributes of the Destination File

    On mainframe‑attached client systems, the attributes of the destination file must be compatible with the export data records that will be written there. (Compatibility is not a problem on network‑attached UNIX and Windows client systems.)

    On mainframe‑attached z/OS systems, the attributes vary, depending on:

  • Disposition of the file – If the execution of the FastExport utility is a restart operation, then the disposition of the destination file should be OLD
  • Response mode – For all response modes, the attributes can specify any RECFM. However, RECFM=FB (fixed blocked) or RECFM=VB (variable blocked) are commonly used.
  • Record length and block size – These must accommodate the specified format as shown in Table 31
  • Teradata Database data types in the mainframe‑attached z/OS environments are described in Table 32. Use this information to calculate the size of the exported data rows to assign appropriate values to the attributes of the destination file.

    Block Size Specification

    Two 64K‑byte buffers are allocated for each session being used to transmit data from Teradata Database to the client system.

    The minimum block size that must be allocated is one which will hold the largest possible parcel returned by Teradata Database.

    If the specified block size is not large enough to hold the largest possible parcel, Teradata Database returns an error to the SELECT statement and the utility is abnormally terminated.

    For a complete description of the parcel sizes, see:

  • Teradata Call-Level Interface Version 2 Reference for Mainframe-Attached Systems (B035‑2417)
  • Teradata Call-Level Interface Version 2 Reference for Network-Attached Systems (B035‑2418)
  • Command Placement and Frequency

    One EXPORT command is required for each export task in a FastExport job script. Place it anywhere between the BEGIN EXPORT command and the END EXPORT command that specify the export task.

    MODE Specifications

    Both the INDICATOR and RECORD mode specifications return data in a client internal format with variable‑length records:

  • Each record has a value for all of the columns specified by the SELECT statement
  • Variable‑length columns are preceded by a two‑byte control value indicating the length of the column data
  • Null columns have a value that is appropriate for the column data type
  • Data records returned in indicator mode, however, have a set of bit flags that identify the columns that have a null value.

    For a complete description of these modes, see:

  • Teradata Call-Level Interface Version 2 Reference for Mainframe-Attached Systems (B035‑2417)
  • Teradata Call-Level Interface Version 2 Reference for Network-Attached Systems (B035‑2418)
  • Multiple SELECT Statements

    If the export task specified multiple SELECT statements, the export data is returned in statement order. All response data for statement 1 is followed by the response data for statement 2, and so forth.

    If the same SELECT statement is executed multiple times, then the results of the first iteration are returned and processed before the second iteration of the SELECT statement is sent to Teradata Database.

    SELECT Statement Processing

    If the export task specified multiple SELECT statements, the response data for all statements is returned in statement order – all response data for statement 1 will be first, followed by the data for statement 2, and so forth.

    If a single SELECT statement is executed multiple times, the results of the first iteration are returned and processed before the second SELECT statement is sent to Teradata Database.

    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 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 nonlabeled, as supported by the operating system.

    Table 31 describes the Record Length and Block Size Specification.

     

    Table 31: Record Length and Block Size Specifications 

    RECFM

    Description

    FB

    LRECL must be exactly equal to the number of bytes of data being returned. The LRECL cannot be larger.

    For RECFM=FB, the BLKSIZE must also be a multiple of the LRECL. If not, records may be truncated, resulting in possible data integrity problems, or FastExport may append.

    Explicitly adding BLKSIZE to the JCL eliminates the possibility of using an invalid default BLKSIZE.

    VB

    Logical record length (LRECL) and block size (BLKSIZE) parameters should be large enough to accommodate the largest record that is anticipated.

    VBS or VS

    Maximum logical record length can exceed the physical length for a given data set.

    Spanned records, either blocked or unblocked, use a well‑established straightforward protocol to break or segment records across blocks where necessary.

    While an individual segment never exceeds the length of a block, the logical record that it is a part of can span multiple blocks, and even volumes. Thus spanned records are the only way to create output files with rows whose length exceed the 32K‑byte block size, up to the maximum of 64K bytes that is supported by Teradata Database.

    Even though the maximum LRECL that can be specified with JCL is 32,760, there is no practical limit on the actual length of spanned records.

    For output consisting of records exceeding this maximum LRECL (greater than approximately 32K, for example), simply specify LRECL=X. There is no other special JCL requirement for creating such records when using the VBS or VS record format.

    Always specify the BLKSIZE according to the performance characteristics of the target device or media. This usually means specifying the largest possible BLKSIZE.

    In some cases, the performance of the FastExport utility may be improved by specifying RECFM=VBS when:

  • The largest row is appreciably smaller than 32K bytes in length
  • There is a large variation in row sizes
  • The spanned/blocked format maximizes data packing. Because fewer blocks are required to convey the same number of logical records, the FastExport job runs quicker.

     

    For example, assuming a block size of 32,756 bytes:

  • Using RECFM=VB, a 20,000‑byte record and a 4,000‑byte record could be packed into a newly created block. But, if the next record were 12,000 bytes long it clearly would exceed the length of the block and would have to be packed into the following block.
  • Using RECFM=VBS, the 12,000‑byte record could be segmented such that the first 8,740 bytes could be packed into the original block and the remaining 3,260 bytes packed into the subsequent block, taking into account that there must be one 4‑byte Block Descriptor Word (BDW) per block and one 4‑byte Segment Descriptor Word (SDW) per segment; and a segment must be fully contained within a block
  • Note: A FastExport job will fail with an Error 1776 if rows greater than 32K bytes are exported using a RECFM= specification other than VBS or VS.

    Note also that not all applications can read spanned data records. Always make sure that applications support spanned records before specifying these formats.

    Example: This DD statement requests spanned records for a FastExport EXPORT to fileid named OUTPUT:

    //OUTPUT  DD DSN=ASG.FEXP.Z,DISP=(NEW,CATLG),
    //     DCB=(RECFM=VBS,LRECL=32760,BLKSIZE=32756,DSORG=PS),
    //     UNIT=SYSDA,SPACE=(CYL,(100,20)) 

    Table 32 contains the Data Type description for default MainframeAttached Client Systems.

     

    Table 32: Data Type Descriptions (MainframeAttached Client Systems) 

    Data Type

    Output Length

    Description

    BYTE(n)

    n bytes

    n bytes

    BYTEINT

    1 byte

    8‑bit signed binary

    CHAR(n)

    CHARS(n)

    CHARACTERS(n)

    n bytes

    n EBCDIC characters

    DATE

    4 bytes

    32‑bit integer in the internal date format of Teradata Database.

    For details, see Database Design (B035‑1094) and SQL Data Types and Literals (B035‑1143).

    Note: If a DATEFORM command has been used to specify ANSIDATE as the DATE data type, the FastExport utility internally converts each DATE data type to a CHAR(10) field.

    DECIMALx

    DECIMAL(x)

    DECIMAL(x,y)

    (x+1) / 2 bytes

    x packed decimaldigits and sign

    FLOAT

    FLOATING

    8 bytes

    64‑bit (double‑precision) floating point

    GEOSPATIAL DATA

    maximum 64000

    FastExport does not support Geospatial data represented by LOBs.

    INTEGER

    4 bytes

    32‑bit signed binary

    LONG VARCHAR

    m+2 characters where m<=n

    Same as VARCHAR (32000) characters

    Fixed Length Period Data Types:

    PERIOD(DATE)

    PERIOD(TIME(n))

    PERIOD(TIME(n)
    WITH TIME ZONE)


    max=8 byte

    max=12 bytes

    max=16 bytes

    The precision specified must be 0<n<6: precision=0 (n/a)

    precision=n

    precision=n

    For details, see Database Design (B035‑1094) and SQL Data Types and Literals (B035‑1143).

    Variable Length Period Data Types:

    PERIOD(TIMESTAMP(n))

    PERIOD(TIMESTAMP(n)
    WITH TIME ZONE)

     

    max=20 bytes

    max=24 bytes

    The precision specified must be 0<n<6:

    precision=n

    precision=n

    For details, see Database Design (B035‑1094) and SQL Data Types and Literals (B035‑1193).

    SMALLINT

    2 bytes

    16‑bit signed binary

    VARBYTE(n)

    m+2 bytes where m<=n

    16‑bit integer, count m, followed by m bytes of data

    VARCHAR(n)

    m+2 bytes where m<=n

    16‑bit integer, count m, followed by m EBCDIC characters

    Refer to SQL Data Types and Literals (B035‑1143) for more information.

    Example Using the OUTFILE and FORMAT Specifications

    The following example specifies that the exported records to be loaded are written to /home/fexpuser/tests/out1 and that the format of each record is unformat:

    .EXPORT OUTFILE /home/fexpuser/tests/out1
    FORMAT UNFORMAT ;

    Example Specifying an OUTMOD Routine

    The following example for a UNIX client system runs an OUTMOD routine that has been compiled and linked as feomod.so:

    .EXPORT OUTMOD ./feomod.so;

    The following example for a Windows client system runs the same OUTMOD routine that has been compiled and linked as feomod.dll:

    .EXPORT OUTMOD .\feomod.dll;

    FIELD

    Purpose  

    The FIELD command specifies a field of the input record that provides data values for the constraint parameters of the SELECT statement. Each field defined by a FIELD command is sent to Teradata Database as part of the data record containing data values defined by a USING modifier for the SELECT statement.

    Syntax  

    where:

     

    Syntax Element

    Description

    fieldname1

    Name of an input record field that is referenced by a variable parameter name in the WHERE condition of the SELECT statement

    startpos

    Starting position of the field in an input data record

    startpos can be specified as an:

  • Unsigned integer, which is a character position starting with 1
  • Asterisk (*), which means the next available character position beyond the preceding field
  • Note: When using the CONTINUEIF condition of the LAYOUT command to continue input records, a startpos specified by an integer value refers to a character position in the final concatenated record from which the continuation indicator has been removed.

    datadesc

    Type and length of data in the field

    This description is used to generate the data description for this field in the USING modifier for the SELECT statement.

    The datadesc specification can be any of the data type phrases shown in SQL Data Types and Literals (B035‑1143).

    fieldexpr

    Concatenation of two or more items, either fields or character constants or string constants or a combination of these in the following form:

    fieldname2 || fieldname2 || fieldname2 ...

    Nested concatenations are not supported. Each fieldname2 that is actually a field by its own FIELD command must be defined.

    Valid character and string constants are as described in SQL Fundamentals (B035‑1141).

    NULLIF nullexpr

    Condition used for selectively inserting a null value into the affected column

    The condition is specified as a conditional expression involving any number of fields, each represented by its fieldname and constants.

    Each fieldname appearing in the conditional expression must be defined by either:

  • The startpos and datadesc parameters of the FIELD command
  • A FILLER command
  • DROP…

    Character positions to be dropped from the fieldname1

    These must be of a character data type.

    Usage Notes  

    Table 33 describes the things to consider when using the FIELD command.

     

    Table 33: FIELD Command Usage Notes 

    Topic

    Usage Notes

    Command Placement and Frequency

    A FIELD command must be preceded by a LAYOUT command.

    One or more FIELD commands, or a combination of FIELD command and FILLER command, define the composition of the input data record to supply values for the USING modifier of the SELECT statement.

    Specifying ARRAY Data Types

    A column that is defined as an ARRAY data type in a Teradata table must be specified as a VARCHAR data type in the FIELD command. The external representation for an ARRAY data type is VARCHAR.

    The following is a sample Teradata table definition that includes a one‑dimensional ARRAY data type for the COL003 column:

    CREATE SET TABLE SOURCE_TABLE ,NO FALLBACK ,
         NO BEFORE JOURNAL,
         NO AFTER JOURNAL,
         CHECKSUM = DEFAULT,
         DEFAULT MERGEBLOCKRATIO
         (
          EMP_ID INTEGER,
          EMP_NO BYTEINT,
          COL003 SYSUDTLIB.PHONENUMBERS_ARY,
          COL004 SYSUDTLIB.DECIMAL_ARY,
          COL005 SYSUDTLIB.INTEGER_ARY)
    UNIQUE PRIMARY INDEX ( EMP_ID );

     

    The following is a sample definition for the PHONENUMBERS_ARY data type:

    CREATE TYPE PHONENUMBERS_ARY AS CHAR(10) CHARACTER SET LATIN ARRAY [2];

    The following is a sample definition for the DECIMAL_ARY data type:

    CREATE TYPE DECIMAL_ARY AS DECIMAL(5,2) ARRAY[2];

    The following is a sample definition for the INTEGER_ARY data type:

    CREATE TYPE INTEGER_ARY AS INTEGER ARRAY[2];

    The following is a sample FastExport layout definition for the sample SOURCE_TABLE table:

     .FIELD  EMP_ID *  INTEGER;
       .FIELD  EMP_NO *  BYTEINT;
       .FIELD  COL003  *  VARCHAR(47);
       .FIELD  COL004  * VARCHAR(17);
       .FIELD  COL005  * VARCHAR(25);

    In the above example, the COL003 column is defined as VARCHAR(47) because it's the maximum representation for the COL003 column in the table.

    The following is the calculation for the maximum representation for the COL003 column:

    1 byte for the left parenthesis

    + 1 byte for the single quote

    + 10 to 20 bytes for the first element

    + 1 byte for the single quote

    + 1 byte for the comma

    + 1 byte for the single quote

    + 10 to 20 bytes for the second element

    + 1 byte for the single quote

    + 1 byte for the right parenthesis

    47 bytes

    The following are two samples of data for the COL003 column:

    Sample data 1: ('3105551234','3105551234')

    Sample data 2: ('''''''''''''''''''''','''''''''''''''''''''')

    Sample data 1 contains 2 elements of phone numbers. Sample data 2 contains 2 elements of all single quote characters.

    In the above example, the COL004 column is defined as VARCHAR(17), because it's the maximum representation for the COL004 column in the table.

    The following is the calculation for the maximum representation for the COL004 column:

     

    1 byte for the left parenthesis

    + 1 to 7 bytes for the first element

    + 1 byte for the comma

    + 1 to 7 bytes for the second element

    + 1 byte for the right parenthesis

    17 bytes

    The following are two samples of data for the COL004 column:

    Sample data 1: (‑123.45,888.10)

    Sample data 2: (+123.45,‑888.10)

    In the above example, the COL005 column is defined as VARCHAR(25), because it's the maximum representation for the COL005 column in the table.

    The following is the calculation for the maximum representation for the COL005 column:

    1 byte for the left parenthesis

    + 1 to 11 bytes for the first element

    + 1 byte for the comma

    + 1 to 11 bytes for the first element

    + 1 byte for the right parenthesis

    25 bytes

    The following are two samples of data for the COL005 column:

    Sample data 1: (‑2147483648,+2147483647)

    Sample data 2: (0,0)

    Use the Teradata SQL "HELP TYPE" command to find out the maximum length for the ARRAY data type. For example, the information for the sample PHONENUMBERS_ARY, DECIMAL_ARY, and INTEGER_ARY ARRAY data types can look like as follows:

    help type PHONENUMBERS_ARY;
     
     *** Help information returned. One row.
     *** Total elapsed time was 1 second.
       Name PHONENUMBERS_ARY
        Internal Type A1
        External Type CV
        Max Length            47
        Array(Y/N) Y
        Dimensions             1
        Element Type CF
        UDT Name ?
        Array Scope [1:2]
        Total Digits    ?
        Fractional Digits    ?

     

    Contains Lob N
        Ordering F
        Ordering Category M
        Ordering Routine LOCAL
        Cast N
        Transform Y
        Method Y
        Char Type  1
    HELP TYPE DECIMAL_ARY;
     
     *** Help information returned. One row.
     *** Total elapsed time was 1 second.
       Name DECIMAL_ARY
        Internal Type A1
        External Type CV
        Max Length            17
        Decimal Total Digits    ?
        Decimal Fractional Digits    ?
        Contains Lob N
        Ordering F
        Ordering Category M
        Ordering Routine LOCAL
        Cast N
        Transform Y
        Method Y
        Char Type  1
        Array(Y/N) Y
        Dimensions             1
        Element Type D
        UDT Name ?
        Array Scope [1:2]
    HELP TYPE INTEGER_ARY;
     
     *** Help information returned. One row.
     *** Total elapsed time was 1 second.
        Name INTEGER_ARY
        Internal Type A1
        External Type CV
        Max Length            25
        Decimal Total Digits    ?
        Decimal Fractional Digits    ?
        Contains Lob N
        Ordering F
        Ordering Category M
        Ordering Routine LOCAL
        Cast N
        Transform Y
        Method Y
        Char Type  1
        Array(Y/N) Y
        Dimensions             1
        Element Type I
        UDT Name ?
        Array Scope [1:2]

    As indicated in the returned information from the HELP TYPE command, the maximum length for the sample PHONENUMBERS_ARY ARRAY data type is 47 bytes. The maximum length for the sample DECIMAL_ARY ARRAY data type is 17 bytes. The maximum length for the sample INTEGER_ARY ARRAY data type is 25 bytes.

     

    For more information about the external representations for the ARRAY data type, see SQL Data Types and Literals (B035‑1143).

    Specifying DECIMAL Data Types

    The following input length and field descriptions apply for the DECIMAL data type specifications which make in the datadesc parameter.

    DECIMAL (x) and DECIMAL (x,y)

  • Length: 1, 2, 4, 8, or 16 bytes (network); packed decimal (mainframe)
  • Description: 128‑bit double precision floating point
  • For more information on the DECIMAL data type, see SQL Data Types and Literals (B035‑1143).

    Specifying Period Data Types

    A period is an anchored duration. It represents a set of contiguous time granules within that duration. A period is implemented using a Period data type. Each period consists of two elements:

  • BEGIN (the beginning element)
  • END (the ending element)
  • The element type is one of the following DateTime data types.

  • PERIOD(DATE)
  • PERIOD(TIME[(n)])
  • PERIOD(TIME[(n)] WITH TIME ZONE)
  • PERIOD(TIMESTAMP[(n)])
  • PERIOD(TIMESTAMP[(n)] WITH TIME ZONE)
  • For more information on the PERIOD data type, see SQL Data Types and Literals (B035‑1143).

    Using ANSI/SQL DateTime Data Types

    When the DATEFORM command is used to specify ANSIDATE as the DATE data type, FastExport internally converts each DATE field to a CHAR(10) field. All ANSI/SQL DateTime TIME, TIMESTAMP, and INTERVAL data types must be converted to fixed‑length CHAR data types to specify column/field names in a FastExport FIELD command.

    Table 34 provides the conversion specifications and format examples for each ANSI/SQL DateTime specification.

    Table 34 describes the ANSI/SQL Date Time Specifications.

     

    Table 34: ANSI/SQL DateTime Specifications 

    DATE

     

    Convert to:

    CHAR(10)

    Format:
    Example:

    yyyy/mm/dd
    1998/01/01

    TIME

    TIME (n)

    Where n is the number of digits after the decimal point, 0 through 6. (Default = 6.)

    Convert to:

    CHAR(8 + n + (1 if n > 0, otherwise 0))

    Format (n = 0):
    Example:

    hh:mm:ss
    11:37:58

    Format: (n = 4):
    Example:

    hh:mm:ss.ssss
    11:37:58.1234

    TIMESTAMP

    TIMESTAMP (n)

    Where n is the number of digits after the decimal point, 0 through 6. (Default = 6.)

    Convert to:

    CHAR(19 + n + (1 if n > 0, otherwise 0))

    Format (n = 0):
    Example:

    yyyymmdd hh:mm:ss
    1998‑09‑04 11:37:58

    Format (n = 4):
    Example:

    yyyymmdd hh:mm:ss.ssss
    1998‑09‑04 11:37:58.1234

    TIME WITH TIME ZONE

    TIME (n) WITH TIME ZONE

    Where n is the number of digits after the decimal point, 0 through 6. (Default = 6.)

    Convert to:

    CHAR(14 + n + (1 if n > 0, otherwise 0))

    Format (n = 0):
    Example:

    hh:mm:ss{±}hh:mm
    11:37:58‑08:00

    Format (n = 4):
    Example:

    hh:mm:ss.ssss {±} hh:mm
    11:37:58.1234‑08:00

    TIMESTAMP WITH TIME ZONE

    TIMESTAMP (n) WITH TIME ZONE

    Where n is the number of digits after the decimal point, 0 through 6. (Default = 6.)

    Convert to:

    CHAR(25 + n + (1 if n > 0, otherwise 0))

    Format (n = 0):
    Example

    yyyymmdd hh:mm:ss{±}hh:mm
    1998‑09‑24 11:37:58+07:00

    Format (n = 4):
    Example:

    yyyymmdd hh:mm:ss.ssss{±}hh:mm
    1998‑09‑24 11:37:58.1234+07:00

    INTERVAL YEAR

    INTERVAL YEAR (n)

    Where n is the number of digits, 1 through 4. (Default = 2.)

    Convert to:

    CHAR(n)

    Format (n = 2):
    Example:

    yy
    98

    Format (n = 4):
    Example:

    yyyy
    1998

    INTERVAL YEAR TO MONTH

    INTERVAL YEAR (n) TO MONTH

    Where n is the number of digits, 1 through 4. (Default = 2.)

    Convert to:

    CHAR(n + 3)

    Format (n = 2):
    Example:

    yymm
    98‑12

    Format (n = 4):
    Example:

    yyyymm
    1998‑12

    INTERVAL MONTH

    INTERVAL MONTH (n)

    Where n is the number of digits, 1 through 4. (Default = 2.)

    Convert to:

    CHAR(n)

    Format (n = 2):
    Example:

    mm
    12

    Format (n = 4):
    Example:

    mmmm
    0012

    INTERVAL DAY

    INTERVAL DAY (n)

    Where n is the number of digits, 1 through 4. (Default = 2.)

    Convert to:

    CHAR(n)

    Format (n = 2):
    Example:

    dd
    31

    Format (n = 4):
    Example:

    dddd
    0031

    INTERVAL DAY TO HOUR

    INTERVAL DAY (n) TO HOUR

    Where n is the number of digits, 1 through 4. (Default = 2.)

    Convert to:

    CHAR(n + 3)

    Format (n = 2):
    Example:

    dd hh
    31 12

    Format (n = 4):
    Example:

    dddd hh
    0031 12

    INTERVAL DAY TO MINUTE

    INTERVAL DAY (n) TO MINUTE

    Where n is the number of digits, 1 through 4. (Default = 2.)

    Convert to:

    CHAR(n + 6)

    Format (n = 2):
    Example:

    dd hh:mm
    31 12:59

    Format (n = 4):
    Example:

    dddd hh:mm
    0031 12:59

    INTERVAL DAY TO SECOND

    INTERVAL DAY (n) TO SECOND

    INTERVAL DAY TO SECOND (m)

    INTERVAL DAY (n) TO SECOND (m)

    Where:

  • n is the number of digits, 1 through 4. (Default = 2.)
  • m is the number of digits after the decimal point, 0 through 6. (Default = 6.)
  • Convert to:

    CHAR(n + 9 + m + (1 if m > 0, 0 otherwise))

    Format (n = 2, m = 0):
    Example:

    dd hh:mm:ss
    31 12:59:59

    Format (n = 4, m = 4):
    Example:

    dddd hh:mm:ss.ssss
    0031 12:59:59:59.1234

    INTERVAL HOUR

    INTERVAL HOUR (n)

    Where n is the number of digits, 1 through 4. (Default = 2.)

    Convert to:

    CHAR(n)

    Format (n = 2):
    Example:

    hh
    12

    Format (n = 4):
    Example:

    hhhh
    0012

    INTERVAL HOUR TO MINUTE

    INTERVAL HOUR (n) TO MINUTE

    Where n is the number of digits, 1 through 4. (Default = 2.)

    Convert to:

    CHAR(n + 3)

    Format (n = 2):
    Example:

    hh:mm
    12:59

    Format (n = 4):
    Example:

    hhhh:mm
    0012:59

    INTERVAL HOUR TO SECOND

    INTERVAL HOUR (n) TO SECOND

    INTERVAL HOUR TO SECOND (m)

    INTERVAL HOUR (n) TO SECOND (m)

    Where:

  • n is the number of digits, 1 through 4. (Default = 2.)
  • m is the number of digits after the decimal point, 0 through 6. (Default = 6.)
  • Convert to:

    CHAR(n + 6 + m + (1 if m > 0, 0 otherwise))

    Format (n = 2, m = 0):
    Example:

    hh:mm:ss
    12:59:59

    Format (n = 4, m = 4):
    Example:

    hhhh:mm:ss.ssss
    0012:59:59.1234

    INTERVAL MINUTE

    INTERVAL MINUTE (n)

    Where n is the number of digits, 1 through 4. (Default = 2.)

    Convert to:

    CHAR(n)

    Format (n = 2):
    Example:

    mm
    59

    Format (n = 4):
    Example:

    mmmm
    0059

    INTERVAL MINUTE TO SECOND

    INTERVAL MINUTE (n) TO SECOND

    INTERVAL MINUTE TO SECOND (m)

    INTERVAL MINUTE (n) TO SECOND (m)

    Where:

  • n is the number of digits, 1 through 4. (Default = 2.)
  • m is the number of digits after the decimal point, 0 through 6. (Default = 6.)
  • Convert to:

    CHAR(n + 3 + m + (1 if m > 0, 0 otherwise))

    Format (n = 2, m = 0):
    Example:

    mm:ss
    59:59

    Format (n = 4, m = 4):
    Example:

    mmmm:ss.ssss
    0059:59.1234

    INTERVAL SECOND

    INTERVAL SECOND (n)

    INTERVAL SECOND (n,m)

    Where:

  • n is the number of digits, 1 through 4. (Default = 2.)
  • m is the number of digits after the decimal point, 0 through 6. (Default = 6.)
  • Convert to:

    CHAR(n + m + (1 if m > 0, 0 otherwise))

    Format (n = 2, m = 0):
    Example:

    ss
    59

    Format (n = 4, m = 4):
    Example:

    ssss.ssss
    0059.1234

    FILLER

    Purpose  

    The FILLER command specifies a field that is not sent to Teradata Database as part of the input record that provides data values for the constraint parameters of the SELECT statement.

    Syntax  

    where:

     

    Syntax Element

    Description

    fieldname

    Optional name for the input record field

    The fieldname specification is required only if the field is referred to by the nullexpr condition of a FIELD command.

    startpos

    Starting position of the specified field in an input data record

    startpos can be specified as an:

  • Unsigned integer, which is the character position starting with 1
  • Asterisk (*), which means the next available character position beyond the preceding field
  • Note: When using the CONTINUEIF condition of the LAYOUT command to continue input records, a startpos specified by an integer value refers to a character position in the final concatenated record from which the continuation indicator has been removed.

    datadesc

    Type and length of data in the field

    The datadesc specification can be any of the data type phrases shown in Utilities (B035‑1102).

    This description is used to generate the data description for this field in the USING modifier for the SELECT statement.

    Usage Notes  

    Table 35 describes the things to consider when using the FILLER command.

     

    Table 35: FILLER Command Usage Notes 

    Topic

    Usage Notes

    Command Placement and Frequency

    A FILLER command must be preceded by a LAYOUT command.

    One or more FILLER commands, or a combination of FILLER commands and FIELD commands, define the composition of the input data record to supply values for the USING modifier of the SELECT statement.

    IF, ELSE, and ENDIF

    Purpose  

    The IF, ELSE, and ENDIF commands provide conditional control of execution processes.

    Syntax  

    Usage Notes  

    Table 36 describes the things to consider when using the IF, ELSE, and ENDIF commands.

     

    Table 36: IF, ELSE and END IF Command Usage Notes 

    Topic

    Usage Notes

    ELSE Clause

    Use the optional ELSE clause to execute commands when the condition is evaluated as false.

    Nesting IF Commands

    FastExport supports the nesting of IF commands to a level of 100.

    Numeric Results of the Conditional Expression

    The conditional expression is an expression that can be evaluated as either true or false.

    When evaluation of the expression returns a numeric result:

  • Zero is interpreted as false
  • Nonzero results are interpreted as true
  • Variables in the IF Expression

    The conditional expression can be either user‑defined variables or predefined system variables.

    Variable Substitutions

    Any ELSE or ENDIF commands must be present in their entirety and cannot be composed simply of variables in need of substitution.

    Commands and statements following an IF, ELSE, or ENDIF structure that are not executed are not parsed and do not have their variables substituted.

    Example  

    FastExport is case sensitive when comparing &SYS system variables. In this example, the RUN FILE command does not execute because the substituted values returned are all capitals:

    0003 .IF ’&SYSDAY’ = ’Fri’ THEN;
    14:10:28  FRI MAY 09, 1993
    UTY2402 Previous statement modified to:
    0004 .IF ’FRI’ = ’Fri’ THEN;
    0005.RUN FILE UTNTS38;
    0006 .ENDIF;  
     

    Always consider this factor when creating a script to force the execution of a predetermined sequence of events. If ‘FRI’ is substituted in line 0003, the compare would work and the RUN FILE command would execute.

    Example  

    In the following example, the user has created the table named &TABLE and a variable named CREATERC, into which is set the system return code resulting from the execution of the CREATE TABLE statement: .SET CREATERC TO &SYSRC;

    .SET CREATERC TO &SYSRC;
    .IF &CREATERC = 3803 /* Table &TABLE exists */ THEN;
    .RUN FILE RUN01;
    .ELSE;
    .IF &CREATERC <> 0 THEN;
    .LOGOFF &CREATRC;
    .ENDIF;
    .ENDIF:
     

    If the table name has not already been used, and the return code is not zero, the return code evaluates to an error condition and the job logs off with the error code displayed.

    IMPORT

    Purpose  

    The IMPORT command defines the client file that provides the USING data values for the FastExport SELECT statement.

    Syntax

    The IMPORT command syntax depends on whether the FastExport utility is running on a mainframeattached or networkattached client system. Several of the syntax elements are common to both configurations, while others are specific to each.

    For Mainframe‑Attached Client Systems

    For Network‑Attached Client Systems

    where:

     

    Syntax Element

    Description

    INFILE fileid

    Input file on the client system

    The client system DD or equivalent statement specifies a file:

  • In UNIX OS and Windows, the fileid is the path name for a file
  • If the path name has embedded white space characters, the path name must be enclosed in single or double quotes.

    If the path name is enclosed by single quotation marks and there is an embedded single quotation mark, then an escape character (single quotation mark) needs to precede the embedded single quotation mark. Likewise, if the path name is enclosed by double quotation marks and there is an embedded double quotation mark, then an escape character (double quotation mark) needs to precede the embedded double quotation mark.

    If the path name contains single or double quotation marks, it should be enclosed in either single or double quotation marks.

  • In z/OS, the fileid is a DDNAME.
  • fileid must refer to a regular file. Specifically, pipes are not supported.
  • AXSMOD name

    Name of the access module file to be used to import data:

  • Named Pipes Access Module
  • Teradata WebSphere® MQ Access Module (client version)
  • Teradata WebSphere®MQ Access Module (server version)
  • See the Teradata Tools and Utilities Access Module Reference (B035‑2425) for the name of the access module file for each platform.

    A shared library file name can be used if a custom access module exists.

    The AXSMOD option is not required for importing from:

  • Disk files on either network‑attached or mainframe‑attached client systems
  • Magnetic tape files on mainframe‑attached client systems
  • It is required for importing from magnetic tape and other types of files on network‑attached client systems.

    For more information about specific Teradata access modules, see the Teradata Tools and Utilities Access Module Reference (B035‑2425).

    initstring

    Optional initialization string for the access module

    The initialization string can contain double quotes, but not single quotes.

    INMOD modulename

    Optional user‑written routine for preprocessing the input data

    In z/OS, modulename is the name of a load module. On UNIX and Windows client systems, it is the pathname for the INMOD executable code file.

    When both the INFILE fileid and the INMOD modulename parameters are specified, FastExport reads the input file and passes the data to the INMOD routine for preprocessing.

    If the INFILE fileid parameter is not specified, FastExport expects the INMOD routine to provide the input data record.

    FastExport provides two parameters to the named routine, as described in “FastExport/INMOD Routine Interface” on page 55.

    Note: On some versions of UNIX operating systems, ./ prefix characters may have to be added to the INMOD modulename specification if the module is in the current directory.

    Note: If INMOD module output messages to stdout, the character set that INMOD uses is independent of the character set that Teradata FastExport uses; the display on stdout can be of mixed character sets. For example, IMMOD can output messages in ASCII and Teradata FastExport can output messages in UTF‑16.

    USING (parms)

    Character string containing parameters can be passed to the INMOD routine:

  • The parms string can include one or more character strings, each delimited on either end by either an apostrophe or a quotation mark
  • The maximum size of the parms string is 1K bytes
  • Parentheses within delimited character strings have the same syntactical significance as alphabetic characters
  • Before passing the parms string to the INMOD routine, FastExport replaces the following with a single blank character:
  • Each comment
  • Each consecutive sequence of white space characters, such as blank, tab, and so on, that appears outside of delimited strings
  • The entire parms string must be enclosed in parentheses and, on mainframe‑attached client systems, the parentheses are included in the string passed to the INMOD routine
  • Note: The parms string must be FDLINMOD for INMOD routines written for the prior Pascal version of FastLoad (program FASTMAIN).

    FORMAT…

    Record format of the input file, where:

  • FASTLOAD specifies that each record is a two‑byte integer, n, followed by n bytes of data, followed by an end‑of‑record marker, either X '0A' or X '0D'
  • BINARY specifies that each record is a two‑byte integer, n, followed by n bytes of data
  • TEXT specifies that each record is an arbitrary number of bytes followed by an end‑of‑record marker, either X '0A' or X '0D'
  • Note: TEXT format does not support numeric data. Do not specify TEXT if the MLSCRIPT option of an EXPORT command is also used.

  • UNFORMAT specifies that each record is imported as it is received from CLIv2 without any client modifications
  • VARTEXT specifies that each record is in variable‑length text record format, with each field separated by a delimiter character
  • Note: All above FORMAT options apply to UNIX and Windows platforms. The VARTEXT option applies to mainframe. The default FORMAT option for UNIX and Windows platforms is FASTLOAD. The default FORMAT for mainframe is “use record boundaries “, meaning the input data is read record‑by‑record and the LAYOUT is applied to each record.

    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.

    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

    NOSTOP

    [Optional] Keyword specification that inhibits the FastExport termination in response to an error condition associated with a variable‑length text record

    LAYOUT layoutname

    Identifier of the file layout description, as specified by a prior LAYOUT command

    EFILE <efilename>

    Optional keyword specification that writes input data records that produce errors to the user specified error file. If the user doesn't specify the error file name, the default error destination is the standard error file.

    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 specified 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:

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

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

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

     

    Table 37: IMPORT Command Usage Notes 

    Topic

    Usage Notes

    Command Frequency and Placement

    If the export task uses a LAYOUT command, then an IMPORT command is required, and it must appear after the LAYOUT command.

    Data Type Specifications

    When using the VARTEXT specification, VARCHAR, VARBYTE, and LONG VARCHAR are the only valid data type specifications which can be used in the FastExport layout FIELD command and FILLER command.

    For additional information on data types, see Table 32 on page 95.

    Error Record Handling

    When FastExport encounters an error condition in an input record, it normally discards the record and terminates. In loading variable‑length text records, either or both of these functions can be inhibited by specifying the options:

  • DISPLAY ERRORS
  • NOSTOP
  • By specifying both options and redirecting STDERR to a file location instead of the terminal screen, the FastExport job will run to completion and save all the error records. Then it can be manually modify and loaded.

    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 FastExport layout FIELD command and FILLER command.

    If it is less, FastExport generates an error message. If it is more, Teradata Database ignores the extra fields.

    The last field of a record does not have to end with a delimiter character. It can end with a delimiter character, but it is not required.

    Multiple Physical Records

    If the FastExport task reads the input file and constructs a logical record from multiple physical records, this is performed before the physical record is passed to the INMOD routine. The INMOD routine is invoked only one time for the generation of each USING record.

    Null Fields

    Two consecutive delimiter characters direct FastExport 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 there is at least one more field to be processed, then FastExport nulls the field corresponding to the next one to be processed, as defined in the layout FIELD command and FILLER command.

    VARTEXT Records

    When VARTEXT is specified, FastExport 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.

    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 nonlabeled, as supported by the operating system.

    Example Using the INFILE and FORMAT Specifications

    This example specifies that the using data for the SELECT statement is contained in /home/fexpuser/tests/data1 and that the format of each record is binary.

    .IMPORT INFILE /home/fexpuser/tests/data1
    FORMAT BINARY
    Layout layl;

    Example Specifying an INMOD Routine

    The following example for a UNIX client system runs an INMOD routine that has been compiled and linked as feimod.so:

    .IMPORT INMOD ./feimod.so LAYOUT lay1;

    The following example for a Windows client system runs the same INMOD routine that has been compiled and linked as feimod.dll:

    .IMPORT INMOD ./feimod.dll LAYOUT lay1;

    LAYOUT

    Purpose  

    The LAYOUT command, used with an immediately following sequence of FIELD and FILLER commands, specifies the layout of the file that provides data values for the USING modifier of the SELECT statement.

    Syntax  

    where:

     

    Syntax Element

    Description

    layoutname

    Name assigned to the layout for reference by one or more subsequent IMPORT commands

    A layoutname must obey the same construction rules as Teradata SQL column names.

    CONTINUEIF condition

    Conditional phrase in which condition is of the form:

       position = value

    where:

  • position is an unsigned integer (never an asterisk) that specifies the starting character position of the field of every input record that contains the continuation indicator
  • value is the continuation indicator specified as a character constant or a string constant. FastExport uses the length of the constant as the length of the continuation indicator field.
  • Note: The condition specified as position = value is case sensitive. Always specify the correct character case for this parameter.

    If the conditional phrase is true, then FastExport forms a single record by concatenating the next input record at the end of the current record. (The current record is the one most recently obtained from the external data source.)

    If the conditional phrase is false, then FastExport uses the current input record either by itself or as the last of a sequence of concatenated records.

    Note: Regardless of whether the condition evaluates to true or false, FastExport removes the tested string (the continuation indicator field) from each record.

    INDICATORS

    Condition that the input records defined by this LAYOUT command are in indicator mode

    That is, the first n bytes of each record are indicator bytes, where n is the rounded up integer quotient of the number of fields defined by the LAYOUT command, divided by 8.

    If this option is specified, the following FIELD commands must accurately define each field of the input record. The number of the defined fields and fillers is used to calculate the number of bytes of indicator data that are in each input record.

    Usage Notes  

    Table 38 describes the things to consider when using the LAYOUT command.

     

    Table 38: LAYOUT Command Usage Notes 

    Topic

    Usage Notes

    Command Frequency and Placement

    A LAYOUT command specification must be referenced by each IMPORT command in the FastExport job script. In all cases, the LAYOUT command must be presented before an IMPORT command that references it. Each LAYOUT command must be immediately followed by a series of FIELD and FILLER commands that define the composition of a logical record.

    Using the same LAYOUT command in Multiple FastExport Tasks

    The same layoutname specification can be referenced in more than one FastExport task, provided that:

  • Each task is delimited by BEGIN EXPORT and END EXPORT commands
  • The LAYOUT command appears before any IMPORT command that references it
  • 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 networkbased platforms.

    Syntax

    where:

     

    Syntax Element

    Description

    logdata_string
    ‘logdata_string’

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

    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. To specify a string containing white space or other special characters, enclose the data string in single quotes.

    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 networkattached 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 disconnects all active sessions from Teradata Database and terminates FastExport.

    Syntax  

    where:

     

    Syntax Element

    Description

    retcode

    [Optional] Completion code to be returned to the client operating system

    If a retcode is not specified, FastExport returns the appropriate terminating return code.

    Usage Notes  

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

     

    Table 39: LOGOFF Command Usage Notes 

    Topic

    Usage Notes

    Optional Completion Code

    The optional completion code value, retcode, can be specified as a conditional or an arithmetic expression, evaluated to a single integer.

    The LOGOFF command processes whenever the highest return code reached was no more than 04 (warning). Any return code other than 00 or 04 terminates the FastExport job.

    If a serious error terminates the program before the LOGOFF command is processed, the return code output is the value generated by the error condition rather than the retcode value specified as a LOGOFF command option.

    Terminating Return Codes

    When a FastExport job terminates, and an optional retcode value is not specified, the utility returns a code indicating the way the job completed:

  • Code 0 – job completed normally
  • Code 4 – a warning condition occurred. Warning conditions do not terminate the job.
  • Code 8 – a user error, such as a syntax error in the FastExport job script, terminated the job
  • Note: The following Teradata Database error messages produce a return code of 08:

    3600       3692       3695
  • Code 12 – a fatal error terminated the job. A fatal error is any error other than a user error.
  • Code 16 – no message destination is available
  • For a complete description of Teradata Database error messages, refer to Messages (B035‑1096).

    When Permitted

    The LOGOFF command is permitted at any point in the input script. It logs off immediately.

    Automatic Logoff

    FastExport performs an automatic logoff function if:

  • All of the export data from Teradata Database has been processed without encountering a LOGOFF command
  • The program fails because of an error
  • Example  

    The following example uses a logical expression as the retcode specification:

    .LOGOFF &SYSRC > 8

    If the expression is true, the retcode is 1. If false, it is 0.

    LOGON

    Purpose  

    The LOGON command establishes a Teradata SQL session with Teradata Database.

    The ACCEPT and SET commands are valid commands preceding LOGON and LOGTABLE commands.

    Note: To prevent the password from appearing in the script, use Teradata Wallet. Refer to Security Administration (B035‑1100) and the appropriate installation guide for more information.

    Syntax  

    Standard LOGON Syntax

    Note: On the z/OS platform, with the use of the User Logon Exit routine in TDP, the user name is not required. See the Teradata Director Program Reference (B035‑2416) for more information.

    Single Sign‑on LOGON Syntax

    Note: On the Windows platform, if logon encryption is enabled on the gateway, then single signon is disabled on the client and standard logon syntax should be used instead

    where:

     

    Syntax Element

    Description

    acctid

    Account identifier associated with the username

    An account identifier can have up to 30 bytes.

    If an acctid is not specified, FastExport uses the default identifier defined when the user was created.

    password

    Password associated with the username

    A password can have up to 30 bytes.

    Note: Passwords that contain special characters must be enclosed in double quotes.

    tdpid

    Optional character string that identifies the name of a TDP

    If the tdpid is not specified, FastExport uses the default TDP established by the system administrator.

    Note: For mainframe‑attached systems, the tdpid string must be in the form:

              TDPn

    where n is the TDP identifier.

    username

    User identifier of up to 30 bytes

    Note: The period preceding the LOGON command is optional.

    Usage Notes  

    Table 40 describes the things to consider when using the LOGON command.

     

    Table 40: LOGON Command Usage Notes 

    Topic

    Usage Notes

    Command Frequency and Placement

    A LOGON command is required for each invocation of the FastExport utility.

    One LOGON command is allowed for each invocation of the FastExport utility, and it must precede any other FastExport commands except RUN FILE command and LOGTABLE command.

    Logon Parameters

    For standard LOGON, the parameters (tdpid, username, password, and acctid) are used in all sessions established with Teradata Database. The LOGON command may occur only once.

    For single sign‑on LOGON, if the Gateway to Teradata Database is configured to use single sign‑on (SSO), and the Teradata client machine has already been logged on, the machine name, user name, and password are not required in the LOGON command. The user name and password combination specified when the Teradata client machine was logged on are authenticated via network security for a SSO such that valid Teradata users will be permitted to log on to Teradata Database. The use of SSO is strictly optional, unless the Gateway has been configured to accept only SSO‑style logons.

    To connect to a Teradata Database other than the one currently logged on, the TDPid must be included in the LOGON command. If the TDPid is not specified, the default contained in clispb.dat will be used. For information about setting defaults, see the Teradata Call-Level Interface Version 2 Reference for Network-Attached Systems (B035‑2418).

    To be interpreted correctly, the TDPid must be followed by the slash separator (‘/’), to distinguish the TDPid from a Teradata Database username. For example, to connect to slugger, enter one of the following:

    .LOGON slugger/;

    .LOGON slugger/,,'acctinfo';

    If an account ID is to be used, the optional account ID must be specified in the LOGON command.

    Using LOGON With the LOGTABLE Command

    Both the LOGON and LOGTABLE commands are required.

    LOGON and LOGTABLE commands may appear in any order, but must precede other commands except RUN FILE commands used to identify the file containing the LOGON command.

    If the LOGON command is entered first, FastExport warns that the LOGTABLE command is also required.

    Note: When Teradata FastExport attempts to connect the Main SQL session the first time and the Teradata Database is down, Teradata FastExport displays an error message and terminates.

    Note: When Teradata FastExport attempts to connect the Auxiliary SQL session, or the data sessions and the Teradata Database is down. Teradata FastExport will retry to connect 16 times; if the Teradata Database is still down, Teradata FastExport displays an error message and terminates.

    Example  

    The following example presents both the LOGON and LOGTABLE commands as they typically occur:

    .logtable logtable001;
    .logon tdpx/me,paswd;

    LOGTABLE

    Purpose  

    The LOGTABLE command specifies a restart log table for the FastExport checkpoint information. FastExport uses the information in the restart log table to restart jobs that are halted because of a Teradata Database or client system failure.

    The ACCEPT and SET commands are valid commands preceding LOGON and LOGTABLE commands.

    Syntax  

    where:

     

    Syntax Element

    Description

    dbname

    Name of the database under which the log table exists

    The default is the database name associated with the username specified in the LOGON command. FastExport searches for or creates the table (tname) in that database unless another database name is specified in this option.

    tname

    Name of the restart log table

    Usage Notes  

    Table 41 describes the things to consider when using the LOGTABLE command.

     

    Table 41: LOGTABLE Command Usage Notes 

    Topic

    Usage Notes

    Using LOGTABLE with the LOGON command

    Both the LOGTABLE and LOGON commands are required.

    LOGTABLE and LOGON commands may appear in any order, but must precede other commands except RUN commands used to identify the file containing the LOGON command.

    If the LOGON command is entered first, FastExport warns that the LOGTABLE command is also required.

    The Restart Log Table

    The table specified as the FastExport restart log table does not have to be fully qualified.

    Note: It is critical that the restart log table not be shared between two or more FastExport jobs. Each FastExport job must have its own restart log table, to ensure proper operation. Failure to use a distinct log table for each FastExport job will cause unexpected results.

    Specifying a New or Existing Table

    If a table is specified that does not exist, FastExport creates the table and uses it as the restart log during this invocation of the utility.

    If a table is specified that already exists, then FastExport checks the table to determine whether the current invocation of the utility is a restart operation.

    Maintaining the Restart Log Table

    FastExport automatically maintains the restart log table. If the table is manipulated in any way, it will invalidate the restart capability.

    The only valid user maintenance function is to drop the restart log table. Never delete rows from the table.

    Changing the dbname Specification

    The LOGTABLE dbname option must be used to change the dbname specification for a FastExport operation. A subsequent Teradata SQL DATABASE statement, which must appear after the LOGTABLE commands and LOGON commands, cannot be used to change the dbname specification.

    Required Privilege

    The following privileges on the database containing the specified restart log table are required:

  • CREATE TABLE
  • INSERT
  • UPDATE
  • SELECT
  • Example  

    The following example presents both the LOGTABLE command and the LOGON command as they typically occur:

    .logtable Mine.Logtable001;
    .logon tdpx/me,paswd;

    ROUTE MESSAGES

    Purpose  

    The ROUTE MESSAGES command identifies an alternate destination for the report output produced by the FastExport utility. One or more ROUTE MESSAGES command may be included anywhere in the command stream.

    Syntax  

    where:

     

    Syntax Element

    Description

    ECHO

    Additional destination, with a fileid specification

    For example, use the ECHO keyword to specify that messages be captured in a file (fileid2) while still being written to the terminal.

    Note: The ECHO OFF specification cancels the additional file specification of a previously established ECHO destination.

    fileid1 and fileid2

    Alternate message destinations in the external system:

  • In z/OS, the fileid is a DDNAME.
  • In UNIX OS and Windows, the fileid is the path name for a file
  • If the path name has embedded white space characters, must enclose the entire path name in single or double quotes.

    If the path name is enclosed by single quotation marks and there is an embedded single quotation mark, then an escape character (single quotation mark) needs to precede the embedded single quotation mark. Likewise, if the path name is enclosed by double quotation marks and there is an embedded double quotation mark, then an escape character (double quotation mark) needs to precede the embedded double quotation mark.

    If the path name contains single or double quotation marks, it should be enclosed in either single or double quotation marks.

    If the same destination with both fileid1 and fileid2 parameters is specified, FastExport duplicates the messages at each destination.

    Usage Notes  

    Table 42 describes the things to consider when using the ROUTE MESSAGES command.

     

    Table 42: ROUTE MESSAGES Command Usage Notes 

    Topic

    Usage Notes

    Specifying the System Console/Standard Output Device

    Use the asterisk (*) character as the fileid1 or fileid2 specifications to route 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 25.

    Default Message Destinations

    If the ROUTE MESSAGES command is not used, FastExport writes output messages to:

  • DDNAME SYSPRINT in z/OS
  • stdout in UNIX OS and Windows
  • z/OS fileid Usage Rules

    If a DDNAME is specified, FastExport writes messages to 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, the tape may be either labeled or nonlabeled, as supported by the operating system.

    Example  

    In the following example, the messages are written to the file designated by OUTPUT from this point unless redirected by another ROUTE MESSAGES command:

    .ROUTE MESSAGES FILE OUTPUT;

    Note: On UNIX and Windows platforms, if the same outfilename is used to redirect stdout and as the fileid in a ROUTE MESSAGES WITH ECHO command, the results written to outfilename may be incomplete due to conflicting writes to the same file.

    RUN FILE

    Purpose  

    The RUN FILE command invokes the specified external file as the current source for utility commands and statements.

    Syntax  

    where:

     

    Syntax Element

    Description

    fileid

    Data source of the external system

    The external system DD (or similar) statement specifies a file:

  • In UNIX OS and Windows, the fileid is the path name for a file
  • If the path name has embedded white space characters, enclose the entire path name in single or double quotes.

    If the path name is enclosed by single quotation marks and there is an embedded single quotation mark, then an escape character (single quotation mark) needs to precede the embedded single quotation mark. Likewise, if the path name is enclosed by double quotation marks and there is an embedded double quotation mark, then an escape character (double quotation mark) needs to precede the embedded double quotation mark.

    If the path name contains single or double quotation marks, it should be enclosed in either single or double quotation marks.

  • In z/OS, the fileid is a DDNAME.
  • IGNORE charpos1 and charpos2

    Start and end character positions of a field in each input record that contains extraneous information. If one of the following is specified:

  • charpos1, then FastExport ignores only the single specified character
  • charpos1 THRU, then FastExport ignores all characters from charpos1 through the end of the record
  • THRU charpos2, then FastExport ignores all characters from the beginning of the record through charpos2
  • charpos1 THRU charpos2, then FastExport ignores all characters from charpos1 through charpos2
  • Usage Notes  

    Table 43 describes the things to consider when using the RUN FILE command.

     

    Table 43: RUN FILE Command Usage Notes 

    Topic

    Usage Notes

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

    z/OS fileid Usage Rules

    If a DDNAME is specified, FastExport 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, the tape may be either labeled or nonlabeled, as supported by the operating system.

    Executing the RUN FILE Command

    After FastExport executes the RUN FILE command, it reads additional commands from the specified source until a LOGOFF command or end‑of‑file condition is encountered, whichever occurs first.

    An end‑of‑file condition automatically causes FastExport to resume reading its commands and DML statements from the previously active source:

  • SYSIN for z/OS
  • stdin (normal or redirected) for UNIX and Windows operating systems
  • Note: SYSIN/stdin remains the active input source after FastExport processes any user‑provided invocation parameters.

    Nested RUN Commands

    The source specified by a RUN FILE command can have up to 16 levels of nested RUN commands.

    SET

    Purpose  

    The SET command assigns a data type and a value to a FastExport utility variable. The SET command is a valid command preceding LOGON and LOGTABLE commands.

    Syntax  

    where:

     

    Syntax Element

    Description

    var

    Name of the FastExport utility variable to be set to the evaluated expression

    Usage Notes  

    Table 44 describes the things to consider when using the SET command.

     

    Table 44: SET Command Usage Notes 

    Topic

    Usage Notes

    Declaring Variables

    Variables need not be declared in advance to be the object of the SET command. If a variable does not already exist, FastExport creates it.

    Variables used to the right of TO in the expression must be declared in advance.

    Changing the
    Data Type

    The SET command also dynamically changes the data type to that of the assigned value if it had already been defined.

    If the expression evaluates to a numeric value, the symbol is assigned an integer value, as in:

       .SET FOONUM TO ‑151 ;

    If the expression is a quoted string, the symbol is assigned a string value, as in:

       .SET FOOCHAR TO ’‑151’ ;

    The minimum and maximum limits for floating point data types are as follows:

    4.0E-75 <=abs(float variable)<7.0E75

    Variable Substitution

    A FastExport variable can be substituted wherever substitution is allowed.

    The utility variable can be substituted wherever substitution is allowed.

    If the expression evaluates to a numeric value, the symbol is assigned an integer value, as in:

    .SET FOONUM TO -151 ;

    If the expression is a quoted string, the symbol is assigned a string value, as in:

    .SET FOOCHAR TO '-151' ;

    The minimum and maximum limits for Floating Point data types are as follows:

    4.0E-75 <=abs(float variable)<7.0E75

    Example  

    Teradata FastExport supports concatenation of variables, using the SET command, such as:

     .SET C TO 1;
     .SET D TO 2;
     .SET X TO &C.&D;

    Example  

    In this example, X evaluates to 12. If a decimal point is added to the concatenated variables, as in:

    .SET C TO 1;
    .SET D TO 2;
    .SET X TO &C..&D;

    X then evaluates to 1.2.

    SYSTEM

    Purpose  

    The SYSTEM command submits an operating system command to the client environment during a FastExport operation.

    Syntax  

    where:

     

    Syntax Element

    Description

    oscommand

    Any legal command in the client operating system

    Usage Notes  

    The SYSTEM command suspends the current FastExport operation to execute the client operating system command.

    When the client operating system command completes, FastExport displays the return code from the invoked command and updates the &SYSRC variable.

    Example  

    The following example deletes the file /home/fexpuser/tests/out1 if it exists. The command string then creates a new /home/fexpuser/tests/out1 file to contain the exported records from the SELECT statement.

    .SYSTEM 'rm -f /home/fexpuser/tests/out1';
    .BEGIN EXPORT;
    SEL * FROM table1;
    .EXPORT OUTFILE /home/fexpuser/tests/out1;
    .END EXPORT;

    VERSION

    Purpose  

    The VERSION command, which is primarily for developer use, displays version information for each utility component, including FastExport, Teradata ICU, Teradata CLI and Teradata Data Connector.

    Syntax  

    Example  

    .version;
    0001 .version;
         EXPVER = EXPCLI   14.10.00.01 2012/05/20
         EXPVER = EXPEXEC  14.10.00.03 2012/05/20
         EXPVER = EXPLANG  14.10.00.01 2012/03/20
         EXPVER = EXPMAIN  14.10.00.01 2012/05/22
         EXPVER = EXPMISC  14.10.00.02 2012/05/22
         EXPVER = EXPNOTFY 14.10.00.00 2012/05/22
         EXPVER = EXPSTMTS 14.10.00.01 2012/04/20
         UT$VER = UMBCNTLR 14.10.00.08 2012/04/25
         UT$VER = UMBDLOC  14.10.00.06 2012/04/17
         UT$VER = UMBEXEC  14.10.00.05 2012/05/15
         UT$VER = UMBOSDEP 14.10.00.03 2012/03/12
         UT$VER = UMBSTFM  14.10.00.04 2012/03/21
         UT$VER = UMBSTIN  14.10.00.00 2012/03/07
         UT$VER = UMBSTLG 14.10.00.02 2012/04/23
         UT$VER = UMBSTOP  14.10.00.03 2012/05/22
         UT$VER = UMBSTQ   14.00.00.01 2011/05/26
         UT$VER = UMBSTX   13.01.00.00 2008/09/10
         UT$VER = UMUCNTIN 14.10.00.03 2012/03/11
         UT$VER = UMUOUT   14.00.00.03 2011/11/04
         UT$VER = UTCLI    14.10.00.12 2012/05/22
         UT$VER = UTYCRMGR 14.10.00.02 2012/03/12
         UT$VER = UTYCVTR  14.10.00.00 2012/04/25
         UT$VER = UTYEXEVL 14.10.00.03 2012/03/26
         UT$VER = UTYEXPRS 13.01.00.00 2008/09/16
         UT$VER = UTYIO    14.10.00.05 2012/03/26
         UT$VER = UTYKUNX  14.10.00.00 2012/04/25
         UT$VER = UTYLOADM 14.10.00.03 2012/03/12
         UT$VER = UTYLOGW  13.01.00.00 2008/09/16
         UT$VER = UTYMBCS  14.10.00.03 2012/03/21
         UT$VER = UTYMISC  14.10.00.03 2012/03/12
         UT$VER = UTYMSG   14.10.00.01 2012/02/15
         UT$VER = UTYMVSQ  13.01.00.00 2008/09/10
         UT$VER = UTYNFY   14.00.00.00 2010/02/08
         UT$VER = UTYPARSR 14.10.00.01 2012/03/06
         UT$VER = UTYSMPR  14.10.00.03 2012/04/25
         UT$VER = UTYVSUB  14.10.00.03 2012/05/21
          CLIV2     : 14.10.00.08
          MTDP      : 14.10.00.05
          MOSIos    : 14.10.00.03
          MOSIDEP   : 14.00.00.04
          OSENCRYPT : N/A
          OSERR     : 14.00.00.00
         ICUVER = TDICU, 14.10a.00.00
         PMVER = Teradata Data Connector, 14.10e.00.00
         PMVER = PMPROCS, 14.10.00.05
         PMVER = PMRWFMT, 14.10.00.01
         PMVER = PMTRCE, 13.10.00.02
         PMVER = PMMM, 13.00.00.01
         PMVER = PMUDDI, 14.10.00.03
         PMVER = DCUDDI, 14.10.00.06
         PMVER = PMHEXDMP, 13.10.00.01
         PMVER = PMUNXDSK, 14.10.00.03