DB2 Version 9.7 for Linux, UNIX, and Windows

EXPORT command

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.

Quick link to File type modifiers for the export utility.

Authorization

One of the following:

DATAACCESS authority
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. Utility access to Linux, UNIX, or Windows database servers from Linux, UNIX, or Windows clients must be a direct connection through the engine and not through a DB2 Connect™ gateway or loop back environment.

Command syntax


>>-EXPORT TO--filename--OF--filetype---------------------------->

>--+-----------------------+--+-----------------------+--------->
   |          .-,--------. |  |          .-,--------. |   
   |          V          | |  |          V          | |   
   '-LOBS TO----lob-path-+-'  '-LOBFILE----filename-+-'   

>--+----------------------+--+-----------------------+---------->
   |         .-,--------. |  |          .-,--------. |   
   |         V          | |  |          V          | |   
   '-XML TO----xml-path-+-'  '-XMLFILE----filename-+-'   

>--+-------------------------------+--+---------------+--------->
   |              .--------------. |  '-XMLSAVESCHEMA-'   
   |              V              | |                      
   '-MODIFIED BY----filetype-mod-+-'                      

>--+---------------------------------+-------------------------->
   |              .-,-----------.    |   
   |              V             |    |   
   '-METHOD N--(----column-name-+--)-'   

>--+------------------------+----------------------------------->
   '-MESSAGES--message-file-'   

>--+-select-statement---------------------------------------+--><
   +-XQUERY--xquery-statement-------------------------------+   
   '-HIERARCHY--+-STARTING--sub-table-name-+--+-----------+-'   
                '-| traversal-order-list |-'  | .-------. |     
                                              | V       | |     
                                              '---WHERE-+-'     

traversal-order-list

      .-,--------------.      
      V                |      
|--(----sub-table-name-+--)-------------------------------------|

Command parameters

HIERARCHY traversal-order-list

Export a sub-hierarchy using the specified traverse order. All sub-tables must be listed in PRE-ORDER fashion. The first sub-table name is used as the target table name for the SELECT statement.

HIERARCHY STARTING sub-table-name

Using the default traverse order (OUTER order for ASC, DEL, or WSF files, or the order stored in PC/IXF data files), export a sub-hierarchy starting from sub-table-name.

LOBFILE filename

Specifies one or more base file names for the LOB files. When name space is exhausted for the first name, the second name is used, and so on. This will implicitly activate the LOBSINFILE behavior.

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 to start and the three character identifier lob. 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.lob, /u/foo/lob/path/bar.002.lob, and so on. The 3-digit sequence number in the LOB file name will grow to 4-digits once 999 is used, 4-digits will grow to 5-digits once 9999 is used, and so on.

LOBS TO lob-path

Specifies one or more paths to directories in which the LOB files are to be stored. There will be at least one file per LOB path, and each file will contain at least one LOB. The maximum number of paths that can be specified is 999. This will implicitly activate the LOBSINFILE behavior.

MESSAGES message-file

Specifies the destination for warning and error messages that occur during an export operation. If the file already exists, the export utility appends the information. If message-file is omitted, the messages are written to standard output.

METHOD N column-name

Specifies one or more column names to be used in the output file. If this parameter is not specified, the column names in the table are used. This parameter is valid only for WSF and IXF files, but is not valid when exporting hierarchical data.

MODIFIED BY filetype-mod

Specifies file type modifier options. See File type modifiers for the export utility.

OF filetype

Specifies the format of the data in the output file:

DEL (delimited ASCII format), which is used by a variety of database manager and file manager programs.
WSF (work sheet format), which is used by programs such as:
- Lotus® 1-2-3®
- Lotus Symphony®
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 might result in incorrect data, depending on the operating system.
Note: Support for the WSF file format is deprecated and might be removed in a future release. It is recommended that you start using a supported file format instead of WSF files before support is removed.
IXF (Integration Exchange Format, PC version) is a proprietary binary format.

select-statement

Specifies the SELECT or XQUERY statement that will return the data to be exported. If the statement causes an error, a message is written to the message file (or to standard output). If the error code is one of SQL0012W, SQL0347W, SQL0360W, SQL0437W, or SQL1824W, the export operation continues; otherwise, it stops.

TO filename

Specifies the name of the file to which data is to be exported. If the complete path to the file is not specified, the export utility uses the current directory and the default drive as the destination.

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.

XMLFILE filename

Specifies one or more base file names for the XML files. When name space is exhausted for the first name, the second name is used, and so on.

When creating XML files during an export operation, file names are constructed by appending the current base name from this list to the current path (from xml-path), appending a 3-digit sequence number, and appending the three character identifier xml. For example, if the current XML path is the directory /u/foo/xml/path/, and the current XML file name is bar, the XML files created will be /u/foo/xml/path/bar.001.xml, /u/foo/xml/path/bar.002.xml, and so on.

