SMI Query Series - Database Size

The sysmaster interface (SMI) presents a comfortable way to extract information about various parts of your running IDS instance. This information can be used for:

• ad hoc analysis of performance problems
• determining the status of several IDS sub components
• monitoring replication metrics
• sampling data for capacity planning purposes
• tracking system usage of individual sessions
• and many more...

The below SMI query is the second one of a new SMI query series at The Informix Zone. The queries show you how to extract valuable information from the sysmaster database.

smi_db_size.sql

The purpose of this SMI query is to display the individual sizes of all databases in your IDS instance .

The smi_db_size.sql comes in two versions. Choose the one that fits your current IDS version. The purpose of the substrings (stn.dbsname[1,35] is only to make sure that the retrieved row is displayed on a single line within the dbaccess utility. You can remove it if you need the full database name.

IDS < 10

DATABASE sysmaster;
SELECT stn.dbsname db_name,
   SUM
   (
      sti.ti_npused *
      (
         select sh_pagesize from sysshmvals
      )/1024/1024
   ) mb_used,
   SUM
   (
      sti.ti_nptotal *
      (
         select sh_pagesize from sysshmvals
      )/1024/1024
    ) mb_total
FROM systabnames stn, systabinfo sti, sysdatabases sdb
WHERE stn.partnum = sti.ti_partnum
AND   stn.dbsname = sdb.name
GROUP BY 1
ORDER BY 1;

IDS >= 10

DATABASE sysmaster;
SELECT stn.dbsname[1,35] db_name,
   SUM
   (
      sti.ti_npused *
      (
         select pagesize
            from  sysdbspaces
            where name = dbinfo('dbspace', sti.ti_partnum)
      )/1024/1024
   ) mb_used,
   SUM
   (
      sti.ti_nptotal *
      (
         select pagesize
            from  sysdbspaces
            where name = dbinfo('dbspace', sti.ti_partnum)
      )/1024/1024
    ) mb_total
FROM systabnames stn, systabinfo sti, sysdatabases sdb
WHERE stn.partnum = sti.ti_partnum
AND   stn.dbsname = sdb.name
GROUP BY 1
ORDER BY 1;