EXPORT command using the ADMIN_CMD procedure

Authorization

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

traversal-order-list

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

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

Result set information

SELECT SQLCODE, MSG
   FROM TABLE (SYSPROC.ADMIN_GET_MSGS
   ('3203498_txu')) AS MSG

CALL SYSPROC.ADMIN_REMOVE_MSGS
   ('3203498_txu')

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