In the past couple of 10-Minute Solutions, I have explored Informix's newly released Informix Server Administrator (ISA), a new tool for monitoring and administrating Informix database instances via a Web-based system. My first 10-Minute Solution involved installing and setting up ISA; my second Solution covered operations and applications for ISA. This third and final article on the subject takes you into the world of modifying and customizing ISA to meet your individual needs.
In order to follow this article, you should have ISA installed and operating on a server that you can access. If you don't have a server up and running, go back to the first two articles and install ISA. ISA is written in Perl using CGI scripts. Although you will not need to be a Perl guru in order to understand this article, it will help if you understand a little bit of Perl, as well as the basics of CGI scripts and HTML operations.
ISA is nothing more than a Web site that has been developed to access Informix data structures and utilities. It is housed in a directory named ISA, which is usually installed in your $INFORMIXDIR. The structure looks like this:
Z:\INFORMIX\ISA>dir
Volume in drive Z has no label
Directory of Z:\INFORMIX\ISA
. <DIR> 05-06-00 11:29p .
.. <DIR> 05-06-00 11:29p ..
bin <DIR> 07-02-00 10:46p bin
config txt 9,646 10-27-99 3:50p config.txt
etc <DIR> 05-06-00 11:28p etc
html <DIR> 05-06-00 11:28p html
images <DIR> 07-02-00 6:57p images
license txt 8,148 10-20-99 4:30p license.txt
readme txt 4,279 11-17-99 11:39a readme.txt
security txt 4,172 10-20-99 2:44p security.txt
Uninst isu 56,738 05-06-00 11:29p Uninst.isu
5 file(s) 82,983 bytes
6 dir(s) 220,594,176 bytes free
Much of the meat of ISA is in the "bin" directory. The "html" directory contains the start page for ISA as well as the help system. Each HTML page has its own help. The "etc" directory contains only one file, "isacfg," which is used during ISA configuration to set environmental variables.
Your Web server that controls and runs ISA will run as user "informix." If you need other environmental variables to run other programs, you need to modify the isacfg file to include these variables in your Web server environment. An easy way to find out if you need to do this is to try to run a program from the "Command" option of ISA. For example, I have a program called checkcrm.bat that checks some of the variables in a CRM program that I sell. Figure 1 shows what happens when I run checkcrm.bat from the command menu.
Figure 1: What happens when "isacfg" is not set correctly
It's obvious that checkcrm is not working because the environment variables are still the default Informix variables. There are two ways to fix this: One is simply to give everybody on the server the same variables so that everyone will be able to run everything. The more elegant and more secure solution is to modify the etc/isacfg file to include these variables:
# This file records environment variable information
# for each Informix instance that is managed by
# this onweb server.
#
# This is a sample. Customize it to suit your needs.
#
# The format is just a list of environment variable names and
# values. A section has to begin with INFORMIXSERVER and it ends with
# END on a line by itself. Each INFORMIXSERVER section has to have a
# unique name. You can specify any variables you want here; you should
# probably include at least INFORMIXSERVER, INFORMIXDIR, and
# ONCONFIG. You can also specify INFORMIXSQLHOSTS and anything else
# that ONWeb needs to set for any of the programs it's going to run.
#
# 8.x users: INFORMIXSERVER should either not include the coserver
# number, if your sqlhosts allows that, or it should specify coserver
# 1.
#
# PATH and LD_LIBRARY_PATH (or equiv)
# are set for you based on INFORMIXDIR, so don't worry
# about that. Be sure to set any GLS or POSIX locale
# variables that you require.
#
# Whitespace and comments are fine.
INFORMIXSERVER ol_scsibox
INFORMIXDIR c:\informix
ONCONFIG onconfig.$INFORMIXSERVER
TEMP C:\TEMP
TMP C:\TEMP
DBHOMEDIR c:\s2
DBNAME seicomart
DBSERVER ol_scsibox
CRMCOMMITRATE 1000
CRMSYSTEMTYPE NT
WORKING %DBHOMEDIR%\working
ARCHIVES %DBHOMEDIR%\archives
LOGS %DBHOMEDIR%\logs
TRANSFERS %DBHOMEDIR%\transfers
BIN c:\s2\bin
CRMINFO TRUE
STAGE e:\stage
LOCALBIN c:\s2\localbin
END
Notice the two lines above in red. Everything between the INFORMIXSERVER and the END refers to INFORMIXSERVER = ol_scsibox. You can enter different variables for different servers. Always end each section with END. When the isacfg file is modified to look like the file above, checkcrm.bat will run correctly because the browser now has the correct environment variables.
The "images" directory contains bitmaps and JPEGs of the graphic elements of ISA. Figure 2 shows a screen shot of the first data page (summary.pl) of ISA, after I've made a few customizations:
Figure 2: Summary.pl data page of ISA, after a few customizations
The first thing you'll notice about my new summary page is that I've put in my own company logo, replacing the ISA logo. This is a simple change to make. Just go into the "images" subdirectory and replace logo.bmp with your own BMP or JPEG graphic. You should play around with sizing until it looks right. Note that the width of the left-hand blue bar is governed by the width of your logo. Keep this in mind when you are designing your own page. Those of you with eagle eyes will probably notice that the first menu element, "Custom," is also new (more about that later).
The left-hand menu bar is a two-tier menu. If you click on some of the elements, the bar will change and display sub-options for the menu item, if they are present. You can then click on the sub-option. First, Informix does not recommend that you make changes in the menus in any area but the Custom area of ISA. This is to ensure that future versions of ISA do not wipe out existing customizations. I'm changing it here just to show you how it is done. Each directory containing Perl scripts also has a file called opt.conf.; this is where the left-hand menu items originate. The opt.conf file in the "bin" directory looks like this:
Menu=Custom:custom.pl,Check:check.pl,Configuration:config.pl,
Logs:log.pl,Memory:shm.pl,Message Log:report.pl,Mode:mode.pl,
Performance:status.pl,SQL:sql.pl,Storage:storage.pl,Summary:summary.pl,
Users:user.pl,VPs:vp.pl,XPS:xps.pl,Index by Utility:index.pl
check.pl=Catalogs:catalogs,Data:data,Indexes:indexes,Info:info,Logs:logs,
Reserved:reserved,Spaces:space
config.pl=DSS:ds,Effective:effect,infos:infos,ONCONFIG:onconfig,
SQLHOSTS:sqlhosts,
Replication:replication,DataBlades:datablades
log.pl=Logical:logical,Physical:physical,Onlog:onlog
mode.pl=
report.pl=
shm.pl=Segments:segments,Pools:pools,Resident:res,Nonresident:nonres,
Dictionary:dict
sql.pl=Query:query,Schema:schema
status.pl=AIO:aio,Buffers:buffers,Cache:cache,Locks:locks,MGM:mgm,Mutexes:mux,
Network:net,Partitions:partitions,Profile:profile,Tablespaces:tblspaces,
Threads:threads,Coserver:xps
storage.pl=Spaces:spaces,Extspaces:extspaces,Chunks:chunks,
Dataskip:dataskip,FAQ:faq
In the first line, "Menu=Custom:custom.pl,..." indicates that when the menu item that I added (Custom) is clicked, the custom.pl Perl program is run. The menu heading and program name need to be separated by a colon (:). Menu items must be separated by a comma.
In the lines below that, you will see each of the programs in the first line followed by a colon followed by another word. This second word controls the submenu items in the program. For example, the second item in the menu is:
Check:check.pl,
When the user clicks on the "Check" option, several submenu items will appear. They are controlled by the following line of opt.conf:
check.pl=Catalogs:catalogs,Data:data,Indexes:indexes,
Info:info,Logs:logs,Reserved:reserved,Spaces:space
When the user checks on "Catalogs," check.pl is run and is passed a variable called $Submenu. In this case, it is passed the value "catalogs."
Now, take a look at the relevant parts of the check.pl Perl program:
if (&ifmx_ok) {
if ($Submenu eq "reserved") {
&reserved_actions();
} elsif ($Submenu eq "logs") {
&logs_actions();
} elsif (&shm_ok()) {
if ($Submenu eq "catalogs") {
if ($action eq $check::CATALOGS_ACTION) {
&do_check_catalogs();
} else {
&show_check_catalogs();
}
} elsif ($Submenu eq "indexes") {
&indexes_actions();
} elsif ($Submenu eq "space")
&space_actions();
} elsif ($Submenu eq "data") {
&data_actions();
} elsif ($Submenu eq "info") {
&info_actions();
}
}
I've highlighted in red the section in which we're interested. When check.pl starts to run, it checks the value of $Submenu that is passed in from the menu and, in this case, performs a Perl subroutine called &do_check_catalogs().
As I have said, Informix does not recommend that you work in this section, so from now on I will work in the custom section as recommended by Informix. ISA buries the "Custom" options way down at the bottom of the page. My first impression was to try to change the menu structures to make the "Custom" option easier to get to. Then I realized that we're dealing with a Web browser, and it is easy to change the navigation. I simply added a link deep into the beginning of the "Custom" section and put it on my links bar. There's more than one way to skin a cat.
In Figure 3, look at the third button bar of the browser window and see the new button "ol_scsibox-Custom," which is the link I created to get directly into the Custom options. Across the left, you'll see that the Custom option has three menu elements: Config_parm, MostSimple, and Testing.
Figure 3: The Custom menu page added as a link to my browser window's link bar
All of the Custom applications are in the $INFORMIXDIR/ISA/bin/opt/custom subdirectory. Let's take a look at this directory:
Z:\INFORMIX\ISA\bin\opt\custom>dir
Volume in drive Z has no label
Directory of Z:\INFORMIX\ISA\bin\opt\custom
. <DIR> 07-02-00 9:20p .
.. <DIR> 07-02-00 9:20p ..
custom pl 2,207 07-02-00 7:56p custom.pl
CUSTOM~1 PL 2,207 06-23-00 7:55p custom_orig.pl
joe pl 2,207 06-24-00 11:26p joe.pl
MOSTSI~1 PL 1,294 07-02-00 9:11p mostsimple.pl
OPT~1 CON 154 07-02-00 9:12p opt.conf
queries1 pl 710 06-30-00 9:09p queries1.pl
smi pl 759 07-02-00 8:41p smi.pl
SQLSTA~1 PL 1,296 07-02-00 9:00p sqlstatements.pl
testing pl 991 07-02-00 10:48p testing.pl
9 file(s) 11,825 bytes
2 dir(s) 220,594,176 bytes free
You'll notice that there is an opt.conf file in this directory. This is where the Custom menu options live. Look at opt.conf:
Name=Custom
Version=1.10.UC1
ISAVersion=1.10
Target=custom.pl
Dir=custom
Menu=Config_parm:queries1.pl,MostSimple:mostsimple.pl,Testing:testing.pl
As you can see, there are only three menu items and no sub-menu items. Each menu item takes you to its own Perl script—in this case, queries1.pl, MostSimple.pl, and testing.pl. Figure 4 shows the "MostSimple" menu option.
Figure 4: The "MostSimple" menu option
The MostSimple menu option is exactly that: a very simple page. It includes two elements—a header and a footer. Let's look at the mostsimple.pl script that generates this page:
#!c:/perl/bin/perl -Ic:/perl/lib
# Copyright (C) 1999 Informix Software, Inc. All rights reserved.
# WARNING: don't modify this file yourself. It will be overwritten
# when you upgrade ISA. Modifications you make to opt.conf, and any
# new files that you add to this directory, will be preserved.
use strict;
use lib "../..";
use Cmd;
use UI;
use Page;
use CGI qw(:standard);
use CGI::Carp qw(carpout fatalsToBrowser);
# "Sample" is the title of the page;
# "sample.pl" is the name of this program
# "custom" is the name of our option pack
# Custom is the title of our option pack
&print_normal_start("Sample", "mostsimple.pl", "custom", "Custom");
&print_normal_end($Page::NO_MAGIC);
I've highlighted the last two lines in red. Everything above here is overhead and should be used in all pages. The first red line actually creates the header for the page and generates the necessary HTML. The parameters passed to &print_normal_start really don't come into play unless you move the page outside of the custom directory. When I tried to put "Custom" onto the main menu, I had to play with this line to make the page work. Because this is undocumented, it is safer to do it Informix's way and not have to change the locations of the pieces. The last red line closes out the page and puts the headers at the bottom. You can use this page as a starting point for developing other ISA pages.
Now let's get a little fancier and create a page that actually does something worthwhile. How about retrieving some data from the database? (See Figure 5.)
Figure 5: Retrieving data from the database
This is the Config_parm menu option. I have a table called config_parm in my CRM database that controls the operation of the system and I thought it would be handy to be able to display this table over the Web. This menu option runs the queries1.pl Perl script, which is derived from mostsimple.pl:
#!c:/perl/bin/perl -Ic:/perl/lib
# Copyright (C) 1999 Informix Software, Inc. All rights reserved.
# WARNING: don't modify this file yourself. It will be overwritten
# when you upgrade ISA. Modifications you make to opt.conf, and any
# new files that you add to this directory, will be preserved.
use strict;
use lib "../..";
use Cmd;
use UI;
use Page;
use CGI qw(:standard);
use CGI::Carp qw(carpout fatalsToBrowser);
&print_normal_start("Config_parm", "queries1.pl", "custom", "Custom");
my $dbaccess = &dbaccess('SELECT * from config_parm', 'seicomart');
&show_output('select * from config_parm',
$dbaccess,
'ISA: no output');
&print_normal_end($Page::NO_MAGIC);
The two lines of interest are highlighted in red. The first line does several uniquely-Perl things. The "my" at the beginning indicates a local variable. The $dbaccess and &dbaccess elements are two different constructs. The $dbaccess indicates a variable. The &dbaccess is a subroutine called from one of the libraries that makes up ISA.
The &dbaccess element takes two strings as parameters. The first string is an SQL statement, enclosed in single quotes. The second string is the name of the database against which to run the query. What this command does is create a file handle called $dbaccess that contains the results of the query.
The second red line in the listing above actually outputs the query to the Web page. The first parameter creates the "blurb" line in the gray area underneath "Command Output" on the Web page. It just tells you the command you've executed. The second parameter, $dbaccess, is the file handle created in the first red statement. Finally, the last parameter is outputted if there is no output from the &dbaccess query.
All of the ISA functions are located in the following *.pm files, located in the "ISA/bin" directory:
Volume Z:\INFORMIX\ISA\bin>dir *.pm
in drive Z has no label
Directory of Z:\INFORMIX\ISA\bin
Build pm 170 07-27-99 1:28p Build.pm
Cmd pm 38,938 11-22-99 2:53p Cmd.pm
OSCmd pm 7,880 10-26-99 3:36p OSCmd.pm
Page pm 42,427 07-02-00 6:41p Page.pm
UI pm 15,700 10-20-99 3:56p UI.pm
5 file(s) 105,115 bytes
0 dir(s) 220,594,176 bytes free
The *.pm files are "Perl Module" files and are similar to "include" files or "library" files in other languages. The &dbaccess subroutine is found in Cmd.pm.
I won't go over all of the included subroutines, but here are some of the most useful:
There are many more features and capabilities in the ISA system. You can explore some of them for yourself by looking at the Perl modules (*.pm) files in the "bin" directory as well as by looking at some of the Perl scripts (*.pl) in the same directory.
I believe that ISA will be a very useful and significant product for Informix users and DBAs. There have been other monitoring and DBA tools before—some decent and some laughable—but the thing that sets ISA apart is its openness. If you don't like the way something works, change the code and make it work the way it should. An added benefit of this approach is that we'll soon see more DBAs and other Informix gurus finally taking the time to learn Perl!
