Usage Notes - FastExport

Teradata® FastExport Reference

Product
FastExport
Release Number
16.20
Published
September 2020
Language
English (United States)
Last Update
2020-09-11
dita:mapPath
lki1527114222329.ditamap
dita:ditavalPath
obe1474387269547.ditaval
dita:id
B035-2410
lifecycle
previous
Product Category
Teradata Tools and Utilities

The following table describes the things to consider when using the EXPORT command.

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 the Record Length and Block Size Specifications table in this section.

Teradata Database data types in the mainframe-attached z/OS environments are described in the Data Type Descriptions (Mainframe-Attached Client Systems) table in this section. 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 Workstation-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 Workstation-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 fileidUsage 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.

The following table 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.

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
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)) 

The following table 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).

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

See SQL Data Types and Literals (B035-1143) for more information.