EXPORT COLUMNS 

You can use the EXPORT COLUMNS command to export individual column values from a table.

Prerequisites

You require the DEMODB demo database.

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.

Start the Loader as database administrator MONA and log on to the demo database DEMODB. Use the session mode.

loadercli –d demodb –u mona,red

Examples

The data of the CUSTOMER table in the HOTEL schema is to be exported.

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.

Exporting all Application Data in a Table

You can export the application data of 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 CNO, TITLE, NAME, ZIP and ADDRESS columns in the CUSTOMER table are exported in the order specified.

EXPORT COLUMNS title, name, cno, zip, address FROM hotel.customer
  DATA OUTSTREAM 'customer_csv.data'

The TITLE, NAME, CNO, ZIP and ADDRESS columns in the CUSTOMER table are exported in the order specified.

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. Observe 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 Options 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. Observe 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 this order: CNO, TITLE, NAME, FIRSTNAME, ZIP, ADDRESS.

This is also possible if you export only selected columns.

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 column values CNO, TITLE, NAME, ZIP, ADDRESS are exported and stored in the data stream in this order: NAME, TITLE, CNO, ZIP, ADDRESS.

Handling LONG Values

If you use the CSV and FWV [BINARY] formats, extra data streams are created for the LONG 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 LONG column INFO. Enter the LOB syntax elements after the DATA syntax element.

EXPORT COLUMNS * FROM hotel.hotel
  DATA OUTSTREAM 'hotel_csv.data'
  LOB OUTSTREAM INFO 'hotel_info_csv.long'

All columns of the HOTEL table are exported. The LONG values are stored in the data stream hotel_info_csv.long.

See also:

Loader, Exporting and Importing LONG Values

Complete Syntax of the EXPORT COLUMNS Command

Combining the syntax options can result in correspondingly complex commands. Observe 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.

See also:

Other commands for Exporting

Corresponding import command IMPORT COLUMNS