EXPORT - Basic Teradata Query

Basic Teradata Query Reference

Product
Basic Teradata Query
Release Number
16.10
Published
May 2017
Language
English (United States)
Last Update
2018-06-28
dita:mapPath
wmy1488824663431.ditamap
dita:ditavalPath
Audience_PDF_include.ditaval
dita:id
B035-2414
lifecycle
previous
Product Category
Teradata Tools and Utilities

Purpose

Specifies the name and format of an export file that BTEQ uses to store records resulting from data-returning SQL requests.

The EXPORT command should not be run on Teradata Database console window of the AWS. This is because the output file specifies the location of the file on the current TPA (trusted parallel application) control node and appears on this current node and not on the AWS where it is being run.

Syntax

The following figures show the syntax for both mainframe-attached and workstation-attached systems.





where the following is true:

AXSMOD
Indicates that data is to be exported using an access module. This option is valid only for DATA and INDICDATA types on workstation-attached systems.
When an export file is opened, BTEQ sets one or more of the following attributes for the access module, depending on the session character set used:
  • CHARSET_NAME
  • CHARSET_NUMBER
  • BYTE_ORDER
  • EXPORT_WIDTHS
  • WRITE_BOM
BOM|NOBOM
Determines if a BOM (Byte Order Mark) is inserted at the beginning of a newly exported text file. Specifying “BOM” inserts a UTF-8 or UTF-16 byte order mark, while “NOBOM” does not. This applies to the following:
  • For REPORT or DIF formatted files under Unicode sessions. This option does not apply if the export file already exists and is non-empty.
  • For LDO text files, transferred in deferred mode or non-deferred mode (see the DEFERLIMITS and DEFERTRANS options). XML objects are always transferred in UTF-8, while other character based objects are transferred in the session character set encoding. A BOM is not applied to the main export file, since it is a non-text file.

The default is “BOM.”

DATA
Returns the results of a query to a file for processing by other programs.

BTEQ returns data in Record Mode and stores it in a FastLoad-format output file. Use this format to interchange data between BTEQ, FastLoad, and FastExport. MultiLoad also accepts the FastLoad format.

For more information about Record Mode, refer to RECORDMODE.

DATALABELS
Includes the returned column titles as the first data row in the DIF file. 
The DATALABELS option is not available on workstation-attached systems.
Workstation-attached systems do not use the TITLEDASHES setting when DIF format is being used for exported data.
ddname
Specifies the name used in the z/OS JCL DD statement that defines the file the export process will use to store results from subsequent data-returning SQL requests.
SYSPRINT is not a valid DDNAME for the BTEQ EXPORT command. Exporting to the SYSPRINT file cannot be done.
Although the keyword FILE is valid in place of the keyword DDNAME for mainframe-attached systems, be aware when doing so as the implementation is intended for the DDNAME concept, not the FILE concept.
If an export file name is not defined, the default field for the exported data is FILE ddname.
When the ddname specified for mainframe BTEQ is not associated with a physical, defined data set, the corresponding error message is printed and BTEQ terminates if running batch mode or continues execution if running in interactive mode.
When using a Unicode-type session charset, z/OS BTEQ does not support exporting a file with REPORT or DIF format to a data set with ASA carriage control character supported record format; for example, RECFM=VBA, FA, FBA, and so on.
DEFERLIMITS
For enabling potential performance improvements, when using a Teradata Database version 15.10 or higher, BTEQ can transfer a Large Data Object (LDO) non-deferred. The DEFERLIMITS option can be used to adjust limit settings that control how and when BTEQ is to attempt to override deferred transfer for a column. It is up to the user to specify the most optimal limits for best performance.

This option indicates the limits for exporting LDOs via a deferred transfer versus a non-deferred transfer. This option takes two arguments. The first argument defines the limit (in bytes) for an individual LDO. The second argument defines the cumulative limit of all non-deferred LDO bytes for a single row. For an LDO to be transferred in non-deferred mode it must be within both limits. If either limit is exceeded, the LDO will be transferred in deferred mode. The second argument must be greater or equal to the first argument.

Valid values for DEFERLIMITS arguments are:

  • Individual LDO = 0..2,097,088,000
  • Cumulative Row = 0..18,446,744,073,709,551,615

However, the database is responsible for deciding which LDOs get transferred in deferred mode versus non-deferred mode, and may impose different maximum values.

Non-deferred LDO transfers can be turned off completely by using values of "0,0".

