Monitor for INFORMIX dbspace utilization

Several months ago I posted a Perl program to process the output of an INFORMIX alarm from the database. The bundle also included a Perl program to directly monitor if the database is online -- If the database crashs, database may not be able to send an alarm. The informix.monitor uses the DBD::Informix module to connect periodically to the database and retrieve the database name. If it can't connect, there's almost always a problem with the database engine. The monitor uses the most excellent "mon" Service Monitor Daemon available at: http://www.kernel.org/software/mon/ The bundle also includes a monitor for the new INFORMIX i.Sell application server. I highly recommend this monitor during development and implementation of the app server. Now, I'd like to release the first hack at a monitor for dbspaces. It is very similar to the freespace.monitor included with the "mon" distribution. The mon.cf configuration file for the dbspace.monitor looks like this: watch dbspaces service dbspace description Monitor freespace in INFORMIX databases interval 15m monitor dbspace.monitor server1:50000 server2:25000 ;; period wd {Sun-Sat} alert snpp.alert 8005551212 alert mail.alert admin@your-domain.com alertevery 1h (Please see the "mon" docs for configuration information) In the example above, if any dbspace in server1 (except the physical and logical log dbspaces -- See the code below) falls below 50MB, the alerts will be processed. If any dbspace in server2 falls below 25MB, the alerts will be processed. It works beautifully -- This is an example output to a pager when I set the limit to 500MB: 157954 free in dbroot on server1, 484874 free in dbtemp1 on server1, 469114 free in dbtemp2 on server1 These monitors have helped me -- I hope they help someone else. ~~~ cut here ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ #!/usr/local/bin/perl -w # # $Id: dbspace.monitor,v 1.1 2000/02/02 21:06:02 clay Exp root $ # Usage: server:KBfree [server:KBfree] # # Copyright (C) 1999, SKECHERS USA, Inc. # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; either version 2 of the License, or # (at your option) any later version. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA # # $Log: dbspace.monitor,v $ # Revision 1.1 2000/02/02 21:06:02 clay # Initial revision # # use DBI; use strict; # Initialize variables my $server = ""; my $min_avail = ""; my $database = "sysmaster"; my $username = ""; my $password = ""; my @failures; # Get the server name from the first parameter foreach (@ARGV) { if (length($_) !=0) { ($server, $min_avail) = split /:/, $_, 2; # Set environment variables $ENV{LD_LIBRARY_PATH} = "/usr/informix/prod/lib:/usr/informix/prod/lib/esql"; $ENV{INFORMIXDIR} = "/usr/informix/prod"; $ENV{INFORMIXSERVER} = "$server"; # Select free dbspace from sysmaster tables -- Assumes a 2K page size my $sel_stmt = "select name, sum(nfree * 2) \\ from sysdbspaces d, syschunks c \\ where d.dbsnum = c.dbsnum \\ group by 1 \\ order by 1"; # Connect to the database my $dbh = DBI->connect($database, $username, $password, 'Informix'); # Prepare the SQL statement (my $sth = $dbh->prepare($sel_stmt)) or die "Failed to prepare '$sel_stmt'\\n"; # Execute the SQL statement $sth->execute; # Check free space while (my ($dbspace, $freekb) = $sth->fetchrow) { $dbspace =~ s/\\s+//; # Note: In our environment, we set up dbspaces for physical and logical logs # By default, we call them dbplog and dbllog, respectively. We don't want # to check free space in these logs if (!defined ($freekb)) { push (@failures, "dbspace error: $!"); next; } elsif (($freekb < $min_avail) && ($dbspace !~ /log/)) { push (@failures, "$freekb free in $dbspace on $server"); } } # Disconnect $dbh->disconnect; } else { print "No server:KBfree specified!\\n"; exit 2; } } if (@failures) { print join (", ", @failures), "\\n"; exit 1; } exit 0; -- Clay Irving <clay@panix.com> When we are planning for posterity, we ought to remember that virtue is not hereditary. - Thomas Paine