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

Materialized Query Syntax--I can't get the samples to work

P: n/a
This is a simplistic test table I am trying to create. The syntax is
directly based up on the sample code from the BI Redbook:

CREATE TABLE CUSDTA021/MQT_DB AS
(
SELECT SUM(ML1OQ) AS TOTAL_SUM,
M1PN
FROM FRNDTA021/M1P M1P
WHERE M1LOT# = ''
GROUP BY M1PN
)

DATA INITIALLY DEFERRED --Create but don't populate
REFRESH DEFERRED --Populate/refresh later
--ENABLE QUERY OPTIMIZATION
--MAINTAINED BY USER ; --instead of system
When I run this command without the commented out parameters, I get the
error message "Valid tokens: ENABLE DISABLE" If I supply the ENABLE
QUERY OPTIMIZATION parameter, I get prompted for the MAINTAINED BY
parameter. I found these in the CREATE TABLE spec for materialized
queries, but they are not listed in the samples.

By putting in the commented out parameters, the table is created
without a hitch. Well, actually, the created table isn't journaled but
I don't really care about that. The problem I have is that I need to
create SYSTEM-maintained tables with a DEFERRED REFRESH so that I can
use staging tables.

Oddly enough, according to page 28 of the Redbook I am using as a guide
(SB24-6546-00) I shouldn't be able to populate the newly created table
using the REFRESH TABLE command. However, it works like a charm. What
I cannot do is a REFRESH TABLE INCREMENTAL, which is really why I am
having to fret over this syntax issue to such a degree.

Any clarification about why I can't create SYSTEM maintained tables
would be very welcome.

Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.