How to Determine the size of All of the User Tables Administered by the Database Server

Problem(Abstract)

This article has a query that reports the total pages allocated to user tables across a database server.

Resolving the problem

INTRODUCTION

You want to determine the size of all user tables on an individual table basis. For example, you might want to know if the total pages for a table is approaching the maximum number. The query reports all the user-created tables for all databases. These columns are in the report:

• database space name
• database name
• total pages allocated
• total pages used
• number of allocated pages that are data pages

Note: The query returns the same data as the oncheck -pT commands. The difference is that the query reports table data for all databases.


BEFORE YOU BEGIN

Determine if any of your user table names begin with "sys". System catalog tables begin with the "sys" prefix and the query selects against this prefix.


STEPS

Run this SQL statement from the sysmaster database:

SELECT s.name dbspace, n.dbsname database, 
n.tabname table, ti_nptotal total, ti_npused used, 
ti_npdata data
FROM systabinfo i, systabnames n, sysdbspaces s
WHERE i.ti_partnum = n.partnum 
AND partdbsnum(i.ti_partnum) = s.dbsnum
AND n.dbsname != "sysuser"
AND n.dbsname != "sysmaster"
AND n.dbsname != "sysutils"
AND n.tabname NOT LIKE "sys%"
AND n.tabname != "TBLSpace";

Tip: By adjusting the WHERE clause you can modify the query to report data from a different set of databases or to include the system catalog tables.

http://www-01.ibm.com/support/docview.wss?uid=swg21175863