Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Posts
    47

    Unanswered: Alternative to MQT

    I have a table called PTBalance with the following columns

    a) Acct_Nbr
    b) Acct_Type
    c) Currency
    d) Balance

    I want to have a table(Physical also fine) having the following data

    a) Acct_nbr
    b) Sum(Balance)

    Every day PTBalance table is replaced by a new table(drop the existing one, create a new one with latest data)


    I created a MQT with acct_nbr and Sum(balance) then I try to delete the base table, db2 did n't allow me because it has MQT reference.

    1)

    Is there any other utility in DB2 which does not keep reference to base tables?


    2) Can I create a physical table not MQT by using Create table as select statement. Can I use group by as part of select criteria?

    3) What is the difference between MQT and Create table as select?



    Thanks in advance.
    Shri
    p.srinivasarao

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The data in an MQT is periodically refreshed (either every time the data in the underlying tables is changed, or when you issue issue a refesh table command).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2003
    Posts
    47
    Thanks for the reply. I have a requirement to create a summary table or MQT or some utility with no reference to base tables bcz base tables are recreated every day.

    If I use MQT , I can't drop base table. Is there any approach in db2 create a table from some sql and no reference to base tables.

    Thanks
    Shri
    p.srinivasarao

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Why do you recreate the base table every day? You could just truncate it.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jan 2003
    Posts
    47
    Unfortunatly yes, we load the data into a intermediate tables and swap with original table.
    p.srinivasarao

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Then you could also construct the new MQT after the load and swap that with the original one.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jan 2003
    Posts
    47
    Thats a good question, But the table has 4-6 Millions of records which might take some time. During that period user won't be able to access neither base table nor MQT.

    Is there any way I can construct a intermediate table with no reference to the base table.

    SELECT ACCOUNTNUMBER,-1*SUM(TRADEBALANCE) as TRADEBALANCE
    FROM PTBALANCE GROUP BY ACCOUNTNUMBER

    Out put of the above query should be added to intermediate table with no reference to the base table.

    Let me know if additional information is required.
    p.srinivasarao

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    How about loading from a cursor?
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Jan 2003
    Posts
    47
    I read the documentation for Cursors. It exactly satisfies our requirements. I don't see any documentation on the performance of this cursor utility. Please provide me if you have any?


    Thanks for your help all of you.


    Shri
    p.srinivasarao

Posting Permissions

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