Web/CGI Scripts to Monitor your Informix Server

 
Lester Knutsen
Advanced DataTools Corporation
Email: lester@advancedatatools.com
www.advancedatatools.com

This article will take a look at scripts that may be used to monitor your Informix server through a web browser. With the widespread use of the Web and Intranets, its helpful to develop tools to use on the web, that will make your job as a DBA easier. This is an introduction to developing web based monitoring scripts. Figure 1, shows a web menu of some of the DBA tools that we will explore in this article.

Figure 1. Web menu or DBA Tools.

The first selection runs "onstat -p" and displays the output in your web browser. The second selection displays another web page with options to onstat. This script was discussed last year in an article that is available at the user group archives in the July 97 issue at www.iiug.org/~waiug/.

The next eight selections are all CGI scripts. CGI scripts can be written in many programming languages. These are written using SQL and a UNIX shell. The scripts select data from the sysmaster database. This article will also focus on how to write these scripts. All the scripts and examples are available for downloading. To download a UNIX tar file containing these examples click on the following: DOWNLOAD EXAMPLES

Establish a CGI Directory

First, lets take a look at the basics. In order for a CGI script to run, a directory on the web server must be established that will contain the scripts. To create this directory, run your web server administration tool and use the option to create a CGI directory. Figure 2, shows this option on Netscape's FastTrack Administration Server. CGI scripts can be a security risk if not managed carefully. This is why most web servers require that a directory must be configured to contain any executable CGI scripts.

Figure 2. Creating a CGI directory using the Netscape Admin Tool

One of the web servers in our office internal network is called merlin. This creates a CGI directory on my web server called dbatools. The actual directory is /u2/ns-home/docs/dbatools. However, in a web browser, you would never use the actual directory name. To access the scripts in this directory I would use the address http://merlin/dbatools/.

Building the Menu Web Page

The menu web page is simple html. Figure 3, contains a listing of the html code. The purpose of this page is to provide your users with one place to find and run the scripts about your Informix server. This code generates the menu you see in Figure 1. Basically, each menu line has some text and a reference to the CGI script to execute when the user clicks on the highlighted text.

<A HREF="/dbatools/wdbstat.sh">Informix Server Status and Profile (onstat -p) </A>

e.g This is the program to run, and this is the highlighted text.

Figure 3. Menu HTML code - dbatools.html



<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
   <TITLE>Monitor your Informix Database Server</TITLE>
   <META NAME="Author" CONTENT="Lester Knutsen">
   <META NAME="GENERATOR" CONTENT="Mozilla/3.0Gold (X11; I; SunOS 5.5 sun4m) [Netscape]">
   <META NAME="Description" CONTENT="Web and CGI Scripts to Monitor your Informix Database
Server">
</HEAD>
<BODY TEXT="#000000" BGCOLOR="#F0FFF0" LINK="#FF0000" VLINK="#800080" ALINK="#0000FF">

<H1 ALIGN=CENTER><B><FONT SIZE=+2>Web/CGI Scripts to Monitor your INFORMIX
Database Server </FONT></B></H1>

<P><B>Informix Server Status</B> </P>
<UL>
<LI><A HREF="/dbatools/wdbstat.sh">Informix Server Status and Profile (onstat -p) 
</A></LI>

<LI><A HREF="/onstat.html">Interface to Onstat (Select List for onstat options) 
</A></LI>
</UL>

<P><B>User Status</B> </P>
<UL>
<LI><A HREF="/dbatools/wdbwho.sh">Server Users - display who is using the server
</A></LI>
<LI><A HREF="/dbatools/wsession.sh">Session Profiles - display status of sessions on the server
</A></LI>
</UL>

<P><B>DBspace and Chunk Status</B> </P>
<UL>
<LI><A HREF="/dbatools/wdbfree.sh">Free Dbspace - display how much spaces is free 
</A></LI>
<LI><A HREF="/dbatools/wchkfree.sh">Chunk Free List - display where free space is located by chunk
</A></LI>
<LI><A HREF="/dbatools/wchkio.sh">Chunk I/O - display which chunks have the most page I/O 
</A></LI>
</UL>

<P><B>Database and Table Status</B> </P>
<UL>
<LI><A HREF="/dbatools/wtabext.sh">Table Extents - display which tables have the most
extents</A></LI>
<LI><A HREF="/dbatools/wtabio.sh">Table I/O - display which tables have the most page I/O
</A></LI>
<LI><A HREF="/dbatools/wdbinfo.sh">DB Info - display database schema </A></LI>
</UL>

<UL>
<CENTER><P>
<HR SIZE=4 WIDTH="100%">
by Lester Knutsen<BR>
Advanced DataTools Corporation <BR>
<I>Email: <A HREF="mailto:lester@advancedatatools.com">lester@advancedatatools.com<BR>
</A>Web:<A HREF="http://www.advancedatatools.com">www.advancedatatools.com</A></I></B>
<HR SIZE=4 WIDTH="100%">
</P></CENTER>
</UL>

</BODY>
</HTML>


CGI Script to Execute the Onstat -p Command

