EXPORT command

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

For more information, see File type modifiers for the export utility.

Authorization

One of the following authorities:
  • 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. Access must not be a direct connection through a Db2 Connect gateway or loop back environment.

Command syntax

Read syntax diagramSkip visual syntax diagramEXPORT TOfilenameOFfiletype LOBS TO,lob-pathLOBFILE,filenameXML TO,xml-pathXMLFILE,filenameMODIFIED BYfiletype-modXMLSAVESCHEMAMETHOD N(,column-name)MESSAGESmessage-fileselect-statementXQUERYxquery-statementHIERARCHYSTARTINGsub-table-nametraversal-order-listWHERE
traversal-order-list
Read syntax diagramSkip visual syntax diagram( ,sub-table-name )

Command parameters

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 exists is specified, the export utility overwrites the contents of the file; it does not append the information.

OF filetype
Specifies the format of the data in the output file:
  • DEL (delimited ASCII format), which is used by various database manager and file manager programs.
  • IXF (Integration Exchange Format, PC version) is a proprietary binary format.
LOBS TO lob-path
Specifies one or more paths to directories in which the LOB files are to be stored. There must be at least one file per LOB path, and each file must contain at least one LOB. The maximum number of paths that can be specified is 999. This setting implicitly activates the LOBSINFILE behavior.
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 setting implicitly activates the LOBSINFILE behavior.

When you create 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). Then, you must append 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 that are created are /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 grows to 4-digits once 999 is used, 4-digits grow to 5-digits once 9999 is used, and so on.

XML TO xml-path
Specifies one or more paths to directories in which the XML files are to be stored. There is at least one file per XML path, and each file contains at least one XQuery Data Model (XDM) instance. If more than one path is specified, then XDM instances are distributed evenly among the paths.
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 you create 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). Then, you must append a 3-digit sequence number, and 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 that are created are /u/foo/xml/path/bar.001.xml, /u/foo/xml/path/bar.002.xml, and so on.

MODIFIED BY filetype-mod
Specifies file type modifier options. See File type modifiers for the export utility.
XMLSAVESCHEMA
Specifies that XML schema information must 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 is 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.

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 IXF files, but is not valid when you export hierarchical data.
MESSAGES message-file
Specifies the destination for warning and error messages that occur during an export operation. If the file exists, the export utility appends the information. If message-file is omitted, the messages are written to standard output.
select-statement
Specifies the SELECT or XQUERY statement that returns 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.
If the SELECT statement is in the form of SELECT * FROM tablename and the table contains implicitly hidden columns, you must explicitly specify whether data for the hidden columns is included in the export operation. Use one of the following methods to indicate whether data for hidden columns is included:
  • Use one of the hidden column file type modifiers: specify implicitlyhiddeninclude when the export contains data for the hidden columns, or implicitlyhiddenmissing when the export does not.
    db2 export to t.del of del modified by implicitlyhiddeninclude
       select * from t
  • Use the DB2_DMU_DEFAULT registry variable on the client-side to set the default behavior when data movement utilities encounter tables with implicitly hidden columns.
    db2set DB2_DMU_DEFAULT=IMPLICITLYHIDDENINCLUDE
    db2 export to t.del of del select * from t 
HIERARCHY STARTING sub-table-name
Using the default traverse order (OUTER order for ASC or DEL files, or the order that is stored in PC/IXF data files), export a sub-hierarchy starting from sub-table-name.
HIERARCHY traversal-order-list
Export a sub-hierarchy by 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.

Examples

The following example shows how to export information from the STAFF table in the SAMPLE database to the file myfile.ixf. The output is in IXF format. You must be connected to the SAMPLE database before you issue the command. The index definitions (if any) are 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 is in IXF format and goes into the awards.ixf file. You must first connect to the SAMPLE database before you issue 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. This step is done by 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 must be used when you import 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 you start an export operation. This step can be done by issuing a COMMIT after you close 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 that are placed in the message file include the information that is returned from the message retrieval service. Each message begins on a new line.
  • PC/IXF import must be used to move data between databases. If character data that contains row separators is exported to a delimited ASCII (DEL) file and processed by a text transfer program, fields that contain the row separators shrinks or expands.
  • 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 z/OS®, Db2 for VM and VSE, and Db2 for OS/400®. Only PC/IXF export is supported.
  • When you export to the IXF format, if identifiers exceed the maximum size that is supported by the IXF format, the export succeeds. However, the resulting datafile cannot be used by a subsequent import operation by using the CREATE mode (SQL27984W).
  • When you export to the IXF format, the export utility does not maintain column-organized table metadata that is needed to re-create the column-organized table during a subsequent import operation by using the CREATE mode.
  • When you export to a diskette on Windows, and the table that has more data than the capacity of a single diskette, the system prompts for another diskette. 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. This record indicates that the files are logically split and shows 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 stores 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 you export typed tables, subselect statements can be expressed only by specifying the target table name and the WHERE clause. Fullselect and select-statement cannot be specified when you export a hierarchy.
  • For file formats other than IXF, it is recommended that you specify the traversal order list. The list 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 that is 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 you export data from a table that has protected rows, the LBAC credentials that are held by the session authorization id might limit the rows that are exported. If the session authorization ID does not have read access to the rows, then they will not be exported. No error or warning is given.
  • If the LBAC credentials that are 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 you run Data Movement utilities such as export and db2move, the query compiler might determine that the underlying query runs more efficiently against an MQT than the base table or tables. In this case, the query executes 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 by using the 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 by using the DATETIME LOC format (locale-specific format), you might see inconsistent behavior between CLP and export if the CLP DATETIME format is different from ISO. For instance, the following SELECT statement might 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 that uses 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 that contain 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 that is 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 at offset 123 in the file db2exp.001, and is 456 bytes long.

If you specify the lobsinfile modifier when you use EXPORT, the LOB data is placed in the locations that are 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 is at least one file per LOB path, and each file contains at least one LOB. The LOBS TO or LOBFILE options implicitly activates 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/.

implicitlyhiddeninclude This modifier is used with SELECT * queries and specifies that the data in implicitly hidden columns is exported even though that data is not included in the result of the SELECT * query. This modifier cannot be used with the implicitlyhiddenmissing modifier.

If this modifier is used and the query is not a SELECT *, then an error is returned (SQLCODE SQL3526N).

This modifier does not apply to the hidden RANDOM_DISTRIBUTION_KEY column of a random distribution tables that uses the random by generation method. The column must be explicitly referenced in the query to be included in the exported data.

implicitlyhiddenmissing This modifier is used with SELECT * queries and specifies that the data in implicitly hidden columns is not exported. This modifier cannot be used with the implicitlyhiddeninclude modifier.

If this modifier is used and the query is not a SELECT *, then an error is returned (SQLCODE SQL3526N).

This modifier does not apply to the hidden RANDOM_DISTRIBUTION_KEY column of a random distribution tables that uses the random by generation method. The column must be explicitly referenced in the query to be included in the exported data.

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 code page. The character codepage is the value that is specified by the codepage file type modifier, or the application code page 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 is 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
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.2 If you want to explicitly specify the double quotation mark as the character string delimiter, it must 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 from the application code page to this 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.

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.2
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.
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.2
nochardel Column data is not surrounded by character delimiters. This option must not be specified if the data is intended to be imported or loaded by 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 options are mutually exclusive.

nodoubledel Suppresses recognition of double character delimiters.2
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 are +00000000000000000000000000001.10. In the second operation, which is identical to the first except for the striplzeros modifier, the content of the exported file data are +1.10.
timestampformat="x" x is the format of the time stamp in the source file.4 Valid time stamp elements are:
YYYY
Year (4 digits in the range 0000 - 9999)
M
Month (1 or 2 digits in the range 1 - 12)
MM
Month (2 digits in the range 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 (1 or 2 digits in the range 1 - 31)
DD
Day (2 digits in the range 01 - 31; mutually exclusive with D)
DDD
Day of the year (3 digits in the range 001 - 366; mutually exclusive with other day or month elements)
H
Hour (1 or 2 digits in the range 0 - 12 for a 12-hour system, and 0 - 24 for a 24-hour system)
HH
Hour (2 digits in the range 00 - 12 for a 12-hour system, and 00 - 24 for a 24-hour system; mutually exclusive with H)
M
Minute (1 or 2 digits in the range 0 - 59)
MM
Minute (2 digits in the range 00 - 59; mutually exclusive with M, minute)
S
Second (1 or 2 digits in the range 0 - 59)
SS
Second (2 digits in the range 00 - 59; mutually exclusive with S)
SSSSS
Second of the day after midnight (5 digits in the range 00000 - 86400; mutually exclusive with other time elements)
U (1 - 12 times)
Fractional seconds (number of occurrences of U represent the number of digits with each digit in the range 0 - 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 produces 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 that contains 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 the application code page to this 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.

Note:
  1. 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 step is attempted, the export operation fails, and an error code is returned.
  2. Delimiter considerations for moving data lists restrictions that apply to the characters that can be used as delimiter overrides.
  3. 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 that is contained in any datetime columns that are specified in the SELECT statement for the export operation are also in these formats.
  4. 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 next to other date fields. A minute field must be next 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 reports an error message, and the operation fails.
    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)
  5. All XDM instances are written to XML files that are separate from the main data file, even if the XMLFILE and XML TO clauses are not 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.
  6. 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.
  7. By default, all XDM instances are written in Unicode unless the XMLCHAR or XMLGRAPHIC file type modifier is specified.
  8. 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
    The default LOB file base name is:
    myfile.del

    The LOBSINFILE file type modifier must be specified in order to have LOB files generated.

  9. 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 zeros (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 that represents 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 have a name in the format:
    myfile.del.001.xml
  10. It is possible to have the export utility export XDM instances that are not well-formed documents by specifying an XQuery. However, you cannot import or load these exported documents directly into an XML column, since XML columns can contain only complete documents.