XML TO xml-path

Specifies one or more paths to directories in which the XML files are to be stored. There will be at least one file per XML path, and each file will contain at least one XQuery Data Model (XDM) instance. If more than one path is specified, then XDM instances are distributed evenly among the paths.

XMLSAVESCHEMA

Specifies that XML schema information should be saved for all XML columns. For each exported XML document that was validated against an XML schema when it was inserted, the fully qualified SQL identifier of that schema will be stored as an (SCH) attribute inside the corresponding XML Data Specifier (XDS). If the exported document was not validated against an XML schema or the schema object no longer exists in the database, an SCH attribute will not be included in the corresponding XDS.

The schema and name portions of the SQL identifier are stored as the "OBJECTSCHEMA" and "OBJECTNAME" values in the row of the SYSCAT.XSROBJECTS catalog table corresponding to the XML schema.

The XMLSAVESCHEMA option is not compatible with XQuery sequences that do not produce well-formed XML documents.

Examples

The following example shows how to export information from the STAFF table in the SAMPLE database to the file myfile.ixf. The output will be in IXF format. You must be connected to the SAMPLE database before issuing the command. The index definitions (if any) will be stored in the output file except when the database connection is made through DB2 Connect.

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. The output will be in IXF format and will go into the awards.ixf file. You must first connect to the SAMPLE database before issuing the command. Also, the actual column name in the table is 'dept' instead of 'department'.

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 a 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 might 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.
You might encounter the SQL27981W message when it does not seem applicable, such as when the EXPORT table is not partitioned. You can safely ignore this warning in this case. This warning message might be returned if the SELECT statement of the EXPORT command includes the word ' from ' before the SQL keyword FROM.
The messages placed in the message file include the information returned from the message retrieval service. Each message begins on a new line.
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, fields containing the row separators will shrink or expand.
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.
When exporting to the IXF format, if identifiers exceed the maximum size supported by the IXF format, the export will succeed but the resulting datafile cannot be used by a subsequent import operation using the CREATE mode. SQL27984W is returned.
When exporting to a diskette on Windows, and the table that has more data than the capacity of a single diskette, the system will prompt for another diskette, and multiple-part PC/IXF files (also known as multi-volume PC/IXF files, or logically split PC/IXF files), are generated and stored in separate diskettes. In each file, with the exception of the last, there is a DB2 CONTINUATION RECORD (or "AC" Record in short) written to indicate the files are logically split and where to look for the next file. The files can then be transferred to an AIX® system, to be read by the import and load utilities. The export utility will not create multiple-part PC/IXF files when invoked from an AIX system. For detailed usage, see the IMPORT command or LOAD command.
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.
Use the same traverse order during an import operation. The load utility does not support loading hierarchies or sub-hierarchies.
When exporting data from a table that has protected rows, the LBAC credentials held by the session authorization id might limit the rows that are exported. Rows that the session authorization ID does not have read access to will not be exported. No error or warning is given.
If the LBAC credentials held by the session authorization id do not allow reading from one or more protected columns included in the export then the export fails and an error (SQLSTATE 42512) is returned.
When running Data Movement utilities such as export and db2move, the query compiler might determine that the underlying query will run more efficiently against an MQT than the base table or tables. In this case, the query will execute against a refresh deferred MQT, and the result of the utilities might not accurately represent the data in the underlying table.
Export packages are bound using DATETIME ISO format, thus, all date/time/timestamp values are converted into ISO format when cast to a string representation. Since the CLP packages are bound using DATETIME LOC format (locale specific format), you may see inconsistent behavior between CLP and export if the CLP DATETIME format is different from ISO. For instance, the following SELECT statement may return expected results:
```
   db2 select col2 from tab1 where char(col2)='05/10/2005';
      COL2
      ----------
      05/10/2005
      05/10/2005
      05/10/2005
      3 record(s) selected.
```
But an export command using the same select clause will not:
```
   db2 export to test.del of del select col2 from test 
   where char(col2)='05/10/2005';
      Number of rows exported: 0
```
Now, replacing the LOCALE date format with ISO format gives the expected results:
```
   db2 export to test.del of del select col2 from test
   where char(col2)='2005-05-10';
      Number of rows exported: 3
```

File type modifiers for the export utility

