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.
Command Syntax
>>-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
When creating LOB files during an export operation, 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.
| Note: | When exporting BIGINT or DECIMAL data, only values that fall within the range of type DOUBLE can be exported accurately. Although values that do not fall within this range are also exported, importing or loading these values back may result in incorrect data, depending on the operating system. |
For more information about file formats, see the "Export/Import/Load Utility File Formats" appendix in the Data Movement Utilities Guide and Reference.
If the name of a file that already exists is specified, the export utility overwrites the contents of the file; it does not append the information.
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. If the database connection is not through DB2 Connect, the index definitions (if any) will be stored in the output file; otherwise, only the data will be stored:
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 SAMPLE 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
The following example shows how to export LOBs to an DEL file:
db2 export to myfile.del of del lobs to mylobs
lobfile lobs1, lobs2 modified by lobsinfile
select * from emp_photo
The following example shows how to export LOBs to a DEL file, specifying a second directory for files that may not fit into the first directory:
db2 export to myfile.del of del
lobs to /db2exp1, /db2exp2 modified by lobsinfile
select * from emp_photo
The following example shows how to export data to a DEL file, using a single quotation mark as the string delimiter, a semicolon as the column delimiter, and a comma as the decimal point. The same convention should be used when importing data back into the database:
db2 export to myfile.del of del
modified by chardel'' coldel; decpt,
select * from staff
Usage Notes
Be sure to complete all table operations and release all locks before starting an export operation. This can be done by issuing a COMMIT after closing all cursors opened WITH HOLD, or by issuing a ROLLBACK.
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 utility will not create multiple-part PC/IXF files when invoked from an AIX system.
The export utility will store the NOT NULL WITH DEFAULT attribute of the table in an IXF file if the SELECT statement provided is in the form SELECT * FROM tablename.
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 Data Links 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.
Successful execution of EXPORT results in generation of the following files:
The dlfm_export utility is provided to export files from a
Data Links server. This utility generates an archive file, which can be
used to restore files in the target Data Links server.
Table 5. Valid File Type Modifiers (Export)
| Modifier | Description | ||
|---|---|---|---|
| All File Formats | |||
| 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 | ||
| 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
| ||
| 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. | ||
|
Notes:
| |||
It is the user's responsibility to ensure that the chosen delimiter character is not part of the data to be moved. If it is, unexpected errors may occur. The following restrictions apply to column, string, DATALINK, and decimal point delimiters when moving data:
" (0x22, double quotation mark; string delimiter) , (0x2c, comma; column delimiter)
" (0x7F, double quotation mark; string delimiter) , (0x6B, comma; column delimiter)
db2 load from ... modified by chardel0x0C coldelX1e ...
The following information about support for double character delimiter recognition in DEL files applies to the export, import, and load utilities:
"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."
See Also
LOAD.