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:
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: |
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: 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.
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: 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 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 |
|
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. |
NOTIFY… |
FastExport implementation of the notify user exit option: |
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 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: 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: |
|
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: 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.
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.
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: There is no general method to determine the optimal number of sessions, because it is dependent on several factors, including, but not limited, to: 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: Possible scenarios for taking advantage of the NOSPOOL mode are as follows: Limitations and functionality: |
Spool/NoSpool (continued...) |
Disadvantages of the NOSPOOL mode:
|
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.
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: 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.
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: 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 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.
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: |
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: 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. |
AXSMOD name |
Name of the access module file to be used to export data. These access modules include: 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. |
’init‑string’ |
[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: 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: For client session character sets other than UTF16, the end‑of‑record marker byte sequence is: For the UTF16 client session character set (in which each character is encoded in two bytes), the end‑of‑record marker byte sequence is: 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. 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: 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. 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.
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: 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: |
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: 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: |
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 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.
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 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: |
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 Mainframe‑Attached 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) |
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) |
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: 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: |
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.
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) 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: The element type is one of the following DateTime data types. 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.
DATE |
|
Convert to: |
CHAR(10) |
Format: |
yyyy/mm/dd |
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): |
hh:mm:ss |
Format: (n = 4): |
hh:mm:ss.ssss |
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): |
yyyy‑mm‑dd hh:mm:ss |
Format (n = 4): |
yyyy‑mm‑dd hh:mm:ss.ssss |
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): |
hh:mm:ss{±}hh:mm |
Format (n = 4): |
hh:mm:ss.ssss {±} hh:mm |
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): |
yyyy‑mm‑dd hh:mm:ss{±}hh:mm |
Format (n = 4): |
yyyy‑mm‑dd hh:mm:ss.ssss{±}hh:mm |
INTERVAL YEAR INTERVAL YEAR (n) |
|
Where n is the number of digits, 1 through 4. (Default = 2.) |
|
Convert to: |
CHAR(n) |
Format (n = 2): |
yy |
Format (n = 4): |
yyyy |
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): |
yy‑mm |
Format (n = 4): |
yyyy‑mm |
INTERVAL MONTH INTERVAL MONTH (n) |
|
Where n is the number of digits, 1 through 4. (Default = 2.) |
|
Convert to: |
CHAR(n) |
Format (n = 2): |
mm |
Format (n = 4): |
mmmm |
INTERVAL DAY INTERVAL DAY (n) |
|
Where n is the number of digits, 1 through 4. (Default = 2.) |
|
Convert to: |
CHAR(n) |
Format (n = 2): |
dd |
Format (n = 4): |
dddd |
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): |
dd hh |
Format (n = 4): |
dddd hh |
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): |
dd hh:mm |
Format (n = 4): |
dddd hh:mm |
INTERVAL DAY TO SECOND INTERVAL DAY (n) TO SECOND INTERVAL DAY TO SECOND (m) INTERVAL DAY (n) TO SECOND (m) |
|
Where: |
|
Convert to: |
CHAR(n + 9 + m + (1 if m > 0, 0 otherwise)) |
Format (n = 2, m = 0): |
dd hh:mm:ss |
Format (n = 4, m = 4): |
dddd hh:mm:ss.ssss |
INTERVAL HOUR INTERVAL HOUR (n) |
|
Where n is the number of digits, 1 through 4. (Default = 2.) |
|
Convert to: |
CHAR(n) |
Format (n = 2): |
hh |
Format (n = 4): |
hhhh |
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): |
hh:mm |
Format (n = 4): |
hhhh:mm |
INTERVAL HOUR TO SECOND INTERVAL HOUR (n) TO SECOND INTERVAL HOUR TO SECOND (m) INTERVAL HOUR (n) TO SECOND (m) |
|
Where: |
|
Convert to: |
CHAR(n + 6 + m + (1 if m > 0, 0 otherwise)) |
Format (n = 2, m = 0): |
hh:mm:ss |
Format (n = 4, m = 4): |
hhhh:mm:ss.ssss |
INTERVAL MINUTE INTERVAL MINUTE (n) |
|
Where n is the number of digits, 1 through 4. (Default = 2.) |
|
Convert to: |
CHAR(n) |
Format (n = 2): |
mm |
Format (n = 4): |
mmmm |
INTERVAL MINUTE TO SECOND INTERVAL MINUTE (n) TO SECOND INTERVAL MINUTE TO SECOND (m) INTERVAL MINUTE (n) TO SECOND (m) |
|
Where: |
|
Convert to: |
CHAR(n + 3 + m + (1 if m > 0, 0 otherwise)) |
Format (n = 2, m = 0): |
mm:ss |
Format (n = 4, m = 4): |
mmmm:ss.ssss |
INTERVAL SECOND INTERVAL SECOND (n) INTERVAL SECOND (n,m) |
|
Where: |
|
Convert to: |
CHAR(n + m + (1 if m > 0, 0 otherwise)) |
Format (n = 2, m = 0): |
ss |
Format (n = 4, m = 4): |
ssss.ssss |
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: 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.
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.
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: |
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 mainframe‑attached or network‑attached 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: 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. |
AXSMOD name |
Name of the access module file to be used to import data: 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: 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). |
init‑string |
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: 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: Note: TEXT format does not support numeric data. Do not specify TEXT if the MLSCRIPT option of an EXPORT command is also used. 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: |
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: |
Usage Notes
Table 37 describes the things to consider when using the IMPORT command.
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: 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 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: 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.
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: |
LOGDATA
Purpose
Supplies parameters to the LOGMECH command beyond those needed by the logon mechanism, such as user ID and password, to successfully authenticate the user. The LOGDATA command is optional. Whether or not parameters are supplied and the values and types of parameters depend on the selected logon method.
LOGDATA is only available on network‑based platforms.
Syntax
where:
Syntax Element |
Description |
logdata_string |
Parameters 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 network‑attached systems.
Syntax
where:
Syntax Element |
Description |
logmech_name |
Defines the logon mechanism. For a discussion of supported logon mechanisms, see Security Administration (B035‑1100) The name is limited to 8 bytes; it is not case‑sensitive. |
Usage Notes
Every session to be connected requires a mechanism name. If none is supplied, a default mechanism can be used instead, as defined on either the server or client system in an XML‑based configuration file.
For more information about logon security, see Security Administration (B035‑1100).
Example
If used, the LOGDATA and LOGMECH commands must precede the LOGON command. The commands themselves may occur in any order.
The following example demonstrates using the LOGDATA, LOGMECH, and LOGON commands in combination to specify the Windows logon authentication method and associated parameters:
.logmech NTLM
.logdata joe@domain1@@mypassword
.logon cs4400s3
LOGOFF
Purpose
The LOGOFF command 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.
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: Note: The following Teradata Database error messages produce a return code of 08: 3600 3692 3695
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: |
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 sign‑on 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.
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.
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: |
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: 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.
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: 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: |
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 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: 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. |
|||
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: |
Usage Notes
Table 43 describes the things to consider when using the RUN FILE command.
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: 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 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: 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.
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 |
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