EXPORT CLPPlus command using external tables

Use the EXPORT CLPPlus command to export an external table file to a local server location, a remote client, a IBM® Cloud Object Storage, or an AWS S3 object store.

Invocation

You must run the EXPORT command from the CLPPlus interface.

Authorization

None.

Required connection

You must be connected to a database.

Restrictions

The options such as DATAOBJECT, which are not supported with transient external tables, cannot be specified in the OPTIONS clause of the EXPORT command.

Command syntax

Read syntax diagramSkip visual syntax diagramEXPORT EXTERNAL TO ' filename ' OPTIONS(options-string)select-statement

Command parameters

EXTERNAL
Specifies that the EXPORT command uses external table operations.
OPTIONS options-string
Specifies the options that control the processing of the export operation. These are described in CREATE EXTERNAL TABLE.
TO filename
Specifies the name of the file to which data is to be exported. If the file already exists, the contents of the file are overwritten, not appended to. The name must be specified in single quotes.
Select-statement
Specifies the SELECT statement that is to return the data that is to be exported.

Example

The following command exports the content of the Employees table on the server to the Employees.txt file in IBM Cloud Object Storage:
SQL> Export external to 'Employees.txt' 
     options(s3('s3.amazonaws.com', 'AKIA99999999999999999', '783nGlH12345678910', 'db2.s3.qa.us-east-1') 
     "DELIMITER ',' LOGDIR '/home/user') select * from employees;
The following command exports the content of the Employees table on the server to the Employees.txt file in an AWS S3 object store:
SQL> Export external to 'Employees.txt' 
     options(swift('default', 'IBMOS28999999999', 'b107aa9172c70f8df16', 'db2_dev') 
     DELIMITER ',' LOGDIR '/home/user/') select * from employees;
The following command exports the content of the Employees table on the server to the Employees.txt file on the client location:
SQL> Export external to 'C:\Employees.txt' 
     options('maxerrors 20 REMOTESOURCE 'JDBC' LOGDIR '/home/user') select * from employees;
The following command exports the content of the Employees table on the server to the Employees.txt file on the server location:
SQL> Export external to '/home/user/Employees.txt' 
     options(maxerrors 20) select * from employees;