set nocount on
go

-- ------------------------------------------------------------------
-- Check version

if @@version not like '%12.5%'
        select 'v12.5 : Skipping install, ' + @@servername + ' is on version ' + convert(varchar(40),@@version)
go
if @@version not like '%12.5%'
        select syb_quit()
go



-- ------------------------------------------------------------------
-- Enable capture

--exec sp_configure 'enable monitoring', 1
--exec sp_configure 'process wait events', 1
--exec sp_configure 'wait event timing', 1
go


-- ------------------------------------------------------------------
-- Prepare temporary objects

-- first in model first to be available when server restarts

use model
go
	
if object_id('dba_mon_monProcessWaits' ) is not null
	drop table dba_mon_monProcessWaits
go
CREATE TABLE  dba_mon_monProcessWaits                                                 
 (                                                                     
  Time         time                        NOT NULL,                   
  SPID         smallint                     NOT NULL,                  
  KPID         int                     NOT NULL,                  
--  ServerUserID int 			not null,
  WaitEventID  smallint                     NOT NULL,                  
  Waits        int                        NOT NULL,                    
  WaitTime     int                        NOT NULL                     
 )                                                                     
go

-- then in tempdb

use tempdb
go
if object_id('dba_mon_monProcessWaits' ) is not null
	drop table dba_mon_monProcessWaits
go
select * into dba_mon_monProcessWaits from model..dba_mon_monProcessWaits
go


-- ------------------------------------------------------------------
-- Create proc

use sybsystemprocs
go
dump tran sybsystemprocs with truncate_only
go

if object_id('sp_dba_getwe') is not null
        drop proc sp_dba_getwe
go
create proc sp_dba_getwe
/*
# ############################################################################
# @(#) Proc    : sp_dba_getwe
# @(#) Auteur  : FA
# @(#) Cree le : 20100913
# @(#) Objet   : Wait events using mda
# @(#) Usage   : sp_dba_getwe [SPID] [ , [OUTPUTTYPE=0|1|2] [ , [CLEAR=0|1] ] , [SHOWUSAGE=0|1] ]
# @(#)           SPID : Process ID
# @(#)           OUTPUTTYPE : 0 = Event detail / 1 = Event summary / 2 = Class summary
# @(#)           CLEAR : 0 = maintain history / 1 = clear history
# @(#)           SHOWUSAGE : 0 = hide usage / 1 = showusage
# @(#) Exemples: sp_dba_getwe
# @(#) Depend. : MDA history maintained in tempdb, structure is first created
# @(#)           on model db to maintain the structure after instance reboot
# @(#) Version : ASE 12.5
# ############################################################################
# Mises a jour
# ----------------------------------------------------------------------------
# ############################################################################
*/

/*
# ############################################################################
# Parameters
*/

	@SPID int = null,
	@outputtype tinyint = 0,
	@clear tinyint = 0, 
	@showusage tinyint = 1
as
begin

/*
# ############################################################################
# Environment
*/

	set nocount on

/*
# ----------------------------------------------------------------------------
# Show usage
*/

if @showusage = 1
begin
        declare @SPID_str varchar(5)
        select @SPID_str=isnull(convert(varchar(5), @SPID),"NULL")

        print ""
	print "-- Usage   : sp_dba_getwe [SPID] [ , [OUTPUTTYPE=0|1|2] [ , [CLEAR=0|1] ] , [SHOWUSAGE=0|1] ]"
        print "-- Running : sp_dba_getwe %1! , %2! , %3! , %4! ",  @SPID_str , @outputtype, @clear, @showusage
        print ""
end

/*
# ----------------------------------------------------------------------------
# Extract MDA
*/

	select * into #tmp_monProcessWaits        from master.dbo.monProcessWaits

/*
# ----------------------------------------------------------------------------
# Store history
*/

	-- remove rows if necessary
	delete tempdb..dba_mon_monProcessWaits where SPID=isnull(@SPID,SPID) and @clear != 0
	delete tempdb..dba_mon_monProcessWaits  
	from tempdb..dba_mon_monProcessWaits a 
	left join #tmp_monProcessWaits b on b.SPID=a.SPID and b.KPID=a.KPID 
	where b.SPID is null
	

	insert 	into tempdb..dba_mon_monProcessWaits 
		(
			Time,
			SPID,KPID,WaitEventID,Waits,WaitTime
		)
	select  getdate(),
		a.SPID, a.KPID, a.WaitEventID,  a.Waits, a.WaitTime 
	from    #tmp_monProcessWaits a


