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

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.

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.

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.

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).

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.

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

Replicated MQTs: use the REPLICATE keyword on the CREATE TABLE statement.

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.

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.

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.