You can use the EXPORT COLUMNS command to export individual column values of a table. The requested tables must be available in the database instance. You can only execute the EXPORT COLUMNS command if you have access authorization for the table(s) you want to export.
You can use all of the demo data in database DEMODB. Start the Loader and log onto demo database DEMODB as database administrator MONA with the password RED. Use the session mode: loadercli -d demodb -u mona,red.
Export the data of the CUSTOMER table in the HOTEL schema. After the keywords EXPORT COLUMNS you can specify the required table columns and their order, like in a SELECT statement. You can make entries after the keyword DATA to control the destination you wish the application data to be exported to.
You can export the application data for a table.
EXPORT COLUMNS * FROM hotel.customer
DATA OUTSTREAM 'customer_csv.data'
The application data (DATA) is exported in the CSV format (default value).
EXPORT COLUMNS * FROM hotel.customer
DATA OUTSTREAM 'customer_fwv.data' FWV
The application data (DATA) is exported in the FWV format.
EXPORT COLUMNS * FROM hotel.customer
DATA OUTSTREAM 'customer_fwvbinary.data' FWV BINARY
The application data (DATA) is exported in the FWV BINARY format. You may not change a data file that has the FWV BINARY format as it is a binary format specific to the Loader.
See also: Loader, Data stream, CSV, FWV, FWV BINARY
You use either the command EXPORT COLUMNS * ... DATA ... or the command EXPORT TABLE ... DATA ... to export all application data of a table. In both cases, the order the data is exported in is determined by the order of the table columns.
You can export the individual column values of a table.
EXPORT COLUMNS cno, title, name, zip, address
FROM hotel.customer
DATA OUTSTREAM 'customer_csv.data'
The columns CNO, TITLE, NAME, ZIP and ADDRESS of the CUSTOMER table are exported in the specified order.
EXPORT COLUMNS title, name, cno, zip, address
FROM hotel.customer
DATA OUTSTREAM 'customer_csv.data'
The columns TITLE, NAME, CNO, ZIP and ADDRESS of the CUSTOMER table are exported in the specified order.
To arrange the table rows in the data stream in a particular sequence, you can use the ORDER clause. The columns specified in the ORDER clause determine the sort sequence. Note the sequence of the syntax elements. Enter the ORDER clause first.
EXPORT COLUMNS name, title, cno, zip, address
FROM hotel.customer
ORDER BY name, zip
DATA OUTSTREAM 'customer_csv.data'
The rows in the CUSTOMER table are sorted by the values in the NAME and ZIP columns and exported in that order into the data stream.
The syntax elements for setting locks and specifying the isolation level are used frequently. Observe the sequence of syntax elements, which is specified by the syntax rules for a SELECT statement.
EXPORT COLUMNS cno, title, name, zip, address
FROM hotel.customer
WITH LOCK
ISOLATION LEVEL 0
DATA OUTSTREAM 'customer_csv.data'
The specified columns of the CUSTOMER table are exported. In doing so, the LOCK option and isolation level 0 are set.
See also: SQL Reference Manual, SELECT Statement (select_statement)
After the key words MAP COLUMNS you can specify the order of the table columns in the data stream. Note the sequence of the syntax elements.
EXPORT COLUMNS * FROM hotel.customer
MAP COLUMNS
cno 1-5
title 6-13
name 14-54
firstname 55-75
zip 76-81
address 82-122
DATA OUTSTREAM 'customer_fwv.data' FWV
All column values are exported and stored in the data stream in the sequence CNO, TITLE, NAME, FIRSTNAME, ZIP, ADDRESS. This is also possible if you export selected columns only, as the following example illustrates:
EXPORT COLUMNS cno, title, name, zip, address
FROM hotel.customer
MAP COLUMNS
name 1
title 2
cno 3
zip 4
address 5
DATA OUTSTREAM 'customer_csv.data'
The CNO, TITLE, NAME, ZIP, ADDRESS column values are exported and stored in the data stream in the sequence NAME, TITLE, CNO, ZIP, ADDRESS.
When you use the CSV and FWV [BINARY] formats, specific data streams are created for the LOB data. The data of the HOTEL table in the HOTEL schema is to be exported to a CSV data stream. The HOTEL table has the LOB column INFO. Enter the LOB syntax element after the DATA syntax element.
EXPORT COLUMNS * FROM hotel.hotel
DATA OUTSTREAM 'hotel_csv.data'
LOB OUTSTREAM INFO 'hotel_info_csv.lob'
All the columns in the HOTEL table are exported. The LOB values are stored in the data stream hotel_info_csv.lob
See also: Loader, Exporting LOB Values
Combining the syntax options can result in correspondingly complex commands. Note the sequence of the syntax elements.
EXPORT COLUMNS cno, title, name, zip, address
FROM hotel.customer
ORDER BY name, zip
WITH LOCK
ISOLATION LEVEL 0
MAP COLUMNS
cno 1-4 INTEGER
title 5-12
name 13-53
zip 54-59
address 60-100
DATA OUTSTREAM 'customer_fwvbinary.data' FWV BINARY
For a complete syntax description, see EXPORT COLUMNS command in the Loader documentation.