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.
--
