Maybe not too useful, but the following succeeds on one partition with
DB2 v8:
create table table_a (aa int, key int not null, primary key (key))
create table table_b (bb int, cc int, key int not null, primary key
(key))
create summary table sum_tab as
(select a.aa, b.bb, b.cc from table_a a ,table_b b
where a.key=b.key)
data initially deferred refresh deferred enable query optimization
So, in order or likely culprits, I would suspect it's failing for one of
these reasons:
1. use of the refresh immediate violates one of the refresh immediate
rules for full selects (see below)
2. the DDL of table_a and table_b has some significant differences from
the DDL I show above.
3. You're running into a v7 restriction that's been lifted in v8.
4. You're on a single partition, so REPLICATED is not required
5. You're breaking a syntax rule (SUMMARY is not a good name for a DB2
object, and there's no need to specify the data type of the columns in
the summary table)
REFRESH IMMEDIATE RULES:
the fullselect must be a subselect
the fullselect cannot include a reference to a nickname (SQLSTATE 428EC)
the subselect cannot include:
functions that are not deterministic
scalar fullselects
predicates with fullselects
special registers
a GROUP BY clause must be included in the subselect unless REPLICATED is
specified, in which case a GROUP BY clause is not allowed.
The supported column functions are SUM, COUNT, COUNT_BIG and GROUPING
(without DISTINCT). The select list must contain a COUNT(*) or
COUNT_BIG(*) column. If the materialized query table select list
contains SUM(X), where X is a nullable argument, the materialized query
table must also have COUNT(X) in its select list. These column functions
cannot be part of any expressions.
if the FROM clause references more than one table or view, it can only
define an inner join without using the explicit INNER JOIN syntax
all GROUP BY items must be included in the select list
GROUPING SETS, CUBE and ROLLUP are supported. The GROUP BY items and
associated GROUPING column functions in the select list must form a
unique key of the result set. Thus, the following restrictions must be
satisfied:
no grouping sets may be repeated. For example, ROLLUP(X,Y), X is not
allowed because it is equivalent to GROUPING SETS((X,Y),(X), (X))
if X is a nullable GROUP BY item that appears within GROUPING SETS,
CUBE, or ROLLUP, then GROUPING(X) must appear in the select list
grouping on constants is not allowed
a HAVING clause is not allowed
if in a multiple partition database partition group, then either the
partitioning key must be a subset of the GROUP BY items, or REPLICATED
must be specified
if REPLICATED is specified, the table must have a unique key
jane wrote:
HI,
I try to create summary table like following:
create table summary (a int, b int, c int)
(select a.aa, b.bb, b.cc from table_a a ,table_b b where a.key=b.key)
data initially deferred refresh immediate enable query optimization in
tablespace_test
replicated;
I got following error message:
SQL20058N The fullselect specified for the summary table summary is
invalid.
Any suggestion is welcomed.
BY the way, my DB2 is UDB Ver. 7.2 + fixpack 9.