The first option runs a CGI script (wdbstat.sh) that runs "onstat -p" and displays the output in a web page. This is a very simple script and illustrates how this works. Figure 4 is a code listing. The script has two key parts. First it must setup the Informix environment, because most web servers are run as the user "nobody" without the proper environment to run SQL and database programs. The four variables that must be set for all these programs are INFORMIXSERVER, INFORMIXDIR, PATH and ONCONFIG. Then it must display to standard output the HTML tags so the web server can display the returning text in a web browser correctly. This is done with the following lines:
 

        echo Content-type: text/html
        echo
        echo "<TITLE>DB Server Status</TITLE>
        <BODY>
        <H1>Informix Server Status for: $INFORMIXSERVER</H1>
        <PRE>
        "
The echo statements tell the web browser that this is html text and to give the output a title, and a header. The tag "<PRE>" tells the browser that the following text is pre-formatted. The text that follows is the output of "onstat -p" After the output of "onstat -p" are the html tags to tell the browser that this is the end of the text.

Figure 4. CGI script to execute "onstat -p"


#!/bin/sh
#############################################################################
# Module:       @(#)wdbstat.sh 1.1     Date: 98/06/30
# Description: Generate Web Page to display the output of onstat -p
#############################################################################
# Set-up the Informix environment
#############################################################################
## Set the location of Informix Programs
INFORMIXDIR=/export/home/informix724; export INFORMIXDIR

## Add the Informix Programs to your PATH
PATH=$INFORMIXDIR/bin:$PATH:; export PATH

## Set the Default Database Server
INFORMIXSERVER=blmdw; export INFORMIXSERVER

## Set the Informix Configuration File
ONCONFIG=onconfig.blmdw; export ONCONFIG

#############################################################################
# Script to generate web page
#############################################################################

echo Content-type: text/html
echo
echo "<TITLE>DB Server Status</TITLE>
<BODY>
<H1>Informix Server Status for: $INFORMIXSERVER</H1>
<PRE>
"
onstat -p
echo "</PRE>
</BODY>"
#############################################################################


Figure 5. Example Output of wdbstat.sh

CGI scripts to execute SQL commands

Embedding SQL commands in shell scripts is a simple way to get CGI scripts that display database information. All of the other selections on the menu run SQL commands using the sysmaster database. (Please see my article on "Exploring the Sysmaster Database" at the WAIUG web site or at my web site for the complete SQL commands") . As an example we will take the script to display how much space is free in each dbspace. This runs an SQL statement against the sysmaster database and calculates the free space in each dbspace. Figure 6 contains sample output.

Figure 6. Example output from wdbfree.sh

Figure 7 lists the SQL statements and shell script to produce this output. SQL commands are embedded in a shell script by redirecting standard input to "dbaccess" from within the shell script. When dbaccess starts, it expects a database name and SQL script name as its arguments, or it will display its menus and prompt you for them. A dash " -" indicates that the database name or commands will come from standard input. The "<<" indicates to the shell that standard input is redirected, and that everything between the two statements "EOF" is to be passed to dbaccess as standard input. This is called a "here document" in shell scripts. The program dbaccess treats these lines as if you had typed them in from the keyboard. This is like typing "dbaccess sysmaster - <filename" where filename is a file with the SQL commands. You do not have to use the words EOF, but you do need two identical words to mark the beginning and end of input redirection. Running this script will start dbaccess, open the sysmaster database and run the query to show how much free space is available.

All of the other items on the menu are run the same way. The code may be downloaded from my web site and the user group web site at the end of the month. Using these examples you can also develop other scripts to monitor your Informix database server.

Figure 7. Wdbfree.sh SQL code


#!/bin/sh
#############################################################################
# Module:       @(#)wdbfree.sh 1.1     Date: 98/06/30
# Author:       Lester B. Knutsen              email: lester@access.digex.net
#               Advanced DataTools Corporation
# Description:  SQL to create Web Page of Free DB Space
#############################################################################

#############################################################################
# Set-up the Informix environment
#############################################################################

## Set the location of Informix Programs
INFORMIXDIR=/export/home/informix724
export INFORMIXDIR

## Add the Informix Programs to your PATH
PATH=$INFORMIXDIR/bin:$PATH:
export PATH

## Set the Default Database Server
INFORMIXSERVER=blmdw
export INFORMIXSERVER

## Set the Informix Configuration File
ONCONFIG=onconfig.blmdw
export ONCONFIG

#############################################################################
# Script to generate web page
#############################################################################

echo Content-type: text/html
echo
echo "<TITLE>Dbspace Free Space </TITLE>
<BODY>
<H1>Informix Server Free Space for: $INFORMIXSERVER</H1>
<PRE>
"
echo "Generating report..."
dbaccess sysmaster - 2>/dev/null <<EOF
set isolation dirty read;

select    name[1,8] dbspace,       -- name truncated to fit on one line
          sum(chksize) Pages_size, -- sum of all chunks size pages
          sum(chksize) - sum(nfree) Pages_used,
          sum(nfree) Pages_free,   -- sum of all chunks free pages
          round ((sum(nfree)) / (sum(chksize)) * 100, 2) percent_free
from      sysdbspaces d, syschunks c
where     d.dbsnum = c.dbsnum
group by 1
order by 1;

EOF

echo "</PRE>
</BODY>"

 
Lester Knutsen
Advanced DataTools Corporation
Email: lester@advancedatatools.com
www.advancedatatools.com