PERL TOOL
I think that I created a PERL tool that is... pretty cool...
take a copy for yourself. Let me know if it is helpful to
you. If the news post hacks this up... I will try to post it
to IIUG(www.iiug.org).
You will need to have DBI/DBD and perl installed correctly.
David Meyer
Guerilla Software
------------------------------------------------------------------------
#!/usr/local/bin/perl
#
# " I created this program for the Informix World... I hope it is
# helpful.... have fun! "
#
# Note: You will need to have perl and perl DBI installed correctly.
# A program log is kept at '/tmp/infmx_sess.pl'.
#
# ...ALSO... make sure you change the 'dbname' to your database...
# it is at line 102.
#
#
# The program starts out by showing current sessions logged into the
# instance by selecting from the syssessions tables. The user has the
# capability then to select a function to perform. The selections are
# as follows:
#
# (1)kill (2)SQL (3)Refresh (4)Session (5)Disk (6)End
#
# KILL - Prompts the user for the session number to kill and then
# executes an 'onmode -z sess#' unix command.
#
# SQL - Prompts the user for the session number to report and then
# continually executes an onstat -u -g sess #' and parses out
# last and current SQL Statement. This command can only be stopped
# by pressing 'ctrl-c'.
#
# Refresh - Will re-perform the select on the syssessions table. New
# sessions will be displayed.
#
# Session - Will prompt the user for the session number to report and
# then executes the 'onstat -u -g sess <sess#>'.
#
# Disk - Will select from the system tables to report database spaces.
#
# End - End application.
#
#
# David Meyer
# Dec. 1997
#
# Guerilla Software
# dmeyer9@ix.netcom.com
#
#
# Define the package to be used - 'Informix.pm'.
#
use DBD::Informix;
#
# This is the Informix Package being used.
#
#require "/usr/local/DBD-Informix-0.56/Informix.pm";
#
#
# Open the log file in the 'tmp' directory.
#
open (LOGFILE, ">>/tmp/infmx_sess.log") ||
die "Error: can not open log output file";
$gtp_number = 0; # Initialize variable before passed from sp_swapgtr.
#
# Verify that program is passed a parameter.
#
if ($#ARGV == -1)
{
die "usage: infmx_sess.pl <session #> \\n";
exit (0);
}
#
# Foreach ARGV as an array ... setting variable
#
foreach $i(0 .. $#ARGV)
{
if ($i == $#ARGV)
{
# print "\\n";
}
else
{
# print " ";
}
if ($i == 0)
{
$sess_number = $ARGV[$i]; # set as next number to be used.
}
} # End of Foreach Statement
#
# Set database parameters... name , userid, password.
#
$dbname = 'afcplatinum';
#
# Connect to respective database defined by variables
# from above.
#
$dbh = DBI->connect("dbi:Informix:$dbname", '', '',{ AutoCommit=>1,PrintError=>1});
&stmt_fail() unless (defined $dbh);
&get_date(); # Call subroutine to get system date and time.
$time1 = sprintf("%02d%02d%02d",$hour, $min, $sec);
#
# Run process forever...
# or until a session is entered.
#
for ($i=0;;)
{
#
# Allow the user to select the session to monitor.
#
system("clear");
print "TTY USER SID CTR\\n";
print "=============================================\\n";
#
# Get the servername to be used in a select statement.
#
$servername = $ENV{"INFORMIXSERVER"};
$system_table = sprintf("%s\\@%s:%s","sysmaster",$servername,"syssessions");
$ctr = 0;
#
# Find all the sessions currently logged into the
# the syssessions table.
#
$stmt = "SELECT sid, username, tty from $system_table";
&select_only($stmt);
while (($sid, $username, $tty) = $cursor->fetchrow)
{
$ctr++;
$print_line = sprintf("%s %s %d %s", $tty,$username,$sid,$ctr);
print "$print_line \\n";
}
print "\\n";
print "\\n";
print "===================================================\\n";
print "(1)KILL (2)SQL (3)Refresh (4)Session (5)Disk (6)End: ";
$answer = <STDIN>;
if ($answer == 1 )
{
print "Session ID? \\n";
$sess_number = <STDIN>;
system('onmode -z $sess_number');
}
if ($answer == 2 )
{
print "Session ID? \\n";
$sess_number = <STDIN>;
last;
}
if ($answer == 3 )
{
print "Refresh Information...\\n";
}
if ($answer == 4 )
{
print "Session ID? \\n";
$sess_number = <STDIN>;
open (SESS, "nice onstat -u -g ses $sess_number |");
while (<SESS>)
{
print " $_ \\n";
}
print "PRESS ENTER ... \\n";
$more = <STDIN>
}
if ($answer == 5 )
{
#
# clear the screen.
#
system("clear");
$pagesize = 4;
$stmt = "select sysmaster\\@dbcci:syschktab.dbsnum, name, sum(chksize) * $pagesize total_size, sum(nfree) * $pagesize amt_free, trunc((sum(nfree) / sum(chksize)) * 100 ,2) percent_free from sysmaster\\@dbcci:syschktab, sysmaster\\@dbcci:sysdbstab where sysmaster\\@dbcci:syschktab.dbsnum = sysmaster\\@dbcci:sysdbstab.dbsnum group by 1,2 order by 1;";
&select_only($stmt);
while (($frst, $scnd, $thr, $fr, $fv, $sx) = $cursor->fetchrow)
{
$ctr++;
$print_line = sprintf("%s %s %d %d %d %s ", $frst,$scnd,$thr,$fr,$fv,$sx);
print "$print_line \\n";
}
print "PRESS ENTER ... \\n";
$more = <STDIN>
}
if ($answer == 6 )
{
print " \\n";
die "That's all folks!!! \\n";
}
} # End of for loop
#
# Log Initial Date/Time stamp.
#
&begin_log_timestamp;
#
# Run process forever.
#
# NOTE: Press CTRL-C to exit out of process.
#
for ($i=0;;)
{
$begin_parse = 99;
$sql_stmt = "";
#
# Open up a pipe to unix and use the session number passed
# in to execute command.
#
open (INPIPE, "nice onstat -u -g ses $sess_number |");
while (<INPIPE>)
{
#
# Look for
#
if (m/User-created Temp tables :/)
{
$begin_parse = 0;
}
if ($begin_parse == 1)
{
push(@sql_stmt,$_);
}
if (m/Last parsed SQL statement :/)
{
print "@sql