Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2002
    Posts
    123

    Unanswered: how to get size of db tables

    Hi,

    Is there an easy way to get the size of an individual db2 table? I want to remove old records from tables that are taking up too much space but think a count query will simply take too long to run on certain tables.. thanks!!

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    You can get the number of rows (card column) or number of pages (npages columns) from syscat.tables. The numbers are as of last runstats execution.

  3. #3
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    - show table size:
    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=’table name here‘ and T.TYPE=’T’”

    - show all tables sizes in DB:
    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.TYPE=’T’ order by table”
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  4. #4
    Join Date
    Dec 2002
    Posts
    123
    thank you Markham!

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    db2top,
    uppercase "T",
    arrow right until you'll see the header "Table-Size"

    You do not see the # rows but the size in Kb/Mb/Gb.

  6. #6
    Join Date
    Dec 2002
    Posts
    123
    I tried Markham's command and for some reason, I get back CARD, USED_MB and ALLOCATED_MB as -1, 0.00, 0.00 for all the tables. Any idea why this would come back as the result? Thanks!

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    See post #2 in this thread.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Duplicate removed
    Last edited by n_i; 10-28-09 at 17:18.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Nov 2007
    Posts
    265
    Hmm, for some reason if I try "select * from syscat.tables" I still get the -1, 0.00, 0.00 combination.. is there some setting option I can change to make this command return the correct values?

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Do runstats. If you still get those values then it isn't a table but a view.

    Andy

Posting Permissions

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