Databases: How to calculate the size of databases

Abstract

The ability to view the size of all your databases can be of great value.

However, there is no command currently provided with IBM Informix Dynamic Server that shows the total size of each of your databases in an instance. If you want to know which databases are using the most space, but do not have a GUI or need an automated script, this article provides an SQL statement that will enable you to view the size of any database you wish to measure .

Content

The following SQL run on the “sysmaster” database returns the totals of allocated and used pages per database:

Copy to Clipboard
Syntax HighlighterSELECT
    dbsname,
    SUM(ti_nptotal * ti_pagesize / 1024) :: INT AS kb_alloc,
    SUM(ti_npused  * ti_pagesize / 1024) :: INT AS kb_used
FROM
    sysdatabases AS d,
    systabnames AS n,
    systabinfo AS i
WHERE n.dbsname = d.name
AND ti_partnum = n.partnum
GROUP BY 1
ORDER BY 1

The sample output is provided below:

dbsname	kb_alloc	kb_used
demo	297784	268662
test	972	2210
example	501790	395588
stores	4232	3014
sysadmin	63450	60840
sysmaster	3930	2538
sysuser	2912	2054
sysutils	4184	2438

For example, the total of all extents (contiguous blocks of disk pages) allocated to tables and indexes in the standard “stores” demo database is 4232 KB, but only 3014 KB of pages within these extents have been consumed so far.

Conclusion

SQL queries on the “sysmaster” database are very useful and sometimes simpler than you might think! Its is a quick and easy way of viewing which databases are using the most space so you can take the necessary steps to address this.

Disclaimer

The code fix suggested above is provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.