If this option is not supplied, but the DEFERTRANS option is defined, then the following default values will be used for DEFERLIMITS:

  • Individual LDO = 1,000
  • Cumulative Row = 10,000

For a non-deferred transfer, each LDO must be 1,000 bytes or less, and the total number of bytes for all non-deferred LDO transfers (including itself) for the current row must be 10,000 bytes or less.

DEFERTRANS
Indicates that LDOs will be exported via deferred or non-deferred transfers and will be stored in their own export files. The main export file will contain data for non-LDO columns plus filenames where LDO data is stored. The DEFERLIMITS default values will be enforced. Therefore, LDOs within the "1000,10000" limits will be exported non-deferred. All others will be exported deferred.

Large Data Objects up to 2GB are supported.

This option is only allowed when used with the INDICDATA format.Without this option or the DEFERLIMITS option, LDOs will be transferred inline along with all other data, all stored within one export file.

DIF
Converts the results of a query to Data Interchange Format (DIF), a text format used by many spreadsheet applications for importing and exporting data. Mainframe BTEQ produces underlines for WITH clause summary values. Workstation BTEQ does not.
This parameter is not supported under the Kanji character sets KANJISJIS_0S or KANJIEUC_0U, or the Chinese character sets TCHBIG5_1R0 or SDTCHBIG5_3R0.
EJECT
Generates an additional page-advance, carriage-control character at the top of the exported REPORT format files. 
This works only for disposition type DISP=(OLD,NEW,SHR) on z/OS. EJECT is the default setting.
filename
Names the workstation-attached system file the export process will use to store results from subsequent data-returning SQL requests.

If the name of the file includes a comma, semicolon, or space character, enclose the entire file name in single or double quotation marks.

Also, note that file names are case-sensitive on systems running on a UNIX system, and they are not case-sensitive on systems running under Windows.

Whenever other command options are specified following the file name in the Windows environment, BTEQ requires the file name to be enclosed by single or double quotation marks.

Filenames must be comprised solely of basic LATIN characters, no matter what character set is used.

Although the keyword ddname is valid in place of the keyword FILE for workstation-attached systems, use caution when doing so as the implementation is for the FILE concept, not the DDNAME concept.
If the file specified for workstation BTEQ exists but is read-only, or the path to it does not exist, an error occurs. When running in batch mode, BTEQ terminates after printing the error message. When running in interactive mode, BTEQ continues execution after printing this error message.
INDICDATA
Returns the results of a query to a file using indicator variables to identify null values.

BTEQ returns data in Indicator Mode and stores it in the appropriate format.

INDICDATA is the only valid data format specification if the LARGEDATAMODE setting is ON.

For more information about Indicator Mode, refer to INDICATORMODE.

Specifying INDICDATA format for an export is not equivalent to using the SET INDICATORMODE or the SET INDICDATA command. 
'init-string'
Names the initiation string used to start the import process.
LDOPREFIX
Identifies a prefix string that will be used for creating LDO filenames. The prefix is only used when the DEFERTRANS or DEFERLIMITS options are enabled and persists across multiple exports. It can help identify and organize LDO files, especially when multiple LDO columns are exported.

This option is valid only for workstation platforms.

LDOSUFFIX
Identifies a suffix to be appended to all LDO filenames. The suffix is only used when the DEFERTRANS or DEFERLIMITS options are enabled and persists across multiple exports.

This option is valid only for workstation platforms.

modname
The access module library name. If the library cannot be found or an incorrect version is being loaded, use a fully qualified path name.
For example: /opt/teradata/client/xx.xx/lib/np_axsmod.so .
n1
BTEQ sets the maximum number of returned rows that are included within the specified export file.

Because the BTEQ RETLIMIT command also sets this specification, the most recent LIMIT =n1 option or RETLIMIT command overrides the previous specification.

The maximum limit for n1 is 18,446,744,073,709,551,615.

The default value is n1=0, which represents no limit. An asterisk (*) can also be used to represent no limit.

(For details, refer to RETLIMIT and RETCANCEL.)

n2
BTEQ sets the maximum number of returned columns that are included within the specified export file.
A value must be entered for n1 before entering a value for n2.

The value of n2 can be an asterisk or a number between 1 and 65535. An * signifies the maximum columns that BTEQ currently supports. This value is subject to change to support future releases of Teradata Database. To fix the limit of columns that are returned, enter a number instead of an asterisk.

For the current Teradata Database limit, refer to SQL Fundamentals (B035-1141), Appendix C.

The default value is n2=100.

