SybaseWiki - User contributions [en]

Sp mon sql2--ASE spid activity monitor

2010-03-24T10:05:33Z

Bobh: corrected spacing

Note: This SP uses the MDA tables. 
This sp is for getting a clear list of currently active spids with full details including login, db, blocking spid, processing time, query start time, cpu usage, logical and physical i/o, currently executing command etc. To maximise clarity, all info is displayed on one line for each spid. Alternate info is also available by running with the "x" switch which includes, login time, host process, originating host and other information which may be useful for tracking down the source of a problem external to ASE.

Example output (normal usage)
<pre>
Spid Login DB Hostname blocked StartTime Dmins Dtime CpuTime LogicalReads PhysicalReads Command
--- ------ ------------ ---------------- ------------ ------- ------------------- ------ ----- ----------- ------------ ------------- ------------------------------
*** 126 sa PROD_DB1 prod-srv1 0 Mar 24 2010 12:13AM 570 09:30 0 42 0 SELECT
*** 143 app_user1 PROD_DB1 user-hosta 0 Mar 24 2010 9:38AM 6 00:06 10 0 0 SELECT
*** 32 app_user1 PROD_DB1 user-hostb 143 Mar 24 2010 9:38AM 5 00:05 0 20 0 SELECT
*** 44 cron cron_tempdb prod-srv1 0 Mar 24 2010 9:40AM 3 00:03 0 0 0 WAITFOR
</pre>

Example output (extended info)
Spid Login DB Hostname blocked StartTime Dmins Dtime Application OS PID Blocked(s) Logged in ClientIP Command
------ ------------ --------------- ------------ ------- ------------------- ------ ----- ------------ ------- ----------- --------- --------------- ------------------------------
126 sa PROD_DB1 prod-srv1 0 Mar 24 2010 12:13AM 570 09:30 isql 6181 NULL 15:25:59 12.7.3.55 SELECT
143 user1 PROD_DB1 user-hosta 0 Mar 24 2010 9:38AM 6 00:06 NULL - NULL 15:25:09 12.7.3.56 SELECT
32 user1 PROD_DB1 user-hostb 143 Mar 24 2010 9:38AM 5 00:05 NULL - NULL 15:26:04 12.7.3.57 SELECT
44 cron cron_tempdb prod-srv1 0 Mar 24 2010 9:40AM 3 00:03 isql 8639 NULL 15:26:04 12.7.3.55 WAITFOR

Usage summary: 
- sp__mon_sql2 ((no params), returns standard info for all spids)
- sp__mon_sql2 spid (returns standard info for a specific spid)
- sp__mon_sql2 null, "x" (returns extended info for all spids)
- sp__mon_sql2 spid, "x" (returns extended info for a specific spid)

<pre>
create procedure sp__mon_sql2
(
@spid int = null, -- default to retrieve info for all spids
@extinfo char(1) = "u" -- extended info switch, default “u” (unset) is for off, “x” and “X” are for on)
)
as
------------------------------
-- Procedure: sp__mon_sql2
-- Created: April 2008
-- Author: Bob Holmes (Email: cambob@gmail.com)
-- Usage: sp__mon_sql2 [spid]
-- Version : 1.1
------------------------------
--Modification history:
--bobh - if 1 is specified as the spid number then alternate info is returned (dirty hack)
--bobh: 14/09/2009: Dirty hack mentioned above removed. Usage is now as follows:
-- - sp__mon_sql2 ((no params), returns standard info for all spids)
-- - sp__mon_sql2 spid (returns standard info for a specific spid)
-- - sp__mon_sql2 null, x (returns extended info for all spids)
-- - sp__mon_sql2 spid, x (returns extended info for a specific spid)
------------------------------
-- INFO:
-- The three *'s are a grep key for a shell script to use to extract the lines needed for alerting.
------------------------------
set nocount on

--prep - setup input variables for run mode
if @spid=null
begin
select @spid=0
end

--part 1 - snapshot
set forceplan on
select s.StartTime,
convert(smallint,(datediff(mi,StartTime,getdate()))) Dmins,
p.spid,
p.hostname,
p.suid,
p.dbid,
p.cmd,
p.blocked,
s.CpuTime,
s.LogicalReads,
s.PhysicalReads,
p.status,
p.loggedindatetime,
p.program_name,
l.ClientOSPID,
p.time_blocked,
p.ipaddr
into #processmon2
from master..sysprocesses p, master..monProcessStatement s, master..monProcessLookup l
where p.spid != @@spid
and p.spid *= s.SPID
and l.SPID = p.spid
and suid <> 0
order by p.spid
set forceplan off

--
--
--case 1 - no params (0 and "u")
if @spid=0 and @extinfo="u" -- display standard information for all spids
begin
-- display header information for multiple spids
print "******************************************************************************"
print "Process Summary (Order by Dmins, CpuTime, LogicalReads, PhysicalReads Desc )"
print "******************************************************************************"
print ""
select distinct "***",
spid Spid,
convert(char(12),suser_name(suid)) Login,
convert(char(16),db_name(dbid)) DB,
(case hostname when "" then "-" else convert(char(12),hostname) end) Hostname,
blocked,
convert(varchar(19),StartTime) StartTime,
Dmins,
right( replicate("0", 2) + convert(varchar(2),(Dmins/60)),2) + ":" + right( replicate("0", 2) + convert(varchar(2),(Dmins%60)),2) Dtime,
CpuTime,
LogicalReads,
PhysicalReads,
cmd Command
from #processmon2
where cmd <> "AWAITING COMMAND"
order by Dmins desc, CpuTime Desc, LogicalReads Desc, PhysicalReads Desc
end
--
--
--case 2
else if @spid=0 and @extinfo="x" -- extended info for all spids
begin
-- display header information for multiple spids
print "******************************************************************************"
print "Process Summary (Order by Dmins, CpuTime, LogicalReads, PhysicalReads Desc )"
print "******************************************************************************"
print ""
select
spid Spid,
convert(char(12),suser_name(suid)) Login,
convert(char(15),db_name(dbid)) DB,
(case hostname when "" then "-" else convert(char(12),hostname) end) Hostname,
blocked,
convert(varchar(19),StartTime) StartTime,
Dmins,
right( replicate("0", 2) + convert(varchar(2),(Dmins/60)),2) + ":" + right( replicate("0", 2) + convert(varchar(2),(Dmins%60)),2) Dtime,
(case program_name when "" then "-" else convert(char(12),program_name) end) Application,
(case ClientOSPID when NULL then "-" else convert(char(7),ClientOSPID) end) "OS PID",
time_blocked "Blocked(s)",
convert(char(8),loggedindatetime,108) "Logged in",
convert(char(15),ipaddr) ClientIP,
cmd Command
from #processmon2
where cmd <> "AWAITING COMMAND"
order by Dmins desc, CpuTime Desc, LogicalReads Desc, PhysicalReads Desc
end
--
--
-- case 3
else if @spid <> 0 and @extinfo = "u" -- display standard info for specific spid
begin
select distinct "***",
spid Spid,
convert(char(12),suser_name(suid)) Login,
convert(char(16),db_name(dbid)) DB,
(case hostname when "" then "-" else convert(char(12),hostname) end) Hostname,
blocked,
convert(varchar(19),StartTime) StartTime,
Dmins,
right( replicate("0", 2) + convert(varchar(2),(Dmins/60)),2) + ":" + right( replicate("0", 2) + convert(varchar(2),(Dmins%60)),2) Dtime,
CpuTime,
LogicalReads,
PhysicalReads,
cmd Command
from #processmon2
where spid = @spid
order by Dmins desc, CpuTime Desc, LogicalReads Desc, PhysicalReads Desc
end
--
--
-- case 4
else if @spid <> 0 and @extinfo = "x" -- display extended info for specific spid
begin
-- display header information for multiple spids
print "******************************************************************************"
print "Process Summary (Order by Dmins, CpuTime, LogicalReads, PhysicalReads Desc )"
print "******************************************************************************"
print ""
select
spid Spid,
convert(char(12),suser_name(suid)) Login,
convert(char(15),db_name(dbid)) DB,
(case hostname when "" then "-" else convert(char(12),hostname) end) Hostname,
blocked,
convert(varchar(19),StartTime) StartTime,
Dmins,
right( replicate("0", 2) + convert(varchar(2),(Dmins/60)),2) + ":" + right( replicate("0", 2) + convert(varchar(2),(Dmins%60)),2) Dtime,
(case program_name when "" then "-" else convert(char(12),program_name) end) Application,
(case ClientOSPID when NULL then "-" else convert(char(7),ClientOSPID) end) "OS PID",
time_blocked "Blocked(s)",
convert(char(8),loggedindatetime,108) "Logged in",
convert(char(15),ipaddr) ClientIP,
cmd Command
from #processmon2
where spid = @spid
order by Dmins desc, CpuTime Desc, LogicalReads Desc, PhysicalReads Desc
end
</pre>

[[Category:ASE]]
[[Category:MDA tables]]

Sp mon sql2--ASE spid activity monitor

2010-03-24T10:04:31Z

Bobh: Added examples of output (with some dummy values)

Sp mon sql2--ASE spid activity monitor

2010-03-24T09:46:27Z

Bobh: Updated version of the script (dirty hack in previous version has been removed)

Note: This SP uses the MDA tables. 
This sp is for getting a clear list of currently active spids with full details including login, db, blocking spid, processing time, query start time, cpu usage, logical and physical i/o, currently executing command etc. To maximise clarity, all info is displayed on one line for each spid. Alternate info is also available by running with the "x" switch which includes, login time, host process, originating host and other information which may be useful for tracking down the source of a problem external to ASE.

Usage summary: 
- sp__mon_sql2 ((no params), returns standard info for all spids)
- sp__mon_sql2 spid (returns standard info for a specific spid)
- sp__mon_sql2 null, "x" (returns extended info for all spids)
- sp__mon_sql2 spid, "x" (returns extended info for a specific spid)

<pre>
create procedure sp__mon_sql2
(
@spid int = null, -- default to retrieve info for all spids
@extinfo char(1) = "u" -- extended info switch, default “u” (unset) is for off, “x” and “X” are for on)
)
as
------------------------------
-- Procedure: sp__mon_sql2
-- Created: April 2008
-- Author: Bob Holmes (Email: cambob@gmail.com)
-- Usage: sp__mon_sql2 [spid]
-- Version : 1.1
------------------------------
--Modification history:
--bobh - if 1 is specified as the spid number then alternate info is returned (dirty hack)
--bobh: 14/09/2009: Dirty hack mentioned above removed. Usage is now as follows:
-- - sp__mon_sql2 ((no params), returns standard info for all spids)
-- - sp__mon_sql2 spid (returns standard info for a specific spid)
-- - sp__mon_sql2 null, x (returns extended info for all spids)
-- - sp__mon_sql2 spid, x (returns extended info for a specific spid)
------------------------------
-- INFO:
-- The three *'s are a grep key for a shell script to use to extract the lines needed for alerting.
------------------------------
set nocount on

--prep - setup input variables for run mode
if @spid=null
begin
select @spid=0
end

--part 1 - snapshot
set forceplan on
select s.StartTime,
convert(smallint,(datediff(mi,StartTime,getdate()))) Dmins,
p.spid,
p.hostname,
p.suid,
p.dbid,
p.cmd,
p.blocked,
s.CpuTime,
s.LogicalReads,
s.PhysicalReads,
p.status,
p.loggedindatetime,
p.program_name,
l.ClientOSPID,
p.time_blocked,
p.ipaddr
into #processmon2
from master..sysprocesses p, master..monProcessStatement s, master..monProcessLookup l
where p.spid != @@spid
and p.spid *= s.SPID
and l.SPID = p.spid
and suid <> 0
order by p.spid
set forceplan off

--
--
--case 1 - no params (0 and "u")
if @spid=0 and @extinfo="u" -- display standard information for all spids
begin
-- display header information for multiple spids
print "******************************************************************************"
print "Process Summary (Order by Dmins, CpuTime, LogicalReads, PhysicalReads Desc )"
print "******************************************************************************"
print ""
select distinct "***",
spid Spid,
convert(char(12),suser_name(suid)) Login,
convert(char(16),db_name(dbid)) DB,
(case hostname when "" then "-" else convert(char(12),hostname) end) Hostname,
blocked,
convert(varchar(19),StartTime) StartTime,
Dmins,
right( replicate("0", 2) + convert(varchar(2),(Dmins/60)),2) + ":" + right( replicate("0", 2) + convert(varchar(2),(Dmins%60)),2) Dtime,
CpuTime,
LogicalReads,
PhysicalReads,
cmd Command
from #processmon2
where cmd <> "AWAITING COMMAND"
order by Dmins desc, CpuTime Desc, LogicalReads Desc, PhysicalReads Desc
end
--
--
--case 2
else if @spid=0 and @extinfo="x" -- extended info for all spids
begin
-- display header information for multiple spids
print "******************************************************************************"
print "Process Summary (Order by Dmins, CpuTime, LogicalReads, PhysicalReads Desc )"
print "******************************************************************************"
print ""
select
spid Spid,
convert(char(12),suser_name(suid)) Login,
convert(char(15),db_name(dbid)) DB,
(case hostname when "" then "-" else convert(char(12),hostname) end) Hostname,
blocked,
convert(varchar(19),StartTime) StartTime,
Dmins,
right( replicate("0", 2) + convert(varchar(2),(Dmins/60)),2) + ":" + right( replicate("0", 2) + convert(varchar(2),(Dmins%60)),2) Dtime,
(case program_name when "" then "-" else convert(char(12),program_name) end) Application,
(case ClientOSPID when NULL then "-" else convert(char(7),ClientOSPID) end) "OS PID",
time_blocked "Blocked(s)",
convert(char(8),loggedindatetime,108) "Logged in",
convert(char(15),ipaddr) ClientIP,
cmd Command
from #processmon2
where cmd <> "AWAITING COMMAND"
order by Dmins desc, CpuTime Desc, LogicalReads Desc, PhysicalReads Desc
end
--
--
-- case 3
else if @spid <> 0 and @extinfo = "u" -- display standard info for specific spid
begin
select distinct "***",
spid Spid,
convert(char(12),suser_name(suid)) Login,
convert(char(16),db_name(dbid)) DB,
(case hostname when "" then "-" else convert(char(12),hostname) end) Hostname,
blocked,
convert(varchar(19),StartTime) StartTime,
Dmins,
right( replicate("0", 2) + convert(varchar(2),(Dmins/60)),2) + ":" + right( replicate("0", 2) + convert(varchar(2),(Dmins%60)),2) Dtime,
CpuTime,
LogicalReads,
PhysicalReads,
cmd Command
from #processmon2
where spid = @spid
order by Dmins desc, CpuTime Desc, LogicalReads Desc, PhysicalReads Desc
end
--
--
-- case 4
else if @spid <> 0 and @extinfo = "x" -- display extended info for specific spid
begin
-- display header information for multiple spids
print "******************************************************************************"
print "Process Summary (Order by Dmins, CpuTime, LogicalReads, PhysicalReads Desc )"
print "******************************************************************************"
print ""
select
spid Spid,
convert(char(12),suser_name(suid)) Login,
convert(char(15),db_name(dbid)) DB,
(case hostname when "" then "-" else convert(char(12),hostname) end) Hostname,
blocked,
convert(varchar(19),StartTime) StartTime,
Dmins,
right( replicate("0", 2) + convert(varchar(2),(Dmins/60)),2) + ":" + right( replicate("0", 2) + convert(varchar(2),(Dmins%60)),2) Dtime,
(case program_name when "" then "-" else convert(char(12),program_name) end) Application,
(case ClientOSPID when NULL then "-" else convert(char(7),ClientOSPID) end) "OS PID",
time_blocked "Blocked(s)",
convert(char(8),loggedindatetime,108) "Logged in",
convert(char(15),ipaddr) ClientIP,
cmd Command
from #processmon2
where spid = @spid
order by Dmins desc, CpuTime Desc, LogicalReads Desc, PhysicalReads Desc
end
</pre>

[[Category:ASE]]
[[Category:MDA tables]]

Corruption of syslogs (dbcc rebuild log)

2009-03-30T12:06:44Z

Bobh: Corruption of syslogs moved to Corruption of syslogs (dbcc rebuild log)

A corrupt syslogs table (the transaction log of a database) can sometimes be fixed. Best approach is to load a clean database dump and it's transaction log. However, that's not always possible. For instance, when the corruption has been unnoticed for some time you need to take other steps.

Some suggestions:

* dump the database and reload the dump. This can solve some issues.
* extract all contect from the database with tools like defncopy and bcp. Then drop the database, recreate and load in the extracted information.

A more complex situation is shown here.
===Solving Msg 2578 and Msg 2503===
Run dbcc checktable(syslogs) to check the syslogs table.
use <database>
go
dbcc checktable(syslogs)
go
Checking syslogs: Logical pagesize is 2048 bytes
Msg 2578, Level 16, State 1:
Server 'ASE1', Line 1:
The first page 67664 in sysindexes for table 'syslogs' has previous page # 67663 in its page header. The previous page # should be NULL. Please check sysindexes.
Msg 2503, Level 16, State 1:
Server 'ASE1', Line 1:
Table Corrupt: Page linkage is not consistent; check the following pages: (current page#=67664; page# pointing to this page=0; previous page# indicated in this page=67663)
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
You might want to run a "dbcc pglinkage" to see if you can fix the page number yourself. When that is not possible you may need to follow the rebuild_log method.
'''WARNING''' you must use great caution with dbcc rebuild_log. This command will increase the current database timestamp value significantly. Once the database timestamp reaches it's limit (for example if you overuse this command) you will have to rebuild the database.
====Bypass recovery mode for the database====
use master
go
select status from sysdatabases where name = "<database>"
go
Make a note of the status
sp_configure "allow updates to system tables",1
go
begin tran
go
update sysdatabases set status = -32768 where name = "<database>"
go
commit tran
go
use <database>
go
checkpoint
go
shutdown
go
====Rebuild the transaction log====
Boot the ASE server. You will see a message in the errorlog that it skips recovery for the database.
server *** Bypassing recovery of database id 7
Run the following commands:
use <database>
go
dbcc rebuild_log(<database>,1,1)
go
use master
go
begin tran
go
update sysdatabases set status = <original status> where name = "<database>"
go
commit tran
go
shutdown
go
====Check if it worked====
Boot the ASE server and check if the syslogs is now OK.
use <database>
go
dbcc checktable(syslogs)
go
sp_configure "allow updates to system tables",0
go

[[Category:ASE]]

Corruption of syslogs

2009-03-30T12:06:44Z

Bobh: Corruption of syslogs moved to Corruption of syslogs (dbcc rebuild log): Title change for added clarity - dbcc rebuild_log vs log suicide

#redirect [[Corruption of syslogs (dbcc rebuild log)]]

Corruption of Syslogs (log suicide)

2009-03-30T12:03:26Z

Bobh:

== Log Suicide ==
----
'''Note: Data integrity cannot be guaranteed - this procedure is not recommended for production systems.'''
----
<pre>
1) update sysdatabases set status=-32768, status2=0 where name='suspect_database'
2) shutdown with nowait
3) restart dataserver
4) dump tran suspect_database with no_log
5) update sysdatabases set status=12, status2=0 where name='suspect_database'
6) recycle dataserver
</pre>
[[Category:ASE]]

RepServer Throughput Script

2009-02-26T11:29:41Z

Bobh:

If you want to know just how much data repserver is processing, then this is the tool. 
This script will tell you: 
o average throughput per queue (mb per minute) 
o average throughput combined (all queues) 
o estimated time for a replicate to catch up (Re-sync ETA) 
o current queue sizes 
There is also a maximum recorded throughput option but this appears not to work well as some of the figures have been suspicious. 
The real maximum throughput can be established by noting when the replicates start to fall behind in conjunction with the current throughput figures. 
This script can be particularly useful in helping to ascertain if you need to throw more CPU at your repserver box.

Notes: 
1) You will need to cron this script with the insert option (rs_throughput.sh insert) and allow it to collect at least 2 samples before being able to run it interactively to obtain throughput information. The script runs silently when run with the insert option. 
2) The script automatically determines the elapsed interval in minutes between samples so you can cron the script to your preferred interval, be it 3 minutes or 10 minutes, the mb/min figure will be calculated correctly. 
3) In addition to configuring the script to your environment, you will also need to create the rs_throughput table prior to first run. 
4) Re-sync ETA: this value is provided based on the current throughput average for the period specified. For example, this means that running rs_throughput.sh 15 may give a different Re-Sync ETA compared to running rs_throughput.sh 20
<pre>
#!/usr/bin/ksh
#
# Script: rs_throughput.sh
# Author: Bob Holmes - email: cambob@gmail.com
# Date : 19/09/08
# Version : 1.0
# Usage : Run "rs_throughput.sh help" for full usage information.
# Description : This script enables monitoring and reporting
# of repserver throughput.
# --------------------------
# *** Variables for customisation can be found by searching for "!!" ***
# --------------------------
# Modification history:
# 19/02/09 : Bob Holmes : Script modified to enable easier customisation prior
# to release on Sybase wiki. (modifications not tested)
# --------------------------
# Comments: This script relies on the reporting..rs_throughput table for data
# collection and reporting. The definition for this table can be
# found at the end of this script.
##############################################################################
# setup environment #
##############################################################################
#
# save input variables
if ! [[ -z $1 ]]
then
export option1=$1
else
export option1="all"
fi

if ! [[ -z $2 ]]
then
export option2=$2
else
export option2="15" # default number of minutes on which to report throughput
fi

# configure environment
HOSTNAME=`hostname`
. $HOME/admin/.syb_cfg.sh $HOSTNAME # !! This line sets up the environment from
# !! from an external shell script.

##############################################################################
# config variables #
##############################################################################
# config:
primary_ds="prim_ds" # !! string used to identify the inbound queue
# queues to query on:-
q_list="prim_ds.db1 prim_ds.db2 replicate_ds.db1 replicate_ds.db2" # !!
insert_filter="db1 db2" # !! data will only be collected for queue names which have this keyword in
REP_SRV="repsrv_rs" # !! the actual repserver from which to get sqm info
RPT_SRV="reporting_ds" # !! dataserver which manages the reporting database
RPT_DB="reporting" # !! reporting database name
RECIPIENTS="user@domain" # !! email config (separate addresses with commas)
ADMINDIR="/opt/home/sybase/admin" # !! sybase admin directory
RSPASS="<command to retrieve pw>" # !! insert equivalent command
ASEPASS="<command to retrieve pw>" # !! insert equivalent command

# static:
SQMFILE="$ADMINDIR/rs_sqm_tmp1.tmp"
DATE=$(date "+%d/%m/%y %H:%M:%S")

# config aware environment variables: # !! You may need to replace the line below with the equivalent
# !! for your own environment.
RISQLCMD="isql -Usa -P${RSPASS} -S${REP_SRV} -w1000"
ISQLCMD="isql -Usa -P${ASEPASS} -S${RPT_SRV} -w200 -D${RPT_DB}"

##############################################################################
# functions #
##############################################################################

check_previous_instance()
{
# Note: next line greps for 8 instances as tests proved that less could cause the script to exit incorrectly
if [ $(ps -ef | grep rs_throughput | egrep -v "vi|grep" | grep -v "sh -c" | wc -l | awk '{print $1}') -gt 8 ]
then
# previous instance still running - probably hanging
echo Previous instance still running.
exit
fi
}

fn_checkusage()
{
if [[ -z $option1 ]]
then
printf " Usage: ./rs_throughput.sh <all>|<q_name>|<help> [<minutes to display>]\n"
exit
fi
}

bcalc()
{
awk 'BEGIN{EQUATION='"$*"';printf("%0.1f\n",EQUATION); exit}'
}

fn_housekeeping()
{
if [ -e $SQMFILE ]
then
rm $SQMFILE
fi
$ISQLCMD <<-EOF
set nocount on
go
delete from rs_throughput where sample_time <= dateadd(dd,-28,getdate())
go
EOF
}

fn_insert_thruput_data()
{
# 1) get current segment usage/progress info
#connect to repserver, get sqm info - put into file
$RISQLCMD << eof > $SQMFILE
admin who,sqm
go
quit
eof

# 2) insert new segment data into rs_throughput table
#date
#echo "Queue Name, First segment, Last segment, Next segment"
cat $SQMFILE | egrep $insert_filter | sed 's/Awaiting Message/AwaitingMessage/; s/Awaiting Wakeup/AwaitingWakeup/; s/$\.[0-9][0-9]*$\.[0-9]/\1/' | grep -v ":0 ${primary_ds}" | awk '{print $4,$14,$15,$16}' | while read q_name first_seg last_seg next_seg
do
q_size=$(bcalc "$last_seg - $first_seg")
$ISQLCMD <<-EOF
set nocount on
go
declare @mb_processed numeric(8,2)
declare @prev_sample datetime
declare @mins numeric(8,2)
declare @tput numeric(8,2)
if not exists (select 1 from rs_throughput where q_name = "${q_name}")
insert rs_throughput values ("${q_name}", getdate(), ${first_seg}, ${last_seg}, ${next_seg}, ${q_size}, 0, 0)
else
begin
-- get previous sample_time as key
select @prev_sample = max(sample_time) from rs_throughput where q_name = "${q_name}"
-- get difference in mb between current next segment and previous next segment
select @mb_processed = (select ${next_seg} - next from rs_throughput where sample_time = @prev_sample and q_name = "${q_name}")

select @prev_sample=dateadd(ss,-10,@prev_sample) -- # must substract 10 seconds to ensure the next datediff doesn't reflect 2m 58s

as only 2mins!
select @mins = datediff(mi,@prev_sample,getdate())
if @mins > 0
begin
select @tput = round((@mb_processed / @mins),2)
insert rs_throughput values ("${q_name}", getdate(), ${first_seg}, ${last_seg}, ${next_seg}, ${q_size}, @mb_processed, @tput)
end
end
go
EOF
done
}

fn_report_thruput()
{
if [[ $option1 = [0-9]* ]]
then
option2=$option1
option1="all"
fi

if ! [[ "$option1" = "all" ]]
then
q_list=$option1
fi

printf " -------------------------------------\n"
printf " Run date $DATE\n"
printf " -------------------------------------\n"

for q_name in $q_list
do
$ISQLCMD <<-EOF
set nocount on
declare @mb_avg numeric(8,2)
declare @size numeric(8,2)
declare @eta datetime
select convert(char(19),sample_time) sample_time, q_name, size q_size, mb_processed, thruput "thruput (mb/min)" from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name like "%${q_name}%"

--get mb average
select @mb_avg = convert(numeric(8,2),avg(thruput)) from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name like "%${q_name}%"
--get current queue size
select @size = size from rs_throughput where q_name like "%${q_name}%" having sample_time=max(sample_time)
select @eta = dateadd(mi,round((@size/@mb_avg),0),getdate())
print ""
print " Average for ${q_name}: %1! mb/min, Re-sync ETA: %2!", @mb_avg, @eta
print ""
go
EOF
done
}

fn_show_help_text()
{
printf "Usage examples:\n"
printf "rs_throughput.sh (runs with default values showing all queues over 15 minutes)\n"
printf "rs_throughput.sh <q_name>|<minutes>\n"
printf "rs_throughput.sh <q_name> <minutes>\n"
printf "rs_throughput.sh size|queue (shows current queue sizes)\n"
printf "rs_throughput.sh max (shows highest ever throughput for queue history)\n"
printf "rs_throughput.sh help (shows this helptext)\n"
printf "rs_throughput.sh insert # this is for cron only (inserts data to $RPT_DB..rs_throughput table)\n"
}

fn_show_max_thruput()
{
$ISQLCMD <<-EOF
print ""
set rowcount 0
set nocount on
declare @q_name char(22)
declare @sample_time datetime
declare @max_thruput numeric(8,2)
select q_name, sample_time, thruput into #throughput from rs_throughput where 1=0
print "Maximum throughput per queue:"
-- get list of queues
select distinct q_name, 0 as processed into #queues from rs_throughput
set rowcount 1
select @q_name=q_name from #queues where processed = 0
while @@rowcount != 0
begin
insert #throughput select q_name, sample_time, thruput from rs_throughput
where sample_time in
(select sample_time from rs_throughput where q_name = @q_name having thruput = max(thruput))
and q_name = @q_name
update #queues set processed = 1 where q_name = @q_name
select @q_name=q_name from #queues where processed = 0
end
set rowcount 0
select * from #throughput order by thruput desc
go
EOF
echo
}

fn_show_current_q_size()
{
$RISQLCMD << eof > $SQMFILE
admin who,sqm
go
quit
eof
printf "Current Stable Queue usage:\n"
cat $SQMFILE | egrep $insert_filter | sed 's/Awaiting Message/AwaitingMessage/; s/Awaiting Wakeup/AwaitingWakeup/; s/$\.[0-9][0-9]*$\.[0-9]/\1/' | grep -v ":0 ${primary_ds}" | awk '{print $4,$14,$15,$16}' | while read q_name first_seg last_seg next_seg
do
q_size=$(bcalc "$last_seg - $first_seg")
printf "$q_name: $q_size\n"
done
}

fn_show_combined_avg_thruput()
{
$ISQLCMD <<-EOF
set rowcount 0
set nocount on
declare @q_name varchar(25)
declare @mb_avg numeric(8,2)
declare @mb_combined_avg numeric(8,2)
select @mb_combined_avg=0
-- get list of queues

select distinct q_name, 0 as processed into #queues from rs_throughput
set rowcount 1
select @q_name=q_name from #queues where processed = 0
while @@rowcount != 0
begin
select @mb_avg = convert(numeric(8,2),avg(thruput)) from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name = @q_name
select @mb_combined_avg = @mb_combined_avg + @mb_avg
update #queues set processed=1 where q_name = @q_name
select @q_name=q_name from #queues where processed = 0
end
print " Combined thruput average: %1! mb/min", @mb_combined_avg
print ""
go
EOF
}

##############################################################################
# main program #
##############################################################################

check_previous_instance
fn_checkusage

case "$option1" in
help) fn_show_help_text;
exit;;

insert) fn_insert_thruput_data;
exit;;

max) fn_show_max_thruput;
exit;;

queue|size) fn_show_current_q_size;
exit;;

*) fn_report_thruput;
fn_show_combined_avg_thruput;;
esac

fn_housekeeping

### TABLE DEFINTION FOR RAW DATA COLLECTION
### Use the table definition below to create the table which this script
### requires in order to insert queue data and furthermore query this
### data in order to provide throughput information.
#create table rs_throughput (
#q_name varchar(25),
#sample_time datetime,
#first numeric(8,2),
#last numeric(8,2),
#next numeric(8,2),
#size numeric(8,2),
#mb_processed numeric(8,2),
#thruput numeric(8,2)
#)

# end program
</pre>

[[Category:RepServer]]

RepServer Throughput Script

2009-02-26T11:25:55Z

Bobh:

If you want to know just how much data repserver is processing, then this is the tool. 
This script will tell you: 
o average throughput per queue (mb per minute) 
o average throughput combined (all queues) 
o estimated time for a replicate to catch up (Re-sync ETA) 
o current queue sizes 
There is also a maximum recorded throughput option but this appears not to work well as some of the figures have been suspicious. 
The real maximum throughput can be established by noting when the replicates start to fall behind in conjunction with the current throughput figures. 
This script can be particularly useful in helping to ascertain if you need to throw more CPU at your repserver box.

Notes: 
1) You will need to cron this script with the insert option (rs_throughput.sh insert) and allow it to collect at least 2 samples before being able to run it interactively to obtain throughput information. The script runs silently when run with the insert option. 
2) The script automatically determines the elapsed interval in minutes between samples so you can cron the script to your preferred interval, be it 3 minutes or 10 minutes, the mb/min figure will be calculated correctly. 
3) In addition to configuring the script to your environment, you will also need to create the rs_throughput table prior to first run. 
4) Re-sync ETA: this value is provided based on the current throughput average for the period specified. For example, this means that running rs_throughput.sh 15 may give a different Re-Sync ETA compared to running rs_throughput.sh 20
<pre>
#!/usr/bin/ksh
#
# Script: rs_throughput.sh
# Author: Bob Holmes - email: cambob@gmail.com
# Date : 19/09/08
# Version : 1.0
# Usage : Run "rs_throughput.sh help" for full usage information.
# Description : This script enables monitoring and reporting
# of repserver throughput.
# --------------------------
# *** Variables for customisation can be found by searching for "!!" ***
# --------------------------
# Modification history:
# 19/02/09 : Bob Holmes : Script modified to enable easier customisation prior
# to release on Sybase wiki. (modifications not tested)
# --------------------------
# Comments: This script relies on the reporting..rs_throughput table for data
# collection and reporting. The definition for this table can be
# found at the end of this script.
##############################################################################
# setup environment #
##############################################################################
#
# save input variables
if ! [[ -z $1 ]]
then
export option1=$1
else
export option1="all"
fi

if ! [[ -z $2 ]]
then
export option2=$2
else
export option2="15" # default number of minutes on which to report throughput
fi

# configure environment
HOSTNAME=`hostname`
. $HOME/admin/.syb_cfg.sh $HOSTNAME # !! This line sets up the environment from
# !! from an external shell script.

##############################################################################
# config variables #
##############################################################################
# config:
primary_ds="prim_ds" # !! string used to identify the inbound queue
# queues to query on:-
q_list="prim_ds.db1 prim_ds.db2 replicate_ds.db1 replicate_ds.db2" # !!
insert_filter="db1 db2" # !! data will only be collected for queue names which have this keyword in
REP_SRV="repsrv_rs" # !! the actual repserver from which to get sqm info
RPT_SRV="reporting_ds" # !! dataserver which manages the reporting database
RPT_DB="reporting" # !! reporting database name
RECIPIENTS="user@domain" # !! email config (separate addresses with commas)
ADMINDIR="/opt/home/sybase/admin" # !! sybase admin directory
RSPASS="<command to retrieve pw>" # !! insert equivalent command
ASEPASS="<command to retrieve pw>" # !! insert equivalent command

# static:
SQMFILE="$ADMINDIR/rs_sqm_tmp1.tmp"
DATE=$(date "+%d/%m/%y %H:%M:%S")

# config aware environment variables: # !! You may need to replace the line below with the equivalent
# !! for your own environment.
RISQLCMD="isql -Usa -P${RSPASS} -S${REP_SRV} -w1000"
ISQLCMD="isql -Usa -P${ASEPASS} -S${RPT_SRV} -w200 -D${RPT_DB}"

##############################################################################
# functions #
##############################################################################

check_previous_instance()
{
# Note: next line greps for 8 instances as tests proved that less could cause the script to exit incorrectly
if [ $(ps -ef | grep rs_throughput | egrep -v "vi|grep" | grep -v "sh -c" | wc -l | awk '{print $1}') -gt 8 ]
then
# previous instance still running - probably hanging
echo Previous instance still running.
exit
fi
}

fn_checkusage()
{
if [[ -z $option1 ]]
then
printf " Usage: ./rs_throughput.sh <all>|<q_name>|<help> [<minutes to display>]\n"
exit
fi
}

bcalc()
{
awk 'BEGIN{EQUATION='"$*"';printf("%0.1f\n",EQUATION); exit}'
}

fn_housekeeping()
{
if [ -e $SQMFILE ]
then
rm $SQMFILE
fi
$ISQLCMD <<-EOF
set nocount on
go
delete from rs_throughput where sample_time <= dateadd(dd,-28,getdate())
go
EOF
}

fn_insert_thruput_data()
{
# 1) get current segment usage/progress info
#connect to repserver, get sqm info - put into file
$RISQLCMD << eof > $SQMFILE
admin who,sqm
go
quit
eof

# 2) insert new segment data into rs_throughput table
#date
#echo "Queue Name, First segment, Last segment, Next segment"
cat $SQMFILE | egrep $insert_filter | sed 's/Awaiting Message/AwaitingMessage/; s/Awaiting Wakeup/AwaitingWakeup/; s/$\.[0-9][0-

9]*$\.[0-9]/\1/' | grep -v ":0 ${primary_ds}" | awk '{print $4,$14,$15,$16}' | while read q_name first_seg last_seg next_seg
do
q_size=$(bcalc "$last_seg - $first_seg")
$ISQLCMD <<-EOF
set nocount on
go
declare @mb_processed numeric(8,2)
declare @prev_sample datetime
declare @mins numeric(8,2)
declare @tput numeric(8,2)
if not exists (select 1 from rs_throughput where q_name = "${q_name}")
insert rs_throughput values ("${q_name}", getdate(), ${first_seg}, ${last_seg}, ${next_seg}, ${q_size}, 0, 0)
else
begin
-- get previous sample_time as key
select @prev_sample = max(sample_time) from rs_throughput where q_name = "${q_name}"
-- get difference in mb between current next segment and previous next segment
select @mb_processed = (select ${next_seg} - next from rs_throughput where sample_time = @prev_sample and q_name = "${q_name}")

select @prev_sample=dateadd(ss,-10,@prev_sample) -- # must substract 10 seconds to ensure the next datediff doesn't reflect 2m 58s

as only 2mins!
select @mins = datediff(mi,@prev_sample,getdate())
if @mins > 0
begin
select @tput = round((@mb_processed / @mins),2)
insert rs_throughput values ("${q_name}", getdate(), ${first_seg}, ${last_seg}, ${next_seg}, ${q_size}, @mb_processed, @tput)
end
end
go
EOF
done
}

fn_report_thruput()
{
if [[ $option1 = [0-9]* ]]
then
option2=$option1
option1="all"
fi

if ! [[ "$option1" = "all" ]]
then
q_list=$option1
fi

printf " -------------------------------------\n"
printf " Run date $DATE\n"
printf " -------------------------------------\n"

for q_name in $q_list
do
$ISQLCMD <<-EOF
set nocount on
declare @mb_avg numeric(8,2)
declare @size numeric(8,2)
declare @eta datetime
select convert(char(19),sample_time) sample_time, q_name, size q_size, mb_processed, thruput "thruput (mb/min)" from rs_throughput
--select convert(char(19),sample_time) sample_time, q_name, size q_size, mb_processed, thruput "thruput (mb/min)", dateadd(mi,

(round((size/thruput),0)),getdate()) ETA from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name like "%${q_name}%"

--get mb average
select @mb_avg = convert(numeric(8,2),avg(thruput)) from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name like "%${q_name}%"
--get current queue size
select @size = size from rs_throughput where q_name like "%${q_name}%" having sample_time=max(sample_time)
select @eta = dateadd(mi,round((@size/@mb_avg),0),getdate())
print ""
print " Average for ${q_name}: %1! mb/min, Re-sync ETA: %2!", @mb_avg, @eta
print ""
go
EOF
done
}

fn_show_help_text()
{
printf "Usage examples:\n"
printf "rs_throughput.sh (runs with default values showing all queues over 15 minutes)\n"
printf "rs_throughput.sh <q_name>|<minutes>\n"
printf "rs_throughput.sh <q_name> <minutes>\n"
printf "rs_throughput.sh size|queue (shows current queue sizes)\n"
printf "rs_throughput.sh max (shows highest ever throughput for queue history)\n"
printf "rs_throughput.sh help (shows this helptext)\n"
printf "rs_throughput.sh insert # this is for cron only (inserts data to $RPT_DB..rs_throughput table)\n"
}

fn_show_max_thruput()
{
$ISQLCMD <<-EOF
print ""
set rowcount 0
set nocount on
declare @q_name char(22)
declare @sample_time datetime
declare @max_thruput numeric(8,2)
select q_name, sample_time, thruput into #throughput from rs_throughput where 1=0
print "Maximum throughput per queue:"
-- get list of queues
select distinct q_name, 0 as processed into #queues from rs_throughput
set rowcount 1
select @q_name=q_name from #queues where processed = 0
while @@rowcount != 0
begin
insert #throughput select q_name, sample_time, thruput from rs_throughput
where sample_time in
(select sample_time from rs_throughput where q_name = @q_name having thruput = max(thruput))
and q_name = @q_name
update #queues set processed = 1 where q_name = @q_name
select @q_name=q_name from #queues where processed = 0
end
set rowcount 0
select * from #throughput order by thruput desc
go
EOF
echo
}

fn_show_current_q_size()
{
$RISQLCMD << eof > $SQMFILE
admin who,sqm
go
quit
eof
printf "Current Stable Queue usage:\n"
cat $SQMFILE | egrep $insert_filter | sed 's/Awaiting Message/AwaitingMessage/; s/Awaiting Wakeup/AwaitingWakeup/; s/$\.[0-9][0-

9]*$\.[0-9]/\1/' | grep -v ":0 ${primary_ds}" | awk '{print $4,$14,$15,$16}' | while read q_name first_seg last_seg next_seg
do
q_size=$(bcalc "$last_seg - $first_seg")
printf "$q_name: $q_size\n"
done
}

fn_show_combined_avg_thruput()
{
$ISQLCMD <<-EOF
set rowcount 0
set nocount on
declare @q_name varchar(25)
declare @mb_avg numeric(8,2)
declare @mb_combined_avg numeric(8,2)
select @mb_combined_avg=0
-- get list of queues

select distinct q_name, 0 as processed into #queues from rs_throughput
set rowcount 1
select @q_name=q_name from #queues where processed = 0
while @@rowcount != 0
begin
select @mb_avg = convert(numeric(8,2),avg(thruput)) from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name = @q_name
select @mb_combined_avg = @mb_combined_avg + @mb_avg
update #queues set processed=1 where q_name = @q_name
select @q_name=q_name from #queues where processed = 0
end
print " Combined thruput average: %1! mb/min", @mb_combined_avg
print ""
go
EOF
}

##############################################################################
# main program #
##############################################################################

check_previous_instance
fn_checkusage

case "$option1" in
help) fn_show_help_text;
exit;;

insert) fn_insert_thruput_data;
exit;;

max) fn_show_max_thruput;
exit;;

queue|size) fn_show_current_q_size;
exit;;

*) fn_report_thruput;
fn_show_combined_avg_thruput;;
esac

fn_housekeeping

### TABLE DEFINTION FOR RAW DATA COLLECTION
### Use the table definition below to create the table which this script
### requires in order to insert queue data and furthermore query this
### data in order to provide throughput information.
#create table rs_throughput (
#q_name varchar(25),
#sample_time datetime,
#first numeric(8,2),
#last numeric(8,2),
#next numeric(8,2),
#size numeric(8,2),
#mb_processed numeric(8,2),
#thruput numeric(8,2)
#)

# end program
</pre>

[[Category:RepServer]]

RepServer Throughput Script

2009-02-26T11:22:17Z

Bobh:

If you want to know just how much data repserver is processing, then this is the tool. 
This script will tell you: 
o average throughput per queue (mb per minute) 
o average throughput combined (all queues) 
o estimated time for a replicate to catch up (Re-sync ETA) 
o current queue sizes 
There is also a maximum recorded throughput option but this appears not to work well as some of the figures have been suspicious. 
The real maximum throughput can be established by noting when the replicates start to fall behind in conjunction with the current throughput figures. 
This script can be particularly useful in helping to ascertain if you need to throw more CPU at your repserver box.

Notes: 
1) You will need to cron this script with the insert option (rs_throughput.sh insert) and allow it to collect at least 2 samples before being able to run it interactively to obtain throughput information. The script runs silently when run with the insert option. 
2) The script automatically determines the elapsed interval in minutes between samples so you can cron the script to your preferred interval, be it 3 minutes or 10 minutes, the mb/min figure will be calculated correctly. 
3) In addition to configuring the script to your environment, you will also need to create the rs_throughput table prior to first run. 
4) Re-sync ETA: this value is provided based on the current throughput average for the period specified. For example, this means that running rs_throughput.sh 15 may give a different Re-Sync ETA compared to running rs_throughput.sh 20
<pre>
#!/usr/bin/ksh
#
# Script: rs_throughput.sh
# Author: Bob Holmes - email: cambob@gmail.com
# Date : 19/09/08
# Version : 1.0
# Usage : Run "rs_throughput.sh help" for full usage information.
# Description : This script enables monitoring and reporting
# of repserver throughput.
# --------------------------
# *** Variables for customisation can be found by searching for "!!" ***
# --------------------------
# Modification history:
# 19/02/09 : Bob Holmes : Script modified to enable easier customisation prior
# to release on Sybase wiki. (modifications not tested)
# --------------------------
# Comments: This script relies on the reporting..rs_throughput table for data
# collection and reporting. The definition for this table can be
# found at the end of this script.
##############################################################################
# setup environment #
##############################################################################
#
# save input variables
if ! [[ -z $1 ]]
then
export option1=$1
else
export option1="all"
fi

if ! [[ -z $2 ]]
then
export option2=$2
else
export option2="15" # default number of minutes on which to report throughput
fi

# configure environment
HOSTNAME=`hostname`
. $HOME/admin/.syb_cfg.sh $HOSTNAME # !! This line sets up the environment from
# !! from an external shell script.

##############################################################################
# config variables #
##############################################################################
# config:
primary_ds="prim_ds" # !! string used to identify the inbound queue
# queues to query on:-
q_list="prim_ds.db1 prim_ds.db2 replicate_ds.db1 replicate_ds.db2" # !!
insert_filter="db1 db2" # !! data will only be collected for queue names which have this keyword in
REP_SRV="repsrv_rs" # !! the actual repserver from which to get sqm info
RPT_SRV="reporting_ds" # !! dataserver which manages the reporting database
RPT_DB="reporting" # !! reporting database name
RECIPIENTS="user@domain" # !! email config (separate addresses with commas)
ADMINDIR="/opt/home/sybase/admin" # !! sybase admin directory
RSPASS="<command to retrieve pw>" # !! insert equivalent command
ASEPASS="<command to retrieve pw>" # !! insert equivalent command

# static:
SQMFILE="$ADMINDIR/rs_sqm_tmp1.tmp"
DATE=$(date "+%d/%m/%y %H:%M:%S")

# config aware environment variables: # !! You may need to replace the line below with the equivalent
# !! for your own environment.
RISQLCMD="isql -Usa -P${RSPASS} -S${REP_SRV} -w1000"
ISQLCMD="isql -Usa -P${SYBPASS} -S${RPT_SRV} -w200 -D${RPT_DB}"

##############################################################################
# functions #
##############################################################################

check_previous_instance()
{
# Note: next line greps for 8 instances as tests proved that less could cause the script to exit incorrectly
if [ $(ps -ef | grep rs_throughput | egrep -v "vi|grep" | grep -v "sh -c" | wc -l | awk '{print $1}') -gt 8 ]
then
# previous instance still running - probably hanging
echo Previous instance still running.
exit
fi
}

fn_checkusage()
{
if [[ -z $option1 ]]
then
printf " Usage: ./rs_throughput.sh <all>|<q_name>|<help> [<minutes to display>]\n"
exit
fi
}

bcalc()
{
awk 'BEGIN{EQUATION='"$*"';printf("%0.1f\n",EQUATION); exit}'
}

fn_housekeeping()
{
if [ -e $SQMFILE ]
then
rm $SQMFILE
fi
$ISQLCMD <<-EOF
set nocount on
go
delete from rs_throughput where sample_time <= dateadd(dd,-28,getdate())
go
EOF
}

fn_insert_thruput_data()
{
# 1) get current segment usage/progress info
#connect to repserver, get sqm info - put into file
$RISQLCMD << eof > $SQMFILE
admin who,sqm
go
quit
eof

# 2) insert new segment data into rs_throughput table
#date
#echo "Queue Name, First segment, Last segment, Next segment"
cat $SQMFILE | egrep $insert_filter | sed 's/Awaiting Message/AwaitingMessage/; s/Awaiting Wakeup/AwaitingWakeup/; s/$\.[0-9][0-

9]*$\.[0-9]/\1/' | grep -v ":0 ${primary_ds}" | awk '{print $4,$14,$15,$16}' | while read q_name first_seg last_seg next_seg
do
q_size=$(bcalc "$last_seg - $first_seg")
$ISQLCMD <<-EOF
set nocount on
go
declare @mb_processed numeric(8,2)
declare @prev_sample datetime
declare @mins numeric(8,2)
declare @tput numeric(8,2)
if not exists (select 1 from rs_throughput where q_name = "${q_name}")
insert rs_throughput values ("${q_name}", getdate(), ${first_seg}, ${last_seg}, ${next_seg}, ${q_size}, 0, 0)
else
begin
-- get previous sample_time as key
select @prev_sample = max(sample_time) from rs_throughput where q_name = "${q_name}"
-- get difference in mb between current next segment and previous next segment
select @mb_processed = (select ${next_seg} - next from rs_throughput where sample_time = @prev_sample and q_name = "${q_name}")

select @prev_sample=dateadd(ss,-10,@prev_sample) -- # must substract 10 seconds to ensure the next datediff doesn't reflect 2m 58s

as only 2mins!
select @mins = datediff(mi,@prev_sample,getdate())
if @mins > 0
begin
select @tput = round((@mb_processed / @mins),2)
insert rs_throughput values ("${q_name}", getdate(), ${first_seg}, ${last_seg}, ${next_seg}, ${q_size}, @mb_processed, @tput)
end
end
go
EOF
done
}

fn_report_thruput()
{
if [[ $option1 = [0-9]* ]]
then
option2=$option1
option1="all"
fi

if ! [[ "$option1" = "all" ]]
then
q_list=$option1
fi

printf " -------------------------------------\n"
printf " Run date $DATE\n"
printf " -------------------------------------\n"

for q_name in $q_list
do
$ISQLCMD <<-EOF
set nocount on
declare @mb_avg numeric(8,2)
declare @size numeric(8,2)
declare @eta datetime
select convert(char(19),sample_time) sample_time, q_name, size q_size, mb_processed, thruput "thruput (mb/min)" from rs_throughput
--select convert(char(19),sample_time) sample_time, q_name, size q_size, mb_processed, thruput "thruput (mb/min)", dateadd(mi,

(round((size/thruput),0)),getdate()) ETA from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name like "%${q_name}%"

--get mb average
select @mb_avg = convert(numeric(8,2),avg(thruput)) from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name like "%${q_name}%"
--get current queue size
select @size = size from rs_throughput where q_name like "%${q_name}%" having sample_time=max(sample_time)
select @eta = dateadd(mi,round((@size/@mb_avg),0),getdate())
print ""
print " Average for ${q_name}: %1! mb/min, Re-sync ETA: %2!", @mb_avg, @eta
print ""
go
EOF
done
}

fn_show_help_text()
{
printf "Usage examples:\n"
printf "rs_throughput.sh (runs with default values showing all queues over 15 minutes)\n"
printf "rs_throughput.sh <q_name>|<minutes>\n"
printf "rs_throughput.sh <q_name> <minutes>\n"
printf "rs_throughput.sh size|queue (shows current queue sizes)\n"
printf "rs_throughput.sh max (shows highest ever throughput for queue history)\n"
printf "rs_throughput.sh help (shows this helptext)\n"
printf "rs_throughput.sh insert # this is for cron only (inserts data to $RPT_DB..rs_throughput table)\n"
}

fn_show_max_thruput()
{
$ISQLCMD <<-EOF
print ""
set rowcount 0
set nocount on
declare @q_name char(22)
declare @sample_time datetime
declare @max_thruput numeric(8,2)
select q_name, sample_time, thruput into #throughput from rs_throughput where 1=0
print "Maximum throughput per queue:"
-- get list of queues
select distinct q_name, 0 as processed into #queues from rs_throughput
set rowcount 1
select @q_name=q_name from #queues where processed = 0
while @@rowcount != 0
begin
insert #throughput select q_name, sample_time, thruput from rs_throughput
where sample_time in
(select sample_time from rs_throughput where q_name = @q_name having thruput = max(thruput))
and q_name = @q_name
update #queues set processed = 1 where q_name = @q_name
select @q_name=q_name from #queues where processed = 0
end
set rowcount 0
select * from #throughput order by thruput desc
go
EOF
echo
}

fn_show_current_q_size()
{
$RISQLCMD << eof > $SQMFILE
admin who,sqm
go
quit
eof
printf "Current Stable Queue usage:\n"
cat $SQMFILE | egrep $insert_filter | sed 's/Awaiting Message/AwaitingMessage/; s/Awaiting Wakeup/AwaitingWakeup/; s/$\.[0-9][0-

9]*$\.[0-9]/\1/' | grep -v ":0 ${primary_ds}" | awk '{print $4,$14,$15,$16}' | while read q_name first_seg last_seg next_seg
do
q_size=$(bcalc "$last_seg - $first_seg")
printf "$q_name: $q_size\n"
done
}

fn_show_combined_avg_thruput()
{
$ISQLCMD <<-EOF
set rowcount 0
set nocount on
declare @q_name varchar(25)
declare @mb_avg numeric(8,2)
declare @mb_combined_avg numeric(8,2)
select @mb_combined_avg=0
-- get list of queues

select distinct q_name, 0 as processed into #queues from rs_throughput
set rowcount 1
select @q_name=q_name from #queues where processed = 0
while @@rowcount != 0
begin
select @mb_avg = convert(numeric(8,2),avg(thruput)) from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name = @q_name
select @mb_combined_avg = @mb_combined_avg + @mb_avg
update #queues set processed=1 where q_name = @q_name
select @q_name=q_name from #queues where processed = 0
end
print " Combined thruput average: %1! mb/min", @mb_combined_avg
print ""
go
EOF
}

##############################################################################
# main program #
##############################################################################

check_previous_instance
fn_checkusage

case "$option1" in
help) fn_show_help_text;
exit;;

insert) fn_insert_thruput_data;
exit;;

max) fn_show_max_thruput;
exit;;

queue|size) fn_show_current_q_size;
exit;;

*) fn_report_thruput;
fn_show_combined_avg_thruput;;
esac

fn_housekeeping

### TABLE DEFINTION FOR RAW DATA COLLECTION
### Use the table definition below to create the table which this script
### requires in order to insert queue data and furthermore query this
### data in order to provide throughput information.
#create table rs_throughput (
#q_name varchar(25),
#sample_time datetime,
#first numeric(8,2),
#last numeric(8,2),
#next numeric(8,2),
#size numeric(8,2),
#mb_processed numeric(8,2),
#thruput numeric(8,2)
#)

# end program
</pre>

[[Category:RepServer]]

Sp mon sql2--ASE spid activity monitor

2009-02-23T23:16:15Z

Bobh:

Sp block--check analyse blocked processes

2009-02-23T12:17:30Z

Bobh: Initial version.

This sp checks for blocked processes and, if found, returns the spid info, sql text and query plan. It requires sp__mon_sql2 and sp__capture_sql. 
It could do with some extra work to filter out the spids which are not the root blocking spids; it can produce a large amount of output if there are a high number of individual spids involved in a chain of blocks.
<pre>
create proc sp__block
as
------------------------------
-- Procedure: sp__block
-- Created: April 2008
-- Author: Bob Holmes
-- Last modification:
-- Dependencies: sp__mon_sql2, sp__capture_sql
------------------------------
declare @numblocks int
select @numblocks = count(*) from master..sysprocesses where blocked <> 0
-- list blocked processes
print " %1! Blocked Processes", @numblocks
print " =============================================================================================="
print " duration"
select spid,
convert(char(12),suser_name(suid)) login,
convert(char(16),db_name(dbid)) db,
hostname,
blocked "blocked by",
convert(varchar(9),StartTime,108) StartTime,
right( replicate("0", 2) + convert(varchar(2),(time_blocked/60/60)),2)
+ ":"
+right( replicate("0", 2) + convert(varchar(2),((time_blocked/60)-((time_blocked/60/60)*60))),2)
+ ":"
+ right( replicate("0", 2) + convert(varchar(2),(time_blocked%60)),2) "hh:mm:ss",
cmd Command
from master..sysprocesses, master..monProcessStatement
where blocked <> 0
and spid = SPID

if @numblocks <> 0
begin
--prep list of blocking spids
select distinct blocked into #blockingspids from master..sysprocesses where blocked <> 0
declare @spid int
--get spid info: summary, sql, query plan
select @spid = min(blocked) from #blockingspids
while @spid >= (select min(blocked) from #blockingspids)
begin
print ""
exec sp__mon_sql2 @spid
exec sp__capture_sql @spid
exec sp_showplan @spid, null, null, null
print "Tables in use by Spid %1!", @spid
print "-----------------------------"
select
a.SPID,
convert(char(12),a.Login) Login,
convert(char(15),a.ClientHost),
convert(char(15),a.ClientIP) ClientIP,
convert(char(15),b.DBName) DBName,
b.ObjectName,
c.LogicalReads,
c.PhysicalReads,
c.PhysicalWrites,
b.TableSize
from master..monProcessLookup a, master..monProcessObject b, master..monProcessActivity c
where a.SPID = b.SPID
and b.SPID = c.SPID
and a.SPID = @spid
order by c.PhysicalWrites desc

if (@spid = (select max(blocked) from #blockingspids)) or (@spid = null)
break
select @spid = min(blocked) from #blockingspids where blocked > @spid
end
end
return
</pre>
[[Category:ASE]]

Sp tempdbuse--Check usage of all tempdbs

2009-02-23T12:03:39Z

Bobh:

This sp cycles through all databases with 'tempdb' in the name and outputs usage information - size, percent used etc.
<pre>
create procedure sp__tempdbuse
as
------------------------------
-- Procedure: sp__tempdbuse
-- Created: May 2008
-- Author: Bob Holmes (Email: cambob@gmail.com)
-- Usage: sp__tempdbuse
------------------------------
set nocount on

select name into #dblist from master..sysdatabases where name like "%tempdb%"

declare db_cursor cursor
for select name from #dblist

declare @current char(20)

open db_cursor

fetch db_cursor into @current

create table #results (dbname char(20), totalspace char(10), freespace char(10),

pctused char(3))

while @@sqlstatus = 0
begin
select db_name(dbid) 'DatabaseName', segmap 'Segmap', name 'Segname',
sum(size) 'Allocated',
sum(curunreservedpgs(dbid,lstart,unreservedpgs)) 'Free'
into #SegAlloc
from master.dbo.sysusages , syssegments
where dbid = db_id(@current) and
segmap & power(2,segment) = power(2,segment)
and syssegments.name != 'system'
and syssegments.name != 'logsegment'
group by dbid,segmap,name
order by dbid,segmap,name

insert #results
select DatabaseName,
rtrim(convert(char(10),sum(Allocated)/512)),
rtrim(convert(char(10),sum(Free)/512)),
rtrim(convert(char(10),100 -(sum((Free)/512) * 100 / (sum

(Allocated)/512))))
from #SegAlloc
group by DatabaseName,Segname
order by DatabaseName,Segname

drop table #SegAlloc

fetch db_cursor into @current
end

select * from #results

close db_cursor

deallocate cursor db_cursor

return
</pre>
[[category:ASE]]

Sp tempdbuse--Check usage of all tempdbs

2009-02-23T12:00:54Z

Bobh: Initial version.

create procedure sp__tempdbuse
as
------------------------------
-- Procedure: sp__tempdbuse
-- Created: May 2008
-- Author: Bob Holmes (Email: cambob@gmail.com)
-- Usage: sp__tempdbuse
------------------------------
set nocount on

select name into #dblist from master..sysdatabases where name like "%tempdb%"

declare db_cursor cursor
for select name from #dblist

declare @current char(20)

open db_cursor

fetch db_cursor into @current

create table #results (dbname char(20), totalspace char(10), freespace char(10),

pctused char(3))

while @@sqlstatus = 0
begin
select db_name(dbid) 'DatabaseName', segmap 'Segmap', name 'Segname',
sum(size) 'Allocated',
sum(curunreservedpgs(dbid,lstart,unreservedpgs)) 'Free'
into #SegAlloc
from master.dbo.sysusages , syssegments
where dbid = db_id(@current) and
segmap & power(2,segment) = power(2,segment)
and syssegments.name != 'system'
and syssegments.name != 'logsegment'
group by dbid,segmap,name
order by dbid,segmap,name

insert #results
select DatabaseName,
rtrim(convert(char(10),sum(Allocated)/512)),
rtrim(convert(char(10),sum(Free)/512)),
rtrim(convert(char(10),100 -(sum((Free)/512) * 100 / (sum

(Allocated)/512))))
from #SegAlloc
group by DatabaseName,Segname
order by DatabaseName,Segname

drop table #SegAlloc

fetch db_cursor into @current
end

select * from #results

close db_cursor

deallocate cursor db_cursor

return
[[category:ASE]]

RepServer Throughput Script

2009-02-20T11:15:17Z

Bobh:

If you want to know just how much data repserver is processing, then this is the tool. 
This script will tell you: 
o average throughput per queue (mb per minute) 
o average throughput combined (all queues) 
o estimated time for a replicate to catch up (Re-sync ETA) 
o current queue sizes 
There is also a maximum recorded throughput option but this appears not to work well as some of the figures have been suspicious. 
The real maximum throughput can be established by noting when the replicates start to fall behind in conjunction with the current throughput figures. 
This script can be particularly useful in helping to ascertain if you need to throw more CPU at your repserver box.

Notes: 
1) You will need to cron this script with the insert option (rs_throughput.sh insert) and allow it to collect at least 2 samples before being able to run it interactively to obtain throughput information. The script runs silently when run with the insert option. 
2) The script automatically determines the elapsed interval in minutes between samples so you can cron the script to your preferred interval, be it 3 minutes or 10 minutes, the mb/min figure will be calculated correctly. 
3) In addition to configuring the script to your environment, you will also need to create the rs_throughput table prior to first run. 
4) Re-sync ETA: this value is provided based on the current throughput average for the period specified. For example, this means that running rs_throughput.sh 15 may give a different Re-Sync ETA compared to running rs_throughput.sh 20
<pre>
#!/usr/bin/ksh
#
# Script: rs_throughput.sh
# Author: Bob Holmes - email: cambob@gmail.com
# Date : 19/09/08
# Version : 1.0
# Usage : Run "rs_throughput.sh help" for full usage information.
# Description : This script enables monitoring and reporting
# of repserver throughput.
# --------------------------
# *** Variables for customisation can be found by searching for "!!" ***
# --------------------------
# Modification history:
# 19/02/09 : Bob Holmes : Script modified to enable easier customisation prior
# to release on Sybase wiki. (modifications not tested)
# --------------------------
# Comments: This script relies on the reporting..rs_throughput table for data
# collection and reporting. The definition for this table can be
# found at the end of this script.
##############################################################################
# setup environment #
##############################################################################
#
# save input variables
if ! [[ -z $1 ]]
then
export option1=$1
else
export option1="all"
fi

if ! [[ -z $2 ]]
then
export option2=$2
else
export option2="15" # default number of minutes on which to report throughput
fi

# configure environment
HOSTNAME=`hostname`
. $HOME/admin/.syb_cfg.sh $HOSTNAME # !! This line sets up the environment from
# !! from an external shell script.

##############################################################################
# config variables #
##############################################################################
# config:
primary_ds="prim_ds" # !! string used to identify the inbound queue
# queues to query on:-
q_list="prim_ds.db1 prim_ds.db2 replicate_ds.db1 replicate_ds.db2" # !!
insert_filter="db1 db2" # !! data will only be collected for queue names which have this keyword in
REP_SRV="repsrv_rs" # !! the actual repserver from which to get sqm info
RPT_SRV="reporting_ds" # !! dataserver which manages the reporting database
RPT_DB="reporting" # !! reporting database name
RECIPIENTS="user@domain" # !! email config (separate addresses with commas)
ADMINDIR="/opt/home/sybase/admin" # !! sybase admin directory

# static:
SQMFILE="$ADMINDIR/rs_sqm_tmp1.tmp"
DATE=$(date "+%d/%m/%y %H:%M:%S")

# config aware environment variables: # !! You may need to replace the line below with the equivalent
# !! for your own environment.
RISQLCMD="isql -Usa -P`grep "$REP_SRV," $HOME/admin/.servers | cut -d , -f 4` -S$REP_SRV -w1000"
ISQLCMD="isql -Usa -P${SYBPASS} -S${RPT_SRV} -w200 -D${RPT_DB}"

##############################################################################
# functions #
##############################################################################

check_previous_instance()
{
# Note: next line greps for 8 instances as tests proved that less could cause the script to exit incorrectly
if [ $(ps -ef | grep rs_throughput | egrep -v "vi|grep" | grep -v "sh -c" | wc -l | awk '{print $1}') -gt 8 ]
then
# previous instance still running - probably hanging
echo Previous instance still running.
exit
fi
}

fn_checkusage()
{
if [[ -z $option1 ]]
then
printf " Usage: ./rs_throughput.sh <all>|<q_name>|<help> [<minutes to display>]\n"
exit
fi
}

bcalc()
{
awk 'BEGIN{EQUATION='"$*"';printf("%0.1f\n",EQUATION); exit}'
}

fn_housekeeping()
{
if [ -e $SQMFILE ]
then
rm $SQMFILE
fi
$ISQLCMD <<-EOF
set nocount on
go
delete from rs_throughput where sample_time <= dateadd(dd,-28,getdate())
go
EOF
}

fn_insert_thruput_data()
{
# 1) get current segment usage/progress info
#connect to repserver, get sqm info - put into file
$RISQLCMD << eof > $SQMFILE
admin who,sqm
go
quit
eof

# 2) insert new segment data into rs_throughput table
#date
#echo "Queue Name, First segment, Last segment, Next segment"
cat $SQMFILE | egrep $insert_filter | sed 's/Awaiting Message/AwaitingMessage/; s/Awaiting Wakeup/AwaitingWakeup/; s/$\.[0-9][0-

9]*$\.[0-9]/\1/' | grep -v ":0 ${primary_ds}" | awk '{print $4,$14,$15,$16}' | while read q_name first_seg last_seg next_seg
do
q_size=$(bcalc "$last_seg - $first_seg")
$ISQLCMD <<-EOF
set nocount on
go
declare @mb_processed numeric(8,2)
declare @prev_sample datetime
declare @mins numeric(8,2)
declare @tput numeric(8,2)
if not exists (select 1 from rs_throughput where q_name = "${q_name}")
insert rs_throughput values ("${q_name}", getdate(), ${first_seg}, ${last_seg}, ${next_seg}, ${q_size}, 0, 0)
else
begin
-- get previous sample_time as key
select @prev_sample = max(sample_time) from rs_throughput where q_name = "${q_name}"
-- get difference in mb between current next segment and previous next segment
select @mb_processed = (select ${next_seg} - next from rs_throughput where sample_time = @prev_sample and q_name = "${q_name}")

select @prev_sample=dateadd(ss,-10,@prev_sample) -- # must substract 10 seconds to ensure the next datediff doesn't reflect 2m 58s

as only 2mins!
select @mins = datediff(mi,@prev_sample,getdate())
if @mins > 0
begin
select @tput = round((@mb_processed / @mins),2)
insert rs_throughput values ("${q_name}", getdate(), ${first_seg}, ${last_seg}, ${next_seg}, ${q_size}, @mb_processed, @tput)
end
end
go
EOF
done
}

fn_report_thruput()
{
if [[ $option1 = [0-9]* ]]
then
option2=$option1
option1="all"
fi

if ! [[ "$option1" = "all" ]]
then
q_list=$option1
fi

printf " -------------------------------------\n"
printf " Run date $DATE\n"
printf " -------------------------------------\n"

for q_name in $q_list
do
$ISQLCMD <<-EOF
set nocount on
declare @mb_avg numeric(8,2)
declare @size numeric(8,2)
declare @eta datetime
select convert(char(19),sample_time) sample_time, q_name, size q_size, mb_processed, thruput "thruput (mb/min)" from rs_throughput
--select convert(char(19),sample_time) sample_time, q_name, size q_size, mb_processed, thruput "thruput (mb/min)", dateadd(mi,

(round((size/thruput),0)),getdate()) ETA from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name like "%${q_name}%"

--get mb average
select @mb_avg = convert(numeric(8,2),avg(thruput)) from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name like "%${q_name}%"
--get current queue size
select @size = size from rs_throughput where q_name like "%${q_name}%" having sample_time=max(sample_time)
select @eta = dateadd(mi,round((@size/@mb_avg),0),getdate())
print ""
print " Average for ${q_name}: %1! mb/min, Re-sync ETA: %2!", @mb_avg, @eta
print ""
go
EOF
done
}

fn_show_help_text()
{
printf "Usage examples:\n"
printf "rs_throughput.sh (runs with default values showing all queues over 15 minutes)\n"
printf "rs_throughput.sh <q_name>|<minutes>\n"
printf "rs_throughput.sh <q_name> <minutes>\n"
printf "rs_throughput.sh size|queue (shows current queue sizes)\n"
printf "rs_throughput.sh max (shows highest ever throughput for queue history)\n"
printf "rs_throughput.sh help (shows this helptext)\n"
printf "rs_throughput.sh insert # this is for cron only (inserts data to $RPT_DB..rs_throughput table)\n"
}

fn_show_max_thruput()
{
$ISQLCMD <<-EOF
print ""
set rowcount 0
set nocount on
declare @q_name char(22)
declare @sample_time datetime
declare @max_thruput numeric(8,2)
select q_name, sample_time, thruput into #throughput from rs_throughput where 1=0
print "Maximum throughput per queue:"
-- get list of queues
select distinct q_name, 0 as processed into #queues from rs_throughput
set rowcount 1
select @q_name=q_name from #queues where processed = 0
while @@rowcount != 0
begin
insert #throughput select q_name, sample_time, thruput from rs_throughput
where sample_time in
(select sample_time from rs_throughput where q_name = @q_name having thruput = max(thruput))
and q_name = @q_name
update #queues set processed = 1 where q_name = @q_name
select @q_name=q_name from #queues where processed = 0
end
set rowcount 0
select * from #throughput order by thruput desc
go
EOF
echo
}

fn_show_current_q_size()
{
$RISQLCMD << eof > $SQMFILE
admin who,sqm
go
quit
eof
printf "Current Stable Queue usage:\n"
cat $SQMFILE | egrep $insert_filter | sed 's/Awaiting Message/AwaitingMessage/; s/Awaiting Wakeup/AwaitingWakeup/; s/$\.[0-9][0-

9]*$\.[0-9]/\1/' | grep -v ":0 ${primary_ds}" | awk '{print $4,$14,$15,$16}' | while read q_name first_seg last_seg next_seg
do
q_size=$(bcalc "$last_seg - $first_seg")
printf "$q_name: $q_size\n"
done
}

fn_show_combined_avg_thruput()
{
$ISQLCMD <<-EOF
set rowcount 0
set nocount on
declare @q_name varchar(25)
declare @mb_avg numeric(8,2)
declare @mb_combined_avg numeric(8,2)
select @mb_combined_avg=0
-- get list of queues

select distinct q_name, 0 as processed into #queues from rs_throughput
set rowcount 1
select @q_name=q_name from #queues where processed = 0
while @@rowcount != 0
begin
select @mb_avg = convert(numeric(8,2),avg(thruput)) from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name = @q_name
select @mb_combined_avg = @mb_combined_avg + @mb_avg
update #queues set processed=1 where q_name = @q_name
select @q_name=q_name from #queues where processed = 0
end
print " Combined thruput average: %1! mb/min", @mb_combined_avg
print ""
go
EOF
}

##############################################################################
# main program #
##############################################################################

check_previous_instance
fn_checkusage

case "$option1" in
help) fn_show_help_text;
exit;;

insert) fn_insert_thruput_data;
exit;;

max) fn_show_max_thruput;
exit;;

queue|size) fn_show_current_q_size;
exit;;

*) fn_report_thruput;
fn_show_combined_avg_thruput;;
esac

fn_housekeeping

### TABLE DEFINTION FOR RAW DATA COLLECTION
### Use the table definition below to create the table which this script
### requires in order to insert queue data and furthermore query this
### data in order to provide throughput information.
#create table rs_throughput (
#q_name varchar(25),
#sample_time datetime,
#first numeric(8,2),
#last numeric(8,2),
#next numeric(8,2),
#size numeric(8,2),
#mb_processed numeric(8,2),
#thruput numeric(8,2)
#)

# end program
</pre>

[[Category:RepServer]]

RepServer Throughput Script

2009-02-20T11:14:46Z

Bobh:

If you want to know just how much data repserver is processing, then this is the tool. 
This script will tell you: 
o average throughput per queue (mb per minute) 
o average throughput combined (all queues) 
o estimated time for a replicate to catch up (Re-sync ETA) 
o current queue sizes 
There is also a maximum recorded throughput option but this appears not to work well as some of the figures have been suspicious. 
The real maximum throughput can be established by noting when the replicates start to fall behind in conjunction with the current throughput figures. 
This script can be particularly useful in helping to ascertain if you need to throw more CPU at your repserver box.

Notes: 
1) You will need to cron this script with the insert option (rs_throughput.sh insert) and allow it to collect at least 2 samples before being able to run it interactively to obtain throughput information. The script runs silently when run with the insert option. 
2) The script automatically determines the elapsed interval in minutes between samples so you can cron the script to your preferred interval, be it 3 minutes or 10 minutes, the mb/min figure will be calculated correctly. 
3) In addition to configuring the script to your environment, you will also need to create the rs_throughput table prior to first run.
4) Re-sync ETA: this value is provided based on the current throughput average for the period specified. For example, this means that running rs_throughput.sh 15 may give a different Re-Sync ETA compared to running rs_throughput.sh 20
<pre>
#!/usr/bin/ksh
#
# Script: rs_throughput.sh
# Author: Bob Holmes - email: cambob@gmail.com
# Date : 19/09/08
# Version : 1.0
# Usage : Run "rs_throughput.sh help" for full usage information.
# Description : This script enables monitoring and reporting
# of repserver throughput.
# --------------------------
# *** Variables for customisation can be found by searching for "!!" ***
# --------------------------
# Modification history:
# 19/02/09 : Bob Holmes : Script modified to enable easier customisation prior
# to release on Sybase wiki. (modifications not tested)
# --------------------------
# Comments: This script relies on the reporting..rs_throughput table for data
# collection and reporting. The definition for this table can be
# found at the end of this script.
##############################################################################
# setup environment #
##############################################################################
#
# save input variables
if ! [[ -z $1 ]]
then
export option1=$1
else
export option1="all"
fi

if ! [[ -z $2 ]]
then
export option2=$2
else
export option2="15" # default number of minutes on which to report throughput
fi

# configure environment
HOSTNAME=`hostname`
. $HOME/admin/.syb_cfg.sh $HOSTNAME # !! This line sets up the environment from
# !! from an external shell script.

##############################################################################
# config variables #
##############################################################################
# config:
primary_ds="prim_ds" # !! string used to identify the inbound queue
# queues to query on:-
q_list="prim_ds.db1 prim_ds.db2 replicate_ds.db1 replicate_ds.db2" # !!
insert_filter="db1 db2" # !! data will only be collected for queue names which have this keyword in
REP_SRV="repsrv_rs" # !! the actual repserver from which to get sqm info
RPT_SRV="reporting_ds" # !! dataserver which manages the reporting database
RPT_DB="reporting" # !! reporting database name
RECIPIENTS="user@domain" # !! email config (separate addresses with commas)
ADMINDIR="/opt/home/sybase/admin" # !! sybase admin directory

# static:
SQMFILE="$ADMINDIR/rs_sqm_tmp1.tmp"
DATE=$(date "+%d/%m/%y %H:%M:%S")

# config aware environment variables: # !! You may need to replace the line below with the equivalent
# !! for your own environment.
RISQLCMD="isql -Usa -P`grep "$REP_SRV," $HOME/admin/.servers | cut -d , -f 4` -S$REP_SRV -w1000"
ISQLCMD="isql -Usa -P${SYBPASS} -S${RPT_SRV} -w200 -D${RPT_DB}"

##############################################################################
# functions #
##############################################################################

check_previous_instance()
{
# Note: next line greps for 8 instances as tests proved that less could cause the script to exit incorrectly
if [ $(ps -ef | grep rs_throughput | egrep -v "vi|grep" | grep -v "sh -c" | wc -l | awk '{print $1}') -gt 8 ]
then
# previous instance still running - probably hanging
echo Previous instance still running.
exit
fi
}

fn_checkusage()
{
if [[ -z $option1 ]]
then
printf " Usage: ./rs_throughput.sh <all>|<q_name>|<help> [<minutes to display>]\n"
exit
fi
}

bcalc()
{
awk 'BEGIN{EQUATION='"$*"';printf("%0.1f\n",EQUATION); exit}'
}

fn_housekeeping()
{
if [ -e $SQMFILE ]
then
rm $SQMFILE
fi
$ISQLCMD <<-EOF
set nocount on
go
delete from rs_throughput where sample_time <= dateadd(dd,-28,getdate())
go
EOF
}

fn_insert_thruput_data()
{
# 1) get current segment usage/progress info
#connect to repserver, get sqm info - put into file
$RISQLCMD << eof > $SQMFILE
admin who,sqm
go
quit
eof

# 2) insert new segment data into rs_throughput table
#date
#echo "Queue Name, First segment, Last segment, Next segment"
cat $SQMFILE | egrep $insert_filter | sed 's/Awaiting Message/AwaitingMessage/; s/Awaiting Wakeup/AwaitingWakeup/; s/$\.[0-9][0-

9]*$\.[0-9]/\1/' | grep -v ":0 ${primary_ds}" | awk '{print $4,$14,$15,$16}' | while read q_name first_seg last_seg next_seg
do
q_size=$(bcalc "$last_seg - $first_seg")
$ISQLCMD <<-EOF
set nocount on
go
declare @mb_processed numeric(8,2)
declare @prev_sample datetime
declare @mins numeric(8,2)
declare @tput numeric(8,2)
if not exists (select 1 from rs_throughput where q_name = "${q_name}")
insert rs_throughput values ("${q_name}", getdate(), ${first_seg}, ${last_seg}, ${next_seg}, ${q_size}, 0, 0)
else
begin
-- get previous sample_time as key
select @prev_sample = max(sample_time) from rs_throughput where q_name = "${q_name}"
-- get difference in mb between current next segment and previous next segment
select @mb_processed = (select ${next_seg} - next from rs_throughput where sample_time = @prev_sample and q_name = "${q_name}")

select @prev_sample=dateadd(ss,-10,@prev_sample) -- # must substract 10 seconds to ensure the next datediff doesn't reflect 2m 58s

as only 2mins!
select @mins = datediff(mi,@prev_sample,getdate())
if @mins > 0
begin
select @tput = round((@mb_processed / @mins),2)
insert rs_throughput values ("${q_name}", getdate(), ${first_seg}, ${last_seg}, ${next_seg}, ${q_size}, @mb_processed, @tput)
end
end
go
EOF
done
}

fn_report_thruput()
{
if [[ $option1 = [0-9]* ]]
then
option2=$option1
option1="all"
fi

if ! [[ "$option1" = "all" ]]
then
q_list=$option1
fi

printf " -------------------------------------\n"
printf " Run date $DATE\n"
printf " -------------------------------------\n"

for q_name in $q_list
do
$ISQLCMD <<-EOF
set nocount on
declare @mb_avg numeric(8,2)
declare @size numeric(8,2)
declare @eta datetime
select convert(char(19),sample_time) sample_time, q_name, size q_size, mb_processed, thruput "thruput (mb/min)" from rs_throughput
--select convert(char(19),sample_time) sample_time, q_name, size q_size, mb_processed, thruput "thruput (mb/min)", dateadd(mi,

(round((size/thruput),0)),getdate()) ETA from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name like "%${q_name}%"

--get mb average
select @mb_avg = convert(numeric(8,2),avg(thruput)) from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name like "%${q_name}%"
--get current queue size
select @size = size from rs_throughput where q_name like "%${q_name}%" having sample_time=max(sample_time)
select @eta = dateadd(mi,round((@size/@mb_avg),0),getdate())
print ""
print " Average for ${q_name}: %1! mb/min, Re-sync ETA: %2!", @mb_avg, @eta
print ""
go
EOF
done
}

fn_show_help_text()
{
printf "Usage examples:\n"
printf "rs_throughput.sh (runs with default values showing all queues over 15 minutes)\n"
printf "rs_throughput.sh <q_name>|<minutes>\n"
printf "rs_throughput.sh <q_name> <minutes>\n"
printf "rs_throughput.sh size|queue (shows current queue sizes)\n"
printf "rs_throughput.sh max (shows highest ever throughput for queue history)\n"
printf "rs_throughput.sh help (shows this helptext)\n"
printf "rs_throughput.sh insert # this is for cron only (inserts data to $RPT_DB..rs_throughput table)\n"
}

fn_show_max_thruput()
{
$ISQLCMD <<-EOF
print ""
set rowcount 0
set nocount on
declare @q_name char(22)
declare @sample_time datetime
declare @max_thruput numeric(8,2)
select q_name, sample_time, thruput into #throughput from rs_throughput where 1=0
print "Maximum throughput per queue:"
-- get list of queues
select distinct q_name, 0 as processed into #queues from rs_throughput
set rowcount 1
select @q_name=q_name from #queues where processed = 0
while @@rowcount != 0
begin
insert #throughput select q_name, sample_time, thruput from rs_throughput
where sample_time in
(select sample_time from rs_throughput where q_name = @q_name having thruput = max(thruput))
and q_name = @q_name
update #queues set processed = 1 where q_name = @q_name
select @q_name=q_name from #queues where processed = 0
end
set rowcount 0
select * from #throughput order by thruput desc
go
EOF
echo
}

fn_show_current_q_size()
{
$RISQLCMD << eof > $SQMFILE
admin who,sqm
go
quit
eof
printf "Current Stable Queue usage:\n"
cat $SQMFILE | egrep $insert_filter | sed 's/Awaiting Message/AwaitingMessage/; s/Awaiting Wakeup/AwaitingWakeup/; s/$\.[0-9][0-

9]*$\.[0-9]/\1/' | grep -v ":0 ${primary_ds}" | awk '{print $4,$14,$15,$16}' | while read q_name first_seg last_seg next_seg
do
q_size=$(bcalc "$last_seg - $first_seg")
printf "$q_name: $q_size\n"
done
}

fn_show_combined_avg_thruput()
{
$ISQLCMD <<-EOF
set rowcount 0
set nocount on
declare @q_name varchar(25)
declare @mb_avg numeric(8,2)
declare @mb_combined_avg numeric(8,2)
select @mb_combined_avg=0
-- get list of queues

select distinct q_name, 0 as processed into #queues from rs_throughput
set rowcount 1
select @q_name=q_name from #queues where processed = 0
while @@rowcount != 0
begin
select @mb_avg = convert(numeric(8,2),avg(thruput)) from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name = @q_name
select @mb_combined_avg = @mb_combined_avg + @mb_avg
update #queues set processed=1 where q_name = @q_name
select @q_name=q_name from #queues where processed = 0
end
print " Combined thruput average: %1! mb/min", @mb_combined_avg
print ""
go
EOF
}

##############################################################################
# main program #
##############################################################################

check_previous_instance
fn_checkusage

case "$option1" in
help) fn_show_help_text;
exit;;

insert) fn_insert_thruput_data;
exit;;

max) fn_show_max_thruput;
exit;;

queue|size) fn_show_current_q_size;
exit;;

*) fn_report_thruput;
fn_show_combined_avg_thruput;;
esac

fn_housekeeping

### TABLE DEFINTION FOR RAW DATA COLLECTION
### Use the table definition below to create the table which this script
### requires in order to insert queue data and furthermore query this
### data in order to provide throughput information.
#create table rs_throughput (
#q_name varchar(25),
#sample_time datetime,
#first numeric(8,2),
#last numeric(8,2),
#next numeric(8,2),
#size numeric(8,2),
#mb_processed numeric(8,2),
#thruput numeric(8,2)
#)

# end program
</pre>

[[Category:RepServer]]

RepServer Throughput Script

2009-02-20T09:44:48Z

Bobh:

If you want to know just how much data repserver is processing, then this is the tool. 
This script will tell you: 
o average throughput per queue (mb per minute) 
o average throughput combined (all queues) 
o estimated time for a replicate to catch up 
o current queue sizes 
There is also a maximum recorded throughput option but this appears not to work well as some of the figures have been suspicious. 
The real maximum throughput can be established by noting when the replicates start to fall behind in conjunction with the current throughput figures. 
This script can be particularly useful in helping to ascertain if you need to throw more CPU at your repserver box.

Notes: 
1) You will need to cron this script with the insert option (rs_throughput.sh insert) and allow it to collect at least 2 samples before being able to run it interactively to obtain throughput information. The script runs silently when run with the insert option. 
2) The script automatically determines the elapsed interval in minutes between samples so you can cron the script to your preferred interval, be it 3 minutes or 10 minutes, the mb/min figure will be calculated correctly. 
3) In addition to configuring the script to your environment, you will also need to create the rs_throughput table prior to first run.
<pre>
#!/usr/bin/ksh
#
# Script: rs_throughput.sh
# Author: Bob Holmes - email: cambob@gmail.com
# Date : 19/09/08
# Version : 1.0
# Usage : Run "rs_throughput.sh help" for full usage information.
# Description : This script enables monitoring and reporting
# of repserver throughput.
# --------------------------
# *** Variables for customisation can be found by searching for "!!" ***
# --------------------------
# Modification history:
# 19/02/09 : Bob Holmes : Script modified to enable easier customisation prior
# to release on Sybase wiki. (modifications not tested)
# --------------------------
# Comments: This script relies on the reporting..rs_throughput table for data
# collection and reporting. The definition for this table can be
# found at the end of this script.
##############################################################################
# setup environment #
##############################################################################
#
# save input variables
if ! [[ -z $1 ]]
then
export option1=$1
else
export option1="all"
fi

if ! [[ -z $2 ]]
then
export option2=$2
else
export option2="15" # default number of minutes on which to report throughput
fi

# configure environment
HOSTNAME=`hostname`
. $HOME/admin/.syb_cfg.sh $HOSTNAME # !! This line sets up the environment from
# !! from an external shell script.

##############################################################################
# config variables #
##############################################################################
# config:
primary_ds="prim_ds" # !! string used to identify the inbound queue
# queues to query on:-
q_list="prim_ds.db1 prim_ds.db2 replicate_ds.db1 replicate_ds.db2" # !!
insert_filter="db1 db2" # !! data will only be collected for queue names which have this keyword in
REP_SRV="repsrv_rs" # !! the actual repserver from which to get sqm info
RPT_SRV="reporting_ds" # !! dataserver which manages the reporting database
RPT_DB="reporting" # !! reporting database name
RECIPIENTS="user@domain" # !! email config (separate addresses with commas)
ADMINDIR="/opt/home/sybase/admin" # !! sybase admin directory

# static:
SQMFILE="$ADMINDIR/rs_sqm_tmp1.tmp"
DATE=$(date "+%d/%m/%y %H:%M:%S")

# config aware environment variables: # !! You may need to replace the line below with the equivalent
# !! for your own environment.
RISQLCMD="isql -Usa -P`grep "$REP_SRV," $HOME/admin/.servers | cut -d , -f 4` -S$REP_SRV -w1000"
ISQLCMD="isql -Usa -P${SYBPASS} -S${RPT_SRV} -w200 -D${RPT_DB}"

##############################################################################
# functions #
##############################################################################

check_previous_instance()
{
# Note: next line greps for 8 instances as tests proved that less could cause the script to exit incorrectly
if [ $(ps -ef | grep rs_throughput | egrep -v "vi|grep" | grep -v "sh -c" | wc -l | awk '{print $1}') -gt 8 ]
then
# previous instance still running - probably hanging
echo Previous instance still running.
exit
fi
}

fn_checkusage()
{
if [[ -z $option1 ]]
then
printf " Usage: ./rs_throughput.sh <all>|<q_name>|<help> [<minutes to display>]\n"
exit
fi
}

bcalc()
{
awk 'BEGIN{EQUATION='"$*"';printf("%0.1f\n",EQUATION); exit}'
}

fn_housekeeping()
{
if [ -e $SQMFILE ]
then
rm $SQMFILE
fi
$ISQLCMD <<-EOF
set nocount on
go
delete from rs_throughput where sample_time <= dateadd(dd,-28,getdate())
go
EOF
}

fn_insert_thruput_data()
{
# 1) get current segment usage/progress info
#connect to repserver, get sqm info - put into file
$RISQLCMD << eof > $SQMFILE
admin who,sqm
go
quit
eof

# 2) insert new segment data into rs_throughput table
#date
#echo "Queue Name, First segment, Last segment, Next segment"
cat $SQMFILE | egrep $insert_filter | sed 's/Awaiting Message/AwaitingMessage/; s/Awaiting Wakeup/AwaitingWakeup/; s/$\.[0-9][0-

9]*$\.[0-9]/\1/' | grep -v ":0 ${primary_ds}" | awk '{print $4,$14,$15,$16}' | while read q_name first_seg last_seg next_seg
do
q_size=$(bcalc "$last_seg - $first_seg")
$ISQLCMD <<-EOF
set nocount on
go
declare @mb_processed numeric(8,2)
declare @prev_sample datetime
declare @mins numeric(8,2)
declare @tput numeric(8,2)
if not exists (select 1 from rs_throughput where q_name = "${q_name}")
insert rs_throughput values ("${q_name}", getdate(), ${first_seg}, ${last_seg}, ${next_seg}, ${q_size}, 0, 0)
else
begin
-- get previous sample_time as key
select @prev_sample = max(sample_time) from rs_throughput where q_name = "${q_name}"
-- get difference in mb between current next segment and previous next segment
select @mb_processed = (select ${next_seg} - next from rs_throughput where sample_time = @prev_sample and q_name = "${q_name}")

select @prev_sample=dateadd(ss,-10,@prev_sample) -- # must substract 10 seconds to ensure the next datediff doesn't reflect 2m 58s

as only 2mins!
select @mins = datediff(mi,@prev_sample,getdate())
if @mins > 0
begin
select @tput = round((@mb_processed / @mins),2)
insert rs_throughput values ("${q_name}", getdate(), ${first_seg}, ${last_seg}, ${next_seg}, ${q_size}, @mb_processed, @tput)
end
end
go
EOF
done
}

fn_report_thruput()
{
if [[ $option1 = [0-9]* ]]
then
option2=$option1
option1="all"
fi

if ! [[ "$option1" = "all" ]]
then
q_list=$option1
fi

printf " -------------------------------------\n"
printf " Run date $DATE\n"
printf " -------------------------------------\n"

for q_name in $q_list
do
$ISQLCMD <<-EOF
set nocount on
declare @mb_avg numeric(8,2)
declare @size numeric(8,2)
declare @eta datetime
select convert(char(19),sample_time) sample_time, q_name, size q_size, mb_processed, thruput "thruput (mb/min)" from rs_throughput
--select convert(char(19),sample_time) sample_time, q_name, size q_size, mb_processed, thruput "thruput (mb/min)", dateadd(mi,

(round((size/thruput),0)),getdate()) ETA from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name like "%${q_name}%"

--get mb average
select @mb_avg = convert(numeric(8,2),avg(thruput)) from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name like "%${q_name}%"
--get current queue size
select @size = size from rs_throughput where q_name like "%${q_name}%" having sample_time=max(sample_time)
select @eta = dateadd(mi,round((@size/@mb_avg),0),getdate())
print ""
print " Average for ${q_name}: %1! mb/min, Re-sync ETA: %2!", @mb_avg, @eta
print ""
go
EOF
done
}

fn_show_help_text()
{
printf "Usage examples:\n"
printf "rs_throughput.sh (runs with default values showing all queues over 15 minutes)\n"
printf "rs_throughput.sh <q_name>|<minutes>\n"
printf "rs_throughput.sh <q_name> <minutes>\n"
printf "rs_throughput.sh size|queue (shows current queue sizes)\n"
printf "rs_throughput.sh max (shows highest ever throughput for queue history)\n"
printf "rs_throughput.sh help (shows this helptext)\n"
printf "rs_throughput.sh insert # this is for cron only (inserts data to $RPT_DB..rs_throughput table)\n"
}

fn_show_max_thruput()
{
$ISQLCMD <<-EOF
print ""
set rowcount 0
set nocount on
declare @q_name char(22)
declare @sample_time datetime
declare @max_thruput numeric(8,2)
select q_name, sample_time, thruput into #throughput from rs_throughput where 1=0
print "Maximum throughput per queue:"
-- get list of queues
select distinct q_name, 0 as processed into #queues from rs_throughput
set rowcount 1
select @q_name=q_name from #queues where processed = 0
while @@rowcount != 0
begin
insert #throughput select q_name, sample_time, thruput from rs_throughput
where sample_time in
(select sample_time from rs_throughput where q_name = @q_name having thruput = max(thruput))
and q_name = @q_name
update #queues set processed = 1 where q_name = @q_name
select @q_name=q_name from #queues where processed = 0
end
set rowcount 0
select * from #throughput order by thruput desc
go
EOF
echo
}

fn_show_current_q_size()
{
$RISQLCMD << eof > $SQMFILE
admin who,sqm
go
quit
eof
printf "Current Stable Queue usage:\n"
cat $SQMFILE | egrep $insert_filter | sed 's/Awaiting Message/AwaitingMessage/; s/Awaiting Wakeup/AwaitingWakeup/; s/$\.[0-9][0-

9]*$\.[0-9]/\1/' | grep -v ":0 ${primary_ds}" | awk '{print $4,$14,$15,$16}' | while read q_name first_seg last_seg next_seg
do
q_size=$(bcalc "$last_seg - $first_seg")
printf "$q_name: $q_size\n"
done
}

fn_show_combined_avg_thruput()
{
$ISQLCMD <<-EOF
set rowcount 0
set nocount on
declare @q_name varchar(25)
declare @mb_avg numeric(8,2)
declare @mb_combined_avg numeric(8,2)
select @mb_combined_avg=0
-- get list of queues

select distinct q_name, 0 as processed into #queues from rs_throughput
set rowcount 1
select @q_name=q_name from #queues where processed = 0
while @@rowcount != 0
begin
select @mb_avg = convert(numeric(8,2),avg(thruput)) from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name = @q_name
select @mb_combined_avg = @mb_combined_avg + @mb_avg
update #queues set processed=1 where q_name = @q_name
select @q_name=q_name from #queues where processed = 0
end
print " Combined thruput average: %1! mb/min", @mb_combined_avg
print ""
go
EOF
}

##############################################################################
# main program #
##############################################################################

check_previous_instance
fn_checkusage

case "$option1" in
help) fn_show_help_text;
exit;;

insert) fn_insert_thruput_data;
exit;;

max) fn_show_max_thruput;
exit;;

queue|size) fn_show_current_q_size;
exit;;

*) fn_report_thruput;
fn_show_combined_avg_thruput;;
esac

fn_housekeeping

### TABLE DEFINTION FOR RAW DATA COLLECTION
### Use the table definition below to create the table which this script
### requires in order to insert queue data and furthermore query this
### data in order to provide throughput information.
#create table rs_throughput (
#q_name varchar(25),
#sample_time datetime,
#first numeric(8,2),
#last numeric(8,2),
#next numeric(8,2),
#size numeric(8,2),
#mb_processed numeric(8,2),
#thruput numeric(8,2)
#)

# end program
</pre>

[[Category:RepServer]]

RepServer Throughput Script

2009-02-20T09:41:31Z

Bobh:

If you want to know just how much data repserver is processing, then this is the tool. 
This script will tell you: 
o average throughput per queue (mb per minute) 
o average throughput combined (all queues) 
o estimated time for a replicate to catch up 
o current queue sizes 
There is also a maximum recorded throughput option but this appears not to work well as some of the figures have been suspicious. 
The real maximum throughput can be established by noting when the replicates start to fall behind in conjunction with the current throughput figures. 
This script can be particularly useful in helping to ascertain if you need to throw more CPU at your repserver box.

Notes: 
1) You will need to cron this script with the insert option (rs_throughput.sh insert) and allow it to collect at least 2 samples before being able to run it interactively to obtain throughput information. The script runs silently when run with the insert option. 
2) The script automatically determines the elapsed interval in minutes between samples so you can cron the script to your preferred interval, be it 3 minutes or 10 minutes, the mb/min figure will be calculated correctly. 
3) In addition to configuring the script to your environment, you will also need to create the rs_throughput table prior to first run.
<pre>
#!/usr/bin/ksh
#
# Script: rs_throughput.sh
# Author: Bob Holmes - email: cambob@gmail.com
# Date : 19/09/08
# Version : 1.0
# Usage : Run "rs_throughput.sh help" for full usage information.
# Description : This script enables monitoring and reporting
# of repserver throughput.
# --------------------------
# *** Variables for customisation can be found by searching for "!!" ***
# --------------------------
# Modification history:
# 19/02/09 : Bob Holmes : Script modified to enable easier customisation prior
# to release on Sybase wiki. (modifications not tested)
# --------------------------
# Comments: This script relies on the reporting..rs_throughput table for data
# collection and reporting. The definition for this table can be
# found at the end of this script.
##############################################################################
# setup environment #
##############################################################################
#
# save input variables
if ! [[ -z $1 ]]
then
export option1=$1
else
export option1="all"
fi

if ! [[ -z $2 ]]
then
export option2=$2
else
export option2="15" # default number of minutes on which to report throughput
fi

# configure environment
HOSTNAME=`hostname`
. $HOME/admin/.syb_cfg.sh $HOSTNAME # !! This line sets up the environment from
# !! from an external shell script.

##############################################################################
# config variables #
##############################################################################
# config:
primary_ds="prim_ds" # !! string used to identify the inbound queue
# queues to query on:-
q_list="prim_ds.db1 prim_ds.db2 replicate_ds.db1 replicate_ds.db2" # !!
insert_filter="db1 db2" # !! data will only be collected for queue names which have this keyword in
REP_SRV="repsrv_rs" # !! the actual repserver from which to get sqm info
RPT_SRV="reporting_ds" # !! dataserver which manages the reporting database
RPT_DB="reporting" # !! reporting database name
RECIPIENTS="user@domain" # !! email config (separate addresses with commas)
ADMINDIR="/opt/home/sybase/admin" # !! sybase admin directory

# static:
SQMFILE="$ADMINDIR/rs_sqm_tmp1.tmp"
DATE=$(date "+%d/%m/%y %H:%M:%S")

# config aware environment variables: # !! You may need to replace the line below with the equivalent
# !! for your own environment.
RISQLCMD="isql -Usa -P`grep "$REP_SRV," $HOME/admin/.servers | cut -d , -f 4` -S$rep_server -w1000"
ISQLCMD="isql -Usa -P${SYBPASS} -S${RPT_SRV} -w200 -D${RPT_DB}"

##############################################################################
# functions #
##############################################################################

check_previous_instance()
{
# Note: next line greps for 8 instances as tests proved that less could cause the script to exit incorrectly
if [ $(ps -ef | grep rs_throughput | egrep -v "vi|grep" | grep -v "sh -c" | wc -l | awk '{print $1}') -gt 8 ]
then
# previous instance still running - probably hanging
echo Previous instance still running.
exit
fi
}

fn_checkusage()
{
if [[ -z $option1 ]]
then
printf " Usage: ./rs_throughput.sh <all>|<q_name>|<help> [<minutes to display>]\n"
exit
fi
}

bcalc()
{
awk 'BEGIN{EQUATION='"$*"';printf("%0.1f\n",EQUATION); exit}'
}

fn_housekeeping()
{
if [ -e $SQMFILE ]
then
rm $SQMFILE
fi
$ISQLCMD <<-EOF
set nocount on
go
delete from rs_throughput where sample_time <= dateadd(dd,-28,getdate())
go
EOF
}

fn_insert_thruput_data()
{
# 1) get current segment usage/progress info
#connect to repserver, get sqm info - put into file
$RISQLCMD << eof > $SQMFILE
admin who,sqm
go
quit
eof

# 2) insert new segment data into rs_throughput table
#date
#echo "Queue Name, First segment, Last segment, Next segment"
cat $SQMFILE | egrep $insert_filter | sed 's/Awaiting Message/AwaitingMessage/; s/Awaiting Wakeup/AwaitingWakeup/; s/$\.[0-9][0-

9]*$\.[0-9]/\1/' | grep -v ":0 ${primary_ds}" | awk '{print $4,$14,$15,$16}' | while read q_name first_seg last_seg next_seg
do
q_size=$(bcalc "$last_seg - $first_seg")
$ISQLCMD <<-EOF
set nocount on
go
declare @mb_processed numeric(8,2)
declare @prev_sample datetime
declare @mins numeric(8,2)
declare @tput numeric(8,2)
if not exists (select 1 from rs_throughput where q_name = "${q_name}")
insert rs_throughput values ("${q_name}", getdate(), ${first_seg}, ${last_seg}, ${next_seg}, ${q_size}, 0, 0)
else
begin
-- get previous sample_time as key
select @prev_sample = max(sample_time) from rs_throughput where q_name = "${q_name}"
-- get difference in mb between current next segment and previous next segment
select @mb_processed = (select ${next_seg} - next from rs_throughput where sample_time = @prev_sample and q_name = "${q_name}")

select @prev_sample=dateadd(ss,-10,@prev_sample) -- # must substract 10 seconds to ensure the next datediff doesn't reflect 2m 58s

as only 2mins!
select @mins = datediff(mi,@prev_sample,getdate())
if @mins > 0
begin
select @tput = round((@mb_processed / @mins),2)
insert rs_throughput values ("${q_name}", getdate(), ${first_seg}, ${last_seg}, ${next_seg}, ${q_size}, @mb_processed, @tput)
end
end
go
EOF
done
}

fn_report_thruput()
{
if [[ $option1 = [0-9]* ]]
then
option2=$option1
option1="all"
fi

if ! [[ "$option1" = "all" ]]
then
q_list=$option1
fi

printf " -------------------------------------\n"
printf " Run date $DATE\n"
printf " -------------------------------------\n"

for q_name in $q_list
do
$ISQLCMD <<-EOF
set nocount on
declare @mb_avg numeric(8,2)
declare @size numeric(8,2)
declare @eta datetime
select convert(char(19),sample_time) sample_time, q_name, size q_size, mb_processed, thruput "thruput (mb/min)" from rs_throughput
--select convert(char(19),sample_time) sample_time, q_name, size q_size, mb_processed, thruput "thruput (mb/min)", dateadd(mi,

(round((size/thruput),0)),getdate()) ETA from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name like "%${q_name}%"

--get mb average
select @mb_avg = convert(numeric(8,2),avg(thruput)) from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name like "%${q_name}%"
--get current queue size
select @size = size from rs_throughput where q_name like "%${q_name}%" having sample_time=max(sample_time)
select @eta = dateadd(mi,round((@size/@mb_avg),0),getdate())
print ""
print " Average for ${q_name}: %1! mb/min, Re-sync ETA: %2!", @mb_avg, @eta
print ""
go
EOF
done
}

fn_show_help_text()
{
printf "Usage examples:\n"
printf "rs_throughput.sh (runs with default values showing all queues over 15 minutes)\n"
printf "rs_throughput.sh <q_name>|<minutes>\n"
printf "rs_throughput.sh <q_name> <minutes>\n"
printf "rs_throughput.sh size|queue (shows current queue sizes)\n"
printf "rs_throughput.sh max (shows highest ever throughput for queue history)\n"
printf "rs_throughput.sh help (shows this helptext)\n"
printf "rs_throughput.sh insert # this is for cron only (inserts data to $RPT_DB..rs_throughput table)\n"
}

fn_show_max_thruput()
{
$ISQLCMD <<-EOF
print ""
set rowcount 0
set nocount on
declare @q_name char(22)
declare @sample_time datetime
declare @max_thruput numeric(8,2)
select q_name, sample_time, thruput into #throughput from rs_throughput where 1=0
print "Maximum throughput per queue:"
-- get list of queues
select distinct q_name, 0 as processed into #queues from rs_throughput
set rowcount 1
select @q_name=q_name from #queues where processed = 0
while @@rowcount != 0
begin
insert #throughput select q_name, sample_time, thruput from rs_throughput
where sample_time in
(select sample_time from rs_throughput where q_name = @q_name having thruput = max(thruput))
and q_name = @q_name
update #queues set processed = 1 where q_name = @q_name
select @q_name=q_name from #queues where processed = 0
end
set rowcount 0
select * from #throughput order by thruput desc
go
EOF
echo
}

fn_show_current_q_size()
{
$RISQLCMD << eof > $SQMFILE
admin who,sqm
go
quit
eof
printf "Current Stable Queue usage:\n"
cat $SQMFILE | egrep $insert_filter | sed 's/Awaiting Message/AwaitingMessage/; s/Awaiting Wakeup/AwaitingWakeup/; s/$\.[0-9][0-

9]*$\.[0-9]/\1/' | grep -v ":0 ${primary_ds}" | awk '{print $4,$14,$15,$16}' | while read q_name first_seg last_seg next_seg
do
q_size=$(bcalc "$last_seg - $first_seg")
printf "$q_name: $q_size\n"
done
}

fn_show_combined_avg_thruput()
{
$ISQLCMD <<-EOF
set rowcount 0
set nocount on
declare @q_name varchar(25)
declare @mb_avg numeric(8,2)
declare @mb_combined_avg numeric(8,2)
select @mb_combined_avg=0
-- get list of queues

select distinct q_name, 0 as processed into #queues from rs_throughput
set rowcount 1
select @q_name=q_name from #queues where processed = 0
while @@rowcount != 0
begin
select @mb_avg = convert(numeric(8,2),avg(thruput)) from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name = @q_name
select @mb_combined_avg = @mb_combined_avg + @mb_avg
update #queues set processed=1 where q_name = @q_name
select @q_name=q_name from #queues where processed = 0
end
print " Combined thruput average: %1! mb/min", @mb_combined_avg
print ""
go
EOF
}

##############################################################################
# main program #
##############################################################################

check_previous_instance
fn_checkusage

case "$option1" in
help) fn_show_help_text;
exit;;

insert) fn_insert_thruput_data;
exit;;

max) fn_show_max_thruput;
exit;;

queue|size) fn_show_current_q_size;
exit;;

*) fn_report_thruput;
fn_show_combined_avg_thruput;;
esac

fn_housekeeping

### TABLE DEFINTION FOR RAW DATA COLLECTION
### Use the table definition below to create the table which this script
### requires in order to insert queue data and furthermore query this
### data in order to provide throughput information.
#create table rs_throughput (
#q_name varchar(25),
#sample_time datetime,
#first numeric(8,2),
#last numeric(8,2),
#next numeric(8,2),
#size numeric(8,2),
#mb_processed numeric(8,2),
#thruput numeric(8,2)
#)

# end program
</pre>

[[Category:RepServer]]

Sp mon sql2--ASE spid activity monitor

2009-02-20T01:03:45Z

Bobh:

RepServer Throughput Script

2009-02-20T00:27:49Z

Bobh:

If you want to know just how much data repserver is processing, then this is the tool. 
This script will tell you: 
o average throughput per queue (mb per minute) 
o average throughput combined (all queues) 
o estimated time for a replicate to catch up 
o current queue sizes 
There is also a maximum recorded throughput option but this appears not to work well as some of the figures have been suspicious. 
The real maximum throughput can be established by noting when the replicates start to fall behind in conjunction with the current throughput figures. 
This script can be particularly useful in helping to ascertain if you need to throw more CPU at your repserver box.

Notes: 
1) You will need to cron this script and allow it to collect at least 2 samples before being able to run it interactively to obtain throughput information. 
2) The script automatically determines the elapsed interval in minutes between samples so you can cron the script to your preferred interval, be it 3 minutes or 10 minutes, the mb/min figure will be calculated correctly. 
3) In addition to configuring the script to your environment, you will also need to create the rs_throughput table prior to first run.
<pre>
#!/usr/bin/ksh
#
# Script: rs_throughput.sh
# Author: Bob Holmes - email: cambob@gmail.com
# Date : 19/09/08
# Version : 1.0
# Usage : Run "rs_throughput.sh help" for full usage information.
# Description : This script enables monitoring and reporting
# of repserver throughput.
# --------------------------
# *** Variables for customisation can be found by searching for "!!" ***
# --------------------------
# Modification history:
# 19/02/09 : Bob Holmes : Script modified to enable easier customisation prior
# to release on Sybase wiki. (modifications not tested)
# --------------------------
# Comments: This script relies on the reporting..rs_throughput table for data
# collection and reporting. The definition for this table can be
# found at the end of this script.
##############################################################################
# setup environment #
##############################################################################
#
# save input variables
if ! [[ -z $1 ]]
then
export option1=$1
else
export option1="all"
fi

if ! [[ -z $2 ]]
then
export option2=$2
else
export option2="15" # default number of minutes on which to report throughput
fi

# configure environment
HOSTNAME=`hostname`
. $HOME/admin/.syb_cfg.sh $HOSTNAME # !! This line sets up the environment from
# !! from an external shell script.

##############################################################################
# config variables #
##############################################################################
# config:
primary_ds="prim_ds" # !! string used to identify the inbound queue
# queues to query on:-
q_list="prim_ds.db1 prim_ds.db2 replicate_ds.db1 replicate_ds.db2" # !!
insert_filter="db1 db2" # !! data will only be collected for queue names which have this keyword in
REP_SRV="repsrv_rs" # !! the actual repserver from which to get sqm info
RPT_SRV="reporting_ds" # !! dataserver which manages the reporting database
RPT_DB="reporting" # !! reporting database name
RECIPIENTS="user@domain" # !! email config (separate addresses with commas)
ADMINDIR="/opt/home/sybase/admin" # !! sybase admin directory

# static:
SQMFILE="$ADMINDIR/rs_sqm_tmp1.tmp"
DATE=$(date "+%d/%m/%y %H:%M:%S")

# config aware environment variables: # !! You may need to replace the line below with the equivalent
# !! for your own environment.
RISQLCMD="isql -Usa -P`grep "$REP_SRV," $HOME/admin/.servers | cut -d , -f 4` -S$rep_server -w1000"
ISQLCMD="isql -Usa -P${SYBPASS} -S${RPT_SRV} -w200 -D${RPT_DB}"

##############################################################################
# functions #
##############################################################################

check_previous_instance()
{
# Note: next line greps for 8 instances as tests proved that less could cause the script to exit incorrectly
if [ $(ps -ef | grep rs_throughput | egrep -v "vi|grep" | grep -v "sh -c" | wc -l | awk '{print $1}') -gt 8 ]
then
# previous instance still running - probably hanging
echo Previous instance still running.
exit
fi
}

fn_checkusage()
{
if [[ -z $option1 ]]
then
printf " Usage: ./rs_throughput.sh <all>|<q_name>|<help> [<minutes to display>]\n"
exit
fi
}

bcalc()
{
awk 'BEGIN{EQUATION='"$*"';printf("%0.1f\n",EQUATION); exit}'
}

fn_housekeeping()
{
if [ -e $SQMFILE ]
then
rm $SQMFILE
fi
$ISQLCMD <<-EOF
set nocount on
go
delete from rs_throughput where sample_time <= dateadd(dd,-28,getdate())
go
EOF
}

fn_insert_thruput_data()
{
# 1) get current segment usage/progress info
#connect to repserver, get sqm info - put into file
$RISQLCMD << eof > $SQMFILE
admin who,sqm
go
quit
eof

# 2) insert new segment data into rs_throughput table
#date
#echo "Queue Name, First segment, Last segment, Next segment"
cat $SQMFILE | egrep $insert_filter | sed 's/Awaiting Message/AwaitingMessage/; s/Awaiting Wakeup/AwaitingWakeup/; s/$\.[0-9][0-

9]*$\.[0-9]/\1/' | grep -v ":0 ${primary_ds}" | awk '{print $4,$14,$15,$16}' | while read q_name first_seg last_seg next_seg
do
q_size=$(bcalc "$last_seg - $first_seg")
$ISQLCMD <<-EOF
set nocount on
go
declare @mb_processed numeric(8,2)
declare @prev_sample datetime
declare @mins numeric(8,2)
declare @tput numeric(8,2)
if not exists (select 1 from rs_throughput where q_name = "${q_name}")
insert rs_throughput values ("${q_name}", getdate(), ${first_seg}, ${last_seg}, ${next_seg}, ${q_size}, 0, 0)
else
begin
-- get previous sample_time as key
select @prev_sample = max(sample_time) from rs_throughput where q_name = "${q_name}"
-- get difference in mb between current next segment and previous next segment
select @mb_processed = (select ${next_seg} - next from rs_throughput where sample_time = @prev_sample and q_name = "${q_name}")

select @prev_sample=dateadd(ss,-10,@prev_sample) -- # must substract 10 seconds to ensure the next datediff doesn't reflect 2m 58s

as only 2mins!
select @mins = datediff(mi,@prev_sample,getdate())
if @mins > 0
begin
select @tput = round((@mb_processed / @mins),2)
insert rs_throughput values ("${q_name}", getdate(), ${first_seg}, ${last_seg}, ${next_seg}, ${q_size}, @mb_processed, @tput)
end
end
go
EOF
done
}

fn_report_thruput()
{
if [[ $option1 = [0-9]* ]]
then
option2=$option1
option1="all"
fi

if ! [[ "$option1" = "all" ]]
then
q_list=$option1
fi

printf " -------------------------------------\n"
printf " Run date $DATE\n"
printf " -------------------------------------\n"

for q_name in $q_list
do
$ISQLCMD <<-EOF
set nocount on
declare @mb_avg numeric(8,2)
declare @size numeric(8,2)
declare @eta datetime
select convert(char(19),sample_time) sample_time, q_name, size q_size, mb_processed, thruput "thruput (mb/min)" from rs_throughput
--select convert(char(19),sample_time) sample_time, q_name, size q_size, mb_processed, thruput "thruput (mb/min)", dateadd(mi,

(round((size/thruput),0)),getdate()) ETA from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name like "%${q_name}%"

--get mb average
select @mb_avg = convert(numeric(8,2),avg(thruput)) from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name like "%${q_name}%"
--get current queue size
select @size = size from rs_throughput where q_name like "%${q_name}%" having sample_time=max(sample_time)
select @eta = dateadd(mi,round((@size/@mb_avg),0),getdate())
print ""
print " Average for ${q_name}: %1! mb/min, Re-sync ETA: %2!", @mb_avg, @eta
print ""
go
EOF
done
}

fn_show_help_text()
{
printf "Usage examples:\n"
printf "rs_throughput.sh (runs with default values showing all queues over 15 minutes)\n"
printf "rs_throughput.sh <q_name>|<minutes>\n"
printf "rs_throughput.sh <q_name> <minutes>\n"
printf "rs_throughput.sh size|queue (shows current queue sizes)\n"
printf "rs_throughput.sh max (shows highest ever throughput for queue history)\n"
printf "rs_throughput.sh help (shows this helptext)\n"
printf "rs_throughput.sh insert # this is for cron only (inserts data to $RPT_DB..rs_throughput table)\n"
}

fn_show_max_thruput()
{
$ISQLCMD <<-EOF
print ""
set rowcount 0
set nocount on
declare @q_name char(22)
declare @sample_time datetime
declare @max_thruput numeric(8,2)
select q_name, sample_time, thruput into #throughput from rs_throughput where 1=0
print "Maximum throughput per queue:"
-- get list of queues
select distinct q_name, 0 as processed into #queues from rs_throughput
set rowcount 1
select @q_name=q_name from #queues where processed = 0
while @@rowcount != 0
begin
insert #throughput select q_name, sample_time, thruput from rs_throughput
where sample_time in
(select sample_time from rs_throughput where q_name = @q_name having thruput = max(thruput))
and q_name = @q_name
update #queues set processed = 1 where q_name = @q_name
select @q_name=q_name from #queues where processed = 0
end
set rowcount 0
select * from #throughput order by thruput desc
go
EOF
echo
}

fn_show_current_q_size()
{
$RISQLCMD << eof > $SQMFILE
admin who,sqm
go
quit
eof
printf "Current Stable Queue usage:\n"
cat $SQMFILE | egrep $insert_filter | sed 's/Awaiting Message/AwaitingMessage/; s/Awaiting Wakeup/AwaitingWakeup/; s/$\.[0-9][0-

9]*$\.[0-9]/\1/' | grep -v ":0 ${primary_ds}" | awk '{print $4,$14,$15,$16}' | while read q_name first_seg last_seg next_seg
do
q_size=$(bcalc "$last_seg - $first_seg")
printf "$q_name: $q_size\n"
done
}

fn_show_combined_avg_thruput()
{
$ISQLCMD <<-EOF
set rowcount 0
set nocount on
declare @q_name varchar(25)
declare @mb_avg numeric(8,2)
declare @mb_combined_avg numeric(8,2)
select @mb_combined_avg=0
-- get list of queues

select distinct q_name, 0 as processed into #queues from rs_throughput
set rowcount 1
select @q_name=q_name from #queues where processed = 0
while @@rowcount != 0
begin
select @mb_avg = convert(numeric(8,2),avg(thruput)) from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name = @q_name
select @mb_combined_avg = @mb_combined_avg + @mb_avg
update #queues set processed=1 where q_name = @q_name
select @q_name=q_name from #queues where processed = 0
end
print " Combined thruput average: %1! mb/min", @mb_combined_avg
print ""
go
EOF
}

##############################################################################
# main program #
##############################################################################

check_previous_instance
fn_checkusage

case "$option1" in
help) fn_show_help_text;
exit;;

insert) fn_insert_thruput_data;
exit;;

max) fn_show_max_thruput;
exit;;

queue|size) fn_show_current_q_size;
exit;;

*) fn_report_thruput;
fn_show_combined_avg_thruput;;
esac

fn_housekeeping

### TABLE DEFINTION FOR RAW DATA COLLECTION
### Use the table definition below to create the table which this script
### requires in order to insert queue data and furthermore query this
### data in order to provide throughput information.
#create table rs_throughput (
#q_name varchar(25),
#sample_time datetime,
#first numeric(8,2),
#last numeric(8,2),
#next numeric(8,2),
#size numeric(8,2),
#mb_processed numeric(8,2),
#thruput numeric(8,2)
#)

# end program
</pre>

[[Category:RepServer]]

RepServer Throughput Script

2009-02-20T00:26:38Z

Bobh:

If you want to know just how much data repserver is processing, then this is the tool. 
This script will tell you: 
o average throughput per queue (mb per minute) 
o average throughput combined (all queues) 
o estimated time for a replicate to catch up 
o current queue sizes 
There is also a maximum recorded throughput option but this appears not to work well as some of the figures have been suspicious. 
The real maximum throughput can be established by noting when the replicates start to fall behind in conjunction with the current throughput figures. 
This script can be particularly useful in helping to ascertain if you need to throw more CPU at your repserver box.

Notes: 
1) You will need to cron this script and allow it to collect at least 2 samples before being able to run it interactively to obtain throughput information. 
2) The script automatically determines the elapsed interval in minutes between samples so you can cron the script to your preferred interval, be it 3 minutes or 10 minutes, the mb/min figure will be calculated correctly.
3) In addition to configuring the script to your environment, you will also need to create the rs_throughput table prior to first run.
<pre>
#!/usr/bin/ksh
#
# Script: rs_throughput.sh
# Author: Bob Holmes - email: cambob@gmail.com
# Date : 19/09/08
# Version : 1.0
# Usage : Run "rs_throughput.sh help" for full usage information.
# Description : This script enables monitoring and reporting
# of repserver throughput.
# --------------------------
# *** Variables for customisation can be found by searching for "!!" ***
# --------------------------
# Modification history:
# 19/02/09 : Bob Holmes : Script modified to enable easier customisation prior
# to release on Sybase wiki. (modifications not tested)
# --------------------------
# Comments: This script relies on the reporting..rs_throughput table for data
# collection and reporting. The definition for this table can be
# found at the end of this script.
##############################################################################
# setup environment #
##############################################################################
#
# save input variables
if ! [[ -z $1 ]]
then
export option1=$1
else
export option1="all"
fi

if ! [[ -z $2 ]]
then
export option2=$2
else
export option2="15" # default number of minutes on which to report throughput
fi

# configure environment
HOSTNAME=`hostname`
. $HOME/admin/.syb_cfg.sh $HOSTNAME # !! This line sets up the environment from
# !! from an external shell script.

##############################################################################
# config variables #
##############################################################################
# config:
primary_ds="prim_ds" # !! string used to identify the inbound queue
# queues to query on:-
q_list="prim_ds.db1 prim_ds.db2 replicate_ds.db1 replicate_ds.db2" # !!
insert_filter="db1 db2" # !! data will only be collected for queue names which have this keyword in
REP_SRV="repsrv_rs" # !! the actual repserver from which to get sqm info
RPT_SRV="reporting_ds" # !! dataserver which manages the reporting database
RPT_DB="reporting" # !! reporting database name
RECIPIENTS="user@domain" # !! email config (separate addresses with commas)
ADMINDIR="/opt/home/sybase/admin" # !! sybase admin directory

# static:
SQMFILE="$ADMINDIR/rs_sqm_tmp1.tmp"
DATE=$(date "+%d/%m/%y %H:%M:%S")

# config aware environment variables: # !! You may need to replace the line below with the equivalent
# !! for your own environment.
RISQLCMD="isql -Usa -P`grep "$REP_SRV," $HOME/admin/.servers | cut -d , -f 4` -S$rep_server -w1000"
ISQLCMD="isql -Usa -P${SYBPASS} -S${RPT_SRV} -w200 -D${RPT_DB}"

##############################################################################
# functions #
##############################################################################

check_previous_instance()
{
# Note: next line greps for 8 instances as tests proved that less could cause the script to exit incorrectly
if [ $(ps -ef | grep rs_throughput | egrep -v "vi|grep" | grep -v "sh -c" | wc -l | awk '{print $1}') -gt 8 ]
then
# previous instance still running - probably hanging
echo Previous instance still running.
exit
fi
}

fn_checkusage()
{
if [[ -z $option1 ]]
then
printf " Usage: ./rs_throughput.sh <all>|<q_name>|<help> [<minutes to display>]\n"
exit
fi
}

bcalc()
{
awk 'BEGIN{EQUATION='"$*"';printf("%0.1f\n",EQUATION); exit}'
}

fn_housekeeping()
{
if [ -e $SQMFILE ]
then
rm $SQMFILE
fi
$ISQLCMD <<-EOF
set nocount on
go
delete from rs_throughput where sample_time <= dateadd(dd,-28,getdate())
go
EOF
}

fn_insert_thruput_data()
{
# 1) get current segment usage/progress info
#connect to repserver, get sqm info - put into file
$RISQLCMD << eof > $SQMFILE
admin who,sqm
go
quit
eof

# 2) insert new segment data into rs_throughput table
#date
#echo "Queue Name, First segment, Last segment, Next segment"
cat $SQMFILE | egrep $insert_filter | sed 's/Awaiting Message/AwaitingMessage/; s/Awaiting Wakeup/AwaitingWakeup/; s/$\.[0-9][0-

9]*$\.[0-9]/\1/' | grep -v ":0 ${primary_ds}" | awk '{print $4,$14,$15,$16}' | while read q_name first_seg last_seg next_seg
do
q_size=$(bcalc "$last_seg - $first_seg")
$ISQLCMD <<-EOF
set nocount on
go
declare @mb_processed numeric(8,2)
declare @prev_sample datetime
declare @mins numeric(8,2)
declare @tput numeric(8,2)
if not exists (select 1 from rs_throughput where q_name = "${q_name}")
insert rs_throughput values ("${q_name}", getdate(), ${first_seg}, ${last_seg}, ${next_seg}, ${q_size}, 0, 0)
else
begin
-- get previous sample_time as key
select @prev_sample = max(sample_time) from rs_throughput where q_name = "${q_name}"
-- get difference in mb between current next segment and previous next segment
select @mb_processed = (select ${next_seg} - next from rs_throughput where sample_time = @prev_sample and q_name = "${q_name}")

select @prev_sample=dateadd(ss,-10,@prev_sample) -- # must substract 10 seconds to ensure the next datediff doesn't reflect 2m 58s

as only 2mins!
select @mins = datediff(mi,@prev_sample,getdate())
if @mins > 0
begin
select @tput = round((@mb_processed / @mins),2)
insert rs_throughput values ("${q_name}", getdate(), ${first_seg}, ${last_seg}, ${next_seg}, ${q_size}, @mb_processed, @tput)
end
end
go
EOF
done
}

fn_report_thruput()
{
if [[ $option1 = [0-9]* ]]
then
option2=$option1
option1="all"
fi

if ! [[ "$option1" = "all" ]]
then
q_list=$option1
fi

printf " -------------------------------------\n"
printf " Run date $DATE\n"
printf " -------------------------------------\n"

for q_name in $q_list
do
$ISQLCMD <<-EOF
set nocount on
declare @mb_avg numeric(8,2)
declare @size numeric(8,2)
declare @eta datetime
select convert(char(19),sample_time) sample_time, q_name, size q_size, mb_processed, thruput "thruput (mb/min)" from rs_throughput
--select convert(char(19),sample_time) sample_time, q_name, size q_size, mb_processed, thruput "thruput (mb/min)", dateadd(mi,

(round((size/thruput),0)),getdate()) ETA from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name like "%${q_name}%"

--get mb average
select @mb_avg = convert(numeric(8,2),avg(thruput)) from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name like "%${q_name}%"
--get current queue size
select @size = size from rs_throughput where q_name like "%${q_name}%" having sample_time=max(sample_time)
select @eta = dateadd(mi,round((@size/@mb_avg),0),getdate())
print ""
print " Average for ${q_name}: %1! mb/min, Re-sync ETA: %2!", @mb_avg, @eta
print ""
go
EOF
done
}

fn_show_help_text()
{
printf "Usage examples:\n"
printf "rs_throughput.sh (runs with default values showing all queues over 15 minutes)\n"
printf "rs_throughput.sh <q_name>|<minutes>\n"
printf "rs_throughput.sh <q_name> <minutes>\n"
printf "rs_throughput.sh size|queue (shows current queue sizes)\n"
printf "rs_throughput.sh max (shows highest ever throughput for queue history)\n"
printf "rs_throughput.sh help (shows this helptext)\n"
printf "rs_throughput.sh insert # this is for cron only (inserts data to $RPT_DB..rs_throughput table)\n"
}

fn_show_max_thruput()
{
$ISQLCMD <<-EOF
print ""
set rowcount 0
set nocount on
declare @q_name char(22)
declare @sample_time datetime
declare @max_thruput numeric(8,2)
select q_name, sample_time, thruput into #throughput from rs_throughput where 1=0
print "Maximum throughput per queue:"
-- get list of queues
select distinct q_name, 0 as processed into #queues from rs_throughput
set rowcount 1
select @q_name=q_name from #queues where processed = 0
while @@rowcount != 0
begin
insert #throughput select q_name, sample_time, thruput from rs_throughput
where sample_time in
(select sample_time from rs_throughput where q_name = @q_name having thruput = max(thruput))
and q_name = @q_name
update #queues set processed = 1 where q_name = @q_name
select @q_name=q_name from #queues where processed = 0
end
set rowcount 0
select * from #throughput order by thruput desc
go
EOF
echo
}

fn_show_current_q_size()
{
$RISQLCMD << eof > $SQMFILE
admin who,sqm
go
quit
eof
printf "Current Stable Queue usage:\n"
cat $SQMFILE | egrep $insert_filter | sed 's/Awaiting Message/AwaitingMessage/; s/Awaiting Wakeup/AwaitingWakeup/; s/$\.[0-9][0-

9]*$\.[0-9]/\1/' | grep -v ":0 ${primary_ds}" | awk '{print $4,$14,$15,$16}' | while read q_name first_seg last_seg next_seg
do
q_size=$(bcalc "$last_seg - $first_seg")
printf "$q_name: $q_size\n"
done
}

fn_show_combined_avg_thruput()
{
$ISQLCMD <<-EOF
set rowcount 0
set nocount on
declare @q_name varchar(25)
declare @mb_avg numeric(8,2)
declare @mb_combined_avg numeric(8,2)
select @mb_combined_avg=0
-- get list of queues

select distinct q_name, 0 as processed into #queues from rs_throughput
set rowcount 1
select @q_name=q_name from #queues where processed = 0
while @@rowcount != 0
begin
select @mb_avg = convert(numeric(8,2),avg(thruput)) from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name = @q_name
select @mb_combined_avg = @mb_combined_avg + @mb_avg
update #queues set processed=1 where q_name = @q_name
select @q_name=q_name from #queues where processed = 0
end
print " Combined thruput average: %1! mb/min", @mb_combined_avg
print ""
go
EOF
}

##############################################################################
# main program #
##############################################################################

check_previous_instance
fn_checkusage

case "$option1" in
help) fn_show_help_text;
exit;;

insert) fn_insert_thruput_data;
exit;;

max) fn_show_max_thruput;
exit;;

queue|size) fn_show_current_q_size;
exit;;

*) fn_report_thruput;
fn_show_combined_avg_thruput;;
esac

fn_housekeeping

### TABLE DEFINTION FOR RAW DATA COLLECTION
### Use the table definition below to create the table which this script
### requires in order to insert queue data and furthermore query this
### data in order to provide throughput information.
#create table rs_throughput (
#q_name varchar(25),
#sample_time datetime,
#first numeric(8,2),
#last numeric(8,2),
#next numeric(8,2),
#size numeric(8,2),
#mb_processed numeric(8,2),
#thruput numeric(8,2)
#)

# end program
</pre>

[[Category:RepServer]]

RepServer Throughput Script

2009-02-20T00:05:28Z

Bobh:

If you want to know just how much data repserver is processing, then this is the tool. 
This script will tell you: 
o average throughput per queue (mb per minute) 
o average throughput combined (all queues) 
o estimated time for a replicate to catch up 
o current queue sizes 
There is also a maximum recorded throughput option but this appears not to work well as some of the figures have been suspicious. 
The real maximum throughput can be established by noting when the replicates start to fall behind in conjunction with the current throughput figures. 
This script can be particularly useful in helping to ascertain if you need to throw more CPU at your repserver box.
<pre>
#!/usr/bin/ksh
#
# Script: rs_throughput.sh
# Author: Bob Holmes - email: cambob@gmail.com
# Date : 19/09/08
# Version : 1.0
# Usage : Run "rs_throughput.sh help" for full usage information.
# Description : This script enables monitoring and reporting
# of repserver throughput.
# --------------------------
# *** Variables for customisation can be found by searching for "!!" ***
# --------------------------
# Modification history:
# 19/02/09 : Bob Holmes : Script modified to enable easier customisation prior
# to release on Sybase wiki. (modifications not tested)
# --------------------------
# Comments: This script relies on the reporting..rs_throughput table for data
# collection and reporting. The definition for this table can be
# found at the end of this script.
##############################################################################
# setup environment #
##############################################################################
#
# save input variables
if ! [[ -z $1 ]]
then
export option1=$1
else
export option1="all"
fi

if ! [[ -z $2 ]]
then
export option2=$2
else
export option2="15" # default number of minutes on which to report throughput
fi

# configure environment
HOSTNAME=`hostname`
. $HOME/admin/.syb_cfg.sh $HOSTNAME # !! This line sets up the environment from
# !! from an external shell script.

##############################################################################
# config variables #
##############################################################################
# config:
primary_ds="prim_ds" # !! string used to identify the inbound queue
# queues to query on:-
q_list="prim_ds.db1 prim_ds.db2 replicate_ds.db1 replicate_ds.db2" # !!
insert_filter="db1 db2" # !! data will only be collected for queue names which have this keyword in
REP_SRV="repsrv_rs" # !! the actual repserver from which to get sqm info
RPT_SRV="reporting_ds" # !! dataserver which manages the reporting database
RPT_DB="reporting" # !! reporting database name
RECIPIENTS="user@domain" # !! email config (separate addresses with commas)
ADMINDIR="/opt/home/sybase/admin" # !! sybase admin directory

# static:
SQMFILE="$ADMINDIR/rs_sqm_tmp1.tmp"
DATE=$(date "+%d/%m/%y %H:%M:%S")

# config aware environment variables: # !! You may need to replace the line below with the equivalent
# !! for your own environment.
RISQLCMD="isql -Usa -P`grep "$REP_SRV," $HOME/admin/.servers | cut -d , -f 4` -S$rep_server -w1000"
ISQLCMD="isql -Usa -P${SYBPASS} -S${RPT_SRV} -w200 -D${RPT_DB}"

##############################################################################
# functions #
##############################################################################

check_previous_instance()
{
# Note: next line greps for 8 instances as tests proved that less could cause the script to exit incorrectly
if [ $(ps -ef | grep rs_throughput | egrep -v "vi|grep" | grep -v "sh -c" | wc -l | awk '{print $1}') -gt 8 ]
then
# previous instance still running - probably hanging
echo Previous instance still running.
exit
fi
}

fn_checkusage()
{
if [[ -z $option1 ]]
then
printf " Usage: ./rs_throughput.sh <all>|<q_name>|<help> [<minutes to display>]\n"
exit
fi
}

bcalc()
{
awk 'BEGIN{EQUATION='"$*"';printf("%0.1f\n",EQUATION); exit}'
}

fn_housekeeping()
{
if [ -e $SQMFILE ]
then
rm $SQMFILE
fi
$ISQLCMD <<-EOF
set nocount on
go
delete from rs_throughput where sample_time <= dateadd(dd,-28,getdate())
go
EOF
}

fn_insert_thruput_data()
{
# 1) get current segment usage/progress info
#connect to repserver, get sqm info - put into file
$RISQLCMD << eof > $SQMFILE
admin who,sqm
go
quit
eof

# 2) insert new segment data into rs_throughput table
#date
#echo "Queue Name, First segment, Last segment, Next segment"
cat $SQMFILE | egrep $insert_filter | sed 's/Awaiting Message/AwaitingMessage/; s/Awaiting Wakeup/AwaitingWakeup/; s/$\.[0-9][0-

9]*$\.[0-9]/\1/' | grep -v ":0 ${primary_ds}" | awk '{print $4,$14,$15,$16}' | while read q_name first_seg last_seg next_seg
do
q_size=$(bcalc "$last_seg - $first_seg")
$ISQLCMD <<-EOF
set nocount on
go
declare @mb_processed numeric(8,2)
declare @prev_sample datetime
declare @mins numeric(8,2)
declare @tput numeric(8,2)
if not exists (select 1 from rs_throughput where q_name = "${q_name}")
insert rs_throughput values ("${q_name}", getdate(), ${first_seg}, ${last_seg}, ${next_seg}, ${q_size}, 0, 0)
else
begin
-- get previous sample_time as key
select @prev_sample = max(sample_time) from rs_throughput where q_name = "${q_name}"
-- get difference in mb between current next segment and previous next segment
select @mb_processed = (select ${next_seg} - next from rs_throughput where sample_time = @prev_sample and q_name = "${q_name}")

select @prev_sample=dateadd(ss,-10,@prev_sample) -- # must substract 10 seconds to ensure the next datediff doesn't reflect 2m 58s

as only 2mins!
select @mins = datediff(mi,@prev_sample,getdate())
if @mins > 0
begin
select @tput = round((@mb_processed / @mins),2)
insert rs_throughput values ("${q_name}", getdate(), ${first_seg}, ${last_seg}, ${next_seg}, ${q_size}, @mb_processed, @tput)
end
end
go
EOF
done
}

fn_report_thruput()
{
if [[ $option1 = [0-9]* ]]
then
option2=$option1
option1="all"
fi

if ! [[ "$option1" = "all" ]]
then
q_list=$option1
fi

printf " -------------------------------------\n"
printf " Run date $DATE\n"
printf " -------------------------------------\n"

for q_name in $q_list
do
$ISQLCMD <<-EOF
set nocount on
declare @mb_avg numeric(8,2)
declare @size numeric(8,2)
declare @eta datetime
select convert(char(19),sample_time) sample_time, q_name, size q_size, mb_processed, thruput "thruput (mb/min)" from rs_throughput
--select convert(char(19),sample_time) sample_time, q_name, size q_size, mb_processed, thruput "thruput (mb/min)", dateadd(mi,

(round((size/thruput),0)),getdate()) ETA from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name like "%${q_name}%"

--get mb average
select @mb_avg = convert(numeric(8,2),avg(thruput)) from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name like "%${q_name}%"
--get current queue size
select @size = size from rs_throughput where q_name like "%${q_name}%" having sample_time=max(sample_time)
select @eta = dateadd(mi,round((@size/@mb_avg),0),getdate())
print ""
print " Average for ${q_name}: %1! mb/min, Re-sync ETA: %2!", @mb_avg, @eta
print ""
go
EOF
done
}

fn_show_help_text()
{
printf "Usage examples:\n"
printf "rs_throughput.sh (runs with default values showing all queues over 15 minutes)\n"
printf "rs_throughput.sh <q_name>|<minutes>\n"
printf "rs_throughput.sh <q_name> <minutes>\n"
printf "rs_throughput.sh size|queue (shows current queue sizes)\n"
printf "rs_throughput.sh max (shows highest ever throughput for queue history)\n"
printf "rs_throughput.sh help (shows this helptext)\n"
printf "rs_throughput.sh insert # this is for cron only (inserts data to $RPT_DB..rs_throughput table)\n"
}

fn_show_max_thruput()
{
$ISQLCMD <<-EOF
print ""
set rowcount 0
set nocount on
declare @q_name char(22)
declare @sample_time datetime
declare @max_thruput numeric(8,2)
select q_name, sample_time, thruput into #throughput from rs_throughput where 1=0
print "Maximum throughput per queue:"
-- get list of queues
select distinct q_name, 0 as processed into #queues from rs_throughput
set rowcount 1
select @q_name=q_name from #queues where processed = 0
while @@rowcount != 0
begin
insert #throughput select q_name, sample_time, thruput from rs_throughput
where sample_time in
(select sample_time from rs_throughput where q_name = @q_name having thruput = max(thruput))
and q_name = @q_name
update #queues set processed = 1 where q_name = @q_name
select @q_name=q_name from #queues where processed = 0
end
set rowcount 0
select * from #throughput order by thruput desc
go
EOF
echo
}

fn_show_current_q_size()
{
$RISQLCMD << eof > $SQMFILE
admin who,sqm
go
quit
eof
printf "Current Stable Queue usage:\n"
cat $SQMFILE | egrep $insert_filter | sed 's/Awaiting Message/AwaitingMessage/; s/Awaiting Wakeup/AwaitingWakeup/; s/$\.[0-9][0-

9]*$\.[0-9]/\1/' | grep -v ":0 ${primary_ds}" | awk '{print $4,$14,$15,$16}' | while read q_name first_seg last_seg next_seg
do
q_size=$(bcalc "$last_seg - $first_seg")
printf "$q_name: $q_size\n"
done
}

fn_show_combined_avg_thruput()
{
$ISQLCMD <<-EOF
set rowcount 0
set nocount on
declare @q_name varchar(25)
declare @mb_avg numeric(8,2)
declare @mb_combined_avg numeric(8,2)
select @mb_combined_avg=0
-- get list of queues

select distinct q_name, 0 as processed into #queues from rs_throughput
set rowcount 1
select @q_name=q_name from #queues where processed = 0
while @@rowcount != 0
begin
select @mb_avg = convert(numeric(8,2),avg(thruput)) from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name = @q_name
select @mb_combined_avg = @mb_combined_avg + @mb_avg
update #queues set processed=1 where q_name = @q_name
select @q_name=q_name from #queues where processed = 0
end
print " Combined thruput average: %1! mb/min", @mb_combined_avg
print ""
go
EOF
}

##############################################################################
# main program #
##############################################################################

check_previous_instance
fn_checkusage

case "$option1" in
help) fn_show_help_text;
exit;;

insert) fn_insert_thruput_data;
exit;;

max) fn_show_max_thruput;
exit;;

queue|size) fn_show_current_q_size;
exit;;

*) fn_report_thruput;
fn_show_combined_avg_thruput;;
esac

fn_housekeeping

### TABLE DEFINTION FOR RAW DATA COLLECTION
### Use the table definition below to create the table which this script
### requires in order to insert queue data and furthermore query this
### data in order to provide throughput information.
#create table rs_throughput (
#q_name varchar(25),
#sample_time datetime,
#first numeric(8,2),
#last numeric(8,2),
#next numeric(8,2),
#size numeric(8,2),
#mb_processed numeric(8,2),
#thruput numeric(8,2)
#)

# end program
</pre>

[[Category:RepServer]]

RepServer Throughput Script

2009-02-20T00:04:57Z

Bobh: Initial version.

If you want to know just how much data repserver is processing, then this is the tool. 
This script will tell you: 
o average throughput per queue (mb per minute) 
o average throughput combined (all queues) 
o estimated time for a replicate to catch up 
o current queue sizes 
There is also a maximum recorded throughput option but this appears not to work well as some of the figures have been suspicious. 
The real maximum throughput can be established by noting when the replicates start to fall behind in conjunction with the current throughput figures. 
This script can be particularly useful in helping to ascertain if you need to throw more CPU at your repserver box.
<pre>
#!/usr/bin/ksh
#
# Script: rs_throughput.sh
# Author: Bob Holmes - email: cambob@gmail.com
# Date : 19/09/08
# Version : 1.0
# Usage : Run "rs_throughput.sh help" for full usage information.
# Description : This script enables monitoring and reporting
# of repserver throughput.
# --------------------------
# *** Variables for customisation can be found by searching for "!!" ***
# --------------------------
# Modification history:
# 19/02/09 : Bob Holmes : Script modified to enable easier customisation prior
# to release on Sybase wiki. (modification not tested)
# --------------------------
# Comments: This script relies on the reporting..rs_throughput table for data
# collection and reporting. The definition for this table can be
# found at the end of this script.
##############################################################################
# setup environment #
##############################################################################
#
# save input variables
if ! [[ -z $1 ]]
then
export option1=$1
else
export option1="all"
fi

if ! [[ -z $2 ]]
then
export option2=$2
else
export option2="15" # default number of minutes on which to report throughput
fi

# configure environment
HOSTNAME=`hostname`
. $HOME/admin/.syb_cfg.sh $HOSTNAME # !! This line sets up the environment from
# !! from an external shell script.

##############################################################################
# config variables #
##############################################################################
# config:
primary_ds="prim_ds" # !! string used to identify the inbound queue
# queues to query on:-
q_list="prim_ds.db1 prim_ds.db2 replicate_ds.db1 replicate_ds.db2" # !!
insert_filter="db1 db2" # !! data will only be collected for queue names which have this keyword in
REP_SRV="repsrv_rs" # !! the actual repserver from which to get sqm info
RPT_SRV="reporting_ds" # !! dataserver which manages the reporting database
RPT_DB="reporting" # !! reporting database name
RECIPIENTS="user@domain" # !! email config (separate addresses with commas)
ADMINDIR="/opt/home/sybase/admin" # !! sybase admin directory

# static:
SQMFILE="$ADMINDIR/rs_sqm_tmp1.tmp"
DATE=$(date "+%d/%m/%y %H:%M:%S")

# config aware environment variables: # !! You may need to replace the line below with the equivalent
# !! for your own environment.
RISQLCMD="isql -Usa -P`grep "$REP_SRV," $HOME/admin/.servers | cut -d , -f 4` -S$rep_server -w1000"
ISQLCMD="isql -Usa -P${SYBPASS} -S${RPT_SRV} -w200 -D${RPT_DB}"

##############################################################################
# functions #
##############################################################################

check_previous_instance()
{
# Note: next line greps for 8 instances as tests proved that less could cause the script to exit incorrectly
if [ $(ps -ef | grep rs_throughput | egrep -v "vi|grep" | grep -v "sh -c" | wc -l | awk '{print $1}') -gt 8 ]
then
# previous instance still running - probably hanging
echo Previous instance still running.
exit
fi
}

fn_checkusage()
{
if [[ -z $option1 ]]
then
printf " Usage: ./rs_throughput.sh <all>|<q_name>|<help> [<minutes to display>]\n"
exit
fi
}

bcalc()
{
awk 'BEGIN{EQUATION='"$*"';printf("%0.1f\n",EQUATION); exit}'
}

fn_housekeeping()
{
if [ -e $SQMFILE ]
then
rm $SQMFILE
fi
$ISQLCMD <<-EOF
set nocount on
go
delete from rs_throughput where sample_time <= dateadd(dd,-28,getdate())
go
EOF
}

fn_insert_thruput_data()
{
# 1) get current segment usage/progress info
#connect to repserver, get sqm info - put into file
$RISQLCMD << eof > $SQMFILE
admin who,sqm
go
quit
eof

# 2) insert new segment data into rs_throughput table
#date
#echo "Queue Name, First segment, Last segment, Next segment"
cat $SQMFILE | egrep $insert_filter | sed 's/Awaiting Message/AwaitingMessage/; s/Awaiting Wakeup/AwaitingWakeup/; s/$\.[0-9][0-

9]*$\.[0-9]/\1/' | grep -v ":0 ${primary_ds}" | awk '{print $4,$14,$15,$16}' | while read q_name first_seg last_seg next_seg
do
q_size=$(bcalc "$last_seg - $first_seg")
$ISQLCMD <<-EOF
set nocount on
go
declare @mb_processed numeric(8,2)
declare @prev_sample datetime
declare @mins numeric(8,2)
declare @tput numeric(8,2)
if not exists (select 1 from rs_throughput where q_name = "${q_name}")
insert rs_throughput values ("${q_name}", getdate(), ${first_seg}, ${last_seg}, ${next_seg}, ${q_size}, 0, 0)
else
begin
-- get previous sample_time as key
select @prev_sample = max(sample_time) from rs_throughput where q_name = "${q_name}"
-- get difference in mb between current next segment and previous next segment
select @mb_processed = (select ${next_seg} - next from rs_throughput where sample_time = @prev_sample and q_name = "${q_name}")

select @prev_sample=dateadd(ss,-10,@prev_sample) -- # must substract 10 seconds to ensure the next datediff doesn't reflect 2m 58s

as only 2mins!
select @mins = datediff(mi,@prev_sample,getdate())
if @mins > 0
begin
select @tput = round((@mb_processed / @mins),2)
insert rs_throughput values ("${q_name}", getdate(), ${first_seg}, ${last_seg}, ${next_seg}, ${q_size}, @mb_processed, @tput)
end
end
go
EOF
done
}

fn_report_thruput()
{
if [[ $option1 = [0-9]* ]]
then
option2=$option1
option1="all"
fi

if ! [[ "$option1" = "all" ]]
then
q_list=$option1
fi

printf " -------------------------------------\n"
printf " Run date $DATE\n"
printf " -------------------------------------\n"

for q_name in $q_list
do
$ISQLCMD <<-EOF
set nocount on
declare @mb_avg numeric(8,2)
declare @size numeric(8,2)
declare @eta datetime
select convert(char(19),sample_time) sample_time, q_name, size q_size, mb_processed, thruput "thruput (mb/min)" from rs_throughput
--select convert(char(19),sample_time) sample_time, q_name, size q_size, mb_processed, thruput "thruput (mb/min)", dateadd(mi,

(round((size/thruput),0)),getdate()) ETA from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name like "%${q_name}%"

--get mb average
select @mb_avg = convert(numeric(8,2),avg(thruput)) from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name like "%${q_name}%"
--get current queue size
select @size = size from rs_throughput where q_name like "%${q_name}%" having sample_time=max(sample_time)
select @eta = dateadd(mi,round((@size/@mb_avg),0),getdate())
print ""
print " Average for ${q_name}: %1! mb/min, Re-sync ETA: %2!", @mb_avg, @eta
print ""
go
EOF
done
}

fn_show_help_text()
{
printf "Usage examples:\n"
printf "rs_throughput.sh (runs with default values showing all queues over 15 minutes)\n"
printf "rs_throughput.sh <q_name>|<minutes>\n"
printf "rs_throughput.sh <q_name> <minutes>\n"
printf "rs_throughput.sh size|queue (shows current queue sizes)\n"
printf "rs_throughput.sh max (shows highest ever throughput for queue history)\n"
printf "rs_throughput.sh help (shows this helptext)\n"
printf "rs_throughput.sh insert # this is for cron only (inserts data to $RPT_DB..rs_throughput table)\n"
}

fn_show_max_thruput()
{
$ISQLCMD <<-EOF
print ""
set rowcount 0
set nocount on
declare @q_name char(22)
declare @sample_time datetime
declare @max_thruput numeric(8,2)
select q_name, sample_time, thruput into #throughput from rs_throughput where 1=0
print "Maximum throughput per queue:"
-- get list of queues
select distinct q_name, 0 as processed into #queues from rs_throughput
set rowcount 1
select @q_name=q_name from #queues where processed = 0
while @@rowcount != 0
begin
insert #throughput select q_name, sample_time, thruput from rs_throughput
where sample_time in
(select sample_time from rs_throughput where q_name = @q_name having thruput = max(thruput))
and q_name = @q_name
update #queues set processed = 1 where q_name = @q_name
select @q_name=q_name from #queues where processed = 0
end
set rowcount 0
select * from #throughput order by thruput desc
go
EOF
echo
}

fn_show_current_q_size()
{
$RISQLCMD << eof > $SQMFILE
admin who,sqm
go
quit
eof
printf "Current Stable Queue usage:\n"
cat $SQMFILE | egrep $insert_filter | sed 's/Awaiting Message/AwaitingMessage/; s/Awaiting Wakeup/AwaitingWakeup/; s/$\.[0-9][0-

9]*$\.[0-9]/\1/' | grep -v ":0 ${primary_ds}" | awk '{print $4,$14,$15,$16}' | while read q_name first_seg last_seg next_seg
do
q_size=$(bcalc "$last_seg - $first_seg")
printf "$q_name: $q_size\n"
done
}

fn_show_combined_avg_thruput()
{
$ISQLCMD <<-EOF
set rowcount 0
set nocount on
declare @q_name varchar(25)
declare @mb_avg numeric(8,2)
declare @mb_combined_avg numeric(8,2)
select @mb_combined_avg=0
-- get list of queues

select distinct q_name, 0 as processed into #queues from rs_throughput
set rowcount 1
select @q_name=q_name from #queues where processed = 0
while @@rowcount != 0
begin
select @mb_avg = convert(numeric(8,2),avg(thruput)) from rs_throughput
where sample_time >= dateadd(mi,-${option2},getdate()) and q_name = @q_name
select @mb_combined_avg = @mb_combined_avg + @mb_avg
update #queues set processed=1 where q_name = @q_name
select @q_name=q_name from #queues where processed = 0
end
print " Combined thruput average: %1! mb/min", @mb_combined_avg
print ""
go
EOF
}

##############################################################################
# main program #
##############################################################################

check_previous_instance
fn_checkusage

case "$option1" in
help) fn_show_help_text;
exit;;

insert) fn_insert_thruput_data;
exit;;

max) fn_show_max_thruput;
exit;;

queue|size) fn_show_current_q_size;
exit;;

*) fn_report_thruput;
fn_show_combined_avg_thruput;;
esac

fn_housekeeping

### TABLE DEFINTION FOR RAW DATA COLLECTION
### Use the table definition below to create the table which this script
### requires in order to insert queue data and furthermore query this
### data in order to provide throughput information.
#create table rs_throughput (
#q_name varchar(25),
#sample_time datetime,
#first numeric(8,2),
#last numeric(8,2),
#next numeric(8,2),
#size numeric(8,2),
#mb_processed numeric(8,2),
#thruput numeric(8,2)
#)

# end program
</pre>

[[Category:RepServer]]

Sp capture sql--Capture sql of an active spid

2009-02-19T18:05:15Z

Bobh: Initial version.

Note: This SP uses the MDA tables. 
This SP is for capturing the SQL of any active spid. If a spid is not currently processing then nothing will be returned.
Usage: sp__capture_sql [spid]
<pre>
create procedure sp__capture_sql
(
@spid int = null
)
as
------------------------------
-- Procedure: sp__capture_sql
-- Created: April 2008
-- Author: Bob Holmes (Email: cambob@gmail.com)
-- Usage: sp__capture_sql [spid]
------------------------------
if @spid = null
begin
select p.spid, suser_name(p.suid), t.SQLText
from master..monProcessStatement s, master..monProcessSQLText t, master..sysprocesses p
where s.SPID = t.SPID
and t.SPID = p.spid
and p.spid <> @@spid
and suser_name(suid) <> "sa"
end
else
begin
select t.SQLText
from master..monProcessStatement s, master..monProcessSQLText t, master..sysprocesses p
where p.spid = @spid
and s.SPID = t.SPID
and t.SPID = p.spid
end
</pre>
[[Category:ASE]]

Sp mon sql2--ASE spid activity monitor

2009-02-19T17:53:39Z

Bobh:

RepServer Errorlog checker

2009-02-18T19:37:36Z

Bobh:

The script below is for monitoring the repserver errorlog - it works with any version of repserver and does not need to write to the errorlog itself in order to monitor it. However, since it doesn't write to the errorlog, it does need a $ADMINDIR/control_files/ directory which you may wish to create as a non-transient file (rserrlogmarker.dat) is maintained there.
Also, as per the script notes, you can easily modify this script to monitor *any* log; be sure to give rserrlogmarker.dat and the other files in the config section different names or keep them elsewhere so that there is no conflict.

<pre>
#!/usr/bin/ksh
#
# Script: rs_log_checker.sh
# Author: Bob Holmes - email: cambob@gmail.com
# Date : 08/05/2008
# Version: 1.1
# Usage : rs_log_checker.sh [v|V] (verbose mode)
# Description: Script for checking the repserver errorlog
# --------------------------
# *** Variables for customisation can be found by searching for "!!" ***
# --------------------------
# Modification history:
#
# --------------------------
# Enhancement notes:
# BobH: V1.0: Modified to re-create rserrlogmarker file if not found, then re-run automatically.
# BobH: V1.1: Updated for detecting a new log file and resetting rserrlogmarker if so.
# --------------------------
# Comments: This script can easily be modified to monitor any log, including ASE.
# The log itself is never written to so this script maintains a control
# file to keep track of the last line it checked.

##############################################################################
# setup environment #
##############################################################################
HOSTNAME=`hostname`

##############################################################################
# config variables #
##############################################################################
# config
RSLOGFILE="/PATH_TO_ERRORLOG_HERE/servername_rs.log" # !! modify for your environment
ADMINDIR="/opt/home/sybase/admin" # !! modify for your environment
RSLOGMARKER="$ADMINDIR/control_files/rserrlogmarker.dat" # !! need $ADMINDIR/control_files/ directory
RECIPIENTS="user@domain.dom" # !! enter email address config here
# (separate with commas for multiple addresses)
# static
TEMPFILE1="$ADMINDIR/rslogchecker1.tmp" # transient file
TEMPFILE2="$ADMINDIR/rslogchecker2.tmp" # transient file
MAILFILE="$ADMINDIR/rslogchecker.mail" # transient file
SCRIPT_NAME=`basename $0`

##############################################################################
# main program #
##############################################################################

# check location of errorlog
if [ ! -f $RSLOGFILE ]
then
echo RS errorlog file not found. Please check config.
exit
fi

# get log marker value from file (or otherwise create it)
# V1.1: marker is now a line number
if [ ! -f $RSLOGMARKER ]
then
MARKER=$(cat -n $RSLOGFILE | tail -1 | awk '{print $1}')
echo $MARKER > $RSLOGMARKER
#echo "rs log marker reset"
$SCRIPT_NAME # must re-run after marker file reset
exit
else
MARKER=$(cat $RSLOGMARKER)
fi

# Compare number of lines in log file with current marker value to check
# if a new log has been created/repserver restarted.
if [ $(wc -l $RSLOGFILE | awk '{print $1}') -lt $MARKER ]
then
# new log file created/repserver restarted
printf "${SCRIPT_NAME}: New log file detected\n" > $TEMPFILE2
RESTARTDT=$(head -1 $RSLOGFILE | awk '{print $2,$3}')
printf "${SCRIPT_NAME}: Repserver restarted at: ${RESTARTDT}\n\n" >> $TEMPFILE2
printf "Errors/Warnings from startup follow:\n" >> $TEMPFILE2
printf "------------------------------------\n" >> $TEMPFILE2
# pick out any errors/or warnings from log file:
egrep "^E. |^W. " $RSLOGFILE >> $TEMPFILE2
mailx -s "$HOSTNAME: Repserver restarted!" $RECIPIENTS < $TEMPFILE2
rm $RSLOGMARKER # force reset of marker file
rm $TEMPFILE2
exit
fi

if [ "$1" = "V" ] || [ "$1" = "v" ] # verbose (diag) mode
then
echo Current marker value is: $MARKER
fi

# increment the marker (line number) to avoid re-reading same last line
((MARKER=MARKER+1))
sed -n "$MARKER,$ p" $RSLOGFILE > $TEMPFILE1
######### next executable line checks for errors #########
######### add your search terms to the egrep below #######
EMSGS=$(egrep -c "^E\. |^W\. " $TEMPFILE1)
if [ $EMSGS -ne 0 ]
then
if [ "$1" = "V" ] || [ "$1" = "v" ] # verbose (diag) mode
then
echo Error message found: $HOSTNAME: $EMSGS messages
cat $TEMPFILE1
echo END OF REPORT
else
cp $TEMPFILE1 $MAILFILE
printf "-------CALLOUT SYBASE DBA - ON CALL----------------\n" >> $MAILFILE # !! optional
mailx -s "$HOSTNAME: $EMSGS errors found in repserver errorlog" $RECIPIENTS < $MAILFILE
fi
fi

#update marker:
# Note: use line count from TEMPFILE1 + (MARKER-1) to avoid re-scanning the whole errorlog;
# cat/cp'ing large log files can cause load issues so we don't do that!
LINECOUNT=$(wc -l $TEMPFILE1 | awk '{print $1}')
((MARKER=MARKER+LINECOUNT-1))
echo $MARKER > $RSLOGMARKER
if [ "$1" = "V" ] || [ "$1" = "v" ] # verbose (diag) mode
then
echo New marker value is: $MARKER
fi

# housekeeping
if [ -e $TEMPFILE1 ]
then
rm $TEMPFILE1
fi
if [ -e $TEMPFILE2 ]
then
rm $TEMPFILE2
fi

#end script
</pre>

[[Category:RepServer]]

RepServer Errorlog checker

2009-02-18T19:35:27Z

Bobh:

The script below is for monitoring the repserver errorlog - it works with any version of repserver and does not need to write to the errorlog itself in order to monitor it. However, since it doesn't write to the errorlog, it does need a $ADMINDIR/control_files/ directory which you may wish to create as a non-transient file (rserrlogmarker.dat) is maintained there.
Also, as per the script notes, you can easily modify this script to monitor *any* log; be sure to give rserrlogmarker.dat and the other files in the config section different names or keep them elsewhere so that there is no conflict.

<pre>
#!/usr/bin/ksh
#
# Script: rs_log_checker.sh
# Author: Bob Holmes - email: cambob@gmail.com
# Date : 08/05/2008
# Version: 1.1
# Usage : rs_log_checker.sh [v|V] (verbose mode)
# Description: Script for checking the repserver errorlog
# --------------------------
# *** Variables for customisation can be found by searching for "!!" ***
# --------------------------
# Modification history:
#
# --------------------------
# Enhancement notes:
# BobH: V1.0: Modified to re-create rserrlogmarker file if not found, then re-run automatically.
# BobH: V1.1: Updated for detecting a new log file and resetting rserrlogmarker if so.
# --------------------------
# Comments: This script can easily be modified to monitor any log, including ASE.
# The log itself is never written to so this script maintains a control
# file to keep track of the last line it checked.

##############################################################################
# setup environment #
##############################################################################
HOSTNAME=`hostname`

##############################################################################
# config variables #
##############################################################################
# config
RSLOGFILE="/PATH_TO_ERRORLOG_HERE/servername_rs.log" # !! modify for your environment
ADMINDIR="/opt/home/sybase/admin" # !! modify for your environment

# static
RSLOGMARKER="$ADMINDIR/control_files/rserrlogmarker.dat" # !! need $ADMINDIR/control_files/ directory
TEMPFILE1="$ADMINDIR/rslogchecker1.tmp" # transient file
TEMPFILE2="$ADMINDIR/rslogchecker2.tmp" # transient file
MAILFILE="$ADMINDIR/rslogchecker.mail" # transient file
RECIPIENTS="user@domain.dom" # !! enter email address config here
# (separate with commas for multiple addresses)
SCRIPT_NAME=`basename $0`

##############################################################################
# main program #
##############################################################################

# check location of errorlog
if [ ! -f $RSLOGFILE ]
then
echo RS errorlog file not found. Please check config.
exit
fi

# get log marker value from file (or otherwise create it)
# V1.1: marker is now a line number
if [ ! -f $RSLOGMARKER ]
then
MARKER=$(cat -n $RSLOGFILE | tail -1 | awk '{print $1}')
echo $MARKER > $RSLOGMARKER
#echo "rs log marker reset"
$SCRIPT_NAME # must re-run after marker file reset
exit
else
MARKER=$(cat $RSLOGMARKER)
fi

# Compare number of lines in log file with current marker value to check
# if a new log has been created/repserver restarted.
if [ $(wc -l $RSLOGFILE | awk '{print $1}') -lt $MARKER ]
then
# new log file created/repserver restarted
printf "${SCRIPT_NAME}: New log file detected\n" > $TEMPFILE2
RESTARTDT=$(head -1 $RSLOGFILE | awk '{print $2,$3}')
printf "${SCRIPT_NAME}: Repserver restarted at: ${RESTARTDT}\n\n" >> $TEMPFILE2
printf "Errors/Warnings from startup follow:\n" >> $TEMPFILE2
printf "------------------------------------\n" >> $TEMPFILE2
# pick out any errors/or warnings from log file:
egrep "^E. |^W. " $RSLOGFILE >> $TEMPFILE2
mailx -s "$HOSTNAME: Repserver restarted!" $RECIPIENTS < $TEMPFILE2
rm $RSLOGMARKER # force reset of marker file
rm $TEMPFILE2
exit
fi

if [ "$1" = "V" ] || [ "$1" = "v" ] # verbose (diag) mode
then
echo Current marker value is: $MARKER
fi

# increment the marker (line number) to avoid re-reading same last line
((MARKER=MARKER+1))
sed -n "$MARKER,$ p" $RSLOGFILE > $TEMPFILE1
######### next executable line checks for errors #########
######### add your search terms to the egrep below #######
EMSGS=$(egrep -c "^E\. |^W\. " $TEMPFILE1)
if [ $EMSGS -ne 0 ]
then
if [ "$1" = "V" ] || [ "$1" = "v" ] # verbose (diag) mode
then
echo Error message found: $HOSTNAME: $EMSGS messages
cat $TEMPFILE1
echo END OF REPORT
else
cp $TEMPFILE1 $MAILFILE
printf "-------CALLOUT SYBASE DBA - ON CALL----------------\n" >> $MAILFILE # !! optional
mailx -s "$HOSTNAME: $EMSGS errors found in repserver errorlog" $RECIPIENTS < $MAILFILE
fi
fi

#update marker:
# Note: use line count from TEMPFILE1 + (MARKER-1) to avoid re-scanning the whole errorlog;
# cat/cp'ing large log files can cause load issues so we don't do that!
LINECOUNT=$(wc -l $TEMPFILE1 | awk '{print $1}')
((MARKER=MARKER+LINECOUNT-1))
echo $MARKER > $RSLOGMARKER
if [ "$1" = "V" ] || [ "$1" = "v" ] # verbose (diag) mode
then
echo New marker value is: $MARKER
fi

# housekeeping
if [ -e $TEMPFILE1 ]
then
rm $TEMPFILE1
fi
if [ -e $TEMPFILE2 ]
then
rm $TEMPFILE2
fi

#end script
</pre>

[[Category:RepServer]]

Sp mon sql2--ASE spid activity monitor

2009-02-18T19:04:24Z

Bobh: Initial version.

RepServer Errorlog checker

2009-02-18T18:22:55Z

Bobh:

The script below is for monitoring the repserver errorlog - it works with any version of repserver and does not need to write to the errorlog itself in order to monitor it. However, since it doesn't write to the errorlog, it does need a $ADMINDIR/control_files/ directory which you may wish to create as a non-transient file (rserrlogmarker.dat) is maintained there.
Also, as per the script notes, you can easily modify this script to monitor *any* log; be sure to give rserrlogmarker.dat and the other files in the config section different names or keep them elsewhere so that there is no conflict.

<pre>
#!/usr/bin/ksh
#
# Script: rs_log_checker.sh
# Author: Bob Holmes - email: cambob@gmail.com)
# Date : 08/05/2008
# Version: 1.1
# Usage : rs_log_checker.sh [v|V] (verbose mode)
# Description: Script for checking the repserver errorlog
# --------------------------
# *** Variables for customisation can be found by searching for "!!" ***
# --------------------------
# Modification history:
#
# --------------------------
# Enhancement notes:
# BobH: V1.0: Modified to re-create rserrlogmarker file if not found, then re-run automatically.
# BobH: V1.1: Updated for detecting a new log file and resetting rserrlogmarker if so.
# --------------------------
# Comments: This script can easily be modified to monitor any log, including ASE.
# The log itself is never written to so this script maintains a control
# file to keep track of the last line it checked.

##############################################################################
# setup environment #
##############################################################################
HOSTNAME=`hostname`

##############################################################################
# config variables #
##############################################################################
# config
RSLOGFILE="/PATH_TO_ERRORLOG_HERE/servername_rs.log" # !! modify for your environment
ADMINDIR="/opt/home/sybase/admin" # !! modify for your environment

# static
RSLOGMARKER="$ADMINDIR/control_files/rserrlogmarker.dat" # !! need $ADMINDIR/control_files/ directory
TEMPFILE1="$ADMINDIR/rslogchecker1.tmp" # transient file
TEMPFILE2="$ADMINDIR/rslogchecker2.tmp" # transient file
MAILFILE="$ADMINDIR/rslogchecker.mail" # transient file
RECIPIENTS="user@domain.dom" # !! enter email address config here
# (separate with commas for multiple addresses)
SCRIPT_NAME=`basename $0`

##############################################################################
# main program #
##############################################################################

# check location of errorlog
if [ ! -f $RSLOGFILE ]
then
echo RS errorlog file not found. Please check config.
exit
fi

# get log marker value from file (or otherwise create it)
# V1.1: marker is now a line number
if [ ! -f $RSLOGMARKER ]
then
MARKER=$(cat -n $RSLOGFILE | tail -1 | awk '{print $1}')
echo $MARKER > $RSLOGMARKER
#echo "rs log marker reset"
$SCRIPT_NAME # must re-run after marker file reset
exit
else
MARKER=$(cat $RSLOGMARKER)
fi

# Compare number of lines in log file with current marker value to check
# if a new log has been created/repserver restarted.
if [ $(wc -l $RSLOGFILE | awk '{print $1}') -lt $MARKER ]
then
# new log file created/repserver restarted
printf "${SCRIPT_NAME}: New log file detected\n" > $TEMPFILE2
RESTARTDT=$(head -1 $RSLOGFILE | awk '{print $2,$3}')
printf "${SCRIPT_NAME}: Repserver restarted at: ${RESTARTDT}\n\n" >> $TEMPFILE2
printf "Errors/Warnings from startup follow:\n" >> $TEMPFILE2
printf "------------------------------------\n" >> $TEMPFILE2
# pick out any errors/or warnings from log file:
egrep "^E. |^W. " $RSLOGFILE >> $TEMPFILE2
mailx -s "$HOSTNAME: Repserver restarted!" $RECIPIENTS < $TEMPFILE2
rm $RSLOGMARKER # force reset of marker file
rm $TEMPFILE2
exit
fi

if [ "$1" = "V" ] || [ "$1" = "v" ] # verbose (diag) mode
then
echo Current marker value is: $MARKER
fi

# increment the marker (line number) to avoid re-reading same last line
((MARKER=MARKER+1))
sed -n "$MARKER,$ p" $RSLOGFILE > $TEMPFILE1
######### next executable line checks for errors #########
######### add your search terms to the egrep below #######
EMSGS=$(egrep -c "^E\. |^W\. " $TEMPFILE1)
if [ $EMSGS -ne 0 ]
then
if [ "$1" = "V" ] || [ "$1" = "v" ] # verbose (diag) mode
then
echo Error message found: $HOSTNAME: $EMSGS messages
cat $TEMPFILE1
echo END OF REPORT
else
cp $TEMPFILE1 $MAILFILE
printf "-------CALLOUT SYBASE DBA - ON CALL----------------\n" >> $MAILFILE # !! optional
mailx -s "$HOSTNAME: $EMSGS errors found in repserver errorlog" $RECIPIENTS < $MAILFILE
fi
fi

#update marker:
# Note: use line count from TEMPFILE1 + (MARKER-1) to avoid re-scanning the whole errorlog;
# cat/cp'ing large log files can cause load issues so we don't do that!
LINECOUNT=$(wc -l $TEMPFILE1 | awk '{print $1}')
((MARKER=MARKER+LINECOUNT-1))
echo $MARKER > $RSLOGMARKER
if [ "$1" = "V" ] || [ "$1" = "v" ] # verbose (diag) mode
then
echo New marker value is: $MARKER
fi

# housekeeping
if [ -e $TEMPFILE1 ]
then
rm $TEMPFILE1
fi
if [ -e $TEMPFILE2 ]
then
rm $TEMPFILE2
fi

#end script
</pre>

[[Category:RepServer]]

RepServer Errorlog checker

2009-02-18T18:17:19Z

Bobh: Initial version.

The script below is for monitoring the repserver errorlog - it works with any version of repserver and does not need to write to the errorlog itself in order to monitor it. However, since it doesn't write to the errorlog, it does need a $ADMINDIR/control_files/ directory which you may wish to create as a non-transient file (rserrlogmarker.dat) is maintained there.
Also, as per the script notes, you can easily modify this script to monitor *any* log; be sure to give rserrlogmarker.dat and the other files in the config section different names or keep them elsewhere so that there is no conflict.

<pre>
#!/usr/bin/ksh
#
# Script: rs_log_checker.sh
# Author: Bob Holmes - email: cambob@gmail.com)
# Date : 08/05/2008
# Version: 1.1
# Usage : rs_log_checker.sh [v|V] (verbose mode)
# Description: Script for checking the repserver errorlog
# --------------------------
# *** Variables for customisation can be found by searching for "!!" ***
# --------------------------
# Modification history:
#
# --------------------------
# Enhancement notes:
# BobH: V1.0: Modified to re-create rserrlogmarker file if not found, then re-run automatically.
# BobH: V1.1: Updated for detecting a new log file and resetting rserrlogmarker if so.
# --------------------------
# Comments: This script can easily be modified to monitor any log, including ASE.
# The log itself is never written to so this script maintains a control
# file to keep track of the last line it checked.

##############################################################################
# setup environment #
##############################################################################
HOSTNAME=`hostname`

##############################################################################
# config variables #
##############################################################################
# config
RSLOGFILE="/PATH_TO_ERRORLOG_HERE/servername_rs.log" # !! modify for your environment
ADMINDIR="/opt/home/sybase/admin" # !! modify for your environment

# static
RSLOGMARKER="$ADMINDIR/control_files/rserrlogmarker.dat" # !! need $ADMINDIR/control_files/ directory
TEMPFILE1="$ADMINDIR/rslogchecker1.tmp" # transient file
TEMPFILE2="$ADMINDIR/rslogchecker2.tmp" # transient file
MAILFILE="$ADMINDIR/rslogchecker.mail" # transient file
RECIPIENTS="user@domain.dom" # !! enter email address config here
# (separate with commas for multiple addresses)
SCRIPT_NAME=`basename $0`

##############################################################################
# main program #
##############################################################################

# check location of errorlog
if [ ! -f $RSLOGFILE ]
then
echo RS errorlog file not found. Please check config.
exit
fi

# get log marker value from file (or otherwise create it)
# V1.1: marker is now a line number
if [ ! -f $RSLOGMARKER ]
then
MARKER=$(cat -n $RSLOGFILE | tail -1 | awk '{print $1}')
echo $MARKER > $RSLOGMARKER
#echo "rs log marker reset"
$SCRIPT_NAME # must re-run after marker file reset
exit
else
MARKER=$(cat $RSLOGMARKER)
fi

# Compare number of lines in log file with current marker value to check
# if a new log has been created/repserver restarted.
if [ $(wc -l $RSLOGFILE | awk '{print $1}') -lt $MARKER ]
then
# new log file created/repserver restarted
printf "${SCRIPT_NAME}: New log file detected\n" > $TEMPFILE2
RESTARTDT=$(head -1 $RSLOGFILE | awk '{print $2,$3}')
printf "${SCRIPT_NAME}: Repserver restarted at: ${RESTARTDT}\n\n" >> $TEMPFILE2
printf "Errors/Warnings from startup follow:\n" >> $TEMPFILE2
printf "------------------------------------\n" >> $TEMPFILE2
# pick out any errors/or warnings from log file:
egrep "^E. |^W. " $RSLOGFILE >> $TEMPFILE2
mailx -s "$HOSTNAME: Repserver restarted!" $RECIPIENTS < $TEMPFILE2
rm $RSLOGMARKER # force reset of marker file
rm $TEMPFILE2
exit
fi

if [ "$1" = "V" ] || [ "$1" = "v" ] # verbose (diag) mode
then
echo Current marker value is: $MARKER
fi

# increment the marker (line number) to avoid re-reading same last line
((MARKER=MARKER+1))
sed -n "$MARKER,$ p" $RSLOGFILE > $TEMPFILE1
######### next executable line checks for errors #########
######### add your search terms to the egrep below #######
EMSGS=$(egrep -c "^E\. |^W\. " $TEMPFILE1)
if [ $EMSGS -ne 0 ]
then
if [ "$1" = "V" ] || [ "$1" = "v" ] # verbose (diag) mode
then
echo Error message found: $HOSTNAME: $EMSGS messages
cat $TEMPFILE1
echo END OF REPORT
else
cp $TEMPFILE1 $MAILFILE
printf "-------CALLOUT SYBASE DBA - ON CALL----------------\n" >> $MAILFILE # !! optional
mailx -s "$HOSTNAME: $EMSGS errors found in repserver errorlog" $RECIPIENTS < $MAILFILE
fi
fi

#update marker:
# Note: use line count from TEMPFILE1 + (MARKER-1) to avoid re-scanning the whole errorlog;
# cat/cp'ing large log files can cause load issues so we don't do that!
LINECOUNT=$(wc -l $TEMPFILE1 | awk '{print $1}')
((MARKER=MARKER+LINECOUNT-1))
echo $MARKER > $RSLOGMARKER
if [ "$1" = "V" ] || [ "$1" = "v" ] # verbose (diag) mode
then
echo New marker value is: $MARKER
fi

# housekeeping
if [ -e $TEMPFILE1 ]
then
rm $TEMPFILE1
fi
if [ -e $TEMPFILE2 ]
then
rm $TEMPFILE2
fi

#end script
</pre>