Record count doesn’t match the CARD in SYSCAT.TABLES

January 22, 2007

Ran into this interesting issue today. The count on the table showed only about 118Million rows, but the CARD in syscat.tables showed to have about 1.04 Billion rows.

db2 “select count(1) from Schema.tableName”

1
———–
118697936

1 record(s) selected.

db2 “select card from syscat.tables where tabname like ‘tableName'”

CARD
——————–
1048386270

1 record(s) selected.

RUNSTATS was executed just two days back so that wasn’t the issue. I did try updating the stats again, with no luck. Upon digging deeper, realized the data was skewed and the partitioning key chosen wasn’t the best.

Node number 11 alone had about 104Million rows, while the rest of the partitions combined holding the reminder.

db2 “select count(1) from Schema.tableName where nodenumber(partitioning_key) = 11

1
———–
104838627

1 record(s) selected.

Since RUNSTATS on partitioned database will only look at the data in the very first data node and extrapolate the value for CARD. For the 10 partitions, the CARD value it showed made sense.

As a temporary fix: RUNSTATS was executed in the non-skewed node.
Permament fix: Determine a better partitioning key that is more unique to have better distribution

tags: , ,
posted in DB2 by Visu

Follow comments via the RSS Feed | Leave a comment | Trackback URL

Leave Your Comment

 
Powered by Wordpress and MySQL. Theme by Shlomi Noach, openark.org