16.20 - EXPORT - Basic Teradata Query

Basic Teradata Query Reference

prodname
Basic Teradata Query
vrm_release
16.20
category
Programming Reference
featnum
B035-2414-108K

Purpose

This control enables users to specify the name, format and attributes of an export file that BTEQ uses to store records resulting from data-returning SQL requests.

The EXPORT command should not be run on the 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 optional initiation string that is passed to the access module at the start of the export process. The string must be enclosed in single quotes.
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 name contains embedded spaces, enclose it within single quotes. 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'.
When using 64-bit BTEQ, the access module also needs to be built in 64-bit mode. For compiler options to be used to build in 64-bit mode, see "Building an Exit Module" in the NOTIFY command description.
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
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.

Suppressing Title Text and Titledashes in Exported REPORT Format Output

The following example demonstrates how to suppress the generation of column headers and titledashes at the top of an exported report.

.LOGON mydbs/myid, mypwd
CREATE TABLE Employer_Location (EmpNo INTEGER, Location CHAR(5));
INSERT INTO Employer_Location VALUES(1, 'LA');
INSERT INTO Employer_Location VALUES(2, 'NYC');
INSERT INTO Employer_Location VALUES(3, 'ATL');
.SET TITLEDASHES OFF
.EXPORT REPORT FILE=myfile1.exp
SELECT EmpNo (TITLE ''), Location (TITLE '') FROM Employer_Location;
.EXPORT RESET
.SET TITLEDASHES ON
.EXPORT REPORT FILE=myfile2.exp
SELECT EmpNo, Location FROM Employer_Location;
.EXPORT RESET
.LOGOFF
.EXIT

Output for myfile1.exp:

1  LA
2  NYC
3  ATL

Output for myfile2.exp:

      EmpNo  Location
-----------  --------
          1  LA
          2  NYC
          3  ATL
For file myfile1.exp, with TITLEDASHES turned OFF and the phrase TITLE '' (title text NULL) used for all columns in the SELECT statement, BTEQ has no header information to print and immediately begins printing data.
For file myfile2.exp, with TITLEDASHES turned ON and no phrase for TITLE used with the columns in the SELECT statement, BTEQ prints the column header and titledashes lines and then begins printing data.

Data-Returning Responses in Record Mode

For data returned using Record Mode (keyword DATA) or Indicator Mode (keyword INDICATORMODE), BTEQ passes each row on to the export file as a record. On workstation-attached systems, each record has the following format:

  • The record begins with a length field, which is an unsigned binary integer of two bytes or four bytes, dependent upon the export-specific RECORDLENGTH setting. This represents the number of bytes in the record (not including the bytes in the length field and not including the byte(s) in the end-of-record field).
  • The record follows with binary-formatted fields that contain the column values of the returned row in record or indicator format.
  • The record ends with an end-of-record (line) indicator, appropriate for the workstation and operating system. For example, for a UNIX system, the indicator is a new line. For Windows, the indicator is a carriage return/line feed pair. In all cases, the end-of-record character, or characters, are in the bit-format appropriate for the workstation. For example, in ASCII for an ASCII workstation.

On mainframe-attached systems, each record has the following format:

The record only consists of binary-formatted fields that contain the column values of the selected row in record or indicator format. It does not contain a record length field or an end-of-record indicator, since that information is inherent in the way z/OS stores records.

Character Constants Treated as Variable-Length Strings (Record Mode)

When a character constant is included in the SELECT statement’s item list, the constant is treated as a variable-length string. In Record Mode, this causes the constant to be prefixed with a two-byte field that defines the length of the character constant. This behavior affects how a record containing such constants is defined when imported later by BTEQ or another program.

To export a fixed-length character string, use a FORMAT phrase in the SQL SELECT statement. For example, the FORMAT phrase in the following SELECT statement causes the character constant 'First' to return as a fixed-length 30-character string:

SELECT 'First' (FORMAT 'X(30)');

Data-Returning Responses in Field Mode

For data returned using Field Mode (keyword REPORT), BTEQ passes each line of the report on to the export file as a record with the following format:

  • The record begins with either a heading line or text-formatted fields that contain the column values of the returned row. In either case, the information is in the bit-format appropriate for the workstation and operating system (for example, in ASCII for an ASCII workstation).
  • The record ends with an end-of-record (line) indicator, appropriate for the workstation and operating system. For example, for a UNIX system, the indicator is a new line. For Windows, the indicator is a carriage return/line feed pair. In all cases, the end-of-record character, or characters, are in the bit-format appropriate for the workstation. For example, in ASCII for an ASCII workstation.
    To edit the exported records on a PC and import them back to the database, be sure to use a PC editor that recognizes both the carriage return and line feed characters as an end-of-record marker.

When staging an export operation, the RECORDMODE command does not need to be used to indicate Record Mode is to be used. The need for using Record Mode is determined by the data format specified as part of the EXPORT command. (Use the SHOW CONTROLS command to see the current response mode.)

Exporting Large Data Objects

LDOs (Large Data Objects) consist of CLOB, BLOB, XML, and JSON data types. LDOs can be exported from the database to BTEQ either inline, as deferred transfers, or as non-deferred transfers. How they are transferred is dependent upon the use of the DEFERTRANS and DEFERLIMITS options.

For inline LDO exports, the DEFERTRANS and DEFERLIMITS options must not be used. In this case, BTEQ does not differentiate LDOs from non-LDO columns. All data is sent from the database in one parcel and stored in a single export file. Inline LDOs are also limited to 64K in size.

Deferred mode LDOs can be larger (up to 2GB) and are sent by the database after all non-LDO data is processed.

Non-deferred mode LDOs can also be up to 2GB in size but are sent by the database at the same time non-LDO data is processed.

Deferred and non-deferred exports are only allowed when using the INDICDATA format along with the LARGEDATAMODE setting turned on. If LARGEDATAMODE is not on, BTEQ will turn it on automatically for the life of the export. Conversely, when importing LDOs, deferred and non-deferred imports can be done with INDICDATA, DATA, and VARTEXT formats.

For both deferred and non-deferred exports, LDOs are stored in their own file for easier accessibility. A main export file (defined by the FILE/DDNAME option) will still be created, containing non-LDO data along with filenames (as VARCHAR data types) for each LDO file, based on column titles. The main export file will be in INDICDATA format. CLOB and JSON files will be written as text, based on the session character set. XML files will always be written in text with a UTF-8 encoding. BLOB files will be written with raw data.

Deferred exports are usually best used for large LDOs and non-deferred exports for small LDOs, as determined by elapsed time performance. Some testing/tuning may be needed to determine the optimal way to export records containing LDOs. The following describes how to define your exports for deferred mode versus non-deferred mode:

  • Export all LDOs in deferred mode: Use the DEFERLIMITS option with values "0,0".
  • Export all LDOs in non-deferred mode: Use the DEFERLIMITS option, using values sufficiently high enough to account for all LDOs. This method is not recommended for very large LDOs.
  • Export some deferred LDOs and some non-deferred LDOs:
    • Use the DEFERTRANS option. This will default the DEFERLIMITS values to "1000,10000". Any LDOs within these limits will be exported non-deferred. All others will be exported deferred.
    • Use the DEFERLIMITS option to specify your own limits. Any LDOs within those limits will be exported non-deferred, while the rest will be exported deferred mode.

When the DEFERLIMITS option is specified, BTEQ will display the number of deferred and non-deferred LDOs that were exported for each statement. The message will look similar to:

*** Total LDOs: 48, Non-deferred: 10, Deferred: 38

For workstation platforms, each LDO filename is created based on the LDO column title. The filename syntax is:

[prefix]<col-name>_r<rec-nbr>[.suffix]

where:

  • prefix = The optional LDOPREFIX value. This can include a relative or absolute directory structure.
  • col-name = The LDO column title, which must contain only Latin characters.
  • rec-nbr = The record number. This value will be incremented for each record encountered. When a new EXPORT command is submitted, the record number is reset.
  • suffix = The optional LDOSUFFIX value.

On the mainframe, each LDO filename is created using an associated DDNAME that is based on the LDO column title. Users are responsible for pre-defining all needed DDNAMEs in their JCL scripts. The DDNAME naming convention is:

<col-name><rec-nbr>

