473,320 Members | 2,193 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.

Estimate tablespace size

Hi all,

I am now doing development using DB2.

Q1.
There is one tablespace to store 5 tables.

But, when in production, there will be one tablespace for EACH of the
tables.

Is there any method to ...
for example, find out the row-size of each of the table
so that I can estimate the tablespace size for each table.

Assuming that the development record volume is 1% of the production
volume.
Q2.
Moreover, just for curiorsity, since now 5 tables are sharing the same
tablespace,
is there any way to know how much each table is consuming the
tablespace ?
Thanks in advance.
Alvin SIU

May 15 '07 #1
4 13161
On May 15, 9:10 am, Alvin SIU <alvin....@gmail.comwrote:
Hi all,

I am now doing development using DB2.

Q1.
There is one tablespace to store 5 tables.

But, when in production, there will be one tablespace for EACH of the
tables.

Is there any method to ...
for example, find out the row-size of each of the table
so that I can estimate the tablespace size for each table.

Assuming that the development record volume is 1% of the production
volume.

Q2.
Moreover, just for curiorsity, since now 5 tables are sharing the same
tablespace,
is there any way to know how much each table is consuming the
tablespace ?

Thanks in advance.
Alvin SIU
DB2CC can do that, estimate per table.
If it is SMS, easy to find out. It is just different files.
If it is DMS, use stats to find out.

May 15 '07 #2
Alvin SIU wrote:
Hi all,

I am now doing development using DB2.

Q1.
There is one tablespace to store 5 tables.

But, when in production, there will be one tablespace for EACH of the
tables.

Is there any method to ...
for example, find out the row-size of each of the table
so that I can estimate the tablespace size for each table.
The system catalog views, in particular the SYSCAT.TABLES view [1] are
your friend here. Ensure you have performed RUNSTATS on each of the
tables, and then try the following query:

SELECT AVGROWSIZE
FROM SYSCAT.TABLES
WHERE TABSCHEMA = 'MYSCHEMA'
AND TABNAME = 'MYTABLE'

This will return the "average row size" for the table in question.
Naturally, if you have variable size columns in the table (VARCHAR et
al.) then this may differ from your production tables (depends on how
representative your development data is).
Assuming that the development record volume is 1% of the production
volume.
Assuming the above, simply multiply the values returned by the queries
below by 100 to obtain the production value...
Q2.
Moreover, just for curiorsity, since now 5 tables are sharing the same
tablespace,
is there any way to know how much each table is consuming the
tablespace ?
The following query will tell you how many pages a table is currently
using in its tablespace:

SELECT FPAGES
FROM SYSCAT.TABLES
WHERE TABSCHEMA = 'MYSCHEMA'
AND TABNAME = 'MYTABLE'

To find out the number of bytes this represents you need to know the
page size of the tablespace:

SELECT T.FPAGES * TS.PAGESIZE
FROM
SYSCAT.TABLES T
INNER JOIN SYSCAT.TABLESPACES TS
ON T.TBSPACEID = TS.TBSPACEID
WHERE T.TABSCHEMA = 'MYSCHEMA'
AND T.TABNAME = 'MYTABLE'

You can also use NPAGES instead of FPAGES to find out the number of
pages containing row data as opposed to the total number of pages taken
up by the table. Note that even in this case, the figure returned is
not necessarily the same as the size of the data held by the table -
for example, there may be unused space at the end of a page.

If you have indexes on these tables, you can also calculate the
approximate size of these using the SYSCAT.INDEXES view [2] (see the
NLEAF column) although if indexes are in a separate tablespace to the
table's data one must take care to join to SYSCAT.TABLESPACES via
SYSCAT.TABLES.INDEX_TBSPACE.

Finally, if the tables contain LOB data, you may have a problem. The
LONG_TBSPACE column of the SYSCAT.TABLES view will tell you the
tablespace in which long data for that table is stored, but I'm not
sure if there's a way to tell how many pages of long data a table is
currently using (unless it's included in NPAGES or FPAGES, but the
documentation doesn't make this clear).

[1] http://tinyurl.com/2yt7yk
[2] http://tinyurl.com/2zecvf
HTH,

Dave.

--

May 15 '07 #3
Alvin SIU wrote:
Hi all,

I am now doing development using DB2.

Q1.
There is one tablespace to store 5 tables.

But, when in production, there will be one tablespace for EACH of the
tables.

Is there any method to ...
for example, find out the row-size of each of the table
so that I can estimate the tablespace size for each table.

Assuming that the development record volume is 1% of the production
volume.
Q2.
Moreover, just for curiorsity, since now 5 tables are sharing the same
tablespace,
is there any way to know how much each table is consuming the
tablespace ?
Which version of DB2 are you using? What's your platform?

It is usual to use 1 table = 1 tablespace on DB2 z/OS. On DB2 LUW, it is
rather unusual...

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
May 15 '07 #4
HI Knut,

Answer your question about why 1 tbs per table.

It is because my system is a data warehouse system built many years
ago.

Each table is very large.
I do not know why the DBA will design in this way.
But, maybe one reason is the filesize constraint.
Maybe at that good old days, putting 2 large tables together will need
a large-large size which may break some size limit.

Another possible reason is maybe for easy management.
When putting 2 tables in 1 tbs, if one table gets error when loading
(we use the "load" command),
the other table will be suffer because the whole tbs will be in
"loading pending" state.
Then, users will compliant why the table-B loading error making me not
able to use table-A.

Therefore, 1 tbs per table can have a "loose coupling" adv.
Only the affected table is suffered without affecting other tables.

Feel free to discuss.
Alvin SIU

May 28 '07 #5

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

Similar topics

2
by: Stanley Sinclair | last post by:
About to create a table which will "include" a BLOB. Am not sure how large to make the container and the tablespace. What I see says that BLOB is stored "separately." However, I don't know...
12
by: xixi | last post by:
hi, i am using db2 udb v8.1 on windows, i try to use control center to estimate table size, but some of them showing 0 rows even though there are 6000 rows in the table, some of them showing...
4
by: Fan Ruo Xin | last post by:
How to estimate the size of log space when I need to run "INSERT INTO tgt_tbl SELECT * FROM src_tbl WHERE..." ? What is the difference of before image between the above INSERT stmt and the...
1
by: Bruce Pullen | last post by:
db27.2 (fp13) on AIX 5.2. The following error is generated when attempting a reorg or, more precisely, when the file associated with the reorg tablespace reaches 1Gb. SQL2216N SQL error...
0
by: Yuri Ludevig | last post by:
I recently was asked to switch from using raw lv's to file system and the first problem I encountered was with calculating the exact size for tablespaces. This is an example: I create LV and...
2
by: ari | last post by:
Hi! Normally Google helps a lot with these simple questions, but now I cannot get correct search words. So I try here: I have db2 udb 8.1 fix pack 6, and it is on windows 2003 server. -...
0
by: Lan W via DBMonster.com | last post by:
I tried to restore DB2 UDB two databases into two different instances on AIX server. the raw devices size of rShortSpace0 and rShortSpace1 on the two instances are the same. # lsvg -l datavg...
0
by: ramesh jampala | last post by:
Hello Gurus, I know that we can get tablespace used and free page information using " db2 list tablespaces show detail" command. But this information should be stored somewhere in catalog tables....
1
by: shraddhamhatre | last post by:
Hi friends I am new in oracle , can you pls help me to know what to keep size of temp and undo tablespace? And also wanted to know is there any inbuild script of oracle which will give the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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...
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)...
0
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: 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
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.