|
View previous topic :: View next topic
|
| Author |
Message |
srilata83
New User
Joined: 02 Apr 2009
Posts: 16
Location: chennai
|
|
|
|
Hi,
I am using V9 DB2 on Mainframe.I created a MQT specifying below.
| Code: |
CREATE TABLE ADVSMRP.DTAC_MODEL_HIERARCHY_MQT
As(..............................................)
DATA INITIALLY DEFERRED REFRESH DEFERRED
[b]MAINTAINED BY SYSTEM [/b]
[b]DISABLE QUERY OPTIMIZATION[/b]
IN DDVSM21D.GDVSMQT1
CCSID UNICODE
NOT VOLATILE
APPEND NO
; |
After that I refreshed the MQT and populated the data.As it was suggested in IBM manuals that the first time when we populate the data, we should create the MQTwith DISABLE QUERY OPTIMIZATION and then after refresh Alter the table to ENABLE QUERY OPTIMIZATION .
But when I ran the following:
ALTER TABLE ADVSMRP.DTAC_MODEL_HIERARCHY_MQT
ENABLE QUERY OPTIMIZATION;
Its giving following error.
| Code: |
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD ENABLE. TOKEN ROTATE
ADD RENAME APPEND VOLATILE AUDIT VALIDPROC CONSTRAINT WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 2 0 0 -1 73 506 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000' X'FFFFFFFF'
X'00000049' X'000001FA' SQL DIAGNOSTIC INFORMATION |
Is there any syntax error?I tried searching in web, but nothing could help me..Please do suggest ...it will be of great help !
Thanks..
Code'd |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
| Back to top |
|
|
srilata83
New User
Joined: 02 Apr 2009
Posts: 16
Location: chennai
|
|
|
|
I tried this:
| Code: |
ALTER MATERIALIZED QUERY
ADVSMRP.DTAC_MODEL_HIERARCHY_MQT
ENABLE QUERY OPTIMIZATION; |
Here what I am getting:
| Code: |
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD MATERIALIZED. TOKEN
VIEW JAR STOGROUP TABLESPACE INDEX TABLE DATABASE FUNCTION WAS
EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 2 0 0 -1 8 506 SQL DIAGNOSTIC INFORMATION |
Am i still missing something? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
can't test it, but I would start from here:
| Code: |
ALTER TABLE ADVSMRP.DTAC_MODEL_HIERARCHY_MQT
ALTER MATERIALIZED QUERY
ENABLE QUERY OPTIMIZATION; |
|
|
| Back to top |
|
|
srilata83
New User
Joined: 02 Apr 2009
Posts: 16
Location: chennai
|
|
|
|
It worked  Thank you so much !!! 
Just need to add "SET"
| Code: |
ALTER TABLE
ADVSMRP.DTAC_MODEL_HIERARCHY_MQT
ALTER MATERIALIZED QUERY
SET ENABLE QUERY OPTIMIZATION;
---------+---------+---------+---------+---------+---------+---------+
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+ |
 |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
Srilata,
congratulations, and thx for getting back to us with the actual syntax. |
|
| Back to top |
|
|
|
|
