set nocount on
go

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

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


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

use master
go
--exec sp_configure 'enable monitoring', 1  
--exec sp_configure 'SQL batch capture',1 
--exec sp_configure 'sql text pipe max messages', 2000
--exec sp_configure 'sql text pipe active', 1
--exec sp_configure 'statement statistics active', 1
--exec sp_configure 'per object statistics active', 1
--exec sp_configure 'statement pipe max messages', 2000
--exec sp_configure 'statement pipe active' , 1 
go 


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

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

use model
go 

if object_id('dba_mon_monSysStatement') is not null 
	drop table dba_mon_monSysStatement
go

CREATE TABLE  dba_mon_monSysStatement
(
SPID 			smallint                NOT NULL,
KPID 			int                     NOT NULL,
DBID 			int                     NOT NULL,
ProcedureID 		int                     NOT NULL,
PlanID			int                     NOT NULL,
BatchID 		int                     NOT NULL,
ContextID 		int                     NOT NULL,
LineNumber		int                     NOT NULL,
CpuTime 		int                     NOT NULL,
WaitTime 		int                     NOT NULL,
MemUsageKB		int                     NOT NULL,
PhysicalReads 		int                     NOT NULL,
LogicalReads 		int                     NOT NULL,
PagesModified 		int                     NOT NULL,
PacketsSent		int                     NOT NULL,
PacketsReceived 	int                     NOT NULL,
NetworkPacketSize 	int                     NOT NULL,
PlansAltered 		int                     NOT NULL,
RowsAffected 		int                     NOT NULL,
ErrorStatus 		int                     NOT NULL,
HashKey 		int                     NOT NULL,
SsqlId 			int                     NOT NULL,
--ProcNestLevel		int          		NOT NULL,
--StatementNumber		int          		NOT NULL,
DBName			varchar(30)             NULL,
StartTime 		datetime                NULL,
EndTime 		datetime                NULL
)
go
create index idx1 on dba_mon_monSysStatement( SPID ) 
go

if object_id('dba_mon_monSysSQLText') is not null       
	drop table dba_mon_monSysSQLText
go

CREATE TABLE  dba_mon_monSysSQLText
(
SPID             smallint                     NOT NULL,
KPID             int                        NOT NULL,
ServerUserID     int                        NOT NULL,
BatchID          int                        NOT NULL,
SequenceInBatch  int                        NOT NULL,
SQLText          varchar(255)                 NULL
)
go
create index idx1 on dba_mon_monSysSQLText( SPID ) 
go


-- then in tempdb

use tempdb
go 
if object_id('dba_mon_monSysStatement') is not null 
	drop table dba_mon_monSysStatement
go
select * into dba_mon_monSysStatement from model.dbo.dba_mon_monSysStatement
go
create index idx1 on dba_mon_monSysStatement( SPID ) 
go
if object_id('dba_mon_monSysSQLText') is not null       
	drop table dba_mon_monSysSQLText
go
select * into dba_mon_monSysSQLText from model.dbo.dba_mon_monSysSQLText
go
create index idx1 on dba_mon_monSysSQLText( SPID ) 
go



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

use sybsystemprocs
go
dump tran sybsystemprocs with truncate_only
go

if object_id('sp_dba_whodoes') is not null 
        drop proc sp_dba_whodoes
go

create proc sp_dba_whodoes
/*
# ############################################################################
# @(#) Proc    : sp_dba_whodoes
# @(#) Auteur  : FA
# @(#) Cree le : 20101013
# @(#) Objet   : Active procs using mda
# @(#) Usage   : sp_dba_whodoes [SPID] , [OUTPUTTYPE=0|1|10|11] , [SHOWUSAGE=0|1]
# @(#)           OUTPUTTYPE : 0 = summary / 1 = detail / 10 = active proc summary / 11 = active proc detail
# @(#)           SHOWUSAGE  : 0 = hide usage / 1 = show usage
# @(#) Exemples: sp_dba_whodoes
# @(#)           sp_dba_whodoes 14
# @(#)           sp_dba_whodoes null, 1
# @(#)           sp_dba_whodoes 22, null, 1
# @(#) Depend. : MDA history maintained in tempdb, structure is first created 
# @(#)           on model db to maintain the structure after instance reboot
# @(#) Version : ASE 15.0 
# ############################################################################
# Historique
# ----------------------------------------------------------------------------
# v2 - 20101013 - ASE 15.0
# v1 - 20101013 - ASE 12.5
# ############################################################################
*/

/*
# ############################################################################
# Parameters
*/
	@SPID 		int = null,
	@outputtype 	tinyint = 10,
	@showusage 	tinyint = 1
as
        

