Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    26

    Unanswered: 30 largest tables in DB2

    Hi all,

    Can someone help with a query to help me find the 30 largest tables in a DB2 UDB database?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    First you need execute the runstats command on each table. See the Command Reference manual. You should also use the "indexes all" option.

    Then, to get the size, query the syscat.tables view and look at the NPAGES times the pagesize (from the syscat.tablespaces). This will give you the number of bytes, which includes page overhead and freespace.

    You can also get the number of rows via the CARD column. See the SQL Reference Volumne 1 appendix for a description of all the columns in the syscat views which are the DB2 Catalog.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2004
    Location
    Phoenix, AZ
    Posts
    42
    To add, after reviewing the catalog tables, if you wanted just the largest tables by row count, you could run a query similar to this one:

    Select name, card from sysibm.systables where card > 1000000 order by card desc

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    ... or of course with something like
    Code:
    SELECT name, card FROM sysibm.systables
    ORDER BY card DESC
    FETCH FIRST 30 ROWS ONLY
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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