Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170

    Unanswered: RUNSTATS does not Work for MQT UDB EEE V9

    Hello guys

    Iam running into a bizzare issue. Runstats is not updating the system catalog tables with accurate row count of MQT. The card is way off and this is resulting poor performance for the queries. My MQT has 100k rows and card shows 6k

    db2_all "db2 connect to testdb;db2 runstats on table test_table with distribution and indexes all"

    UDB DPF V9 fixpack4 on AIX 5.3 (64 bit)

    Any one ran into this bizzare issue!!!!. I have a ticket open with IBM

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You realize, of course, that in a partitioned database statistics are collected for one partition only. If your MQT is distributed across 10 partitions, CARD will show approximately 1/10th of the actual number of rows in the table.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170
    Nick Does it mean DB2 maintains system catalogs on each of the nodes ?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by koganti
    Nick Does it mean DB2 maintains system catalogs on each of the nodes ?
    No, it does not. The system catalog exists only on the curiously named catalog node.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    I checked the same in my DB for the MQTs and the system catalogs showing correct number of records. Moreover if your table is in "n" different partitions ideally the system catalog shows you the total row count of the tables irrespective of the number of partitions.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The title of this thread is somewhat curious in that:

    1. Starting with DB2 V9, the name UDB has been dropped. It is now DB2 9 for Linux/UNIX/Windows.

    2. EEE hasn't existed since V7, was replaced in V8 by DPF feature added to ESE, and replaced in V9.5 by Data Warehouse Edition (although OP is on 9.1 and it is still called DPF feature).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170
    Quote Originally Posted by JAYANTA_DATTA
    I checked the same in my DB for the MQTs and the system catalogs showing correct number of records. Moreover if your table is in "n" different partitions ideally the system catalog shows you the total row count of the tables irrespective of the number of partitions.

    Jayanta , based on IBM when you run the runstats, DB2 will only take the first node for the sampling and saves that information in your catalogs.
    If you ever see the card is same as your number of rows then it may mean all your rows are on partition.

  8. #8
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    The MQTs I was refering are REPLICATED ones. So if you have 15 nodes, all the nodes have same number of records.
    Second case is, if you talk about a DPF, I am always refering a table which is partitioned on HASH (partition-key) for the above example. You don't have any tablespace defined on the co-ordinator node, and you will be interested in the total number of records in the table across all the Nodes.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  9. #9
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170
    Quote Originally Posted by JAYANTA_DATTA
    The MQTs I was refering are REPLICATED ones. So if you have 15 nodes, all the nodes have same number of records.
    Second case is, if you talk about a DPF, I am always refering a table which is partitioned on HASH (partition-key) for the above example. You don't have any tablespace defined on the co-ordinator node, and you will be interested in the total number of records in the table across all the Nodes.
    Jayanta , i just recreated my mQT with "distribute by hash" option and i see the rows are distributed across two nodes and i ran the runstats. Now the cardinality shows the total rows = 2*rows on first node.

    Iam trying to figure out on how to create replicated MQTs. Do you have steps on how you did it.

    Thx
    Prasad

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Replicated MQTs: use the REPLICATE keyword on the CREATE TABLE statement.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  11. #11
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    As Stolze has mentioned, you can created the replicated MQT that way. But you need to understand first whether you really need a Replicated MQT for your application. Using replicated MQTs improve collocation of joins for database partitioning as well as reduce the Intra-Node communication in a multi-node situation.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  12. #12
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170
    I dont see any Broadcasting , so iam not worried about collocation at this time.When i used hash distribution for MQT , i see huge performance gains.

    Thank you guys for the info.

  13. #13
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Great that HASHING has improved the performance significantly. You can also try running COMPRESSION ON the MQT (followed by REORG). You can achive around 55-65% compression on MQTs based on the data-quality. This will ensure that lesser number of data-pages will be scanned while querying the same.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

Posting Permissions

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