Estimate tablespace size

[Alvin SIU]

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

[shenanwei@gmail.com]

Re: Estimate tablespace size

On May 15, 9:10 am, Alvin SIU <alvin....@gmai l.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.

[Dave Hughes]

Re: Estimate tablespace size

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.TABLESPA CES 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.TABLESPA CES via
SYSCAT.TABLES.I NDEX_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.

--

[Knut Stolze]

Re: Estimate tablespace size

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

[Alvin SIU]

Re: Estimate tablespace size

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