Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2013
    Posts
    23

    Unanswered: Creation of MQTs...

    Hi DBA Gods,

    I have been asked to construct MQT from an existing sql query,can you kindly give inputs on how to go about it..

    Thanks

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see materialized-query-definition in CREATE TABLE statement.

    CREATE TABLE

    Here is the extraction from the page of Information Center.
    Syntax

    Code:
    >>-CREATE TABLE--table-name------------------------------------->
    
    >--+-| element-list |----------------------------+--●----------->
       +-OF--type-name1--+-------------------------+-+      
       |                 '-| typed-table-options |-' |      
       +-LIKE--+-table-name1-+--+------------------+-+      
       |       +-view-name---+  '-| copy-options |-' |      
       |       '-nickname----'                       |      
       +-| as-result-table |--+------------------+---+      
       |                      '-| copy-options |-'   |      
       +-| materialized-query-definition |-----------+      
       '-| staging-table-definition |----------------'      
    
    ...
    ...
    ...
    
    materialized-query-definition
    
    |--+-----------------------+--AS--(--fullselect--)-------------->
       |    .-,-----------.    |                         
       |    V             |    |                         
       '-(----column-name-+--)-'                         
    
    >--| refreshable-table-options |--------------------------------|
    
    ...
    ...

  3. #3
    Join Date
    Nov 2013
    Posts
    23

    MQTs creation

    Thanks tokuma,

    But I have a query which combines more than 2 tables with joins and subquery as well,so how to go about it...any insights

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see fullselect in Queries in DB2 Information Center,
    to know which kind of queries you can use for materialized-query-definition.

    fullselect

    But, the fullselet have many restrictions depending on the option chosen.
    The restrictions are documented in CREATE TABLE statement.

    CREATE TABLE

    The following is the extraction from the document.

    ...

    materialized-query-definition

    ...
    ...

    fullselect

    Defines the query on which the table is based. The resulting column definitions are the same as those for a view defined with the same query. A column of the new table that corresponds to an implicitly hidden column of a base table referenced in the fullselect is not considered hidden in the new table.

    Every select list element must have a name (use the AS clause for expressions). The materialized-query-definition defines attributes of the materialized query table. The option chosen also defines the contents of the fullselect as follows.

    The fullselect cannot include a data-change-table-reference clause (SQLSTATE 428FL).

    When REFRESH DEFERRED or REFRESH IMMEDIATE is specified, the fullselect cannot include (SQLSTATE 428EC):
    ...
    ...
    ...

    When DISTRIBUTE BY REPLICATION is specified, the following restrictions apply:
    ...
    ...

    When REFRESH IMMEDIATE is specified:
    ...
    ...
    ...

    When REFRESH DEFERRED is specified:
    ...
    ...

    ...
    ...

  5. #5
    Join Date
    Nov 2013
    Posts
    23

    MQT for SQL Query

    Hi,

    I have the following query
    SELECT COUNT (*)
    FROM ORDER_FIRST.W_KINGS K
    , ORDER_FIRST.W_QUEEN Q
    , ORDER_FIRST.W_JACKS J
    , ORDER_FIRST.W_ROOKS R
    WHERE (((W.ROOK_ID IN
    ( select w.rooks_id from ORDER_FIRST.x_rooks r
    left join
    ORDER_FIRST.w_masti
    on w.rooks_id = m.rooks_id
    left join
    ORDER_FIRST.w_chasti c on w.masti_id = o.masti_id
    join
    ORDER_FIRST.k_kusti ki on ki.kusti_id = j_kusti_id



    WHERE (ki.kusti_alias = 'A_B_ID_5' and
    and w.chasti_AVG < 10 ))) )

    AND ( Q.QUEEN_ID = W_QUEEN_ID
    AND W.JACKS_ID = T.JACKS_ID
    AND G.KINGS_ID = M.KINGS_ID
    AND M.KINGS_IN_PA IS NULL
    AND W.ROOKS_VALIDITY = 0 ) with ur

    Any inputs on writing an equivalent MQT for this query???

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as Mr Tonkuma from Japan gave you all hints and tips and links, I don't understand why you just repeat your question ? Don't you have access to the info-center ?
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    thayums,
    this is your third post with the same query that is riddled with syntax errors. We all donate our time to help each other and to sometimes be helped. It is a little bit of a slap in the face to all when a poster just continues to waste our time. In the future, please provide a proper posting with all the required info for someone to be able to help. If a question is asked, respond with an answer, remember we aren't the one having an issue, but are trying to help you.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although, dav1mo already pointed out the syntax errors in your query when I was writing and editing my post,
    I want to show the syntax errors more concretely.

    Quote Originally Posted by thayums View Post
    Hi,

    I have the following query
    SELECT COUNT (*)
    FROM ORDER_FIRST.W_KINGS K
    , ORDER_FIRST.W_QUEEN Q
    , ORDER_FIRST.W_JACKS J
    , ORDER_FIRST.W_ROOKS R
    WHERE (((W.ROOK_ID IN
    ( select w.rooks_id from ORDER_FIRST.x_rooks r
    left join
    ORDER_FIRST.w_masti
    on w.rooks_id = m.rooks_id
    left join
    ORDER_FIRST.w_chasti c on w.masti_id = o.masti_id
    join
    ORDER_FIRST.k_kusti ki on ki.kusti_id = j_kusti_id



    WHERE (ki.kusti_alias = 'A_B_ID_5' and
    and w.chasti_AVG < 10 ))) )

    AND ( Q.QUEEN_ID = W_QUEEN_ID
    AND W.JACKS_ID = T.JACKS_ID
    AND G.KINGS_ID = M.KINGS_ID
    AND M.KINGS_IN_PA IS NULL
    AND W.ROOKS_VALIDITY = 0 ) with ur

    Any inputs on writing an equivalent MQT for this query???
    I thought that the query might not work, it included some syntax errors.
    Please construct a working query,
    then try to put the query in fullselec of materialized-query-definition in CREATE TABLE syntax.

    (1) There was unmatched parentheses, extra "and" and doubts of typo.
    Formatted and added vertical bars(i.e. "|") to show matched parentheses.
    Code:
    SELECT COUNT (*)
     FROM  ORDER_FIRST.W_KINGS K
         , ORDER_FIRST.W_QUEEN Q
         , ORDER_FIRST.W_JACKS J
         , ORDER_FIRST.W_ROOKS R
     WHERE
     (
     |     (
     |     | (W.ROOK_ID
     |     | |IN (select w.rooks_id
     |     | |   | from  ORDER_FIRST.x_rooks r
     |     | |   | left  join 
     |     | |   |       ORDER_FIRST.w_masti
     |     | |   |  on   w.rooks_id = m.rooks_id 
     |     | |   | left  join 
     |     | |   |       ORDER_FIRST.w_chasti c
     |     | |   |  on   w.masti_id = o.masti_id 
     |     | |   | join
     |     | |   |       ORDER_FIRST.k_kusti ki
     |     | |   |  on   ki.kusti_id = j_kusti_id /* j.kusti_id ? */
     |     | |   | WHERE
     |     | |   | (     ki.kusti_alias = 'A_B_ID_5'
     |     | |   | | and and w.chasti_AVG < 10
     |     | |   | )
     |     | |   )
     |     | )
     |     )
     |AND
     | (     Q.QUEEN_ID = W_QUEEN_ID /* W.QUEEN_ID ? */ 
     | | AND W.JACKS_ID = T.JACKS_ID
     | | AND G.KINGS_ID = M.KINGS_ID 
     | | AND M.KINGS_IN_PA IS NULL 
     | | AND W.ROOKS_VALIDITY = 0
     | )
     | with ur
    (2) Remove unneccesary parentheses. And, amended by considering my guess.
    Aliases in your query are not consistent.
    (duplicated aliases R and r, undefined aliases like w, m , o, W, T, G, M)

    Code:
    SELECT COUNT (*) 
     FROM  ORDER_FIRST.W_KINGS K
         , ORDER_FIRST.W_QUEEN Q
         , ORDER_FIRST.W_JACKS J
         , ORDER_FIRST.W_ROOKS R
     WHERE
           W.ROOK_ID
           IN (select w.rooks_id
                from  ORDER_FIRST.x_rooks  r
                left  join 
                      ORDER_FIRST.w_masti
                 on   w.rooks_id = m.rooks_id 
                left  join 
                      ORDER_FIRST.w_chasti c
                 on   w.masti_id = o.masti_id 
                join
                      ORDER_FIRST.k_kusti  ki
                 on   ki.kusti_id = j.kusti_id
                WHERE
                      ki.kusti_alias = 'A_B_ID_5'
                  and w.chasti_AVG < 10
              )
       AND
           Q.QUEEN_ID = W.QUEEN_ID 
       AND W.JACKS_ID = T.JACKS_ID
       AND G.KINGS_ID = M.KINGS_ID 
       AND M.KINGS_IN_PA IS NULL 
       AND W.ROOKS_VALIDITY = 0
     with ur

  9. #9
    Join Date
    Nov 2013
    Posts
    23
    Hi all,
    I apologize for the inconvenience caused by me. I fully understand your difficulties...it is one of those days where things not going ur way..i got stuckup with this activity...

    Thanks a bunch!! Tonkuma for all your inputs....and Dav and Guy...am sorry

  10. #10
    Join Date
    Nov 2013
    Posts
    23

    How to capture the query statistics ..

    Hi all,
    I have used db2batch to find out the elapsed time for the given sql query statement...can anybody give their inputs on how to capture the SQL query statistics during the actual runtime of the application...becuase during the runtime the application takes longer time to load so i need to capture the same.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •