Re: User's conference - handout
Hi,
> dianne.pendleton@autodesk.com asked:
> To anyone who attended the Informix User's Conference and went to Exploring
> the Sysmaster Database, session A7 - I am looking for a copy of the
> handout which included many nifty sqls that I could really use right now.
> They ran out of handouts and when I went to the copy center they didn't
> have any either.
> If anyone knows of a way that I can get this sooner than waiting for
> the cd to come out, please let me know.
>
> Thanks,
Attached is a shell archive with the scripts. A paper I wrote on which
the talk was based on is at my web site: www.advancedatatools.com
You will have to wait for the CD to get the powerpoint slides.
Sorry we ran out of copies.
Regards - Lester
#############################################################################
# Lester Knutsen lester@advancedatatools.com #
# Advanced DataTools Corporation Voice: 703-256-0267 #
# Grant group privileges for Informix databases with DB Privileges #
# Visit our Web page: www.advancedatatools.com #
# Washington Area Informix User Group: www.iiug.org/~waiug/ #
#############################################################################
#-------------------------- cut here --------------------------------------
: "@(#)shar.sh 1.8"
#! /bin/sh
#
# This is a shell archive.
# Remove everything above this line and run sh on the resulting file.
# If this archive is complete, you will see this message at the end:
# "All files extracted"
#
# Created: Fri Jul 18 17:26:46 EDT 1997
# By: Lester Knutsen - Advanced DataTools Corporation
# Email: lester@advancedatatools.com
#
# Files archived in this archive:
# -------------------------------
# README
# blobfree.sql
# buffcach.sql
# chkflist.sql
# chkio.sql
# chkiosum.sql
# chkstatus.sql
# dblist.sql
# dbsfree.sql
# dbsfree72.sql
# dbwho.sql
# getconfig.sql
# keyprofile.sql
# locks.sql
# lockwait.sql
# logstat.sql
# sesprof.sql
# session.sql
# seswait.sql
# syssql.sql
# tabextent.sql
# tabextplan.sql
# tablayout.sql
# tabprof.sql
# txlogcnt.sql
# txlogpos.sql
# vpprof.sql
# vpstat.sql
# dbwho.sh
#
#--------------------
if [ -f README -a "$1" != "-c" ]
then echo shar: README already exists
else
echo 'x - README (2513 characters)'
sed -e 's/^X//' >README <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)README 1.2 Date: 97/07/18
X-- Author: Lester B. Knutsen Email: lester@advancedatatools.com
X-- Advanced DataTools Corporation
X-- Discription: This is a set of SQL scripts that use the sysmaster
X-- database to provide information on the status of your server
X-----------------------------------------------------------------------------
Xblobfree.sql - Displays number of free blob pages in blobspace
Xbuffcach.sql - Displays % of reads and writes from buffers
Xchkflist.sql - Displays free space within a chunk
Xchkio.sql - Displays chunk IO status
Xchkiosum.sql - Displays chunk IO as percent of total IO
Xchkstatus.sql - Displays info and status for a chunk
Xdblist.sql - Displays database list, owner, and logging status
Xdbsfree.sql - Displays free space in all dbspaces like Unix "df -k "
Xdbsfree72.sql - Displays free space in all dbspaces like Unix "df -k " (7.2X)
Xdbwho.sh - Shell Script to list database, user, and workstation
Xdbwho.sql - Displays who is using what database
Xgetconfig.sql - Displays effective configuration paramaters
Xkeyprofile.sql - Displays key server profile/perfomance statatics
Xlocks.sql - Displays locks, users and tables
Xlockwait.sql - Displays only locks with other users waiting on them
Xlogstat.sql - Displays logical log status
Xsesprof.sql - Displays user session profile info
Xsession.sql - Displays short list of user sessions
Xseswait.sql - Displays session wait status
Xsyssql.sql - Displays users SQL statement
Xtabextent.sql - Displays tables, number of extents, and size of table
Xtabextplan.sql - Displays extents and proposed new extent sizes
Xtablayout.sql - Displays tables and extents
Xtabprof.sql - Displays table IO performance
Xtxlogcnt.sql - Displays how many open transactions are in each log
Xtxlogpos.sql - Displays users and position in logical logs
Xvpprof.sql - Displays VP status
Xvpstat.sql - Displays VP status like onstat -g sch
X-------------------------------------------------------------------------------
X Lester Knutsen
X Advanced DataTools Corporation
X 4216 Evergreen Lane, #136
X Annandale, VA 22003 USA
X 703-256-0267
X lester@advancedatatools.com
X www.advancedatatools.com
X-------------------------------------------------------------------------------
X
X
SHAR-EOF
if [ `wc -c <README` -ne 2513 ]
then echo shar: README unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f blobfree.sql -a "$1" != "-c" ]
then echo shar: blobfree.sql already exists
else
echo 'x - blobfree.sql (661 characters)'
sed -e 's/^X//' >blobfree.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)blobfree.sql 1.5 Date: 97/07/18
X-- Author: Lester B. Knutsen Email: lester@advancedatatools.com
X-- Advanced DataTools Corporation
X-- Discription: Display number of free blob pages in blobspace
X-----------------------------------------------------------------------------
X
Xdatabase sysmaster;
X
Xselect
X name dbspace,
X sum(chksize) Size_in_Pages, -- sum of all chuncks size pages
X sum(nfree) Num_free_blob_page -- sum of all chunks free pages
Xfrom sysdbspaces d, syschunks c
Xwhere d.dbsnum = c.dbsnum
Xand d.is_blobspace = 1
Xgroup by 1
Xorder by 1
SHAR-EOF
if [ `wc -c <blobfree.sql` -ne 661 ]
then echo shar: blobfree.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f buffcach.sql -a "$1" != "-c" ]
then echo shar: buffcach.sql already exists
else
echo 'x - buffcach.sql (788 characters)'
sed -e 's/^X//' >buffcach.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)buffcach.sql 1.3 Date: 97/07/18
X-- Author: Lester B. Knutsen Email: lester@advancedatatools.com
X-- Advanced DataTools Corporation
X-- Discription: Displays % of reads and writes from buffers
X-----------------------------------------------------------------------------
X
Xdatabase sysmaster;
X
Xselect dr.value dskreads,
X br.value bufreads,
X round ((( 1 - ( dr.value / br.value )) *100 ), 2) cached
Xfrom sysprofile dr, sysprofile br
Xwhere dr.name = "dskreads"
Xand br.name = "bufreads";
X
Xselect dw.v