Background documentationEXPORT TABLE Locate this document in the navigation structure

 

You can use the EXPORT TABLE command to export all the data from a specified table. The requested tables must be available in the database instance. Only the owner of the table may execute the EXPORT TABLE ...PAGES command. In all other cases you must have access authorization for the tables you want to export.

Prerequisites

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

Activities

All data in the ROOM table in the HOTEL schema is to be exported. It is not possible to select specific column values with the EXPORT TABLE command; you can only do this with the EXPORT COLUMNS command. You can make entries after the keywords CATALOG and/or DATA to select what is to be exported, the database catalog and/or the application data.

Exporting the Database Catalog of a Table

You can export all the database catalog information for a table.

EXPORT TABLE hotel.room

  CATALOG OUTSTREAM 'room_ddl.catalog'

The database catalog (CATALOG) is exported in the DDL format.

See also: Loader, Data Stream

Exporting the Application Data of a Table

You can export all the application data of a table.

EXPORT TABLE hotel.room

  DATA OUTSTREAM 'room_csv.data'

The application data (DATA) is exported in the CSV format (default value).

EXPORT TABLE hotel.room

  DATA OUTSTREAM 'room_fwv.data' FWV

The application data (DATA) is exported in the FWV format.

EXPORT TABLE hotel.  

  DATA OUTSTREAM 'room_fwvbinary.data' FWV BINARY

The application data (DATA) is exported in the FWV BINARY format.

See also: Loader, CSV, FWV, FWV BINARY

EXPORT TABLE hotel.room

  DATA OUTSTREAM 'room_pages.data' PAGES

The application data (DATA) is exported in the PAGES format.

EXPORT TABLE hotel.room

  DATA OUTSTREAM 'room_records.data' RECORDS

The application data (DATA) is exported in the RECORDS format.

A PAGES export uses a database format (based on a page), and is therefore faster than an export that uses, for example, the RECORDS format of the Loader. You may not change a data file in the PAGES, RECORDS or FWV BINARY formats, as these are binary formats specific to the Loader.

See also: Loader, PAGES, RECORDS

Exporting the Database Catalog and all the Application Data of a Table

You can export all the data in the specified table.

EXPORT TABLE hotel.room

  CATALOG OUTSTREAM 'room_ddl.catalog'

  DATA OUTSTREAM 'room_csv.data'

Note the sequence of the syntax elements. Always enter the syntax element for exporting the database catalog first, and then the syntax element for exporting the application data.

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 TABLE hotel.room

  ORDER BY price

  CATALOG OUTSTREAM 'room_ddl.catalog'

  DATA OUTSTREAM 'room_csv.data'

The rows in the ROOM table are sorted by the values in the PRICE column and exported in that order to the data stream.

Additional Log Functions

If you specify a data stream after the keyword PACKAGE, the command entries from the SYSLOADER.TRANSFORMATIONMODEL table are exported to the specified data stream. This is an additional logging option. Always enter the PACKAGE syntax element as the last syntax element.

EXPORT TABLE hotel.room

  CATALOG OUTSTREAM 'room_ddl.catalog'

  PACKAGE OUTSTREAM 'room_csv.package'

See also: Loader, Additional Log Functions (package_spec)

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 TABLE 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 file hotel_info_csv.lob.

When you use the PAGES or RECORDS formats, all data from the table, including LOB data, is stored in a data stream. A LOB syntax element is therefore not necessary.

EXPORT TABLE hotel.hotel.

  DATA OUTSTREAM 'hotel_records.data' RECORDS

All columns of the HOTEL table, including the LOB column INFO, are exported to the hotel_records.data data stream.

See also: Loader, Exporting LOB Values

Complete Syntax of the EXPORT TABLE Command

Combining the syntax options can result in correspondingly complex commands. Note the sequence of the syntax elements.

EXPORT TABLE hotel.room

  ORDER BY price

  CATALOG OUTSTREAM FILE 'room_ddl.catalog' DDL

  DATA OUTSTREAM FILE 'room_csv.data' CSV

  PACKAGE OUTSTREAM FILE 'room_csv.package' CSV

For a complete syntax description, see EXPORT TABLE command in the Loader documentation.

If you want to export only selected column values instead of all the table data, select the EXPORT COLUMNS command.

More Information

Other commands for Exporting

Corresponding import command IMPORT TABLE

Session Mode