EXPORT command using the ADMIN_CMD procedure
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. The data is exported to the server only.
For more information, see File type modifiers for the export utility.
Authorization
- 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. Access must not be a direct connection through a Db2 Connect gateway or loop back environment.
Command syntax
Command parameters
- TO filename
- Specifies the name of the file to which data is to be
exported to on the server. This parameter must be a fully qualified path and must exist on the
server coordinator partition. The maximum length of the filename parameter is 255
bytes.
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.
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.
- 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. The path (or paths) must exist on the coordinator partition of the server and
must be fully qualified. 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 isbar
, 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 isbar
, 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.
- 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
- 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.
- 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.
- MESSAGES ON SERVER
- Specifies that the message file that is created on the server by the EXPORT
command is to be saved. The result set returned includes the following two columns: MSG_RETRIEVAL,
and MSG_REMOVAL. MSG_RETRIEVAL is the SQL statement that is required to retrieve all the warnings
and error messages that occur during this operation. MSG_REMOVAL is the SQL statement that is
required to clean up the messages.
If this clause is not specified, the message file is deleted when the ADMIN_CMD procedure returns to the caller. The MSG_RETRIEVAL and MSG_REMOVAL column in the result set contains null values.
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.
Example
CALL SYSPROC.ADMIN_CMD ('EXPORT to /home/user1/data/myfile.ixf
OF ixf MESSAGES ON SERVER select * from staff')
Usage notes
- Any path that is used in the EXPORT command must be a valid fully qualified path on the server.
- If a table contains LOB columns, at least one fully qualified LOB path and LOB name must be specified, by using the LOBS TO and LOBFILE clauses.
- The export utility issues a COMMIT statement at the beginning of the operation, which with Type 2 connections, causes the procedure to return SQL30090N with reason code 2.
- When you export from a UCS-2 database to a delimited ASCII (DEL) file, all character data is converted to the code page that is in effect where the procedure is executing. Both character string and graphic string data are converted to the same SBCS or MBCS code page of the server.
- 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 theDATETIME 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:
But an export command that uses the same select clause will not: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.
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)='05/10/2005'; Number of rows exported: 0
db2 export to test.del of del select col2 from test where char(col2)='2005-05-10'; Number of rows exported: 3
Result set information
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 that are created by this
utility. For example:
|
MSG_REMOVAL | VARCHAR(512) | SQL statement that is used to clean up messages that are created by this
utility. For example:
|
File type modifiers for the export utility
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
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. |
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:
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 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:
|
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 |
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 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:
Following is an example of a time stamp format:
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':
|
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. |
- 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.
- Delimiter considerations for moving data lists restrictions that apply to the characters that can be used as delimiter overrides.
- The export utility normally writes date and time data types in the following formats:
date
data in YYYYMMDD formatchar(date)
data in "YYYY-MM-DD" formattime
data in "HH.MM.SS" formattimestamp
data in "YYYY-MM-DD-HH.MM.SS.uuuuuu
" format
- 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:
In ambiguous cases, the utility reports an error message, and the operation fails."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)
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)
- 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.
- 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.
- By default, all XDM instances are written in Unicode unless the XMLCHAR or XMLGRAPHIC file type modifier is specified.
- 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:
The default path for XML data and LOB data is:/mypath/myfile.del
The default XML file base name is:/mypath"
The default LOB file base name is:myfile.del
myfile.del
The LOBSINFILE file type modifier must be specified in order to have LOB files generated.
- 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:
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:.001
Following the numeric identifier is a three character type identifier that represents the data type, either:.1000
or.lob
For example, a generated LOB file would have a name in the format:.xml
and a generated XML file would have a name in the format:myfile.del.001.lob
myfile.del.001.xml
- 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.