Duplicating a table

[DB_2]

I was wondering if there was a way to create a copy of a table in a
single SQL statement, together with its column structure and data. In
Oracle, there is a "CREATE TABLE new-table AS select-statement" that I
can use to accomplish this.

Is there a similar statement in DB2? I found a similar syntax
structure under materialized query tables, but it appeared that the
new table would remain tied to the original source in some way.
Perhaps I am mistaken?

[Mark A]

"DB_2" <de*******@yahoo.com> wrote in message
news:9b**************************@posting.google.c om...

I was wondering if there was a way to create a copy of a table in a
single SQL statement, together with its column structure and data. In
Oracle, there is a "CREATE TABLE new-table AS select-statement" that I
can use to accomplish this.

Is there a similar statement in DB2? I found a similar syntax
structure under materialized query tables, but it appeared that the
new table would remain tied to the original source in some way.
Perhaps I am mistaken?

Create table 2 like table 1;
Insert into table 2 select * from table 1;

Two SQL statements. I don't understand what the big deal is.

[Robert Stearns]

DB_2 wrote:

I was wondering if there was a way to create a copy of a table in a
single SQL statement, together with its column structure and data. In
Oracle, there is a "CREATE TABLE new-table AS select-statement" that I
can use to accomplish this.

Is there a similar statement in DB2? I found a similar syntax
structure under materialized query tables, but it appeared that the
new table would remain tied to the original source in some way.
Perhaps I am mistaken?

More importantly, is there any way of *completely* duplicating a table:
columns, constraints, indexes, etc., with minimal pain, perhaps into
another schema or database? If not, is there an easy (I realize that
easy is subjective, relative to the knowledge/experience of the
(be)holder thereof) way of getting the names or (in a pleasant world)
the ddl/dml for all the relevant objects? In one swell foop?

[Mark A]

> More importantly, is there any way of *completely* duplicating a table:

columns, constraints, indexes, etc., with minimal pain, perhaps into
another schema or database? If not, is there an easy (I realize that
easy is subjective, relative to the knowledge/experience of the
(be)holder thereof) way of getting the names or (in a pleasant world)
the ddl/dml for all the relevant objects? In one swell foop?

You can use the db2look command to generate the DDL.

To move the data to another database, you can use the export and import
commands, or look at the db2move utility.

For details, please consult the "Command Reference Manual," and/or the "Data
Movement Utilities Guide and Reference."

[Knut Stolze]

Mark A wrote:

"DB_2" <de*******@yahoo.com> wrote in message
news:9b**************************@posting.google.c om...

I was wondering if there was a way to create a copy of a table in a
single SQL statement, together with its column structure and data. In
Oracle, there is a "CREATE TABLE new-table AS select-statement" that I
can use to accomplish this.

Is there a similar statement in DB2? I found a similar syntax
structure under materialized query tables, but it appeared that the
new table would remain tied to the original source in some way.
Perhaps I am mistaken?

Create table 2 like table 1;
Insert into table 2 select * from table 1;

Two SQL statements. I don't understand what the big deal is.

One could wrap the two statements into a SQL procedure and, thus, just
invoke the procedure from the client in a single statement.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Blair Adamache]

There is an option to do this in the DB2 Control Center.

Robert Stearns wrote:

DB_2 wrote:

I was wondering if there was a way to create a copy of a table in a
single SQL statement, together with its column structure and data. In
Oracle, there is a "CREATE TABLE new-table AS select-statement" that I
can use to accomplish this.

Is there a similar statement in DB2? I found a similar syntax
structure under materialized query tables, but it appeared that the
new table would remain tied to the original source in some way.
Perhaps I am mistaken?

More importantly, is there any way of *completely* duplicating a table:
columns, constraints, indexes, etc., with minimal pain, perhaps into
another schema or database? If not, is there an easy (I realize that
easy is subjective, relative to the knowledge/experience of the
(be)holder thereof) way of getting the names or (in a pleasant world)
the ddl/dml for all the relevant objects? In one swell foop?