Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Need help figuring out size of table

    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!
    Last edited by db2user24; 11-21-11 at 16:12.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Did you account for the size of indexes on the table?

  3. #3
    Join Date
    Nov 2007
    Posts
    265
    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!
    Last edited by db2user24; 11-21-11 at 18:23.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

  5. #5
    Join Date
    Nov 2007
    Posts
    265
    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!
    Last edited by db2user24; 11-21-11 at 19:44.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •