DB2 10.5 for Linux, UNIX, and Windows

EXPORT command using the ADMIN_CMD procedure

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. The data is exported to the server only.

Quick link to 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. 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

Read syntax diagramSkip visual syntax diagram
>>-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 ON SERVER-'   

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

traversal-order-list

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

Command parameters

TO filename
Specifies the name of the file to which data is to be exported to on the server. This must be a fully qualified path and must exist on the server coordinator partition.

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.

Note that with or without the clause, the fenced user ID must have the authority to create files under the directory indicated by the DB2_UTIL_MSGPATH registry variable, as well as the directory where the data is to be exported to.

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.
  • 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. The path(s) must exist on the coordinator partition of the server and must be fully qualified. 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.
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.

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

MODIFIED BY filetype-mod
Specifies file type modifier options. See File type modifiers for the export utility.
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.

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 exporting hierarchical data.
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.
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 if 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 stored in PC/IXF data files), export a sub-hierarchy starting from sub-table-name.
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.
MESSAGES ON SERVER
Specifies that the message file created on the server by the EXPORT command is to be saved. The result set returned will include the following two columns: MSG_RETRIEVAL, which is the SQL statement required to retrieve all the warnings and error messages that occur during this operation, and MSG_REMOVAL, which is the SQL statement required to clean up the messages.

If this clause is not specified, the message file will be deleted when the ADMIN_CMD procedure returns to the caller. The MSG_RETRIEVAL and MSG_REMOVAL column in the result set will contain null values.

Note that with or without the clause, the fenced user ID or the instance owner's primary group must have the authority to create files under the directory indicated by the DB2_UTIL_MSGPATH registry variable, as well as the directory where the data is to be exported to.

Example

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.
CALL SYSPROC.ADMIN_CMD ('EXPORT to /home/user1/data/myfile.ixf 
   OF ixf MESSAGES ON SERVER select * from staff')

Usage notes

Result set information

Command execution status is returned in the SQLCA resulting from the CALL statement. If execution is successful, the command returns additional information in result sets as follows:
Table 1. Result set returned by the EXPORT command
Column name Data type Description
ROWS_EXPORTED BIGINT Total number of exported rows.
MSG_RETRIEVAL VARCHAR(512) SQL statement that is used to retrieve messages created by this utility. For example:
SELECT SQLCODE, MSG
   FROM TABLE (SYSPROC.ADMIN_GET_MSGS
   ('3203498_txu')) AS MSG
MSG_REMOVAL VARCHAR(512) SQL statement that is used to clean up messages created by this utility. For example:
CALL SYSPROC.ADMIN_REMOVE_MSGS
   ('3203498_txu')

File type modifiers for the export utility

Table 2. 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/.

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

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

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. Note that the character codepage is the value 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 will be encoded in the UTF-16 code page regardless of the application code page or the codepage file type modifier.
Table 3. 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 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.

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

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 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 contained in any datetime columns specified in the SELECT statement for the export operation will also be 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 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)
  5. 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.
  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
    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.

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