DB2 Version 9.7 for Linux, UNIX, and Windows

Imported table re-creation

You can use the import utility's CREATE mode to re-create a table that was saved through the export utility. However, there are a number of limitations on the process, as many of the input table's attributes are not retained.

For import to be able to re-create the table, the export operation must meet some requirements. The original table must have been exported to an IXF file. If you export files with DEL or ASC file formats, the output files do not contain descriptions of the target table, but they contain the record data. To re-create a table with data stored in these file formats, create the target table, then use the load or import utility to populate the table from these files. You can use the db2look utility to capture the original table definitions and to generate the corresponding data definition language (DDL). As well, the SELECT statement used during the export can only contain certain action strings. For example, no column names can be used in the SELECT clause and only SELECT * is permitted.

Note: Import's CREATE mode is being deprecated. Use the db2look utility to capture and re-create your tables.

Retained attributes
The re-created table will retain the following attributes of the original table:

The primary key name, and definition
Column information, including:
- Column name
- Column data type, including user-defined distinct types, which are preserved as their base type
- Identity properties
- Lengths (except for lob_file types)
- Code page (if applicable)
- Identity options
- Whether the column is defined as nullable or not nullable
- Default values for constants, if any, but not other types of default values
Index information, including:
- Index name
- Index creator name
- Column names, and whether each column is sorted in ascending or descending order
- Whether the index is defined as unique
- Whether the index is clustered
- Whether the index allows reverse scans
- PCTFREE values
- MINPCTUSED values

Note: No index information is retained if the column names in the index contain the characters - or +, in which case SQL27984W is returned.

Lost attributes
The re-created table does not retain several attributes of the original table, including:

Whether the source was a normal table, a materialized query table (MQT), a view, or a set of columns from any or all of these sources
Unique constraints and other types of constraints or triggers (not including primary key constraints)
Table information, including:
- MQT definition (if applicable)
- MQT options (if applicable)
- Table space options; however, this information can be specified through the IMPORT command
- Multidimensional clustering (MDC) dimensions
- Partitioned table dimensions
- Table partitioning key
- NOT LOGGED INITIALLY property
- Check constraints
- Table code page
- Protected table properties
- Table or value compression options
Column information, including:
- Any default value except constant values
- LOB options (if any)
- XML properties
- References clause of the CREATE TABLE statement (if any)
- Referential constraints (if any)
- Check constraints (if any)
- Generated column options (if any)
- Columns dependent on database scope sequences
Index information, including:
- INCLUDE columns (if any)
- Index name, if the index is a primary key index
- Descending order of keys, if the index is a primary key index (ascending is the default)
- Index column names that contain hexadecimal values of 0x2B or 0x2D
- Index names that contain more than 128 bytes after code page conversion
- PCTFREE2 value
- Unique constraints

Note: This list is not exhaustive, use with care.

If the import fails and SQL3311N is returned, you can still re-create the table using the file type modifier forcecreate. This modifier allows you to create the table with missing or limited information.