Before increasing the column limit, other settings might need to be reestablished so that the effect of the setting is populated over the additional columns. For example, to omit all but the third column, use the OMIT ON ALL command before changing the column limit. After changing the column limit, use the OMIT ON 3 command. Otherwise, the ON attribute is not populated for the additional columns.

NOEJECT
Suppresses the generation of an additional page advance character at the top of the exported REPORT format files. This works only for disposition type DISP=(OLD,NEW,SHR) on z/OS. EJECT is the default setting.
OPEN or CLOSE
Determines the disposition of the export file in the event of a retryable error during a query.

If the RETRY command option is set to OFF, BTEQ does not resubmit the query when a retryable error is encountered. In this case, the query results can be incomplete.

If the OPEN option is specified with the RETRY value set to ON and a retryable error occurs, BTEQ starts the query again and adds rows to the end of the currently open export file. This can duplicate the rows that were exported before the error occurred.

If the CLOSE option is specified with the RETRY value set to ON and a retryable error occurs, BTEQ starts the query again, but first closes the export file and then reopens it in write mode. In this case, all previous query results are overwritten, even if the current query is within a transaction (BTEQ is not transaction aware).

OPEN is the default setting.

prefix
The LDOPREFIX value will be prepended to each LDO filename. This value can be a simple string or it can include directory names, but all directories must already exist. BTEQ does not create any directories itself.

The prefix value must consist of only Latin characters. Using an '=' sign is optional.

RECORDLENGTH
Defines the record (row) size limit for the current export. This overrides the current export-related RECORDLENGTH command setting, for the life of the export.

Valid arguments for the RECORDLENGTH option are:

  • MAX64 – Limits record sizes to 64,768 bytes. For DATA/INDICDATA exports, 2-byte record lengths will be written to the export file.
  • MAX1MB – Limits record sizes to 1,024,000 bytes. For DATA/INDICDATA exports, 4-byte record lengths will be written to the export file.

A DATA/INDICDATA export file must contain record lengths of the same size. Mixing records with 2-byte lengths and records with 4-byte lengths is not supported.

This option only affects the linelength of REPORT/DIF exports. An appropriate WIDTH setting should be used for large records.

If exported data is to be subsequently imported, the RECORDLENGTH option settings must match, otherwise the import will fail.

If this option is not specified, BTEQ will take the value from the current export-related RECORDLENGTH command setting. If the RECORDLENGTH command was not previously submitted, then a default value of MAX64 will be used.

Submit the SHOW CONTROLS EXPORT command to see if a RECORDLENGTH override is in effect for the current export. Otherwise submit the SHOW CONTROLS RECORDLENGTH command to see the parent settings.

REPORT
Outputs a query result to the previously defined file. This is the default.

For z/OS, the lines in the export file are truncated to 253 characters by default. The width limit can be increased to 1,048,575 characters using the WIDTH command.

The REPORTWIDE keyword is also accepted. This keyword specifies that the REPORT format for the data should be used, and changes the WIDTH setting to 32765. Because this keyword is deprecated, a SET WIDTH command should be used instead.

See the WIDTH command WIDTH.

RESET
Disables the previous export state and recommences sending results to the standard output stream. If the WIDTH setting was changed during the export, it is reset to its original value, the value in effect before that export.
suffix
The LDOSUFFIX value should be considered a file extension and will be automatically prefixed with a period ('.').

The suffix value must consist of only Latin characters. Using an '=' sign is optional.

Usage Notes

If the file being exported using the EXPORT command is to be used as the source for the IMPORT command across a different platform type, ensure that the endianness type of both platforms is the same. This can be verified from the “Client Platform Byte Order” tab in the output of the SHOW CONTROLS command.

BTEQ opens the specified export file when Teradata Database begins returning data, not when the EXPORT command is used. BTEQ also automatically opens an export file if Teradata Database returns any of the following parcels in response to an SQL statement:

  • OK
  • Success
  • Failure
  • Error

    It is not recommended to specify a file that is already opened from a previous IMPORT command. Unexpected results might occur due to records being written and read at the same time.

    BTEQ does not open an export file if Teradata Database returns an error parcel.
    For information on I/O errors and abends, refer to I/O Errors and Abends.

If an existing export file is specified, BTEQ appends new records after the existing records. If errors exist, however, then the standard output stream with errors is directed to the file specified by the ERROROUT command.

Any device name can be used that is valid on the user’s system as an export file name. Each row fetched for a data-returning SQL request generates one record in the export file.

The EXPORT command cannot be used in a Teradata SQL macro.