I want to create a new table based on an existing table, but I don't
want the tables to have any enforced relationship. Is this possible
without having to do a CREATE TABLE and an INSERT?

create table customer_Temp
as (select credit_Card_number, personal_id_number from customer)
DATA INITIALLY DEFERRED REFRESH deferred;

refresh table customer_temp;



But when I tried to rename "customer", it returns the following:

db2 => rename table customer to customer_new;
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0750N The source table cannot be renamed because it is referenced
in a
view, materialized query table, trigger, SQL function, SQL method,
check
constraint, or referential constraint. SQLSTATE=42986
db2 =>

"Bruce" <sandell@pacbell.net> wrote in message
news:595024a5.0406081853.14ff32bc@posting.google.c om...
> I want to create a new table based on an existing table, but I don't
> want the tables to have any enforced relationship. Is this possible
> without having to do a CREATE TABLE and an INSERT?
>
> create table customer_Temp
> as (select credit_Card_number, personal_id_number from customer)
> DATA INITIALLY DEFERRED REFRESH deferred;
>
> refresh table customer_temp;
>
>
>
> But when I tried to rename "customer", it returns the following:
>
> db2 => rename table customer to customer_new;
> DB21034E The command was processed as an SQL statement because it was
> not a
> valid Command Line Processor command. During SQL processing it
> returned:
> SQL0750N The source table cannot be renamed because it is referenced
> in a
> view, materialized query table, trigger, SQL function, SQL method,
> check
> constraint, or referential constraint. SQLSTATE=42986
> db2 =>

These are called MQT (materialized query tables) so they are materialized.

You can create a regular table and then do the following so they are not
connected:

insert into customer_Temp select credit_Card_number, personal_id_number from
customer

see the SQL Reference for details.

WITH NO DATA
instead of
DATA INITIALLY DEFERRED REFRESH DEFERRED
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Serge Rielau wrote:

> WITH NO DATA
> instead of
> DATA INITIALLY DEFERRED REFRESH DEFERRED
Could also be DEFINITION ONLY on an older release
WITH NO DATA is SQL Standard.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Thanks very much for the help.

DEFINITION ONLY followed by an insert worked great.

Bruce

"Serge Rielau" <srielau@ca.eye-be-em.com> wrote in message
news:ca74q6$99r$4@hanover.torolab.ibm.com...
> Serge Rielau wrote:
>
> > WITH NO DATA
> > instead of
> > DATA INITIALLY DEFERRED REFRESH DEFERRED
> Could also be DEFINITION ONLY on an older release
> WITH NO DATA is SQL Standard.

And here's me temporary creating views, so I can do CREATE table LIKE view.
You learn something new every day. I wonder why I missed it....

So, even though WITH NO DATA is part of the
<i>materialized-query-definition</i> section on the "CREATE TABLE" DDL, the
clause actually creates you a *plain table* with no entry in SYSCAT.VIEWS
and a TYPE of 'T' in SYSCAT.TABLES.

Interestingly, the manual states

"The query is used only to define the table. The table is not populated
using the results of query and the REFRESH TABLE statement cannot be used.
When the CREATE TABLE statement is completed, the table is no longer
considered a materialized query table."

So at the start (!) of the atomic CREATE TABLE statement, the table is
considered an MQT, but at the end of the atomic statement it is not an MQT.
Hummm.

Nit picking a bit more, the next sentance is
"The columns of the table are defined based on the definitions of the
columns that result from the fullselect. If the fullselect references a
single table in the FROM clause, select list items that are columns of that
table are defined using the column name, data type, and nullability
characteristic of the referenced table."

So if the full select references columns from single table, things are
exactly the same as if it references columns or expressions over many
tables. What is the point of that second sentence?

Regards
Paul Vernon
Business Intelligence, IBM Global Services

Paul,
I'm always amazed how the same topic pops up in unrelated areas at the
same time.
Having stared at that text just a few days ago I think we have a typical
case of developer-tunnel vision here.
The code originated with MQT and now every added property points back to it.
Needs to be cleaned up.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab