473,320 Members | 2,098 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

MDC woes

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?

May 15 '06 #1
3 1519
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
<cb******@gmail.com> a écrit dans le message de
news:11**********************@y43g2000cwc.googlegr oups.com...
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?

May 15 '06 #2
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

May 16 '06 #3
Thanks guys, I appreciate yout help!

Chris

May 17 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Martin the Mac Addict | last post by:
Good evening to the skilled Ladies and Gentlemen of the PHP world. I am fairly new to all of this but learning quickly. I have run in to a need to remove blank lines from a form submission after...
7
by: Mark | last post by:
O, woe is me, to have seen what I have seen, see what I see! (That's Shakespeare for those who were wondering what I'm on about) I am "having fun" with cookies. And I wonder if I have...
0
by: Cedric | last post by:
This is a 3 weeks old problem, but having found a solution (and having looked for one here, finding only this message), I'm replying now. From: Jive (someone@microsoft.com) Subject: Upgrade...
3
by: Angel Cat | last post by:
Trying to get my jobs to send mail when job fails. Should be easy but it's giving me headache Had a whole slew of issues. Outlook is installed with a n outlook mail profile set up that can...
2
by: Andrew Thompson | last post by:
- NN 4.78 rendering woes, links at far left - I am trying to rework an old site, make it valid html and css, improving the x-browser and 'older browser' compatibility. My efforts so far, have...
0
by: Arun Bhalla | last post by:
I'm having some inconsistency problems with my deployment project ("Setup") and its custom actions ("Installer"). I'm using Visual Studio .NET 2003 (.NET 1.1, no service pack) on Windows XPSP1. ...
9
by: Mark Rae | last post by:
Hi, This time, I'm looking for a regular expression which says "the string must contain exactly seven or exactly eight digits" e.g. 123456 fails 1234567 passes 12345678 passes 123456789...
1
by: hdogg | last post by:
Scope Woes - IF statement nested in WHILE statement -PHP I have an array $actuals_sum. <?php while(conditions) { if($i == '24) {
4
by: =?Utf-8?B?VkIgSm9ubmll?= | last post by:
I am at my witless end here, please help! I have an ASP.Net aspx web page, hosted on Windows Server 2003, that receives a query string with the path to an autocad drawing file selected from a...
1
by: Mike Howarth | last post by:
I've been having a few problems with connecting to SQL Server, initially I was using dblib however found some problems with returning text fields whereby all text fields were ignored and it bawked...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.