Db2 Warehouse Client Container - Again

In my last blog post, I explained how to get, set up, and run the Db2 Warehouse client container and connect to a database. While working with that Db2 environment, I also read some Db2 Warehouse client container documentation. The following page attracted my interest: commands command. A command for commands?!

So I had to try it. The output starts with this text, followed by a list of commands with their short descriptions.

#######################################################################
#     ----- The IBM Db2 Warehouse client container commands -----     #
#######################################################################

One of the listed commands is db_size. You can also find it in the Db2 support tools overview as part of the Db2 Warehouse documentation. db_size can be used to retrieve the database size, and size information for tables identified by schema or owner. I ran the command for one of my cloud databases, a schema DATAHENRIK, specified to return bytes and Gigabytes, and handed over my username and password:

db_size -d cloudde -schema DATAHENRIK -bytes -gb -u <user> -w <password>

That worked well and was simple, but how did the command do it? I found that it is a simple shell script that calls the Db2 CLP and runs SQL. So, for Db2, how do you obtain the database size or size information for tables?

Db2 database size information

For just obtaining the database size information, Db2 offers the GET_DBSIZE_INFO procedure. I tested in my command line environment and directly in the SQL tool in Db2 on Cloud:

CALL GET_DBSIZE_INFO(?, ?, ?, -1)

The three result values are the snapshot timestamp, the database size (dbsize) at that timestamp, and the database capacity - basically the database size plus free disk space available to the database storage containers. Because computing those values is expensive, Db2 caches them in a table SYSTOOLS.STMG_DBSIZE_INFO. That table is created once the procedure is called the first time. Thereafter, you can query that table:

select * from SYSTOOLS.STMG_DBSIZE_INFO

In the output for db_size (shown earlier), you may have noticed that it included size information for the table DATAHENRIK.TESTTABLE. That value is computed based on information obtained from ADMIN_GET_TAB_INFO / ADMINTABINFO, an administrative table function and administrative view. Both return logical and physical size data for data objects, indexes, large objects, long objects, and XML data, all per table. So all db_size does is to go over the tables for a schema and sum up the physical sizes for the components that make up a table, then nicely format it.

If you want to learn/read more about how to find the size of a Db2 database, I recommend the linked year 2012 blog post from Ember Crooks.

If you have feedback, suggestions, or questions about this post, please reach out to me on Mastodon (@data_henrik@mastodon.social) or LinkedIn.