/*
# ############################################################################
# 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_whodoes [SPID] , [OUTPUTTYPE=0|1|10|11] , [SHOWUSAGE=0|1] "
	print "-- Running : sp_dba_whodoes %1! , %2! , %3!",  @SPID_str , @outputtype, @showusage
	print ""
end 


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

select * into #tmp_monSysSQLText 	from master.dbo.monSysSQLText
select * into #tmp_monSysStatement 	from master.dbo.monSysStatement
select * into #tmp_sysprocesses 	from master.dbo.sysprocesses where spid=isnull(@SPID,spid)


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

-- contexte historique monSysSQLText   
insert tempdb.dbo.dba_mon_monSysSQLText select * from #tmp_monSysSQLText

-- contexte historique monSysStatement
insert tempdb..dba_mon_monSysStatement select * from #tmp_monSysStatement

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

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

select  SPID, 
        'CpuTime'	= sum(CpuTime),
	'WaitTime'	= sum(WaitTime) ,
	'MemUsageKB'	= sum(MemUsageKB) ,
        'PhysicalReads'	= sum(convert(bigint,PhysicalReads/1000)) ,
	'LogicalReads'  = sum(convert(bigint,LogicalReads/1000)) ,
        'RowsAffected'	= sum(RowsAffected) ,
        'PagesModified'	= sum(PagesModified) ,
	'ErrorStatus'	= max(ErrorStatus) ,
        'StartTime'	= min(StartTime), 
	'EndTime'	= max(EndTime) 	
into    #aggregate
from    tempdb.dbo.dba_mon_monSysStatement 
where   SPID=isnull(@SPID,SPID)
group   by SPID
order   by StartTime desc


/*
# ----------------------------------------------------------------------------
# OUTPUT TYPE 0 = summary  , 10 = summary active processes
*/
if @outputtype in ( 0 , 10 ) 
begin
	select 	a.spid,
		'Cnx'		= convert(char(2),datepart(day,loggedindatetime)) + ' ' + convert(char(5),loggedindatetime,108) ,
		'Login_DB_Prog'	= suser_name(a.suid) +'/'+ db_name(a.dbid) +'/'+ a.program_name,
		'Cpu'		= CpuTime,
		'R_W'	= convert(varchar(10),LogicalReads) +'/'+ convert(varchar(10),PagesModified),
		"Rws"		= RowsAffected,
		"Err"		= ErrorStatus,
		'DtEnd'		= convert(char(8),EndTime,108) 
	into	#result0
	from    #tmp_sysprocesses 	a
	left join #aggregate 		b on b.SPID = a.spid
	where 	a.suid>0
	and 	spid != @@spid
	and 	(
			( @outputtype = 10 and b.CpuTime is not null ) 
			or 
			( @outputtype = 0 ) 
		) 
	order by EndTime desc

	exec sp_autoformat #result0
end

/*
# ----------------------------------------------------------------------------
# OUTPUT TYPE 1 = detailed view
*/
if @outputtype in ( 1, 11 ) 
begin
	select 	a.spid,
		'Cnx'		= convert(char(2),datepart(day,loggedindatetime)) + ' ' + convert(char(5),loggedindatetime,108) ,
		'Login'		= suser_name(a.suid) ,
		'DB'		= db_name(a.dbid) ,
		'Program'	= a.program_name ,
		'Cpu'		= CpuTime,
		"Wait"		= WaitTime,
		"MemKb"		= MemUsageKB,
		'ReadL'		= LogicalReads,
		'ReadP'		= PhysicalReads,
		'PgMod'		= PagesModified,
		"Rws"		= RowsAffected,
		"Err"		= ErrorStatus,
		'DtStart'	= convert(char(8),StartTime,108) ,
		'DtEnd'		= convert(char(8),EndTime,108) 
	into #result1
	from    	#tmp_sysprocesses 	a
	left join  	#aggregate 		b on b.SPID = a.spid
	where 		a.suid>0
	and 		a.spid != @@spid
	and 		(
			( @outputtype = 11 and b.CpuTime is not null ) 
			or 
			( @outputtype = 1 ) 
		) 
	order by EndTime desc

	exec sp_autoformat #result1

end

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

/*
# ----------------------------------------------------------------------------
# Limit history
*/

declare @rowcount int  
select @rowcount=count(1) from tempdb.dbo.dba_mon_monSysStatement
if @rowcount > 100000
begin
	set rowcount 10000
	delete tempdb.dbo.dba_mon_monSysStatement
	set rowcount 0
end
select @rowcount=count(1) from tempdb.dbo.dba_mon_monSysSQLText
if @rowcount > 100000
begin
	set rowcount 10000
	delete tempdb.dbo.dba_mon_monSysSQLText
	set rowcount 0
end

go



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

grant exec on sp_dba_whodoes to public
go

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

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