Table 1. Valid file type modifiers for the export utility: All file formats
Modifier	Description
lobsinfile	`lob-path` specifies the path to the files containing LOB data. Each path contains at least one file that contains at least one LOB pointed to by a Lob Location Specifier (LLS) in the data file. The LLS is a string representation of the location of a LOB in a file stored in the LOB file path. The format of an LLS is `filename.ext.nnn.mmm/`, where `filename.ext` is the name of the file that contains the LOB, `nnn` is the offset in bytes of the LOB within the file, and `mmm` is the length of the LOB in bytes. For example, if the string `db2exp.001.123.456/` is stored in the data file, the LOB is located at offset 123 in the file db2exp.001, and is 456 bytes long. If you specify the lobsinfile modifier when using EXPORT, the LOB data is placed in the locations specified by the LOBS TO clause. Otherwise the LOB data is sent to the data file directory. The LOBS TO clause specifies one or more paths to directories in which the LOB files are to be stored. There will be at least one file per LOB path, and each file will contain at least one LOB. The LOBS TO or LOBFILE options will implicitly activate the LOBSINFILE behavior. To indicate a null LOB , enter the size as -1. If the size is specified as 0, it is treated as a 0 length LOB. For null LOBS with length of -1, the offset and the file name are ignored. For example, the LLS of a null LOB might be db2exp.001.7.-1/.
xmlinsepfiles	Each XQuery Data Model (XDM) instance is written to a separate file. By default, multiple values are concatenated together in the same file.
lobsinsepfiles	Each LOB value is written to a separate file. By default, multiple values are concatenated together in the same file.
xmlnodeclaration	XDM instances are written without an XML declaration tag. By default, XDM instances are exported with an XML declaration tag at the beginning that includes an encoding attribute.
xmlchar	XDM instances are written in the character codepage. Note that the character codepage is the value specified by the `codepage` file type modifier, or the application codepage if it is not specified. By default, XDM instances are written out in Unicode.
xmlgraphic	If the `xmlgraphic` modifier is specified with the EXPORT command, the exported XML document will be encoded in the UTF-16 code page regardless of the application code page or the `codepage` file type modifier.

Table 2. Valid file type modifiers for the export utility: DEL (delimited ASCII) file format
Modifier	Description
chardel`x`	`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.² If you want to explicitly specify the double quotation mark as the character string delimiter, it should be specified as follows: `modified by chardel""` The single quotation mark (') can also be specified as a character string delimiter as follows: `modified by chardel''`
codepage=`x`	`x` is an ASCII character string. The value is interpreted as the code page of the data in the output data set. Converts character data to this code page from the application code page during the export operation. For pure DBCS (graphic), mixed DBCS, and EUC, delimiters are restricted to the range of x00 to x3F, inclusive.
coldel`x`	`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.² In the following example, `coldel;` causes the export utility to use the semicolon character (;) as a column delimiter for the exported data: `db2 "export to temp of del modified by coldel; select * from staff where dept = 20"`
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.
decpt`x`	`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.²
nochardel	Column data will not be surrounded by character delimiters. This option should not be specified if the data is intended to be imported or loaded using DB2. It is provided to support vendor data files that do not have character delimiters. Improper usage might result in data loss or corruption. This option cannot be specified with `chardelx` or `nodoubledel`. These are mutually exclusive options.
nodoubledel	Suppresses recognition of double character delimiters.²
striplzeros	Removes the leading zeros from all exported decimal columns. Consider the following example: `db2 create table decimalTable ( c1 decimal( 31, 2 ) ) db2 insert into decimalTable values ( 1.1 ) db2 export to data of del select * from decimalTable db2 export to data of del modified by STRIPLZEROS select * from decimalTable` In the first export operation, the content of the exported file data will be +00000000000000000000000000001.10. In the second operation, which is identical to the first except for the `striplzeros` modifier, the content of the exported file data will be +1.10.
timestampformat="`x`"	`x` is the format of the time stamp in the source file.⁴ Valid time stamp elements are: YYYY - Year (four digits ranging from 0000 - 9999) M - Month (one or two digits ranging from 1 - 12) MM - Month (two digits ranging from 01 - 12; mutually exclusive with M and MMM) MMM - Month (three-letter case-insensitive abbreviation for the month name; mutually exclusive with M and MM) D - Day (one or two digits ranging from 1 - 31) DD - Day (two digits ranging from 01 - 31; mutually exclusive with D) DDD - Day of the year (three digits ranging from 001 - 366; mutually exclusive with other day or month elements) H - Hour (one or two digits ranging from 0 - 12 for a 12 hour system, and 0 - 24 for a 24 hour system) HH - Hour (two digits ranging from 00 - 12 for a 12 hour system, and 00 - 24 for a 24 hour system; mutually exclusive with H) M - Minute (one or two digits ranging from 0 - 59) MM - Minute (two digits ranging from 00 - 59; mutually exclusive with M, minute) S - Second (one or two digits ranging from 0 - 59) SS - Second (two digits ranging from 00 - 59; mutually exclusive with S) SSSSS - Second of the day after midnight (5 digits ranging from 00000 - 86400; mutually exclusive with other time elements) U (1 to 12 times) - Fractional seconds(number of occurrences of U represent the number of digits with each digit ranging from 0 to 9 TT - Meridian indicator (AM or PM) Following is an example of a time stamp format: `"YYYY/MM/DD HH:MM:SS.UUUUUU"` The MMM element will produce the following values: 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', and 'Dec'. 'Jan' is equal to month 1, and 'Dec' is equal to month 12. The following example illustrates how to export data containing user-defined time stamp formats from a table called 'schedule': `db2 export to delfile2 of del modified by timestampformat="yyyy.mm.dd hh:mm tt" select * from schedule`

Table 3. Valid file type modifiers for the export utility: IXF file format
Modifier	Description
codepage=`x`	`x` is an ASCII character string. The value is interpreted as the code page of the data in the output data set. Converts character data from this code page to the application code page during the export operation. For pure DBCS (graphic), mixed DBCS, and EUC, delimiters are restricted to the range of x00 to x3F, inclusive.

Table 4. Valid file type modifiers for the export utility: WSF file format⁶
Modifier	Description
1	Creates a WSF file that is compatible with Lotus 1-2-3 Release 1, or Lotus 1-2-3 Release 1a.⁵ This is the default.
2	Creates a WSF file that is compatible with Lotus Symphony Release 1.0.⁵
3	Creates a WSF file that is compatible with Lotus 1-2-3 Version 2, or Lotus Symphony Release 1.1.⁵
4	Creates a WSF file containing DBCS characters.

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 operation fails, and an error code is returned.
Delimiter considerations for moving data lists restrictions that apply to the characters that can be used as delimiter overrides.
The export utility normally writes
- date data in YYYYMMDD format
- char(date) data in "YYYY-MM-DD" format
- time data in "HH.MM.SS" format
- time stamp data in "YYYY-MM-DD-HH.MM.SS.uuuuuu" format
Data contained in any datetime columns specified in the SELECT statement for the export operation will also be in these formats.
For time stamp formats, care must be taken to avoid ambiguity between the month and the minute descriptors, since they both use the letter M. A month field must be adjacent to other date fields. A minute field must be adjacent to other time fields. Following are some ambiguous time stamp formats:
```
   "M" (could be a month, or a minute)
   "M:M" (Which is which?)
   "M:YYYY:M" (Both are interpreted as month.)
   "S:M:YYYY" (adjacent to both a time value and a date value)
