EXPORT TABLE 

You can use the EXPORT TABLE command to export all the data from a specified table.

Prerequisites

You require the Demo Database DEMODB.

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.

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

All the data of 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 keyword CATALOG and/or DATA to select what is to be exported, the database catalog and/or the application data.

Export of the Database Catalog of a Table

You can export all the database catalog information of 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

Export of 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.room
  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 data files that have the PAGES, RECORDS and FWV BINARY formats as they are binary formats specific to the Loader.

See also:

Loader, PAGES, RECORDS

Export of 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'

Observe the sequence of the syntax elements. Always enter the syntax element for the export of the database catalog first, and then the syntax element for the export of 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. Observe 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 columns and exported in that order into 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 log 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 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 TABLE 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 file hotel_info_csv.long.

If you use the PAGES and RECORDS formats, all data from the table, including LONG 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 LONG column INFO, are exported to the hotel_records.data data stream.

See also:

Loader, Exporting and Importing LONG Values

Complete Syntax of the EXPORT TABLE Command

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

See also:

Other Commands for Exporting

Corresponding Import command IMPORT TABLE