where:

  • col-name = The LDO column title, which must contain only Latin characters.
  • rec-nbr = The record number. This value will be incremented for each record encountered. When a new EXPORT command is submitted, the record number is reset.

Since DDNAMEs are limited to 8 characters, and the first character must be a letter, the maximum number of records that can be retrieved is 9,999,999. This, however, may be superseded by the z/OS DDNAME limit.

It is important to make sure that the filenames/DDNAMEs for each LDO column are unique. If a naming conflict occurs between multiple LDO columns, use the TITLE clause in the query to create new column names. This is also a good workaround for a column name that contains non-Latin characters. For example, on the mainframe, the following query has two conflicting LDO column names (when truncated):

SELECT customer_name_clob, customer_image_blob FROM customer_table;

But it can be fixed by making a change similar to:

SELECT customer_name_clob (TITLE 'CNAME'),
       customer_image_blob (TITLE 'CIMAGE')
       FROM customer_table;

LDO data is written to its own file as is, without any formatting. The only exception is with the possible insertion of a Unicode Byte Order Mark (BOM). A BOM can be inserted at the beginning of XML objects, which are always transferred in the UTF-8 encoding, or for character based objects when a Unicode session character set is used. The user can disable BOM insertion by using the NOBOM option.

Individual LDO files are never appended. If the file already exists, BTEQ will simply overwrite the existing data.

If LDO exports are to be subsequently used during an import, the LDO columns should be “ordered” first in the SELECT list. This is a requirement of the IMPORT command when the DEFERCOLS option is used.

Examine examples 7, 8, and 9 (EXPORT Examples) to see how deferred and non-deferred exports can be used within a script.

If a retryable error occurs during an export containing deferred or non-deferred LDOs and RETRY is set to ON, BTEQ will not resubmit the current SQL request, due to recoverability issues for the LDO files. Instead, BTEQ will automatically terminate.

Closing the Export File

BTEQ closes the export file when one of the following options is used:

  • An EXPORT command specifying either another export file or the RESET option
  • A LOGOFF, EXIT or QUIT command

BTEQ does not close the file until the last file record has been written. If the file is read before it is closed, the last few records might be missing. They appear when the file is closed in response to the EXPORT, LOGOFF, EXIT or QUIT command.

In z/OS, if a disposition of MOD is specified in the JCL, the CLOSE option does not work.

EXPORT and Mainframe-Attached Systems

The following information applies only to mainframe-attached systems. If LRECL is set and the blocksize is too small for the file, the data is shortened to those settings and Teradata Database returns the following error message:

***Increase test file record length and blocksize  to match data.

BTEQ supports all QSAM-compatible formats. When using the REPORT option, the DCB for the DD statement should specify a RECFM of VA, VBA, FBA, or FA to preserve page ejects.

When using the DATA or INDICDATA options, the DCB for the DD statement can specify any RECFM; however, RECFM=FB or RECFM=VB are commonly used.

Regardless of the output option used, if RECFM is fixed (F, FB, FA, or FBA), then LRECL must be exact (including indicator bits, if any). Therefore, a variable RECFM (V, VA, VB, or VBA) with a larger record or blocksize is recommended, especially when the REPORT option is used or when a multibyte session character set is used.

For example:

(// DCB=(RECFM=VB,BLKSIZE=32760,DSORG=PS)

Also, when using DATA, INDICDATA, or REPORT format for an export, the DCB for the receiving file should enter LRECL and BLKSIZE values that are big enough to accommodate the largest expected record.

Append records to an existing file by entering a disposition of MOD for the file.

For z/OS BTEQ, when a REPORT or DIF export is in progress, the width setting used for writing to the SYSPRINT file temporarily changes to match the LRECL value of the export file. This condition exists only when the LRECL of the export file is less than the current width of the SYSPRINT file. Also, this WIDTH gets reset to its original value once an EXPORT RESET command is executed.

Backdoor Exports

Different types of exports can be created using a combination of the EXPORT command along with one of the response mode commands (INDICATORMODE, LARGEDATAMODE, or RECORDMODE). The format (DATA, INDICDATA, REPORT, or DIF) specified in the EXPORT command determines how data will be written to the export file. The response mode command determines the format of the data returned by the database.

To produce Record Mode output in a readable hex format to a text export file, specify REPORT for the EXPORT format type and then turn the RECORDMODE command on.

To produce Indicator Mode output in a readable hex format to a text export file, specify REPORT for the EXPORT format type and then turn the INDICATORMODE command on.

To produce Multipart Indicator Mode output in a readable hex format to a text export file, specify REPORT for the EXPORT format type and then turn the LARGEDATAMODE command on.

To produce an export file in Field Mode without any column titles or titledashes, specify DATA for the EXPORT format type and then turn the RECORDMODE command off. On z/OS, when the dataset supports ASA carriage-control characters (as indicated by usage of FBA/VBA), and FORMAT is set to OFF, BTEQ considers the contents being written out to be data in text format as opposed to a report. So it will use a space for all ASA carriage control characters.

On Windows, backdoor export files may not include carriage returns, since BTEQ is writing the data exactly how it is sent from the database.

EXPORT Examples

Example 1 – EXPORT

In Record Mode, to send the results of a SELECT statement to the data set allocated to the file SAVEDATA, enter the following command:

.EXPORT DATA FILE=SAVEDATA

Example 2 – EXPORT

To include indicator variables in the result of an SQL SELECT statement sent to the data set known as ALLDATA, enter the following command:

.EXPORT INDICDATA DDNAME=ALLDATA

Example 3 – EXPORT

To include indicator variables in the result of an SQL SELECT statement sent to the file ALLDATA, use the command shown below. If a retryable error occurs during this export, BTEQ re-submits the query (if RETRY is set to ON), and overwrites any previous data in the file.

.EXPORT INDICDATA FILE=’ALLDATA’, CLOSE

Example 4 – EXPORT

This example exports data to a file named EXPORT.DAT by way of an Access Module (library name is MYMOD). Note that an init-string is not used.

.EXPORT DATA FILE = 'EXPORT.DAT' AXSMOD MYMOD

Example 5 – EXPORT

The following will produce a backdoor export file in readable Indicator Mode hex format.

 CREATE TABLE my_table (
   Col1 INTEGER,
   Col2 VARCHAR(10)
 ) NO PRIMARY INDEX;
 INSERT INTO my_table values (1, 'one');
 INSERT INTO my_table values (2, 'two');
 INSERT INTO my_table values (3, 'three');
 .EXPORT REPORT FILE=HEXDATA
 .INDICATORMODE ON
 SELECT * FROM my_table ORDER BY 1;
 .EXPORT RESET

The export file will look like:

0000  0200 F101 0400 C101  0A00                 *..........*
0000  0001 0000 0003 006F  6E65                 *.......one*
0000  0002 0000 0003 0074  776F                 *.......two*
0000  0003 0000 0005 0074  6872 6565            *.......three*

Example 6 – EXPORT

The following will produce a backdoor export file in Field Mode with only column data (no column titles or titledashes). The same table from the previous example is used.

.EXPORT DATA FILE=DATAONLY
.RECORDMODE OFF
SELECT * FROM my_table ORDER BY 1;
.EXPORT RESET

The export file will look like:

1  one
2  two
3  three

Example 7 – EXPORT

To transfer LDOs in deferred or non-deferred mode on z/OS, DDNAMEs must be pre-defined to match the columns being returned. In this example, the query returns 2 rows with 2 columns. BTEQ produces one main export data set and 4 individual LDO data sets. LDOs that are 2,000 bytes or less will be exported non-deferred, and all others will be exported in deferred mode.

//BTEQ   EXEC PGM=BTQMAIN
//EXPFILE  DD  DSN=USERID.BTEQ.EXP1,DISP=SHR
//MYCLOB1  DD  DSN=USERID.BTEQ.COL11,DISP=SHR
//MYCLOB2  DD  DSN=USERID.BTEQ.COL12,DISP=SHR
//MYBLOB1  DD  DSN=USERID.BTEQ.COL21,DISP=SHR
//MYBLOB2  DD  DSN=USERID.BTEQ.COL22,DISP=SHR
//SYSPRINT DD  DSN=USERID.BTEQ.OUTPUT,DISP=MOD
//SYSABEND DD  SYSOUT=*
//SYSOUT   DD  SYSOUT=*
//SYSTERM  DD  SYSOUT=*
//SYSIN    DD  DATA,DLM=##
.LOGON xxx/yyy,zzz
.LARGEDATAMODE ON
.EXPORT INDICDATA DEFERLIMITS=2000,4000 DDNAME = EXPFILE
SELECT myclob, myblob FROM mytable;
.EXPORT RESET
.QUIT
##

Example 8 – EXPORT

For deferred LDO transfers with workstation BTEQ, where column names need to be re-worded, use the TITLE clause. Notice that "DEFERLIMITS=0,0" will force all LDOs to be exported in deferred mode.

.LARGEDATAMODE ON
.EXPORT INDICDATA DEFERLIMITS=0,0 FILE = SchoolImages.indicdata
SELECT s_pic (TITLE 'FirstGradeStudentImage'),
       t_pic (TITLE 'FirstGradeTeacherImage') 
       FROM FirstGradeImages;
SELECT s_pic (TITLE 'SecondGradeStudentImage'),
       t_pic (TITLE 'SecondGradeTeacherImage') 
       FROM SecondGradeImages;
.EXPORT RESET

In this example, notice that the record count is independent of each query's row count, and does not get reset until EXPORT RESET is executed. The following files are produced by BTEQ:

SchoolImages.indicdata

FirstGradeStudentImage_r1

FirstGradeTeacherImage_r1

FirstGradeStudentImage_r2

FirstGradeTeacherImage_r2

SecondGradeStudentImage_r3

SecondGradeTeacherImage_r3

SecondGradeStudentImage_r4

SecondGradeTeacherImage_r4

Example 9 – EXPORT

For both deferred and non-deferred LDO transfers with workstation BTEQ, where columns for separate tables use the same record numbers, try using a table join. Because DEFERLIMITS is not specified, default limits will be used. Those LDOs within the "1000,10000" limits will be exported as non-deferred transfers, while all others will be exported as deferred transfers.

.LARGEDATAMODE ON
.EXPORT LDOPREFIX 'doc_'
.EXPORT LDOSUFFIX 'txt'
.EXPORT INDICDATA DEFERTRANS FILE = document.indicdata
SELECT doclist1.prososal, doclist1.draft,
       doclist2.reviewed, doclist2.final
       FROM doclist1, doclist2
       Where doclist1.id = doclist2.id;
.EXPORT RESET

In this example, notice that the rows match up with the record count, and how the prefix and suffix values are used. The following files are produced by BTEQ:

document.indicdata

doc_proposal_r1.txt

doc_draft_r1.txt

doc_reviewed_r1.txt

doc_final_r1.txt

doc_proposal_r2.txt

doc_draft_r2.txt

doc_reviewed_r2.txt

doc_final_r2.txt

Example 10 – EXPORT

If a single export includes multiple statements, but different DEFERLIMITS values are needed for each statement, simply add a new EXPORT command in between the queries to reset the DEFERLIMITS option. The main export file will be appended to, so no data will be overwritten. However, for data in the main export file to be consistent, each query must include the same types of columns.
.LARGEDATAMODE ON
.EXPORT LDOPREFIX 'query1_' 
.EXPORT INDICDATA DEFERLIMITS=500,5000 FILE=test.dat
SELECT * FROM table1;
.EXPORT LDOPREFIX 'query2_' 
.EXPORT INDICDATA DEFERLIMITS=2000,8000 FILE=test.dat
SELECT * FROM table2; 
.EXPORT RESET

Example 11 – EXPORT

The following will produce a RECORDMODE export with 2-byte record lengths:

.EXPORT DATA RECORDLENGTH=MAX64 FILE=test1.data
SELECT 'abc';
.EXPORT RESET

Notice the first 2 bytes define the record length in this hex-dump output of test1.data:

05000300 6162630A

Example 12 – EXPORT

The following will produce a RECORDMODE export with 4-byte record lengths:

.EXPORT DATA RECORDLENGTH=MAX1MB FILE=test2.data
SELECT 'abc';
.EXPORT RESET

Notice the first 4 bytes define the record length in this hex-dump output of test1.data:

05000000  03006162  630A