```
In ambiguous cases, the utility will report an error message, and the operation will fail.
Following are some unambiguous time stamp formats:
```
   "M:YYYY" (Month)
   "S:M" (Minute)
   "M:YYYY:S:M" (Month....Minute)
   "M:H:YYYY:M:D" (Minute....Month)
```
These files can also be directed to a specific product by specifying an L for Lotus 1-2-3, or an S for Symphony in the filetype-mod parameter string. Only one value or product designator can be specified. Support for the WSF file format is deprecated and might be removed in a future release. It is recommended that you start using a supported file format instead of WSF files before support is removed.
The WSF file format is not supported for XML columns. Support for this file format is deprecated and might be removed in a future release. It is recommended that you start using a supported file format instead of WSF files before support is removed.
All XDM instances are written to XML files that are separate from the main data file, even if neither the XMLFILE nor the XML TO clause is specified. By default, XML files are written to the path of the exported data file. The default base name for XML files is the name of the exported data file with the extension ".xml" appended to it.
All XDM instances are written with an XML declaration at the beginning that includes an encoding attribute, unless the XMLNODECLARATION file type modifier is specified.
By default, all XDM instances are written in Unicode unless the XMLCHAR or XMLGRAPHIC file type modifier is specified.
The default path for XML data and LOB data is the path of the main data file. The default XML file base name is the main data file. The default LOB file base name is the main data file. For example, if the main data file is:
```
/mypath/myfile.del
```
the default path for XML data and LOB data is:
```
/mypath"
```
the default XML file base name is:
```
myfile.del
```
and the default LOB file base name is:
```
myfile.del
```
The LOBSINFILE file type modifier must be specified in order to have LOB files generated.
The export utility appends a numeric identifier to each LOB file or XML file. The identifier starts as a 3 digit, 0 padded sequence value, starting at:
```
.001
```
After the 999th LOB file or XML file, the identifier will no longer be padded with zeroes (for example, the 1000th LOG file or XML file will have an extension of:
```
.1000
```
Following the numeric identifier is a three character type identifier representing the data type, either:
```
.lob
```
or
```
.xml
```
For example, a generated LOB file would have a name in the format:
```
myfile.del.001.lob
```
and a generated XML file would be have a name in the format:
```
myfile.del.001.xml
```
It is possible to have the export utility export XDM instances that are not well-formed documents by specifying an XQuery. However, you will not be able to import or load these exported documents directly into an XML column, since XML columns can only contain complete documents.