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

about replicated summary table on two base tables

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


P: n/a
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.


Nov 12 '05 #2

P: n/a
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.


We don't support refresh immediate for join summary tables in Version
7 (we do in Version 8). Please refer to the SQL Reference, the CREATE
TABLE section for the complete list of rules and restrictions.

I also recommend the following redbook, it describes the functionality
available in V8:

http://www.redbooks.ibm.com/redbooks/pdfs/sg246546.pdf

Regards,
Miro

Nov 12 '05 #3

P: n/a
miro flasza <mi**@torolab.ibm.com> wrote in message news:<br**********@hanover.torolab.ibm.com>...
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.


We don't support refresh immediate for join summary tables in Version
7 (we do in Version 8). Please refer to the SQL Reference, the CREATE
TABLE section for the complete list of rules and restrictions.

I also recommend the following redbook, it describes the functionality
available in V8:

http://www.redbooks.ibm.com/redbooks/pdfs/sg246546.pdf

Regards,
Miro

HI, Thanks for your reply.

Actually, I could create refresh immediate summary table , but now I
was ask to replicate the summary table. It seems V7 did not support
replicate for this case. I read the manual for V7, it did not say
clearly about it....
Nov 12 '05 #4

P: n/a
miro flasza <mi**@torolab.ibm.com> wrote in message news:<br**********@hanover.torolab.ibm.com>...
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.


We don't support refresh immediate for join summary tables in Version
7 (we do in Version 8). Please refer to the SQL Reference, the CREATE
TABLE section for the complete list of rules and restrictions.

I also recommend the following redbook, it describes the functionality
available in V8:

http://www.redbooks.ibm.com/redbooks/pdfs/sg246546.pdf

Regards,
Miro

HI, Thanks for your reply.

Actually, I could create refresh immediate summary table , but now I
was ask to replicate the summary table. It seems V7 did not support
replicate for this case. I read the manual for V7, it did not say
clearly about it....
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.