Hi,

One of the tables in our database ( UDB 8.2 FP 11 on linux) is setup with the following partition group, bufferpool, tablespace settings --

CREATE DATABASE PARTITION GROUP "EMPLOYEE_GRP" ON DBPARTITIONNUMS (0);

CREATE BUFFERPOOL "EMPLOYEE_BP" DATABASE PARTITION GROUP "EMPLOYEE_GRP" SIZE 640000 PAGESIZE 8192 NOT EXTENDED STORAGE;

CREATE REGULAR TABLESPACE EMPLOYEE_TBLSP IN DATABASE PARTITION GROUP EMPLOYEE_GRP PAGESIZE 8192 MANAGED BY SYSTEM
USING ('/DBDATA/emp_data/emp_data_sp1',
'/DBDATA/emp_data/emp_data_sp2',
'/DBDATA/emp_data/emp_data_sp3',
'/DBDATA/emp_data/emp_data_sp4',
'/DBDATA/emp_data/emp_data_sp5',
'/DBDATA/emp_data/emp_data_sp6',
'/DBDATA/emp_data/emp_data_sp7',
'/DBDATA/emp_data/emp_data_sp8',
'/DBDATA/emp_data/emp_data_sp9',
'/DBDATA/emp_data/emp_data_sp10',
'/DBDATA/emp_data/emp_data_sp11',
'/DBDATA/emp_data/emp_data_sp12',
'/DBDATA/emp_data/emp_data_sp13',
'/DBDATA/emp_data/emp_data_sp14',
'/DBDATA/emp_data/emp_data_sp15',
'/DBDATA/emp_data/emp_data_sp16')
EXTENTSIZE 32
PREFETCHSIZE 192
BUFFERPOOL EMPLOYEE_BP
OVERHEAD 24.100000
TRANSFERRATE 0.900000
FILE SYSTEM CACHING
DROPPED TABLE RECOVERY ON;


------------------------------------------------------------------------------------
If I check under /DBDATA/ and give the command 'du -h -s *' to get the size of the data for each tablespace, I get 76GB for emp_data.

There is only one table EMPLOYEE in the EMPLOYEE_TBLSP and if I run stats on it and use this command posted by someone earlier, I get the size as 38428.18 MB ( 37.52 GB) --

db2 "RUNSTATS ON TABLE EMPLOYEE"
DB20000I The RUNSTATS command completed successfully.

[db2user@testdb ~]$ db2 "select substr(t.tabschema,1,10)||'.'||substr(t.tabname,1, 20) as table ,char(date(t.stats_time)) as statsdate ,char(time(t.stats_time)) as statstime ,T.CARD as rows_per_tbl, decimal(float(t.npages)/(1024/(ts.pagesize/1024)),9,2) as used_mb ,decimal(float(t.fpages)/(1024/(ts.pagesize/1024)),9,2) as allocated_mb from SYSCAT.TABLES T ,SYSCAT.TABLESPACES TS where t.tbspace=ts.tbspace and T.tabname='EMPLOYEE' and T.TYPE='T'"

TABLE STATSDATE STATSTIME ROWS_PER_TBL USED_MB ALLOCATED_MB
------------------------------- ---------- --------- -------------------- ----------- ------------
DB2USER .EMPLOYEE 11/21/2011 17:45:08 811088680 38428.18 38428.18



Can you tell me what the real size of my table is? Is there an error in the USED_MB calculation in the above command ? I'm aware that with the tablespace params, the total table size allowed is 128GB ( for 8K page size) so I just want to make sure that I'm reading this correctly. Thanks!

Did you account for the size of indexes on the table?

Oh, I didn't count those.. do I need to run this command instead --

db2 "RUNSTATS ON TABLE EMPLOYEE WITH DISTRIBUTION AND INDEXES ALL"

OR

db2 "RUNSTATS ON TABLE EMPLOYEE AND INDEXES ALL"

How can I get the size of indexes too? If I see a total of 76GB and the table is only 37.52 GB, is the rest of the space being taken up by the size of the index only? It seems that it would be something else ( along with the table + index size maybe? ) that's making it add up to such a big number..

Thanks for your help!

You may want to try "db2pd -tcbstats index", which will show you the actual page numbers for all table objects, including LOBs and XML, and indexes.

Thanks, here are the values I get back that are non-zero... I see here from the table stats that the index size is greater than the table size :

Table Stats --


TableName = EMPLOYEE

Scans = 2

ObjClass = Perm

UDI = 15913

DataSize = 4919163

IndexSize = 4921058

FscrUpdates = 717

Inserts = 71620




TCB Index Stats:

TableName = EMPLOYEE

IID = 1

Scans = 1

NonBndSpts = 449

PgAllocs = 451

IntNodSpl = 2


Also, if the put the DataSize and IndexSize in the formula from the query above [[ decimal(float(t.npages)/(1024/(ts.pagesize/1024)),9,2) ]] I get --

38430.96 MB + 38445.76 MB = 37.53 GB + 37.54 GB = 75.07 GB which is close to the 76GB I get when I go to the db location and check the tablespace size directly...am I on the right track? thanks much for your help!