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