:	"@(#)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.value dskwrites,
X	bw.value bufwrites,
X	round ((( 1 - ( dw.value / bw.value )) *100 ), 2) cached
Xfrom sysprofile dw, sysprofile bw
Xwhere dw.name = "dskwrites"
Xand   bw.name = "bufwrites"
SHAR-EOF
if [ `wc -c <buffcach.sql` -ne 788 ]
then echo shar: buffcach.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f chkflist.sql -a "$1" != "-c" ]
then echo shar: chkflist.sql already exists
else
echo 'x - chkflist.sql (756 characters)'
sed -e 's/^X//' >chkflist.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)chkflist.sql	1.4     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	Displays free space within a chunk
X-----------------------------------------------------------------------------
X
Xdatabase sysmaster;
X
Xselect
X     name dbspace,  -- dbspace name 
X     f.chknum,      -- chunk number
X     f.extnum,      -- extent number of free space
X     f.start,       -- starting address of free space
X     f.leng free_pages   -- length of free space
Xfrom      sysdbspaces d, syschunks c, syschfree f
Xwhere d.dbsnum = c.dbsnum
Xand   c.chknum = f.chknum
Xorder by dbspace, chknum, extnum
SHAR-EOF
if [ `wc -c <chkflist.sql` -ne 756 ]
then echo shar: chkflist.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f chkio.sql -a "$1" != "-c" ]
then echo shar: chkio.sql already exists
else
echo 'x - chkio.sql (829 characters)'
sed -e 's/^X//' >chkio.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)chkio.sql	1.4     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	Displays chunk IO status
X-----------------------------------------------------------------------------
X
Xdatabase sysmaster;
X
Xselect
X     name[1,16] dbspace, -- truncated to fit 80 char screen line
X     chknum,
X     "P" type,		-- Primary 
X     reads,
X     writes,
X     pagesread,
X     pageswritten
Xfrom syschktab c, sysdbstab d
Xwhere     c.dbsnum = d.dbsnum
Xunion all
Xselect
X     name[1,16]     dbspace,
X     chknum,
X     "M"    type,	-- Mirror
X     reads,
X     writes,
X     pagesread,
X     pageswritten
Xfrom sysmchktab c, sysdbstab d
Xwhere     c.dbsnum = d.dbsnum
Xorder by 1,2,3;
SHAR-EOF
if [ `wc -c <chkio.sql` -ne 829 ]
then echo shar: chkio.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f chkiosum.sql -a "$1" != "-c" ]
then echo shar: chkiosum.sql already exists
else
echo 'x - chkiosum.sql (1547 characters)'
sed -e 's/^X//' >chkiosum.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)chkiosum.sql	1.4     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	Displays chunk IO percent of total IO ordered by chunk
X--		with highest percent of reads
X-----------------------------------------------------------------------------
X
Xdatabase sysmaster;
X
X-- Collect chuck IO stats into temp table A
Xselect
X     name dbspace,
X     chknum,
X     "P" chktype,
X     reads,
X     writes,
X     pagesread,
X     pageswritten
Xfrom syschktab c, sysdbstab d
Xwhere     c.dbsnum = d.dbsnum
Xunion all
Xselect
X     name dbspace,
X     chknum,
X     "M" chktype,
X     reads,
X     writes,
X     pagesread,
X     pageswritten
Xfrom sysmchktab c, sysdbstab d
Xwhere     c.dbsnum = d.dbsnum
Xinto temp A;
X
X-- Collect total IO stats into temp table B
Xselect
X     sum(reads) total_reads,
X     sum(writes) total_writes,
X     sum(pagesread) total_pgreads,
X     sum(pageswritten) total_pgwrites
Xfrom A
Xinto temp B;
X
X-- Report showing each chunks percent of total IO
Xselect
X     dbspace,
X     chknum,
X     chktype,
X     reads,
X     writes,
X     pagesread,
X     pageswritten,
X     round((reads/total_reads) *100, 2) percent_reads,
X     round((writes/total_writes) *100, 2) percent_writes,
X     round((pagesread/total_pgreads) *100, 2) percent_pg_reads,
X     round((pageswritten/total_pgwrites) *100, 2) percent_pg_writes
Xfrom A, B
Xorder by percent_pg_reads desc ; -- order by percentof total reads
SHAR-EOF
if [ `wc -c <chkiosum.sql` -ne 1547 ]
then echo shar: chkiosum.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f chkstatus.sql -a "$1" != "-c" ]
then echo shar: chkstatus.sql already exists
else
echo 'x - chkstatus.sql (1439 characters)'
sed -e 's/^X//' >chkstatus.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)chkstatus.sql	1.4     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	Displays info and status for a chunk
X-----------------------------------------------------------------------------
X
Xdatabase sysmaster;
X
Xselect
X     name dbspace,       -- dbspace name
X     is_mirrored,        -- dbspace is mirrored 1=Yes 0=No
X     is_blobspace,       -- dbspace is blobspace 1=Yes 0=No
X     is_temp,            -- dbspace is temp 1=Yes 0=No
X     chknum chunknum,    -- chuck number
X     fname  device,      -- dev path
X     offset dev_offset,  -- dev offset
X     is_offline,         -- Offline 1=Yes 0=No
X     is_recovering,      -- Recovering 1=Yes 0=No
X     is_blobchunk,       -- Blobspace 1=Yes 0=No
X     is_inconsistent,    -- Inconsistent 1=Yes 0=No
X     chksize Pages_size, -- chuck size in pages
X     (chksize - nfree) Pages_used, -- chunk pages used
X     nfree Pages_free,   -- chunk free pages
X     round ((nfree / chksize) * 100, 2) percent_free, -- free
X     mfname mirror_device,    -- mirror dev path
X     moffset mirror_offset,   -- mirror dev offset
X     mis_offline ,       -- mirror offline 1=Yes 0=No
X     mis_recovering      -- mirror recovering  1=Yes 0=No
Xfrom      sysdbspaces d, syschunks c
Xwhere d.dbsnum = c.dbsnum
Xorder by dbspace, chunknum
SHAR-EOF
if [ `wc -c <chkstatus.sql` -ne 1439 ]
then echo shar: chkstatus.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f dblist.sql -a "$1" != "-c" ]
then echo shar: dblist.sql already exists
else
echo 'x - dblist.sql (568 characters)'
sed -e 's/^X//' >dblist.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)dblist.sql	1.4     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	Displays database list,owner and logging status
X-----------------------------------------------------------------------------
X
Xdatabase sysmaster;
X
Xselect
X        dbinfo("DBSPACE",partnum) dbspace,
X        name database,
X        owner,
X        is_logging,
X        is_buff_log
Xfrom sysdatabases
Xorder by dbspace, name;
SHAR-EOF
if [ `wc -c <dblist.sql` -ne 568 ]
then echo shar: dblist.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f dbsfree.sql -a "$1" != "-c" ]
then echo shar: dbsfree.sql already exists
else
echo 'x - dbsfree.sql (1014 characters)'
sed -e 's/^X//' >dbsfree.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)dbsfree.sql	1.4     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	Displays free space in all dbspaces like Unix "df -k " command
X-----------------------------------------------------------------------------
X-- Note:On some versions (e.g 7.3)  need to use the 7.2 version of
X--	this script to correctly display the truncated dbspace name.
X--	If the dbspace name is blank - use the 72 version.
X
Xdatabase sysmaster;
X
Xselect    name[1,8] dbspace,       -- name truncated to fit on one line
X          sum(chksize) Pages_size, -- sum of all chuncks size pages
X          sum(chksize) - sum(nfree) Pages_used,
X          sum(nfree) Pages_free,   -- sum of all chunks free pages
X          round ((sum(nfree)) / (sum(chksize)) * 100, 2) percent_free
Xfrom      sysdbspaces d, syschunks c
Xwhere     d.dbsnum = c.dbsnum
Xgroup by 1
Xorder by 1;
SHAR-EOF
if [ `wc -c <dbsfree.sql` -ne 1014 ]
then echo shar: dbsfree.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f dbsfree72.sql -a "$1" != "-c" ]
then echo shar: dbsfree72.sql already exists
else
echo 'x - dbsfree72.sql (1020 characters)'
sed -e 's/^X//' >dbsfree72.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)dbsfree72.sql	1.5     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	display free dbspace like Unix "df -k " command
X-----------------------------------------------------------------------------
X-- Note: This is the 7.2 vesrion of this script modified to correctly
X--      correctly display the truncated dbspace name
X
Xdatabase sysmaster;
X
Xselect    d.dbsnum,
X	name dbspace,       -- name truncated to fit on one line
X	sum(chksize) Pages_size, -- sum of all chuncks size pages
X	sum(chksize) - sum(nfree) Pages_used,
X	sum(nfree) Pages_free,   -- sum of all chunks free pages
X	round ((sum(nfree)) / (sum(chksize)) * 100, 2) percent_free
Xfrom	sysdbspaces d, syschunks c
Xwhere	d.dbsnum = c.dbsnum
Xand 	d.is_blobspace = 0	
Xgroup by 1, 2
Xorder by 1
Xinto temp A;
X
Xselect 	dbspace[1,8], pages_size, pages_used, pages_free, percent_free
Xfrom A;
SHAR-EOF
if [ `wc -c <dbsfree72.sql` -ne 1020 ]
then echo shar: dbsfree72.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f dbwho.sql -a "$1" != "-c" ]
then echo shar: dbwho.sql already exists
else
echo 'x - dbwho.sql (701 characters)'
sed -e 's/^X//' >dbwho.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)dbwho.sql	1.4     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	 Displays who is using what database
X-----------------------------------------------------------------------------
X
Xdatabase sysmaster;
X
Xselect
X        sysdatabases.name database,
X        syssessions.username,
X        syssessions.hostname,
X        syslocks.owner sid
Xfrom  syslocks, sysdatabases , outer syssessions
Xwhere syslocks.rowidlk = sysdatabases.rowid
Xand   syslocks.tabname = "sysdatabases"
Xand   syslocks.owner = syssessions.sid
Xorder by 1;
X
SHAR-EOF
if [ `wc -c <dbwho.sql` -ne 701 ]
then echo shar: dbwho.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f getconfig.sql -a "$1" != "-c" ]
then echo shar: getconfig.sql already exists
else
echo 'x - getconfig.sql (477 characters)'
sed -e 's/^X//' >getconfig.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)getconfig.sql	1.3     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	displays effective configuration paramaters
X-----------------------------------------------------------------------------
X
Xdatabase sysmaster;
X
Xselect 	cf_name parameter, 
X	cf_effective[1,58] effective_value
Xfrom 	sysconfig
SHAR-EOF
if [ `wc -c <getconfig.sql` -ne 477 ]
then echo shar: getconfig.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f keyprofile.sql -a "$1" != "-c" ]
then echo shar: keyprofile.sql already exists
else
echo 'x - keyprofile.sql (602 characters)'
sed -e 's/^X//' >keyprofile.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)keyprofile.sql	1.2     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	Displays key server profile/perfomance statatics
X-----------------------------------------------------------------------------
X
Xdatabase sysmaster;
X
Xselect * from sysprofile
Xwhere name in ( 
X	"ovlock", 
X	"ovuser", 
X	"ovtrans", 
X	"latchwts", 
X	"buffwts", 
X	"lockwts",
X	"ckptwts",
X	"deadlks", 
X	"lktouts",
X	"fgwrites",
X	"lruwrites",
X	"chunkwrites" )
SHAR-EOF
if [ `wc -c <keyprofile.sql` -ne 602 ]
then echo shar: keyprofile.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f locks.sql -a "$1" != "-c" ]
then echo shar: locks.sql already exists
else
echo 'x - locks.sql (1249 characters)'
sed -e 's/^X//' >locks.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)locks.sql	1.3     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	Displays locks, users and tables using the base tables
X--	used to create the view syslocks.  This script was tested with
X--	OnLine 7.3. It may not work in all versions because the base
X--	tables may change with versions.
X-----------------------------------------------------------------------------
X
Xdatabase sysmaster;
X
Xselect 	dbsname,
X	b.tabname,
X	rowidr,
X	keynum,
X	e.txt	type,
X	d.sid 	owner,
X	g.username ownername,
X	f.sid	waiter,
X	h.username waitname
Xfrom 	syslcktab a,
X	systabnames b,
X	systxptab c,
X	sysrstcb d,
X	sysscblst g,
X	flags_text e,
X	outer ( sysrstcb f , sysscblst h  )
Xwhere 	a.partnum = b.partnum
Xand 	a.owner = c.address
Xand 	c.owner = d.address
Xand 	a.wtlist = f.address
Xand	d.sid = g.sid
Xand 	e.tabname = 'syslcktab'
Xand 	e.flags = a.type
Xand	f.sid = h.sid
Xinto temp A;
X
X-- Some fields are commented out to fit on an 80 column display
Xselect 	dbsname,
X	tabname,
X	rowidr,
X	-- keynum,
X	type[1,4],
X	owner,
X	ownername 
X	-- waiter,
X	-- waitname
Xfrom A
Xorder by dbsname, tabname, owner ;
SHAR-EOF
if [ `wc -c <locks.sql` -ne 1249 ]
then echo shar: locks.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f lockwait.sql -a "$1" != "-c" ]
then echo shar: lockwait.sql already exists
else
echo 'x - lockwait.sql (1159 characters)'
sed -e 's/^X//' >lockwait.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)lockwait.sql	1.2     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	Displays Only locks with other users waiting on them.
X-- 	This script uses the base tables used to create the view syslocks.
X--  	This script was tested with OnLine 7.3. It may not work in all 
X--	versions because the base tables may change with versions.
X-----------------------------------------------------------------------------
X
Xdatabase sysmaster;
X
Xselect 	dbsname, 
X	b.tabname, 
X	rowidr, 
X	keynum, 
X	e.txt	type, 
X	d.sid 	owner, 
X	g.username ownername,
X	f.sid	waiter,
X	h.username waitname
Xfrom 	syslcktab a, 
X	systabnames b, 
X	systxptab c, 
X	sysrstcb d,
X	sysscblst g,
X	flags_text e, 
X	sysrstcb f , sysscblst h  
Xwhere 	a.partnum = b.partnum
Xand 	a.owner = c.address
Xand 	c.owner = d.address
Xand 	a.wtlist = f.address
Xand	d.sid = g.sid
Xand 	e.tabname = 'syslcktab'
Xand 	e.flags = a.type
Xand	f.sid = h.sid
Xinto temp A;
X
Xselect 	dbsname, 
X	tabname, 
X	type[1,4], 
X	owner, 
X	ownername ,
X	waitname
Xfrom A;
SHAR-EOF
if [ `wc -c <lockwait.sql` -ne 1159 ]
then echo shar: lockwait.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f logstat.sql -a "$1" != "-c" ]
then echo shar: logstat.sql already exists
else
echo 'x - logstat.sql (1069 characters)'
sed -e 's/^X//' >logstat.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)logstat.sql	1.2     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	Displays logical log status. This script is based on an
X--	undocumented sysmaster table systrans and may not work in all versions.
X--	It was tested with 7.23.  
X--	This script shows how many open transactions are in each log.
X-----------------------------------------------------------------------------
X
Xdatabase sysmaster;
X
Xselect 	tx_logbeg,
X	tx_loguniq
Xfrom 	systrans
Xinto temp b;
X
Xselect tx_logbeg, count(*) cnt
Xfrom B
Xwhere tx_logbeg > 0
Xgroup by tx_logbeg
Xinto temp C;
X
Xselect tx_loguniq, count(*) cnt
Xfrom B
Xwhere tx_loguniq > 0
Xgroup by tx_loguniq
Xinto temp D;
X
Xselect
X	-- number,
X	uniqid,
X	-- size,
X	-- used,
X	-- is_used,
X	-- is_current,
X	is_backed_up,
X	is_archived,
X	c.cnt	tx_beg_cnt,
X	d.cnt	tx_curr_cnt
Xfrom 	syslogs, outer c, outer D
Xwhere 	uniqid = c.tx_logbeg
Xand 	uniqid = d.tx_loguniq
Xorder by uniqid
SHAR-EOF
if [ `wc -c <logstat.sql` -ne 1069 ]
then echo shar: logstat.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f sesprof.sql -a "$1" != "-c" ]
then echo shar: sesprof.sql already exists
else
echo 'x - sesprof.sql (943 characters)'
sed -e 's/^X//' >sesprof.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)sesprof.sql	1.2     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	Displays user session profile info.
X-----------------------------------------------------------------------------
X
Xdatabase sysmaster;
X
X-- Most of the following columns are commented out so this will
X-- display on a 80 column screen. 
X
Xselect 	username,
X	syssesprof.sid,
X	lockreqs,
X	-- locksheld,
X	-- lockwts,
X	-- deadlks,
X	-- lktouts,
X	-- logrecs,
X	-- isreads,
X	-- iswrites,
X	-- isrewrites,
X	-- isdeletes,
X	-- iscommits,
X	-- isrollbacks,
X	-- longtxs,
X	bufreads,
X	bufwrites
X	-- seqscans,
X	-- pagreads,
X	-- pagwrites,
X	-- total_sorts,
X	-- dsksorts,
X	-- max_sortdiskspace,
X	-- logspused,
X	-- maxlogsp
Xfrom 	syssesprof, syssessions
Xwhere 	syssesprof.sid = syssessions.sid
Xorder by bufreads desc
SHAR-EOF
if [ `wc -c <sesprof.sql` -ne 943 ]
then echo shar: sesprof.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f session.sql -a "$1" != "-c" ]
then echo shar: session.sql already exists
else
echo 'x - session.sql (476 characters)'
sed -e 's/^X//' >session.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)session.sql	1.2     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	Displays short list of user sessions
X-----------------------------------------------------------------------------
X
Xdatabase sysmaster;
X
Xselect 	sid,
X	username,
X	pid,
X	hostname,
X	l2date(connected) startdate
Xfrom 	syssessions
SHAR-EOF
if [ `wc -c <session.sql` -ne 476 ]
then echo shar: session.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f seswait.sql -a "$1" != "-c" ]
then echo shar: seswait.sql already exists
else
echo 'x - seswait.sql (680 characters)'
sed -e 's/^X//' >seswait.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)seswait.sql	1.2     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	Displays session status 
X-----------------------------------------------------------------------------
X
Xdatabase sysmaster;
X
X-- Some of the fields are commented out to display on a 80 column screen
Xselect
X	-- sid,
X	username,
X	-- pid,
X	-- hostname,
X	-- tty,
X	-- l2date(connected),
X	is_wlatch,
X	is_wlock,
X	is_wbuff,
X	is_wckpt,
X	-- is_wlogbuf,
X	-- is_wtrans,
X	-- is_monitor,
X	is_incrit
X	-- state
Xfrom 	syssessions
Xorder by username
SHAR-EOF
if [ `wc -c <seswait.sql` -ne 680 ]
then echo shar: seswait.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f syssql.sql -a "$1" != "-c" ]
then echo shar: syssql.sql already exists
else
echo 'x - syssql.sql (534 characters)'
sed -e 's/^X//' >syssql.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)syssql.sql	1.2     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	Displays users SQL statement
X-----------------------------------------------------------------------------
X
Xdatabase sysmaster;
X
Xselect 	username,
X	sqx_sessionid,
X	sqx_conbno,
X	sqx_sqlstatement
Xfrom syssqexplain, sysscblst
Xwhere 	sqx_sessionid = sid
Xorder by sqx_sessionid,sqx_conbno
SHAR-EOF
if [ `wc -c <syssql.sql` -ne 534 ]
then echo shar: syssql.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f tabextent.sql -a "$1" != "-c" ]
then echo shar: tabextent.sql already exists
else
echo 'x - tabextent.sql (602 characters)'
sed -e 's/^X//' >tabextent.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)tabextent.sql	1.4     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	Displays tables, number of extents and size of table.
X-----------------------------------------------------------------------------
X
Xdatabase sysmaster;
X
Xselect  dbsname,
X        tabname,
X        count(*) num_of_extents,
X        sum( pe_size ) total_size
Xfrom systabnames, sysptnext
Xwhere partnum = pe_partnum
Xgroup by 1, 2
Xorder by 3 desc, 4 desc;
SHAR-EOF
if [ `wc -c <tabextent.sql` -ne 602 ]
then echo shar: tabextent.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f tabextplan.sql -a "$1" != "-c" ]
then echo shar: tabextplan.sql already exists
else
echo 'x - tabextplan.sql (1132 characters)'
sed -e 's/^X//' >tabextplan.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)tabextplan.sql	1.4     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	Display extents and proposed new extent sizes
X-----------------------------------------------------------------------------
X-- NOTE: if you page size is 4096 change the "* 2 { Your systems page " to 4
X-- 	 if you growth factor is greater the 20% per year make the nessary
X--	 changes.
X
Xdatabase sysmaster;
X
Xselect dbsname,
X        tabname,
X        count(*) num_of_extents,
X        sum (pe_size ) pages_used,
X        round (sum (pe_size )
X                * 2 { Your systems page size in KB }
X                * 1.2 { Add 20% Growth factor })
X                ext_size, { First Extent Size in KB }
X        round (sum (pe_size )
X                * 2 { Your systems page size in KB }
X                * .2 { Estimated 20% Yearly Growth })
X                next_size { Next Extent Size in KB }
Xfrom systabnames, sysptnext
Xwhere partnum = pe_partnum
Xgroup by 1, 2
Xorder by 3 desc, 4 desc;
SHAR-EOF
if [ `wc -c <tabextplan.sql` -ne 1132 ]
then echo shar: tabextplan.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f tablayout.sql -a "$1" != "-c" ]
then echo shar: tablayout.sql already exists
else
echo 'x - tablayout.sql (600 characters)'
sed -e 's/^X//' >tablayout.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)tablayout.sql	1.4     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	Displays tables and extents
X-----------------------------------------------------------------------------
X
Xdatabase sysmaster;
X
Xselect dbinfo( "DBSPACE" , pe_partnum ) dbspace,
X     dbsname[1,10],
X     tabname,
X     pe_phys   start,
X     pe_size size
Xfrom      sysptnext, outer systabnames
Xwhere     pe_partnum = partnum
Xorder by dbspace, start;
X
SHAR-EOF
if [ `wc -c <tablayout.sql` -ne 600 ]
then echo shar: tablayout.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f tabprof.sql -a "$1" != "-c" ]
then echo shar: tabprof.sql already exists
else
echo 'x - tabprof.sql (840 characters)'
sed -e 's/^X//' >tabprof.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)tabprof.sql	1.4     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	Displays table IO performance
X-----------------------------------------------------------------------------
X
Xdatabase sysmaster;
X
X-- Some of the fields are commented out so the display will fit on 
X-- a 80 column screen
X
Xselect
X     dbsname,
X     tabname,
X     isreads,
X     bufreads,
X     pagreads
X     -- uncomment the following to show writes
X     -- iswrites,
X     -- bufwrites,
X     -- pagwrites
X     -- uncomment the following to show locks
X     -- lockreqs,
X     -- lockwts,
X     -- deadlks
Xfrom sysptprof
Xorder by isreads desc; -- change this sort to whatever you need to monitor.
SHAR-EOF
if [ `wc -c <tabprof.sql` -ne 840 ]
then echo shar: tabprof.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f txlogcnt.sql -a "$1" != "-c" ]
then echo shar: txlogcnt.sql already exists
else
echo 'x - txlogcnt.sql (1179 characters)'
sed -e 's/^X//' >txlogcnt.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)txlogcnt.sql	1.2     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	 Displays how many open transactions are in each log
X-- 	This script is based on an undocumented sysmaster table systrans 
X--	and may not work in all versions.  It was tested with 7.23.
X-----------------------------------------------------------------------------
X
Xdatabase sysmaster;
X
X-- Select the logical logs numbers for each transaction
Xselect 	tx_logbeg,
X	tx_loguniq
Xfrom 	systrans
Xinto temp b;
X
X-- Count the number of transactions begining in each log
Xselect tx_logbeg, count(*) cnt
Xfrom B
Xwhere tx_logbeg > 0
Xgroup by tx_logbeg
Xinto temp C;
X
X-- Count the number of transactions currently in each log
Xselect tx_loguniq, count(*) cnt
Xfrom B
Xwhere tx_loguniq > 0
Xgroup by tx_loguniq
Xinto temp D;
X
Xselect
X	number,
X	uniqid,
X	size,
X	used,
X	-- is_used,
X	-- is_current,
X	-- is_backed_up,
X	-- is_archived
X	c.cnt	tx_beg_cnt,
X	d.cnt	tx_curr_cnt
Xfrom syslogs, outer c, outer D
Xwhere uniqid = c.tx_logbeg
Xand 	uniqid = d.tx_loguniq
SHAR-EOF
if [ `wc -c <txlogcnt.sql` -ne 1179 ]
then echo shar: txlogcnt.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f txlogpos.sql -a "$1" != "-c" ]
then echo shar: txlogpos.sql already exists
else
echo 'x - txlogpos.sql (946 characters)'
sed -e 's/^X//' >txlogpos.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)txlogpos.sql	1.2     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	Displays users and position in logical logs.
X--	This script is based on an undocumented sysmaster table systrans 
X--	and may not work in all versions.  It was tested with 7.23.
X-----------------------------------------------------------------------------
X
Xdatabase sysmaster;
X
X-- Some fields are commented out so the display will fit on 80 columns
Xselect 
X	t.username,
X	t.sid,
X	-- tx_id,
X	-- tx_addr,
X	-- tx_flags,
X	-- tx_mutex,
X	tx_logbeg,
X	tx_loguniq,
X	tx_logpos
X	-- tx_lklist,
X	-- tx_lkmutex,
X	-- tx_owner,
X	-- tx_wtlist,
X	-- tx_ptlist,
X	-- tx_nlocks,
X	-- tx_lktout,
X	-- tx_isolevel,
X	-- tx_longtx,
X	-- tx_coordinator,
X	-- tx_nremotes
Xfrom 	systrans x, sysrstcb t
Xwhere	tx_owner = t.address
SHAR-EOF
if [ `wc -c <txlogpos.sql` -ne 946 ]
then echo shar: txlogpos.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f vpprof.sql -a "$1" != "-c" ]
then echo shar: vpprof.sql already exists
else
echo 'x - vpprof.sql (554 characters)'
sed -e 's/^X//' >vpprof.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)vpprof.sql	1.2     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	Displays VP status
X-----------------------------------------------------------------------------
X
Xdatabase sysmaster;
X
Xselect
X	vpid,
X	pid,
X	txt[1,5] class,
X	round( usecs_user, 2) usercpu,
X	round( usecs_sys, 2)  syscpu
Xfrom 	sysvplst a, flags_text b
Xwhere 	a.class = b.flags
Xand   	b.tabname = "sysvplst"
SHAR-EOF
if [ `wc -c <vpprof.sql` -ne 554 ]
then echo shar: vpprof.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f vpstat.sql -a "$1" != "-c" ]
then echo shar: vpstat.sql already exists
else
echo 'x - vpstat.sql (594 characters)'
sed -e 's/^X//' >vpstat.sql <<'SHAR-EOF'
X-----------------------------------------------------------------------------
X-- Module: @(#)vpstat.sql	1.4     Date: 97/07/18
X-- Author: Lester B. Knutsen  Email: lester@advancedatatools.com
X--         Advanced DataTools Corporation
X-- Discription:	 Displays VP status like onstat -g sch
X-----------------------------------------------------------------------------
X
Xdatabase sysmaster;
X
Xselect    vpid,
X       txt[1,5] class,
X       pid,
X       usecs_user,
X       usecs_sys,
X       num_ready
Xfrom sysvplst a, flags_text b
Xwhere a.flags != 6
Xand  a.class = b.flags
Xand b.tabname = 'sysvplst';
SHAR-EOF
if [ `wc -c <vpstat.sql` -ne 594 ]
then echo shar: vpstat.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f dbwho.sh -a "$1" != "-c" ]
then echo shar: dbwho.sh already exists
else
echo 'x - dbwho.sh (792 characters)'
sed -e 's/^X//' >dbwho.sh <<'SHAR-EOF'
X#!/bin/sh
X###########################################################################
X# Program:  @(#)dbwho.sh	1.2     Date: 97/07/17
X# Author:  Lester Knutsen  Email: lester@advancedatatools.com
X# Date:    10/28/1995      Advanced DataTools Corporation
X# Description: List database, user and workstation of all db users
X###########################################################################
X
Xecho "Generating list of users by database ..."
X
Xdbaccess sysmaster - <<EOF
Xselect
X        sysdatabases.name database,
X        syssessions.username,
X        syssessions.hostname,
X        syslocks.owner sid
Xfrom  syslocks, sysdatabases , outer syssessions
Xwhere syslocks.rowidlk = sysdatabases.rowid
Xand   syslocks.tabname = "sysdatabases"
Xand   syslocks.owner = syssessions.sid
Xorder by 1;
XEOF
SHAR-EOF
if [ `wc -c <dbwho.sh` -ne 792 ]
then echo shar: dbwho.sh unpacked with wrong size
fi
# end of overwriting check
fi
echo All files extracted
exit 0