Sybase ASE memory dump

1. from unix prompt run this command.
2 set DSQUERY to your servername
3.dataserver -X
4. password is quine
5. you will get
:active>
5. at the prompt type following
memdump
6. the filename will be in your $SYBASE directory.
7. send output of (when you send dump )
which dataserver

or

1 dataserver -X
2 password is quine
3 cat /cis_apps/sybase/1252/ASE_12-5
4 attach
5 memdump
6 detach

Parse sysmon report using awk

## Awk Script for processing sysmon reports
## engine.awk -- Summarizes data on engine busy utilization

BEGIN {printf "Date\tTime\tEng 0\tEng 1\tEng 2\tEng 3\tEng4\tEng 5\tEng 6\n";headercount=1;}

## Get Time & Date for Sysmon Report
/Run Date/ {printf "%s %s %s\t", $3, $4, $5 }
/Cleared/ {printf"%s\t", $4 }

## Get Engine Busy Utilization
/Engine Busy/,/Summary/ {
headercount+=1;
if ($2 == 0 || $2 == 1 || $2 == 2 || $2 == 3 || $2 == 4 || $2 == 5 || $2 == 6)
{
printf "%s\t", $3;
}

if ($1 == "Summary")
{
printf "\n";
}
}

search_ver_all

CREATE PROCEDURE dbo.search_ver_all
@schema_name varchar(20),
@table_name varchar(20)
AS
BEGIN
set transaction isolation level 0
-- find out the forms that have been checked in
-- and lastest version is not same as in production.
select a.ulabel form ,a.uvers develop, b.uvers production
from UNF7..uform a , UNF7PROD..uform b
where a.ulabel in
(
select uform
from uxgroup
where ubase = @schema_name
and ulabel in (
select u_glab
from ucgroup
where u_tlab = @table_name
and u_vlab = @schema_name
)
)
and a.ulabel = b.ulabel
and substring(a.uvers,charindex('F',a.uvers)+1, char_length(a.uvers)) != substring(b.uvers,charindex('F',b.uvers)+1, char_length(b.uvers))
and a.uvers != 'DEFFREE'

union
-- find out the forms that have been checked out
-- and lastest version is not same as in production.
select a.ulabel form, max(v.uversion) develop , b.uvers production
from UNF7..uform a, UNF7PROD..uform b, UNF7..uvvers v
where a.ulabel in
(
select uform
from uxgroup
where ubase = @schema_name
and ulabel in (
select u_glab
from ucgroup
where u_tlab = @table_name
and u_vlab = @schema_name
)
)
and a.ulabel = b.ulabel
and a.ulabel = v.ulabel
and a.uvers = 'DEFFREE'
group by a.ulabel , b.uvers
having substring(max(v.uversion),charindex('F',max(v.uversion))+1, char_length(max(v.uversion))) != substring(b.uvers,charindex('F',b.uvers)+1, char_length(b.uvers))
END

Capture SQL of running process

dbcc traceon(3604)
go
dbcc pss(suid,spid,1)
go
or
dbcc sqltext(spid)
go
(output if limited to the first 400 bytes of sql text)

How monitor elapsed time of stored procedures

select ProcName = isnull(object_name(ProcedureID,DBID), "UNKNOW"),
DBName = isnull(db_name(DBID), "UNKNOWN"),
ElapsedTime = datediff(ms,min(StartTime), max(EndTime))
from monSysStatement
group by SPID,DBID,ProcedureID,BatchID
having ProcedureID != 0

mon table - wait events

select Waits,WaitTime,Description
from monWaitEventInfo i, monSysWaits s
where i.WaitEventID = s.WaitEventID
order by 1

Using repserver counter

1) enable flushing counter infor to RSSD
configure replication server set 'stats_daemon_sleep_time' to '60';
configure replication server set 'stats_flush_rssd' to 'on';

2) check flush status
admin statistics, flush_status;

3) turn on flushing for a module
admin stats_config_module, SQM,'on';

4) view counter information in RSSD
rs_statdetail rs_statrun rs_statcounters

select counter_name, module_name, instance_id,
counter_val, run_date
from rs_statcounters c,
rs_statdetail d,
rs_statrun r
where c.counter_id = d.counter_id
and d.run_id = r.run_id
and c.module_name = 'CM'
order by counter_name,run_date


5) To view current values for all enabled counters
admin statistics, 'all_modules'

6) to view a summary of values for DSI,DSIEXEC,REPAGENT and RSI
admin statistics, sysmon [, sample_period]

7) to view current values for one or all counters of a module
admin statistics, module_name [, display_name]

How to trace deadlock

1) sp_configure "print deadlock information",1

2) add traceflag 602/603/8203 to trace deadlock info and print sql statement

Sybase Code Exchange

www.codexchange.sybase.com
jamesp@yahoo.com
synnex