Table export considerations

A typical export operation involves the outputting of selected data that is inserted or loaded into existing tables. However, it is also possible to export an entire table for subsequent re-creation using the import utility.

To export a table, you must specify the PC/IXF file format. You can then re-create your saved table (including its indexes) using the import utility in CREATE mode. However, some information is not saved to the exported IXF file if any of the following conditions exist:
  • The index column names contain hexadecimal values of 0x2B or 0x2D.
  • The table contains XML columns.
  • The table is multidimensional clustered (MDC).
  • The table contains a table partitioning key.
  • The index name is longer than 128 bytes due to code page conversion.
  • The table is protected.
  • The EXPORT command contains action strings other than SELECT * FROM tablename
  • You specify the METHOD N parameter for the export utility.
For a list of table attributes that are lost, see "Table import considerations." If any information is not saved, warning SQL27984W is returned when the table is re-created.
Note: Import's CREATE mode is being deprecated. Use the db2look utility to capture and re-create your tables.
Index information
If the column names specified in the index contain either - or + characters, the index information is not collected, and warning SQL27984W is returned. The export utility completes its processing, and the data exported is unaffected. However, the index information is not saved in the IXF file. As a result, you must create the indexes separately using the db2look utility.
Space limitations
The export operation fails if the data that you are exporting exceeds the space available on the file system on which the exported file is created. In this case, you should limit the amount of data selected by specifying conditions on the WHERE clause so that the exported file fits on the target file system. You can run the export utility multiple times to export all of the data.
Tables with other file formats
If you do not export using the IXF file format, the output files do not contain descriptions of the target table, but they contain the record data. To re-create a table and its data, create the target table, then use the load or import utility to populate the table. You can use the db2look utility to capture the original table definitions and to generate the corresponding data definition language (DDL).