Is there an easy to copy a table: structure and data?

[Robert Stearns]

For testing purposes I propose to add a schema (testing, how original)
and would like to copy some of my live tables to it, both structure and
data. I know I could use something like dump/restore for the data and
Quest's (BTW this a very nice product) 'create like' function. That
would work, but it seems a lot of work for something DB2 should know how
to do.

[Blair Adamache]

db2look with -e -t can give you the table CREATE statements.

The DB2 Control Center will allow you to copy a table and its contents.

You can also do a CREATE ... LIKE to copy the table structure (but not
the rows):

create table emp2 like employee

you could then do an insert:

insert into emp2 (select * from employee)

Robert Stearns wrote:

For testing purposes I propose to add a schema (testing, how original)
and would like to copy some of my live tables to it, both structure and
data. I know I could use something like dump/restore for the data and
Quest's (BTW this a very nice product) 'create like' function. That
would work, but it seems a lot of work for something DB2 should know how
to do.

[Blair Adamache]

db2look with -e -t can give you the table CREATE statements.

The DB2 Control Center will allow you to copy a table and its contents.

You can also do a CREATE ... LIKE to copy the table structure (but not
the rows):

create table emp2 like employee

you could then do an insert:

insert into emp2 (select * from employee)

Robert Stearns wrote:

For testing purposes I propose to add a schema (testing, how original)
and would like to copy some of my live tables to it, both structure and
data. I know I could use something like dump/restore for the data and
Quest's (BTW this a very nice product) 'create like' function. That
would work, but it seems a lot of work for something DB2 should know how
to do.

[Philip Nelson]

Blair Adamache wrote:

db2look with -e -t can give you the table CREATE statements.

The DB2 Control Center will allow you to copy a table and its contents.

You can also do a CREATE ... LIKE to copy the table structure (but not
the rows):

create table emp2 like employee

you could then do an insert:

insert into emp2 (select * from employee)

Robert Stearns wrote:

For testing purposes I propose to add a schema (testing, how original)
and would like to copy some of my live tables to it, both structure and
data. I know I could use something like dump/restore for the data and
Quest's (BTW this a very nice product) 'create like' function. That
would work, but it seems a lot of work for something DB2 should know how
to do.

Another alternative would be to take an EXPORT to IXF format, and then do an
IMPORT with the "REPLACE_CREATE" option. This would work across databases
as well. It will only give you the basic table structure and data, and
you'd need to use db2look to generate the statements to add indexes etc.

HTH

Phil

[Philip Nelson]

Blair Adamache wrote:

db2look with -e -t can give you the table CREATE statements.

The DB2 Control Center will allow you to copy a table and its contents.

You can also do a CREATE ... LIKE to copy the table structure (but not
the rows):

create table emp2 like employee

you could then do an insert:

insert into emp2 (select * from employee)

Robert Stearns wrote:

For testing purposes I propose to add a schema (testing, how original)
and would like to copy some of my live tables to it, both structure and
data. I know I could use something like dump/restore for the data and
Quest's (BTW this a very nice product) 'create like' function. That
would work, but it seems a lot of work for something DB2 should know how
to do.

Another alternative would be to take an EXPORT to IXF format, and then do an
IMPORT with the "REPLACE_CREATE" option. This would work across databases
as well. It will only give you the basic table structure and data, and
you'd need to use db2look to generate the statements to add indexes etc.

HTH

Phil