Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / May 2006

Tip: Looking for answers? Try searching our database.

MDC woes

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
cbielins@gmail.com - 15 May 2006 19:37 GMT
I am seeing this problem where we have an MDC table organized by time
dimension id.

There are currently 1301 total rows within the table, and 350 distinct
time ids avg'ing out to 3.8 rows per time id.

We run an mdc_blk check with the SQL below:

select          substr(tabschema,1,20) as tabschema                  ,
                    substr(tabname,1,30) as tabname
   ,
                     card                             ,
                     npages                       ,
                     fpages                       ,
                     clustered         ,
                    active_blocks        ,
                    substr(index_tbspace,1,20) as index_tbspace  ,
                    substr(tbspace,1,20) as tbspace
from syscat.tables
where tbspace = '<tbl_space>'
    and clustered = 'Y'
order by 4,5

Back to this table: there are

CARDS = 1301 CARDS
NPAGES =  350
FPAGES = 22464

So from this information, 1301 rows are taking up to 22,000 32K pages,
which is roughly 700MB worth of storage.

Does this sound reasonable?  Can someone explain to why this is
happening?
Jean-Marc Blaise - 15 May 2006 21:06 GMT
Hi,

22464 / 350 = 64,  every cell takes 1 extent and it seems your extents are
64 32K pages. Your dimension has too many distinct values; if you still want
to choose time as dimension, you need to rollup the values and use a
generated column ...

CREATE TABLE MDC (
   cols ...
   dim generated alwars as (int(time)/100  integer)
) organize by (dim)

HTH,

JM

> I am seeing this problem where we have an MDC table organized by time
> dimension id.
[quoted text clipped - 30 lines]
> Does this sound reasonable?  Can someone explain to why this is
> happening?
ChrisC - 16 May 2006 16:33 GMT
Hi,

Another option you might want to look into is creating a tablespace
with less extents (maybe even 1) for this MDC table.  If this table is
going to stay this size, and it is returning improved performance over
non-MDC table, then I think the smaller extents would be a good idea.

If, however, you are expecting to dramatically grow the table -
especially grow the number of rows for each dimension value - then
reducing the extent size probably wouldn't be a good idea.

-Chris
cbielins@gmail.com - 18 May 2006 00:41 GMT
Thanks guys, I appreciate yout help!

Chris
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.