Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2003
    Location
    Near D.C.
    Posts
    56

    Unanswered: db2 indexed views?

    Haven't found it.
    Does DB2 LUW Enterprise 9.7 have indexed views?

    Or if not, is there likely idea in a near future version?



    Thanks for any feedback.

    /long-time DBA, now knee-deep in DB2...

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Views are the virtual tables and therefore using the indexes of the tables which consist in create views statements.

    Lenny

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You can achieve the functionality similar to MS SQL Server indexed views by creating DB2 materialized query tables and building indexes upon them.

  4. #4
    Join Date
    Oct 2003
    Location
    Near D.C.
    Posts
    56
    Quote Originally Posted by n_i View Post
    You can achieve the functionality similar to MS SQL Server indexed views by creating DB2 materialized query tables and building indexes upon them.
    that's something to consider, thanks much.

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow Fyi

    Quote Originally Posted by rogue49 View Post
    that's something to consider, thanks much.
    MQT is NOT a VIEW. You have to execute REFRESH TABLE mqt_tbl
    before retrieve information from mqt_tbl table.

    Lenny

  6. #6
    Join Date
    Oct 2003
    Location
    Near D.C.
    Posts
    56
    Quote Originally Posted by Lenny77 View Post
    MQT is NOT a VIEW. You have to execute REFRESH TABLE mqt_tbl
    before retrieve information from mqt_tbl table.

    Lenny
    But that's not the point of what I'm going after, whether something is a literally a view or not.
    But something that allows me to apply indexing to standing SQL statements to potentially optimize the performance of its results.
    Not guaranteed, but I'm trying to consider my options and think outside the box.

    MQT, Materialized View, Indexed View...

    I believe the gentleperson's answer gave me an option to the situation that was inferred in the question.

    But thanks for the input, I do appreciate it.

  7. #7
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    rogue49
    Location: Near D.C
    - or -
    DMV?
    Dick Brenholtz, Ami in Deutschland

  8. #8
    Join Date
    Oct 2003
    Location
    Near D.C.
    Posts
    56
    Quote Originally Posted by dbzTHEdinosaur View Post
    rogue49
    Location: Near D.C
    - or -
    DMV?
    Live MD of the DMV was VA
    Work DC...I'd tell you where, but I'd have to kill you.

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow Fyi-2

    Quote Originally Posted by rogue49 View Post
    But that's not the point of what I'm going after, whether something is a literally a view or not.
    But something that allows me to apply indexing to standing SQL statements to potentially optimize the performance of its results.
    Not guaranteed, but I'm trying to consider my options and think outside the box.

    MQT, Materialized View, Indexed View...

    I believe the gentleperson's answer gave me an option to the situation that was inferred in the question.

    But thanks for the input, I do appreciate it.
    MQT is working faster than view (with the same structure), but REFRESH could take a long time.

    Lenny

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Lenny77
    You have to execute REFRESH TABLE mqt_tbl
    before retrieve information from mqt_tbl table.
    Quote Originally Posted by Lenny77 View Post
    MQT is working faster than view (with the same structure), but REFRESH could take a long time.
    Both these statements, like all hasty generalizations, may prove incorrect under certain conditions.

  11. #11
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Cool

    Quote Originally Posted by n_i View Post
    Both these statements, like all hasty generalizations, may prove incorrect under certain conditions.
    Everything is possible !

    Lenny

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Lenny77 View Post
    Everything is possible !
    Another hasty generalization.

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Lenny77 View Post
    MQT is NOT a VIEW. You have to execute REFRESH TABLE mqt_tbl
    before retrieve information from mqt_tbl table.

    Lenny
    You only have to explicitly issue the refresh one time (right after you create the MQT) if you define the MQT as system maintained. Thereafter, DB2 will automatically keep the MQT in synch with the base table. Obviously, you might not want an MQT to be system maintained if there were a lot of changes being made to the base table and the MQT was a summary table.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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