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.
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.
>>-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-+--)-------------------------------------|
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.
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 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.
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.
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.
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.
CALL SYSPROC.ADMIN_CMD ('EXPORT to /home/user1/data/myfile.ixf
OF ixf MESSAGES ON SERVER select * from staff')
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
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:
|
MSG_REMOVAL | VARCHAR(512) | SQL statement that is used to clean up messages
created by this utility. For example:
|
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. |
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:
The single
quotation mark (') can also be specified as a character string delimiter
as follows:
|
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. The codepage modifier cannot be used with the lobsinfile modifier. |
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:
|
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:
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:
Following
is an example of a time stamp format:
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':
|
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. |
Modifier | Description |
---|---|
1 | Creates a WSF file that is compatible with Lotus 1-2-3 Release 1, or Lotus 1-2-3 Release 1a.5 This is the default. |
2 | Creates a WSF file that is compatible with Lotus Symphony Release 1.0.5 |
3 | Creates a WSF file that is compatible with Lotus 1-2-3 Version 2, or Lotus Symphony Release 1.1.5 |
4 | Creates a WSF file containing DBCS characters. |
"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. "M:YYYY" (Month)
"S:M" (Minute)
"M:YYYY:S:M" (Month....Minute)
"M:H:YYYY:M:D" (Minute....Month)
/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.
.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