Exports data from a database to one of several external file formats. The user specifies the data to be exported by supplying an SQL SELECT statement, or by providing hierarchical information for typed tables.
Authorization
One of the following:
or CONTROL or SELECT privilege on each participating table or view.
Required Connection
Database. If implicit connect is enabled, a connection to the default database is established.
>>-EXPORT TO--filename--OF--filetype---------------------------->
>-----+---------------------------+----------------------------->
| .-,-----------. |
| V | |
'-LOBS TO-----lob-path---+--'
>-----+---------------------------+----------------------------->
| .-,-----------. |
| V | |
'-LOBFILE-----filename---+--'
>-----+-----------------------------------+--------------------->
| .-----------------. |
| V | |
'-MODIFIED BY-----filetype-mod---+--'
>-----+--------------------------------------+------------------>
| .-,--------------. |
| V | |
'-METHOD N--(-----column-name---+---)--'
>-----+-------------------------+------------------------------->
'-MESSAGES--message-file--'
>-----+-select-statement---------------------------------------------+>
'-HIERARCHY--+-STARTING--sub-table-name--+---+---------------+-'
'-| traversal-order-list |--' '-where-clause--'
>--------------------------------------------------------------><
traversal-order-list
.-,-----------------.
V |
|---(-----sub-table-name---+---)--------------------------------|
|
Command Parameters
If the name of a file that already exists is specified, EXPORT overwrites the contents of the file; it does not append the information.
For more information about file formats, see the Appendix C. IMPORT/EXPORT/LOAD Utility File Formats.
When creating LOB files during an export, file names are constructed by appending the current base name from this list to the current path (from lob-path), and then appending a 3-digit sequence number. For example, if the current LOB path is the directory /u/foo/lob/path, and the current LOB file name is bar, the LOB files created will be /u/foo/lob/path/bar.001, /u/foo/lob/path/bar.002, and so on.
Examples
The following example shows how to export information from the STAFF table in the SAMPLE database (to which the user must be connected) to myfile.ixf, with the output in IXF format:
db2 export to myfile.ixf of ixf messages msgs.txt select * from staff
The following example shows how to export the information about employees in Department 20 from the STAFF table (in the database to which the user must be connected) to awards.ixf, with the output in IXF format:
db2 export to awards.ixf of ixf messages msgs.txt select * from staff
where dept = 20
Usage Notes
Be sure to complete all table operations and release all locks before issuing the EXPORT command. This can be done either by issuing a COMMIT after closing all cursors opened WITH HOLD, or by issuing a ROLLBACK. A COMMIT is performed during the export process.
Table aliases can be used in the SELECT statement.
The messages placed in the message file include the information returned from the message retrieval service. Each message begins on a new line.
The export utility produces a warning message whenever a character column with a length greater than 254 is selected for export to DEL format files.
PC/IXF import should be used to move data between databases. If character data containing row separators is exported to a delimited ASCII (DEL) file and processed by a text transfer program (moving, for example, between OS/2 and AIX systems), fields containing the row separators will shrink or expand.
PC/IXF file format specifications permit migration of data between OS/2 (IBM Extended Services for OS/2, OS/2 Extended Edition and DB2 for OS/2) databases and DB2 for AIX databases via export, binary copying of files between OS/2 and AIX, and import. The file copying step is not necessary if the source and the target databases are both accessible from the same client.
DB2 Connect can be used to export tables from DRDA servers such as DB2 for OS/390, DB2 for VM and VSE, and DB2 for OS/400. Only PC/IXF export is supported.
The EXPORT command will not create multiple-part PC/IXF files when invoked from an AIX system.
When exporting typed tables, subselect statements can only be expressed by specifying the target table name and the WHERE clause. Fullselect and select-statement cannot be specified when exporting a hierarchy.
For file formats other than IXF, it is recommended that the traversal order list be specified, because it tells DB2 how to traverse the hierarchy, and what sub-tables to export. If this list is not specified, all tables in the hierarchy are exported, and the default order is the OUTER order. The alternative is to use the default order, which is the order given by the OUTER function.
| Note: | Use the same traverse order during an import operation. The load utility does not support loading hierarchies or sub-hierarchies. |
DB2 File Manager Considerations
To ensure that a consistent copy of the table and the corresponding files referenced by the DATALINK columns are copied for export, do the following:
This ensures that no update transactions are in progress when EXPORT is run.
This makes the table available for updates.
EXPORT is executed as an SQL application. The rows and columns satisfying the SELECT statement conditions are extracted from the database. For the DATALINK columns, the SELECT statement should not specify any scalar function. The export utility uses APIs to extract parts of the DATALINK value, such as link type, file server name, file path name, and comments.
Successful execution of EXPORT results in generation of the following files:
The dlfm_export utility is provided to export files from a file server.
dlfm_export control_file > filelist
Once the filelist is obtained, the user should archive the files listed in filelist, and restore the archive in the target file server. On UNIX based systems, the following can be done to accomplish this:
tar -cvBf - -L filelist | dd bs=256k of=/dev/rmt0
dd bs=256k if=/dev/rmt0 | tar xvBf -
| Note: | The export utility does not issue a warning if an attempt is made to use unsupported file types with the MODIFIED BY option. If this is attempted, the export fails, and an error code is returned. |
Table 5. Valid File Type Modifications (EXPORT)
| Modification | Description | ||
|---|---|---|---|
| All File Formats | |||
| dldelx | x is a single character DATALINK delimiter. The default
value is a semicolon (;). The specified character is used in place
of a semicolon as the inter-field separator for a DATALINK value. It is
needed because a DATALINK value may have more than one sub-value.
ab
| ||
| lobsinfile | lob-path specifies the path to the files containing LOB values. | ||
| DEL (Delimited ASCII) File Format | |||
| chardelx | x is a single character string delimiter. The default
value is a double quotation mark ("). The specified character is
used in place of double quotation marks to enclose a character
string.a
The single quotation mark (') can also be specified as a character string delimiter as follows: modified by chardel'' | ||
| coldelx | x is a single character column delimiter. The default
value is a comma (,). The specified character is used in place of
a comma to signal the end of a column.a
In the following example, coldel; causes the export utility to interpret any semicolon (;) it encounters as a column delimiter:
db2 "export to temp of del modified by coldel;
select * from staff where dept = 20"
| ||
| datesiso | Date format. Causes all date data values to be exported in ISO format. | ||
| decplusblank | Plus sign character. Causes positive decimal values to be prefixed with a blank space instead of a plus sign (+). The default action is to prefix positive decimal values with a plus sign. | ||
| decptx | x is a single character substitute for the period as a decimal point character. The default value is a period (.). The specified character is used in place of a period as a decimal point character.a | ||
| nodoubledel | Suppresses recognition of double character delimiters. | ||
| WSF File Format | |||
| 1 | Creates a WSF file that is compatible with Lotus 1-2-3 Release 1, or Lotus 1-2-3 Release 1a.b This is the default. | ||
| 2 | Creates a WSF file that is compatible with Lotus Symphony Release 1.0.b | ||
| 3 | Creates a WSF file that is compatible with Lotus 1-2-3 Version 2, or Lotus Symphony Release 1.1.b | ||
| 4 | Creates a WSF file containing DBCS characters. | ||
| |||
| Hex | Char | Character Name | ||
|---|---|---|---|---|
| X'22' | " | Double Quotation Marks | ||
| X'25' | % | Percent Sign | ||
| X'26' | & | Ampersand | ||
| X'27' | ' | Apostrophe | ||
| X'28' | ( | Left Parenthesis | ||
| X'29' | ) | Right Parenthesis | ||
| X'2A' | * | Asterisk | ||
| X'2C' | , | Comma | ||
| X'2E' | . | Period (not valid as a character string delimiter) | ||
| X'2F' | / | Slash | ||
| X'3A' | : | Colon | ||
| X'3B' | ; | Semicolon | ||
| X'3C' | < | Less Than Sign | ||
| X'3D' | = | Equals Sign | ||
| X'3E' | > | Greater Than Sign | ||
| X'3F' | ? | Question Mark | ||
| X'7C' | | | Vertical Bar | ||
| X'5F' | _ | Underscore (valid in the SBCS environment only) | ||
| ||||
The following restrictions apply to column, string, and decimal point
delimiters when moving data:
| ||||
| The following information about support for double character delimiter
recognition in DEL files applies to the load, import, and export
utilities:
Character delimiters are permitted within the character-based fields of a DEL file. Any pair of character delimiters found between the enclosing character delimiters is imported or loaded into the database. For example, "What a ""nice"" day!" will be imported as: What a "nice" day! In the case of export, the rule applies in reverse. For example, I am 6" tall. will be exported to a DEL file as: "I am 6"" tall." This support applies to fields of type CHAR, VARCHAR, LONG VARCHAR, or CLOB (except where LOBSINFILE is specified). | ||||
See Also