hi prasad,
When REFRESH IMMEDIATE is specified:
----> 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 summary table select
list contains SUM(X) where X is a nullable argument, then the summary table
must also have COUNT(X) in its select list. These column functions cannot be
part of any expressions.
the difference between refresh deferred and refresh immediate
is :
as and when you make changes to the base tables the summary table is updated
for refresh immediate... but for refresh deferred the summary table is
refreshed only by explicit "refresh table summary-tab-name"
so when ever you are using refresh deferred summary table
issue a refresh stmt before issuing the query but even then you cannot
gaurantee the use of summary table with refresh deferred option .
bye,
saravana.
-----Original Message-----
From: SaiPrasad
[mailto:db2udbdba-ezmlmshield-x82526950.[Email address protected]
Sent: Friday, December 24, 2004 6:09 PM
To: LazyDBA Discussion
Subject: RE: Summary table error
Hi saravana,
Great !
Now i could able to create the summary table.
But still, Wht is the difference of using Refresh deferred and Refresh
Immediate.
Bcoz for the following summary table creation, i could able to succed
while
using refresh deferred but not with refresh immediate.
Also for some tables i could able to create summary table using refresh
immediate.
Moreover if summary table is created using refresh deferred. And if i put
a query to
the corresponding base table, the query is not using Summary table. But it
uses
base table only.
But if a summary table is created using refresh immediate, then if i put a
query to
the corresponding base table, the query is using Summary table.
Can you pls.explain me in detail. Bcoz i am struggling with it since long
time.
With regards
saiprasad
Chennai, INDIA
"Saravana_Ganesan " <[Email address protected]
12/24/2004 04:03 AM
To: SAIPRASAD.[Email address protected]
cc:
Subject: RE: Summary table error
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text
hi,
try the following stmt, it will work.
db2 => CREATE TABLE summary2(colname) AS \
db2 (cont.) => ( \
db2 (cont.) => select count(vemail) from msg,reportng where
msg.ticket=reportng.ticket \
db2 (cont.) => ) \
db2 (cont.) => DATA INITIALLY DEFERRED REFRESH deferred \
db2 (cont.) => ENABLE QUERY OPTIMIZATION \
db2 (cont.) => MAINTAINED BY SYSTEM
regards,
saravana.
-----Original Message-----
From: SaiPrasad
[mailto:db2udbdba-ezmlmshield-x19608639.[Email address protected]
Sent: Friday, December 24, 2004 4:30 PM
To: LazyDBA Discussion
Subject: Summary table error
I tried to create summary table with the following Syntax, which resulted
in following error msg.
Also i tried to create summary table using DATA INITIALLY DEFERRED REFRESH
DEFERRED.
But still same error is thrown out.
db2 => CREATE TABLE summary2 AS \
db2 (cont.) => ( \
db2 (cont.) => select count(vemail) from msg,reportng where
msg.ticket=reportng.ticket \
db2 (cont.) => ) \
db2 (cont.) => DATA INITIALLY DEFERRED REFRESH deferred \
db2 (cont.) => ENABLE QUERY OPTIMIZATION \
db2 (cont.) => MAINTAINED BY SYSTEM
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
"DEVAIX1.SUMMARY2" is not valid. SQLSTATE=428EC
Pls. help me to close this ASAP.
Thanks In Advance
saiprasad
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
**************************************************************************
This email (including any attachments) is intended for the sole use of the
intended recipient/s and may contain material that is CONFIDENTIAL AND
PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying
or
distribution or forwarding of any or all of the contents in this message
is
STRICTLY PROHIBITED. If you are not the intended recipient, please contact
the sender by email and delete all copies; your cooperation in this regard
is appreciated.
**************************************************************************
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
**************************************************************************
This email (including any attachments) is intended for the sole use of the
intended recipient/s and may contain material that is CONFIDENTIAL AND
PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or
distribution or forwarding of any or all of the contents in this message is
STRICTLY PROHIBITED. If you are not the intended recipient, please contact
the sender by email and delete all copies; your cooperation in this regard
is appreciated.
**************************************************************************
DB2 & UDB email list listserv db2-l LazyDBA home page