Exporting all Application Data in a Table

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.

Exporting the Application Data from Specified Columns in a Table

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.

Sequence of Table Rows

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.

Specifying LOCK Option and the Isolation Level

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)

Sequence of the Table Columns in the Data Stream

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.

Handling LOB Values

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

Complete Syntax of the EXPORT COLUMNS Command

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.