Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to enable qury optimization for MQT ?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
srilata83

New User


Joined: 02 Apr 2009
Posts: 16
Location: chennai

PostPosted: Tue Sep 25, 2012 4:34 pm    Post subject: How to enable qury optimization for MQT ?
Reply with quote

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
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Tue Sep 25, 2012 5:18 pm    Post subject:
Reply with quote

use the correct syntax
scroll down until you see the red type.
Back to top
View user's profile Send private message
srilata83

New User


Joined: 02 Apr 2009
Posts: 16
Location: chennai

PostPosted: Tue Sep 25, 2012 6:37 pm    Post subject: Reply to: How to enable qury optimization for MQT ?
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Tue Sep 25, 2012 7:13 pm    Post subject:
Reply with quote

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
View user's profile Send private message
srilata83

New User


Joined: 02 Apr 2009
Posts: 16
Location: chennai

PostPosted: Wed Sep 26, 2012 10:56 am    Post subject: Reply to: How to enable qury optimization for MQT ?
Reply with quote

It worked icon_surprised.gif Thank you so much !!! icon_biggrin.gif
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             
---------+---------+---------+---------+---------+---------+---------+


2014.gif
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Wed Sep 26, 2012 4:06 pm    Post subject:
Reply with quote

Srilata,

congratulations, and thx for getting back to us with the actual syntax.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts What is the code in CLIST to enable t... jackzhang75 CLIST & REXX 1 Fri Dec 02, 2016 3:02 am
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts JCL to Check whether a VSAM file is ... raj4neo CICS 3 Wed Aug 06, 2014 2:58 pm
No new posts Help needed for query optimization. Anil Khanna DB2 11 Tue Jun 24, 2014 11:21 am
No new posts Query fine tuning/optimization Satish5 DB2 1 Mon Sep 09, 2013 12:38 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us