Just a copy from a excellent script to get the top sessions in the informix instance:
#!/usr/bin/perl -w
######################################################################
#
# dbtop
#
# Quick real-time resource monitor for Informix database servers,
# similar in presentation and operation to the Unix 'top' command.
# Execute with -h for explanation of usage.
#
# Author: Chris Kelch
# Version: $Id: dbtop,v 1.5 2007/05/18 15:31:17 ckelc Exp $
# Copyright 2007, TransWorks, Inc.
#
# This software is provided for your personal and/or business use and
# may be distributed. The entire risk arising out of the use or
# performance of this software and documentation remains with you. In
# no event shall TransWorks, Inc. be liable for any damages whatsoever
# (including, without limitation, damages for loss of business
# profits, business interruption, loss of business information, or
# other pecuniary loss) arising out of the use of or inability to use
# the software or documentation, even if TransWorks, Inc. has been
# advised of the possibility of such damages.
#
# For additional information, feel free to contact us at:
#
# TransWorks, Inc.
# 9910 Dupont Circle Dr. E, Ste. 200
# Fort Wayne, IN 46825
#
# Phone: (260) 487-4450 or (800) 435-4691
# Web: http://www.trnswrks.com
# e-mail: tw_info@trnswrks.com
#
######################################################################
use strict;
# Optionally use Curses, when available.
eval { require Curses; };
my $cursesAvail = !$@;
my $ESC = "\033";
# Prototypes.
sub usage();
sub clearScreen();
sub numberedSlice($$);
# Args.
my $refreshSecs = 2;
my @ignoreUsers = ();
my $ignoreOn = 0;
while (@ARGV) {
my $arg = shift @ARGV;
if ($arg eq '-h' || $arg eq '--help') {
print usage();
exit 0;
}
elsif ($arg eq '-r' || $arg eq '--refresh') {
$refreshSecs = shift @ARGV;
}
elsif ($arg eq '-i' || $arg eq '--ignore') {
push @ignoreUsers, split /,/, shift @ARGV;
$ignoreOn = 1;
}
else {
print STDERR "dbtop: error: unknown arg '$arg'\n";
exit 1;
}
}
push @ignoreUsers, 'informix' unless $ignoreOn;
# Make sure environment looks OK.
if (!defined($ENV{'INFORMIXSERVER'})) {
print STDERR "dbtop: error: must set up Informix environment before running\n";
exit 1;
}
# Init Curses and find height and width of screen.
my ($screenHeight, $screenWidth) = (24, 80);
my $cursesInited = 0;
# Info buffers.
my @sesLines = ();
my $sesHeader = ' Session User Act% Locks Reads Writes';
my $sesListTop = 1;
my $selectedLine = 0;
my $statusMsg = '';
my $statusSecs = 0;
# Loop forever or until user presses 'q' or until we get an error.
my $quitting = 0;
my $errMsg = undef;
while (!$quitting && !defined($errMsg)) {
# Gather list of sessions from onstat -u and run some asserts on the output.
my @userLines = qx{onstat -u};
my $userHeaderIndex = 4;
++$userHeaderIndex while $userHeaderIndex < $#userLines && $userLines[$userHeaderIndex] !~ m/^address\s+flags\s+sessid\s+user\s+tty\s+wait\s+tout\s+locks\s+nreads\s+nwrites/;
if ($userHeaderIndex >= $#userLines) {
$errMsg = 'unrecognized onstat -u header; first 10 lines: ' . numberedSlice(\@userLines, 10);
last;
}
my $onstatUFooter = $userLines[$#userLines - 1];
chomp $onstatUFooter;
if ($onstatUFooter !~ m/^ ([0-9]+) active, [0-9]+ total/) {
$errMsg = "unrecognized onstat -u footer: " . $onstatUFooter . "; last 10 lines: " . numberedSlice(\@userLines, -10);
last;
}
if ($1 != @userLines - $userHeaderIndex - 3) {
$errMsg = "number of active users doesn't add up; we found " . (@userLines - $userHeaderIndex - 3) . " users but onstat -u reported $1";
last;
}
# Hash sessions by control block address.
my %sessions = ();
foreach my $ses (@userLines[$userHeaderIndex + 1 .. $#userLines - 2]) {
my ($address, $flags, $sessid, $user, $tty, $wait, $tout, $locks, $nreads, $nwrites) = split /\s+/, $ses;
next if $ignoreOn && grep { $user eq $_ } @ignoreUsers;
$sessions{$address} = {
'flags' => $flags,
'sessid' => $sessid,
'user' => $user,
'tty' => $tty,
'wait' => $wait,
'tout' => $tout,
'locks' => $locks,
'nreads' => $nreads,
'nwrites' => $nwrites,
'cpu' => 0,
};
}
# Now for 2 seconds, add up the thread time for each of the above sessions
# who are active based on the onstat -g act output.
for (my $accumCounter = 0; $accumCounter < $refreshSecs * 10; ++$accumCounter) {
# See if user typed something.
if ($cursesInited) {
my $ch = Curses::getch(); # Curses: see if user has pressed a key.
if ($ch eq 'q' || $ch eq 'Q') {
$quitting = 1;
last;
}
elsif ($ch eq 'h' || $ch eq 'H' || $ch eq '?') {
Curses::endwin();
print usage();
exit 0;
}
elsif ($ch eq 'k' || $ch eq Curses::KEY_UP()) {
if ($selectedLine > 0) {
Curses::addstr($selectedLine + $sesListTop, 0, $sesLines[$selectedLine]);
--$selectedLine;
Curses::attron(Curses::A_REVERSE());
Curses::addstr($selectedLine + $sesListTop, 0, $sesLines[$selectedLine]);
Curses::attroff(Curses::A_REVERSE());
Curses::refresh();
}
}
elsif ($ch eq 'j' || $ch eq Curses::KEY_DOWN()) {
if ($selectedLine + 1 < @sesLines) {
Curses::addstr($selectedLine + $sesListTop, 0, $sesLines[$selectedLine]);
++$selectedLine;
Curses::attron(Curses::A_REVERSE());
Curses::addstr($selectedLine + $sesListTop, 0, $sesLines[$selectedLine]);
Curses::attroff(Curses::A_REVERSE());
Curses::refresh();
}
}
elsif ($ch eq 'i') {
$ignoreOn = !$ignoreOn;
$statusMsg = 'Change to ignore may take ' . ($refreshSecs * 2) . ' secs.';
$statusSecs = 10;
}
# Show status message.
if ($statusSecs > 0) {
Curses::addstr($screenHeight - 1, 0, $statusMsg);
Curses::refresh();
}
}
# Run onstat -g act.
my @onstatLines = qx{onstat -g act};
my $onstatHeaderIndex = 4;
++$onstatHeaderIndex while $onstatHeaderIndex < $#onstatLines && $onstatLines[$onstatHeaderIndex] !~ m/^\s+tid\s+tcb\s+rstcb\s+prty\s+status\s+vp-class\s+name/;
if ($onstatHeaderIndex >= $#onstatLines) {
$errMsg = 'unrecognized onstat -g act header; first 10 lines: ' . numberedSlice(\@onstatLines, 10);
last;
}
# Loop over active threads and match to sessions.
foreach my $thr (@onstatLines) {
my ($junk, $tid, $tcb, $rstcb, $prty, $status, $vpclass, $name) = split /\s+/, $thr;
next unless defined($name) && $name eq 'sqlexec';
if (defined($sessions{$rstcb})) {
++$sessions{$rstcb}->{'cpu'};
}
}
# Sleep a tenth of a second.
select(undef, undef, undef, 0.1);
}
# Sort active sessions by CPU, descending.
my @sortedSessionAddrs = sort {
$sessions{$b}->{'cpu'} <=> $sessions{$a}->{'cpu'}
|| $sessions{$b}->{'nreads'} + $sessions{$b}->{'nwrites'} <=> $sessions{$a}->{'nreads'} + $sessions{$a}->{'nwrites'}
|| $sessions{$b}->{'locks'} <=> $sessions{$a}->{'locks'}
|| $sessions{$b}->{'sessid'} <=> $sessions{$a}->{'sessid'}
} keys %sessions;
# Clear screen and show sorted list of active sessions.
if ($cursesAvail) {
if (!$cursesInited) {
$cursesInited = 1;
Curses::initscr();
Curses::curs_set(0); # make cursor invisible (if supported)
Curses::cbreak(); # let ^C and ^Z work as normal
Curses::keypad(1); # allow parsing of F1 etc. keys
Curses::noecho(); # don't echo characters to the screen
Curses::nodelay(1); # don't block when calling getch
Curses::getmaxyx($screenHeight, $screenWidth);
}
Curses::addstr(0, 0, $sesHeader); # Curses print to screen buffer.
}
else {
clearScreen();
print $sesHeader;
}
my $lineCount = 0;
@sesLines = ();
foreach my $sesAddr (@sortedSessionAddrs) {
my $ses = $sessions{$sesAddr};
my $str = sprintf("\%8d \%-8s\%5.0f\%7d\%10d\%10d",
$ses->{'sessid'},
$ses->{'user'},
$ses->{'cpu'} * 10 / $refreshSecs,
$ses->{'locks'},
$ses->{'nreads'},
$ses->{'nwrites'},
);
$sesLines[$lineCount] = $str;
if ($cursesInited) {
if ($lineCount == $selectedLine) {
Curses::attron(Curses::A_REVERSE());
}
Curses::addstr($lineCount + $sesListTop, 0, $str); # Curses print to screen buffer.
if ($lineCount == $selectedLine) {
Curses::attroff(Curses::A_REVERSE());
}
}
else {
print "\n$str";
}
last if ++$lineCount > $screenHeight - 3;
}
if ($selectedLine + 1 >= @sesLines) {
$selectedLine = @sesLines - 1;
if ($cursesInited) {
Curses::attron(Curses::A_REVERSE());
Curses::addstr($selectedLine + $sesListTop, 0, $sesLines[$selectedLine]);
Curses::attroff(Curses::A_REVERSE());
}
}
if ($cursesInited) {
# Clear after current end of list.
Curses::addstr($lineCount + $sesListTop, 0, '');
Curses::clrtobot();
# Show status message.
if ($statusSecs > 0) {
Curses::addstr($screenHeight - 1, 0, $statusMsg);
$statusSecs -= $refreshSecs;
}
# Draw screen.
Curses::refresh();
}
}
# Done. Clean up.
if ($cursesInited) {
Curses::endwin(); # end curses mode
}
if (defined($errMsg)) {
print STDERR "$errMsg\n";
exit 1;
}
exit 0;
sub usage() {
return < (also -r) : number of seconds between refreshes
(default 2)
--ignore [,...] (also -i) : ignore listed user(s)
The following keys are active while running if Curses is installed,
otherwise just hit CTRL+C to stop:
q : quit
h : show help then quit
k or up-arrow : select previous session
j or down-arrow : select next session
i : do/don't ignore users listed with --ignore (or informix)
EOH
}
# Pass negative number for $numLines if you want to show a chunk from the end
# of $arrayRef, otherwise it will show from the beginning.
sub numberedSlice($$) {
my $arrayRef = shift;
my $numLines = shift;
my ($start, $stop);
if ($numLines < 0) {
$stop = @$arrayRef - 1;
$start = $stop + $numLines + 1;
}
elsif ($numLines > 0) {
$start = 0;
$stop = $numLines - 1;
}
$start = 0 if $start < 0;
$stop = @$arrayRef - 1 if $stop >= @$arrayRef;
my $buf = '';
while ($start <= $stop) {
$buf .= '; ' if length $buf;
$buf .= $start . '. ' . $arrayRef->[$start];
++$start;
}
return $buf;
}
# Clear the screen (when Curses was not available).
sub clearScreen() {
print $ESC . '[H';
print $ESC . '[2J';
}
0;