Utilities for Informix DBA
For UNIX
All utilities tested on 7.23 on HP-UX 10.01 and SunOS 5.5.1
For Windows NT
Tested on 7.30 on Windows NT 4.0
 
  • dbcontime
    Date and time of connection for sessions. You can find this information from
    onstat -g ntt output, but sometimes you need to see some combined output
    of onstat -u, onstat -g ses, onstat -g ntt. Session that retrieving this information
    excluded from output. Note that this version of 'dbcontime' depends on time
    zone and written for USA Eastern time.
  • tbldbspace
    While database tables reside by default in the same dbspace where database
    created, you can place table in any dbspace. And fragmented tables obviously
    reside in many dbspaces. This information can be checked with the dbschema
    utility. Script below just produces simple "table_name dbspace_name" output
    for each table in each database using query to sysmaster database. 
 

#!/bin/ksh

########################################################################
# dbcontime						: Version 1.1
#				by Vardan Aroustamian	: 03/26/98
#
# print session id, username, date and time of connection, ... for each
#	session excluding session that retrieving this info itself
# ======================================================================
#


#set -x

{
dbaccess sysmaster - <<EOT!
select	s.sid sesssion_id, s.username,
	datetime( 1969-12-31 20:00:00 ) year to second +
	connected units second connected,
	pid, hostname[1,13], hex(r.flags) state
from	sysscblst s, sysrstcb r
where	s.address = r.scb and
	bitval(r.flags, '0x80000') = 1 and
	dbinfo('sessionid') != s.sid
order by 3;
EOT!
} 2>/dev/null

## end #################################################################
  
 

#!/bin/ksh

########################################################################
# tbldbspace						: Version 1.3
#				by Vardan Aroustamian	: 07/13/98 09:20
#
# print dbspace for each table in each database
#	excluding sysmaster and sysutils databases and catalog tables
#

#set -x

TMPFILE="/tmp/dblist.vaar"

[ -f $TMPFILE ] && rm $TMPFILE

(
dbaccess sysmaster - <<EOT!
output to $TMPFILE
without headings
select name from sysdatabases where name != "sysmaster" and name != "sysutils";
EOT!
) > /dev/null 2>&1

[[ -f $TMPFILE ]] || { echo "Error: at '$TMPFILE' generation"; exit 1; }

if [ $(sed -e "/^$/d" $TMPFILE | wc -l) -eq 0 ]
then
	echo "There is not user database on instance"
	exit 1
fi

DBLIST=`grep -v "^$" $TMPFILE`

for DB in ${DBLIST}
do
	echo "Database '$DB'"

	(
	dbaccess $DB - <<EOT!
		select	tabname,
			dbinfo('dbspace', partnum) dbspace
		from systables
		where tabid > 99 and tabtype = 'T' and partnum != 0
		union all
		select	tabname,
			dbspace dbspace
		from    systables t, outer sysfragments f
		where   t.tabid > 99 and
			tabtype = 'T' and
			partnum = 0 and
			t.tabid = f.tabid
		order by 1,2;
EOT!
	) 2> /dev/null

	echo "\n\n"
done

rm $TMPFILE

## end #################################################################
1