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.

--