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.
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.
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.
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
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
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.
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.
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)
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
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.