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.

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
  • Implicitly hidden property
• 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

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.