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