Purpose
Specifies the name and format of an export file that BTEQ uses to store database information returned by a subsequent SQL SELECT statement.
Note: 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
For Mainframe-Attached Systems:
For Workstation-attached Systems:
where:
|
Syntax Element |
Specification |
|
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: |
|
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: 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” on page 270. |
|
DATALABELS |
Includes the column titles of the selection results as the first data row in the DIF
file. Note: Workstation-attached systems do not use the TITLEDASHES setting when DIF format is being used for exported data. |
|
ddname |
Specifies the name of the z/OS JCL DD statement that defines the file specified by the EXPORT command for receiving the results from the subsequent SQL SELECT statement. Note: SYSPRINT is not a valid DDNAME for the BTEQ EXPORT command. Exporting to the SYSPRINT file cannot be done. Note: 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. Note: If an export file name is not defined, the default field for the exported data is FILE ddname. Note: 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. Note: 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. Note: 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. |
|
filename |
Names the workstation-attached system file that receives the results of the SQL SELECT statement. 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. Note: 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. Note: 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. Note: INDICDATA is the only valid data format specification if the LARGEDATAMODE setting is ON. For more information about Indicator Mode, refer to INDICATORMODE on page 217. Note: 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 |
Names the file that is to receive the imported data. |
|
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” in this chapter.) |
|
n2 |
BTEQ sets the maximum number of returned columns that are included within the specified export file. Note: 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, 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). |
|
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. |
|
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 65531 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” on page 353. |
|
RESET |
Disables the previous EXPORT command and sends selection 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:
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.
Note: BTEQ does not open an export file if Teradata Database returns an error parcel.
Note: For information on I/O errors and abends, refer to “I/O Errors and Abends” on page 96.
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 returned by the Teradata SQL SELECT statement 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
Note: 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.
Note: 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.
Note: After suppressing the title text and titledashes in the exported report, the IMPORT command can be used with data format REPORT on this file without making any modifications.
SELECT Statement Response in Record Mode
If the SQL SELECT statement generates its response in 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:
On mainframe-attached systems, each record has the following format:
The record consists of binary-formatted fields that contain the column values of the selected row in record or indicator format.
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)');
SELECT Statement Response in Field Mode
If the SQL SELECT statement generates its response in Field Mode (keyword REPORT), BTEQ passes each line of the report on to the export file as a record with the following format:
Note: 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:
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:
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:
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 below to see how deferred and non-deferred exports can be used within a script.
Closing the Export File
BTEQ closes the export file when one of the following options is used:
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.
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 multi-byte 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 Windows, backdoor export files may not include carriage returns, since BTEQ is writing the data exactly how it is sent from the database.
Example
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
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
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
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
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
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
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
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
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
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