30 largest tables in DB2

cougartrace · #1 (**permalink**) 10-12-05, 15:18

Hi all,

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

Marcus_A · #2 (**permalink**) 10-12-05, 22:01

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.

rvsc36 · #3 (**permalink**) 10-14-05, 15:01

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

Peter.Vanroose · #4 (**permalink**) 10-15-05, 09:21

... or of course with something like

Code:

SELECT name, card FROM sysibm.systables
ORDER BY card DESC
FETCH FIRST 30 ROWS ONLY

Internet Services

Web Development

Digital Marketing

Consumer Tech