Skip to Content

SMI Query Series - Logical Log Space Used

SMI Query Series - Logical Log Space Used

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 fourth 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_log_used.sql

The purpose of this SMI query is to display the used and total logical log space. The corresponding onstat command is: onstat -l:

SELECT SUM
       (
         size *
         (
            select sh_pagesize from sysshmvals
         )/1024/1024
      ) mb_used_and_total
FROM   syslogs
WHERE uniqid >=
       (
       SELECT min(tx_logbeg)
          FROM  systrans
          WHERE tx_logbeg > 0
       )
OR     is_backed_up = 0
 
UNION ALL
 
SELECT SUM
       (
         size *
         (
            select sh_pagesize from sysshmvals
         )/1024/1024
       )
FROM   syslogs;

Examplary Output:

mb_used_and_total 
 
120.00
400.00

In the above example we have a total configured logical space of about 400 mb. Currently 120 mb of the total log space is either used by ongoing transactions or because certain logs have not been backed up yet. There is about 280 mb of free log space available on the IDS instance. Actually there might be more than 280 mb because the current logical log is considered to be full by this query. You might further adjust the SMI query and select the sum(syslogs.used) column for the current log (syslogs.is_current=1) if you prefer. The available log space for ongoing transactions might be further limited by the LTXHWM/LTXEHWM (Long Transaction Highwater Marks) settings in your onconfig file.

The above query has been tested with IDS 10. It should also run with IDS versions >= 9.30. In IDS <= 9.30 you might also need to check the column syslogs.is_new because newly added logs are not immediately available for use. A Level-0-Backup has to be taken first.

Copyrightblog |