Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Sep 2005
    Posts
    9

    Unanswered: calculate table size

    Hi

    I have the row count for a table. Is there a way i could find the avergae row size and then use it to calculate table size

    row count: X
    avg. column size: Y

    Table size = X * Y

    Please let me know.

    Thanks

    kayur

  2. #2
    Join Date
    Nov 2004
    Posts
    374

    table size

    sysibm.systables keeps the nbr of rows
    syscolumns keeps average column length
    make a sum of avg collength * rows
    Best Regards, Guy Przytula
    DB2/ORA/SQL Services
    DB2 DBA & Advanced DBA Certified
    DB2 Dprop Certified
    http://users.skynet.be/przytula/dbss.html

  3. #3
    Join Date
    Sep 2005
    Posts
    9

    more inof plzz

    hi

    Thanks.

    syscolums is fine. i am a little new... can u give the detail SQl query.. that would be great

    kayur

  4. #4
    Join Date
    Nov 2004
    Posts
    374

    table size

    first runstats on table to keep real info
    db2 "select sum(c.avgcollen)*t.card from sysibm.syscolumns c,sysibm.systables t where c.tbname='xxxx' and c.tbcreator='yyy' and c.tbname=t.name and c.tbcreator=t.creator group by t.card"
    Best Regards, Guy Przytula
    DB2/ORA/SQL Services
    DB2 DBA & Advanced DBA Certified
    DB2 Dprop Certified
    http://users.skynet.be/przytula/dbss.html

  5. #5
    Join Date
    Oct 2005
    Posts
    109
    to get the real physical size I'd rather go with no of pages and pagesize.

    I mean it could be that you have long rows and just using 2/3rd of a page - so then the result could be somewhat irritating.
    Juliane

  6. #6
    Join Date
    Aug 2004
    Posts
    138
    do db2 'reorgchk update statistics on table tablename' ..look for TSIZE

    this has been posted many times..please search the forum before posting

  7. #7
    Join Date
    Mar 2013
    Posts
    9
    hi can u provide me query to calculate average table size for db2...urgent

  8. #8
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    john sergo :
    why are you posting an "urgent" question in a thread that has been started by skumbhan ?
    this matter has been handled many times in the forum. do a simple search ..
    or look (and read) at infocenter about this topic...
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question avgcollen

    Quote Originally Posted by przytula View Post
    first runstats on table to keep real info
    db2 "select sum(c.avgcollen)*t.card from sysibm.syscolumns c,sysibm.systables t where c.tbname='xxxx' and c.tbcreator='yyy' and c.tbname=t.name and c.tbcreator=t.creator group by t.card"
    Formula is looking good. But, sorry, where you found column with name avgcollen ?

    Lenny

  10. #10
    Join Date
    Mar 2013
    Posts
    9
    ok can u tell me how to find average table size in db2....

  11. #11
    Join Date
    Aug 2008
    Posts
    147
    Page size is the way to go:
    SELECT SUBSTR(TABSCHEMA,1,18) TABSCHEMA,SUBSTR(TABNAME,1,30) TABNAME,SUM(DATA_OBJECT_P_SIZE) DATA_OBJECT_P_SIZE,SUM(INDEX_OBJECT_P_SIZE) INDEX_OBJECT_P_SIZE,SUM(LONG_OBJECT_P_SIZE) LONG_OBJECT_P_SIZE,SUM(LOB_OBJECT_P_SIZE) LOB_OBJECT_P_SIZE,SUM(XML_OBJECT_P_SIZE) XML_OBJECT_P_SIZE FROM SYSIBMADM.ADMINTABINFO WHERE TABSCHEMA NOT LIKE 'SYS%' GROUP BY TABSCHEMA, TABNAME

    To view a shell script you can use to run across all the databases on an instance - http://www.dba-db2.com/2011/05/db2-t...ll-script.html
    Last edited by JackVamvas; 04-24-13 at 12:03. Reason: revised
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

  12. #12
    Join Date
    Mar 2013
    Posts
    9
    hi thanks JackVamvas ....for my project i required average of all table sizes...for your query ..when i executed i got answer as follows

    db2 => select substr(tabname,1,30) tabname , sum(data_object_p_size) data_object_p_size,sum(index_object_p_size) index_object_p_size from sysibmadm.admintabinfo where tabschema not like 'sys%' group by tabschema,tabname


    TABNAME DATA_OBJECT_P_SIZE INDEX_OBJECT_P_SIZE
    ------------------------------ -------------------- --------------------
    C_CUSTOMER 240256 8960
    C_DISTRICT 256 256
    C_HISTORY 22528 0
    C_ITEM 10752 2048
    C_NEW_ORDER 2432 2944
    C_ORDER 15232 9216
    C_ORDER_LINE 256896 104960
    C_STOCK 364032 33280
    C_WAREHOUSE 256 256
    HMON_ATM_INFO 80 48
    HMON_COLLECTION 32 32
    POLICY 32 32
    STUDENT1 256 0
    SYSATTRIBUTES 32 48
    SYSAUDITPOLICIES 32 48
    SYSAUDITUSE 32 32
    SYSBUFFERPOOLNODES 32 32
    SYSBUFFERPOOLS 32 48
    SYSCHECKS 32 32
    SYSCODEPROPERTIES 64 48
    SYSCOLAUTH 32 48
    SYSCOLCHECKS 32 48
    SYSWORKLOADAUTH 32 64
    SYSWORKLOADCONNATTR 32 48
    SYSWORKLOADS 32 64
    SYSWRAPOPTIONS 32 32
    SYSWRAPPERS 32 32
    SYSXDBMAPGRAPHS 32 32
    SYSXDBMAPSHREDTREES 32 32
    SYSXMLPATHS 32 48
    SYSXMLSTRINGS 32 48
    SYSXSROBJECTAUTH 32 48
    SYSXSROBJECTCOMPONENTS 32 48
    SYSXSROBJECTHIERARCHIES 32 48
    SYSXSROBJECTS 32 48
    .
    .
    ..
    .........

    143 record(s) selected.

    now i required to sum DATA_OBJECT_P_SIZE and INDEX_OBJECT_P_SIZE to get total size of a respective table..i got these many tables bec i ve runned bench mark factory to generate workload...now can u give me the query to add DATA_OBJECT_P_SIZE & INDEX_OBJECT_P_SIZE and average of this..
    thanks...

  13. #13
    Join Date
    Mar 2013
    Posts
    9
    Quote Originally Posted by Lenny77 View Post
    Formula is looking good. But, sorry, where you found column with name avgcollen ?

    Lenny
    avgcollen-----average column length..it is not a separate column in a database...it will give average of all columns in a database..if u r not getting..just run it and observe the output..

  14. #14
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow

    Quote Originally Posted by john sergo View Post
    avgcollen-----average column length..it is not a separate column in a database...it will give average of all columns in a database..if u r not getting..just run it and observe the output..
    I'm asking not about how but about where get.

    Let see your formula:

    "select sum(c.avgcollen)*t.card from sysibm.syscolumns c,sysibm.systables t where c.tbname='xxxx' and c.tbcreator='yyy' and c.tbname=t.name and c.tbcreator=t.creator group by t.card"
    I can see c.avgcollen and table sysibm.syscolumns c, so it has to be column of this table, but in this table we don't have this column.

    Code:
    select c.avgcollen from sysibm.syscolumns c
    Returns:
    SQL0206N "C.AVGCOLLEN" is not valid in the context where it is used. SQLSTATE=42703

    State: S0022,Native:-206

    Lenny

  15. #15
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by Lenny77 View Post
    I'm asking not about how but about where get.

    Let see your formula:



    I can see c.avgcollen and table sysibm.syscolumns c, so it has to be column of this table, but in this table we don't have this column.

    Code:
    select c.avgcollen from sysibm.syscolumns c
    Returns:
    SQL0206N "C.AVGCOLLEN" is not valid in the context where it is used. SQLSTATE=42703

    State: S0022,Native:-206

    Lenny

    What platform are you using?

    LUW - exists
    zOS - nope

Posting Permissions

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