EXPORT command

Authorization

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

traversal-order-list

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

If DB2_LOAD_RESTRICTED_IO_PATH is enabled, the file must exist within the restricted paths.

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.

If DB2_LOAD_RESTRICTED_IO_PATH is enabled, the lob-path must exist within the restricted paths.

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 three-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 three-digit sequence number in the LOB file name grows to four digits when 999 is used, four digits grow to five digits when 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.

If DB2_LOAD_RESTRICTED_IO_PATH is enabled, the xml-path must exist within the restricted 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 three-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 is validated against an XML schema when it is inserted, the fully qualified SQL identifier of that schema is stored as an SCH attribute. This attribute is stored inside the corresponding XML Data Specifier (XDS). If the exported document is not validated against an XML schema or the schema object no longer exists in the database, an SCH attribute is not 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 subhierarchy, starting from sub-table-name.

HIERARCHY traversal-order-list

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

Examples

db2 export to myfile.ixf of ixf messages msgs.txt select * from staff

db2 export to awards.ixf of ixf messages msgs.txt select * from staff
      where dept = 20

   db2 export to myfile.del of del lobs to mylobs/
      lobfile lobs1, lobs2 modified by lobsinfile
      select * from emp_photo

   db2 export to myfile.del of del
      lobs to /db2exp1/, /db2exp2/ modified by lobsinfile
      select * from emp_photo

   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 that are 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 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 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 data file 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, except for the last, a Db2 CONTINUATION RECORD (or "AC" Record in short) is 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 does not create multiple-part PC/IXF files when launched 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, specify the traversal order list. The list tells Db2 how to traverse the hierarchy, and what subtables 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 subhierarchies.
• When you export data from a table that contains 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-only access to the rows, the rows are not exported. No error or warning is given.
• The LBAC credentials that are held by the session authorization ID must allow reading from one or more protected columns that are included in the export. If these credentials do not allow reading from these protected columns, the export fails and a SQLSTATE 42512 error 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 runs 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

   modified by chardel""

   modified by chardel''

   db2 "export to temp of del modified by coldel;
      select * from staff where dept = 20"

   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

   "YYYY/MM/DD HH:MM:SS.UUUUUU"

   db2 export to delfile2 of del
      modified by timestampformat="yyyy.mm.dd hh:mm tt"
      select * from schedule