DB2的CTAS失败，这个是错在什么地方呢？
db2 connect to proddb
Database Connection Information
Database server = DB2/LINUXX8664 9.5.0
SQL authorization ID = DB2INST1
Local database alias = PRODDB
[db2inst1@DB2 ~]$ db2 "create table test.test as (select * from syscat.schemata) data initially deferred refresh deferred"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL20058N The fullselect specified for the materialized query table
"TEST.TEST" is not valid. Reason code = "2". SQLSTATE=428EC
[db2inst1@DB2 ~]$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 9.5.0
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 => create table test.test as (select * from syscat.schemata) data initially deferred refresh deferred
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL20058N The fullselect specified for the materialized query table
"TEST.TEST" is not valid. Reason code = "2". SQLSTATE=428EC
Database Connection Information
Database server = DB2/LINUXX8664 9.5.0
SQL authorization ID = DB2INST1
Local database alias = PRODDB
[db2inst1@DB2 ~]$ db2 "create table test.test as (select * from syscat.schemata) data initially deferred refresh deferred"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL20058N The fullselect specified for the materialized query table
"TEST.TEST" is not valid. Reason code = "2". SQLSTATE=428EC
[db2inst1@DB2 ~]$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 9.5.0
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 => create table test.test as (select * from syscat.schemata) data initially deferred refresh deferred
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL20058N The fullselect specified for the materialized query table
"TEST.TEST" is not valid. Reason code = "2". SQLSTATE=428EC
2同行回答
db2 ? SQL20058N
看一下reason code 2
2
The fullselect must not reference any of the following object
types:
* materialized query table
* staging table
* created global temporary table
* declared global temporary table
* created global temporary table
* typed table
* system catalog table
* view that violates any of the materialized query table
restrictions
* A protected table
* A nickname that was created using the DISALLOW CACHING
clause in the CREATE NICKNAME or ALTER NICKNAME statement
* A view that depends, either directly or indirectly, on a
protected table收起
看一下reason code 2
2
The fullselect must not reference any of the following object
types:
* materialized query table
* staging table
* created global temporary table
* declared global temporary table
* created global temporary table
* typed table
* system catalog table
* view that violates any of the materialized query table
restrictions
* A protected table
* A nickname that was created using the DISALLOW CACHING
clause in the CREATE NICKNAME or ALTER NICKNAME statement
* A view that depends, either directly or indirectly, on a
protected table收起
浏览1403