Creating a table using the IXF file format for data exported using a SELECT * statement

[Trent]

Hello, everyone.

Which of the following is being kept in the new table?

Check constraints

Catalog statistics

Primary key definitions

Foreign key definitions

I think catalog statistics is being preserved. Isn't that right?

Thanks.

Trent

[Blair Kenneth Adamache]

All you can grab is column defaults - see CREATE TABLE in the SQL
REFERENCE under LIKE:

"Thus the new table does not have any unique constraints, foreign key
constraints, triggers, or indexes". Runstats must be issued separately
as well (after all, CREATE LIKE doesn't bring the data with it, just the
basic columns and defaults).

Answers inline for DB2 v8 on Linux/Unix/Windows:

Trent wrote:

> Hello, everyone.
>
> Which of the following is being kept in the new table?
>
> Check constraints

no

>
>
> Catalog statistics

no

>
>
> Primary key definitions

no

>
>
> Foreign key definitions

no

>
>
> I think catalog statistics is being preserved. Isn't that right?

no

>
>
> Thanks.
>
> Trent

[PM \(pm3iinc-nospam\)]

Your wording makes me believe you already read this one

[url]http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/v8infoce[/url]
nter.d2w/report?target=mainFrame&fn=c0004580.htm




Using Import to Recreate an Exported Table
You can use the import utility to recreate a table that was saved
through the export utility. The table must have been exported to an IXF
file, and the SELECT statement used during the export operation must have
met certain conditions (for example, no column names can be used in the
SELECT clause; only select * is permitted). When creating a table from an
IXF file, not all attributes of the original table are preserved. For
example, referential constraints, foreign key definitions, and user-defined
data types are not retained. The following attributes of the original table
are retained:

a.. Primary key name, and definition
b.. Unique constraints names, and definitions, but not other
types of constraints or triggers
c.. Column information:
a.. Column name
b.. Column data type, including user-defined distinct types,
which are preserved as their base type
c.. Identity properties
d.. Lengths (except for lob_file types)
e.. Code page (if applicable)
f.. DATALINK options
g.. Identity options
h.. Whether the column is defined as nullable or not nullable
i.. Default values for constants, if any, but not other types
of default values
d.. Index information:
a.. Index name
b.. Index creator name
c.. Column names, and whether each column is sorted in
ascending, or in descending order
d.. Whether the index is defined as unique
e.. Whether the index is clustered
f.. Whether the index allows reverse scans
g.. pctfree values
h.. minpctused values



I tested the table recreation with a primary key index and all went well so
i'd have to say that for
Primary key definitions, it's a Yes.



DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08012" with
level identifier "02030106".
Informational tokens are "DB2 v8.1.2.169", "s030508", "WR21318", and FixPak
"2".

PM

"Trent" <pineme@yahoo.com> a écrit dans le message de news:
[email]7ad7239b.0307011420.597e9d9b@posting.google.com[/email]...

> Hello, everyone.
>
> Which of the following is being kept in the new table?
>
> Check constraints
>
> Catalog statistics
>
> Primary key definitions
>
> Foreign key definitions
>
> I think catalog statistics is being preserved. Isn't that right?
>
> Thanks.
>
> Trent