Calculating the size of databases

Abstract

There is no command provided with IBM Informix Dynamic Server that shows the total size of each database in an instance. If you want to know which are using the most space, but do not have a GUI or need an automated script, this article provides an SQL statement for that purpose.

Content

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

SELECT
    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

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!



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.