DB2 Administrative SQL Cookbook: Listing Tables and Current Size

Note: updated on 2/6/2015 to include long, lob, and xml data at a commenter’s suggestion.

The DB2 Administrative SQL Cookbook is a series of blog entries that are short. Each focuses on one or a few SQL statements that may be useful when administering DB2 databases. They usually address system catalog views, snapshot tables/views, or snapshot table functions that are standard in all DB2 LUW implementations. The version the SQL has been tested on is noted.

Purpose

To list tables and their current sizes in descending order by size. Also lists whether table is column or row organized. Can be useful for finding large tables or for identifying large tables that do not match the organization of other tables in the database.

Version

Used on DB2 10.5, Fixpack 5. Version provided for older DB2 versions, below.

select substr(t.tabschema,1,18) as tabschema
, substr(t.tabname,1,40) as tabname
, (DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE)/1024 as tab_size_mb
from syscat.tables t
join sysibmadm.admintabinfo ati
on t.tabname=ati.tabname
and t.tabschema=ati.tabschema
where t.type='T'
and t.tabschema not like ('SYS%')
order by 3 desc
with ur

Caveats and Modifications

Hard numbers are used to limit the length of the schema and table names. If you have longer schema and table names, you may want to increase these numbers.
The table size reported here is the physical size of the table and it’s indexes. I find this more useful than a logical size or looking only at table size when I want to figure out what my largest tables really are.
Strictly speaking “with ur” is not needed, but I’m in the habit of adding it to the end of every query that doesn’t have an isolation requirement.

Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science.
Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.

Ember,
This is not a response to this post but more of a question regarding some earlier post that you wrote on “Your new best friend…the db2 MON_* table functions and views”. I want to ask you if by any chance you are in a position to share how you used these new functions and views especially how you built your tables for history/trending performance metrics. I am in need of how to do this and would appreciate your experience and guidance in this matter. Obviously you can guess I follow your blog religiously…
Do let me know if you are able to help…

Then I added history tables and table functions to populate those history tables. The history tables consisted of all of the columns of the original mon_get table function plus columns for a baseline timestamp, a snapshot timestamp and a record type. I added a group of table functions and tables to cover mon_get_pkg_cache_stmt, which is not in the referenced article. I also created functions to properly summarize data so I could store less granular data longer – in many databases, I found that a week’s worth of data was close to 5 GB. I then had a perl script that would:

Capture baseline values (reset)

Wait some specified amount of time (usually an hour, but for some situations, I would use 5 or 15 minutes – it was a variable I could pass in)

Capture the history values to the history tables

Summarize old data based on some retention guidelines

Delete the oldest data from the database

The perl script then ran hourly or so on all of my databases so I could go out and get trends and look at past performance. I kept all of the data in a separate tablespace in a separate storage group on a separate filesystem that did not auto resize so I did not risk my monitoring data filling anything up that would affect database availability.

I could then query my history data the exact same way I would query the MON_GET table functions, simply adding a time component to it. I had queries that would help me find problem hours, and queries that would let me specify an hour or time range to see data on critical KPIs.

Honestly, it was a heck of a lot of work, and I spent weeks on it. The data, however, was invaluable and easy to query and made performance troubleshooting so much easier. If you have the ability to get a third party performance tool, that’s probably easier and cheaper. But I learned so much in the process. After completing these, I was able to pass the certification exam for 9.7 Stored Procedure Developer cold, with no study time at all, and it’s not an easy exam. I used Merge statements for the first time, and learned what columns actually uniquely identified the various objects – some of them were surprising or hard to figure out.