Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2013
    Location
    India
    Posts
    249

    Unanswered: About table size

    Hi All,

    Environment:

    DB2 expres c 11.0 on RHEL 6

    I issued below query, but out put is not showing table size. Please suggest

    select substr(b.tabschema,1,10) as tabschema,substr(a.tbspace,1,20) as tablespace, substr(b.tabname,1,20) as table_name,substr(char(card*avgrowsize/1024/1024/1024),1,7) as size_KB, substr(char(b.card),1,8) as cardinality ,substr(char(b.stats_time), 1,10) as stats_time from syscat.tablespaces a, syscat.tables b where b.tabschema = 'DB2INST1' and a.tbspaceid = b.tbspaceid and type = 'T' order by table_name

    I am getting this output.

    TABSCHEMA TABLESPACE TABLE_NAME SIZE_KB CARDINALITY STATS_TIME
    ---------- -------------------- -------------------- ------- ----------- ----------
    DB2INST1 USERSPACE1 AUCTION_MASTER_ARCHI 0 259 2017-06-07
    DB2INST1 USERSPACE1 CATEGORY_MASTER 0 0 2017-06-07
    DB2INST1 USERSPACE1 DD_MASTER_ARCHIVE 0 714 2017-06-07
    DB2INST1 USERSPACE1 DELIVERY_ORDER_ARCHI 0 196 2017-06-07
    DB2INST1 USERSPACE1 DEL_TAX_ARCHIVE_DATA 0 198 2017-06-07
    DB2INST1 USERSPACE1 INDIAN_STATES_DATA 0 36 2017-06-07
    DB2INST1 USERSPACE1 ITEM_DETAILS_ARCHIVE 0 681 2017-07-11
    DB2INST1 USERSPACE1 ITEM_MASTER_ARCHIVE_ 0 1035 2017-07-11
    DB2INST1 USERSPACE1 ITEM_MASTER_ARCHIVE_ 0 101430 2017-07-11
    DB2INST1 USERSPACE1 ITEM_SCHEDULE_ARCHIV 0 1035 2017-06-07
    DB2INST1 USERSPACE1 PCB_GROUP_MASTER_DAT 0 0 2017-06-07
    DB2INST1 USERSPACE1 VOUCHER_MASTER_ARCHI 0 430 2017-06-07
    ssumit

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    282
    Provided Answers: 41
    Regards,
    Mark.

  3. #3
    Join Date
    Apr 2016
    Posts
    26
    I think this will help you

    db2 "select substr(t.tabschema,1,18) as tabschema , substr(t.tabname,1,40) as tabname , t.CARD as card, (DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + XML_OBJECT_P_SIZE) as tab_size from syscat.tables t join sysibmadm.admintabinfo ati on t.tabname=ati.tabname and t.tabschema=ati.tabschema where t.type='T' and t.tabschema not like ('SYS%') order by 3 desc with ur"

  4. #4
    Join Date
    Nov 2004
    Posts
    77
    @ssumit

    Dividing 3 times by 1024 gives you gigabytes.

  5. #5
    Join Date
    Aug 2013
    Posts
    81
    Hi CCMF,
    I think is divide 2 times 1024 instead of 3 times, according to documentation the size for the column is in KB not in Bytes.
    My question is if I have x number of new columns to be added to the table, due to this we would like to know how much estimate size require after introduce new columns.

  6. #6
    Join Date
    Nov 2004
    Posts
    77
    @ckwan123

    I think is divide 2 times 1024 instead of 3 times, according to documentation the size for the column is in KB not in Bytes.
    Look at the following link:
    https://www.ibm.com/support/knowledg.../r0001063.html. AVGROWSIZE is given in bytes.

    If you have 15.000 rows with an avgrowsize of 120 bytes then you have an estimated size of 15.000 * 120 = 1.800.000 bytes
    1.800.000 bytes / 1024 = 1.757,8125 KB
    1.757,8125 KB / 1024 = 1,72 MB (rounded to 2 decimal places)
    MB / 1024 = GB

    My question is if I have x number of new columns to be added to the table, due to this we would like to know how much estimate size require after introduce new columns.
    number_of_rows * estimated_size_per_row should give you the estimated size for a column in bytes. Repeat this for each new column and add the values.

Posting Permissions

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