/*
# ############################################################################
# Program
*/

	select * into #data from tempdb..dba_mon_monProcessWaits where SPID=isnull(@SPID,SPID)

	-- only if at least 2 snapshots
	if ( select count(distinct Time) from #data ) < 2
	begin
		print 'At least 2 snapshots'
		return 0
	end

/*
# ----------------------------------------------------------------------------
# Compute stats
*/

select 	SPID, 
	WaitEventID, 	
	min(Time) 'mintime', 
	max(Time) 'maxtime'  
into 	#scope 
from 	#data 
where 	SPID=isnull(@SPID,SPID)
group by SPID, WaitEventID

select  a.SPID, 
	a.WaitEventID,
	d.WaitClassID,
	( c.Waits - isnull(b.Waits,0) ) 	'Waits',
	( c.WaitTime - isnull(b.WaitTime,0) ) 	'WaitTime',
	b.Time 					'Time_o',
	b.Waits 				'Waits_o', 
	b.WaitTime 				'WaitTime_o',
	c.Time 					'Time_l',
	c.Waits 				'Waits_l', 
	c.WaitTime				'WaitTime_l',
	b.Waits 				'Waits_f', 
	b.WaitTime 				'WaitTime_f',
               str_replace(
                str_replace(
                str_replace(
                        d.Description,
                'waiting ',null),
                'wait ', null),
                'for ', null) 'Evt',
                str_replace(
                str_replace(
                str_replace(
                        e.Description,
                'waiting ',null),
                'wait ', null),
                'for ', null) 'Cls'

into 	#stats
from 		#scope 				a 
join 		#data 				c on c.SPID=a.SPID and c.WaitEventID=a.WaitEventID and c.Time=a.maxtime
left join 	#data 				b on b.SPID=a.SPID and b.WaitEventID=a.WaitEventID and b.Time=a.mintime
join 		master.dbo.monWaitEventInfo 	d on d.WaitEventID  = a.WaitEventID
join 		master.dbo.monWaitClassInfo 	e on e.WaitClassID=d.WaitClassID

declare @total float
select  @total=sum(WaitTime*1.0)+1 from #stats 

/*
# ----------------------------------------------------------------------------
# OUTPUT TYPE 0 = Class report
*/

if @outputtype = 0 and @SPID is not null
begin
select  a.SPID, 
        'Cl_Evt'	= convert(varchar(5),a.WaitClassID) + '_' + convert(varchar(5),a.WaitEventID),
	'Description'	= convert( varchar(60), a.Cls  + '/' + a.Evt), 
	'T_Waits'	= a.Waits_l,
	'T_Seconds'	= a.WaitTime_l/1000,
	'DtFrom'	= convert(char(8),a.Time_o,108), 
	'DtTo'		= convert(char(8),a.Time_l,108),
	'Waits'		= a.Waits,
	'Seconds'	= a.WaitTime/1000,
	'sPct'		= convert(smallint,convert(float,100.0*a.WaitTime)/@total) 
into 	#result0
from 	#stats a 
order by WaitClassID, WaitEventID, WaitTime desc

exec sp_autoformat #result0
end


/*
# ----------------------------------------------------------------------------
# OUTPUT TYPE 1 = Class report
*/

if @outputtype = 1 or (@outputtype = 0  and @SPID is null ) 
begin
select  
        'Cl_Evt'	= convert(varchar(5),a.WaitClassID) + '/' + convert(varchar(5),a.WaitEventID),
	'Description'	= convert( varchar(60), a.Cls  + '/' + a.Evt), 
	'T_Waits'	= sum(a.Waits_l),
	'T_Seconds'	= sum(a.WaitTime_l/1000),
	'dtFrom'	= min(convert(char(8),a.Time_o,108)),
	'dtTo'		= max(convert(char(8),a.Time_l,108)),
	'Waits'		= sum(a.Waits),
	'Seconds'	= sum(a.WaitTime/1000),
	'sPct'		= convert(int,convert(float,100.0*sum(a.WaitTime))/@total) 
into 	#result1
from 	#stats a 
group by 
        convert(varchar(5),a.WaitClassID) + '/' + convert(varchar(5),a.WaitEventID),
	convert( varchar(60), a.Cls  + '/' + a.Evt)
order by 1

exec sp_autoformat #result1
end


/*
# ----------------------------------------------------------------------------
# OUTPUT TYPE 2 = Class report
*/

if @outputtype = 2
begin
select  
        'Cls'		= a.WaitClassID ,
	'Description'	= convert( varchar(30), a.Cls  ) ,
	'T_Waits'	= convert(numeric(10),sum(a.Waits_l*1.0)),
	'T_Seconds'	= convert(numeric(10),sum(a.WaitTime_l*1.0))/1000,
	'dtFrom'	= min(convert(char(8),a.Time_o,108)),
	'dtTo'		= max(convert(char(8),a.Time_l,108)),
	'Waits'		= convert(numeric(10),sum(a.Waits*1.0)),
	'Seconds'	= sum(a.WaitTime)/1000,
	'sPct'		= convert(smallint,convert(float,100.0*sum(a.WaitTime))/@total) 
into 	#result2
from 	#stats a 
group by 
        a.WaitClassID ,
	convert( varchar(30), a.Cls  ) 
order by 1

exec sp_autoformat #result2
end

end

go

-- ------------------------------------------------------------------
-- Permissions

grant exec on sp_dba_getwe to public
go

-- ------------------------------------------------------------------
-- Control

if object_id('sp_dba_getwe') is not null
        print 'sp_dba_getwe install OK on %1!', @@servername
else
        print 'sp_dba_getwe install FAILED on %1!', @@servername
go

