SMI Query Series - Dbspace Sizes

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 fith 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_dbspace_size.sql

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

-- ****************
-- normal dbspaces
-- ****************
SELECT 
   sd.name[1,18],
   SUM(sc.chksize * ( SELECT sh_pagesize FROM sysshmvals)) / (1024*1024) mb_allocated,
   SUM(sc.nfree * ( SELECT sh_pagesize FROM sysshmvals)) / (1024*1024) mb_free,
   100 / SUM(sc.chksize) * SUM(sc.nfree) percent_free
FROM sysdbspaces sd, syschunks sc
WHERE sd.dbsnum = sc.dbsnum
AND   sd.is_sbspace = 0
AND   sd.is_blobspace = 0
GROUP by sd.name
 
UNION
 
--  *************
--  blob dbspaces
--  *************
SELECT
   sd.name[1,18],
   SUM(sc.chksize * ( SELECT sh_pagesize FROM sysshmvals)) / (1024*1024) mb_allocated,
   SUM(sc.nfree * sdt.pagesize) / (1024*1024) mb_free,
   100 / SUM(sc.chksize) * SUM(sc.nfree * sdt.pagesize) / ( SELECT sh_pagesize FROM sysshmvals) percent_free
FROM sysdbstab sdt, sysdbspaces sd, syschunks sc
WHERE sd.dbsnum = sc.dbsnum
AND   sd.dbsnum = sdt.dbsnum
AND   sd.is_blobspace = 1
GROUP by sd.name
 
UNION
 
--  ******************
--  smartblob dbspaces
--  ******************
SELECT
   sd.name[1,18],
   SUM(sc.chksize) * ( SELECT sh_pagesize FROM sysshmvals) / (1024*1024) mb_allocated,
   SUM(sc.udfree) * ( SELECT sh_pagesize FROM sysshmvals) / (1024*1024) mb_free,
   100 / SUM(sc.chksize) * SUM(sc.udfree) percent_free
FROM sysdbspaces sd, syschunks sc
WHERE sd.dbsnum = sc.dbsnum
AND sd.is_sbspace = 1
GROUP by sd.name
ORDER BY 4;