By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,812 Members | 856 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,812 IT Pros & Developers. It's quick & easy.

CREATE TABLE AS - not materialized?

P: n/a
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 =>
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a

"Bruce" <sa*****@pacbell.net> wrote in message
news:59**************************@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.
Nov 12 '05 #2

P: n/a
WITH NO DATA
instead of
DATA INITIALLY DEFERRED REFRESH DEFERRED
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3

P: n/a
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
Nov 12 '05 #4

P: n/a
Thanks very much for the help.

DEFINITION ONLY followed by an insert worked great.

Bruce
Nov 12 '05 #5

P: n/a
"Serge Rielau" <sr*****@ca.eye-be-em.com> wrote in message
news:ca**********@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
Nov 12 '05 #6

P: n/a
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
Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.