set nocount on
go

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

if @@version not like '%15.0.3%'
        select 'v15.0.3 : Skipping, ' + @@servername + ' is on version ' + convert(varchar(40),@@version)
go
if @@version not like '%15.0.3%'
        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_whatdoes') is not null 
        drop proc sp_dba_whatdoes
go

create proc sp_dba_whatdoes
/*
# ############################################################################
# @(#) Proc    : sp_dba_whatdoes
# @(#) Auteur  : FA
# @(#) Cree le : 20101013
# @(#) Objet   : Active SQL using mda
# @(#) Usage   : sp_dba_whatdoes [SPID] , [OUTPUTTYPE=0|1] , [ROWS] , [SHOWUSAGE=0|1]
# @(#)           SPID : Process spid
# @(#)           OUTPUTTYPE : 0 = Summary / 1 = SQL text only
# @(#)           ROWS : limits the number of rows returned 
# @(#)           SHOWUSAGE : 0 = hide usage / 1 = show usage
# @(#) Exemples: sp_dba_whatdoeso
# @(#)           In this case, no spid given, then run sp_dba_whodoes to get active processes
# @(#)           sp_dba_whodoes 14
# @(#)           sp_dba_whodoes null, 1
# @(#)           sp_dba_whodoes null, 2, 100
# @(#) Depend. : MDA history maintained in tempdb, structure is first created
# @(#)           on model db to maintain the structure after instance reboot
# @(#) Version : ASE 15.0.3
# ############################################################################
# Historique
# ----------------------------------------------------------------------------
# v3 - 20101013 - ASE 15.0.3
# v2 - 20101013 - ASE 15.0
# v1 - 20101013 - ASE 12.5
# ############################################################################
*/

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

	@SPID 		int = null, 
	@outputtype 	tinyint = 0,
	@count 		smallint = 100,
	@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_whatdoes [SPID] [ , [OUTPUTTYPE=0|1] [ , [ROWS] ] [ , SHOWUSAGE=0|1 ] ] ]"
	print "-- Running : sp_dba_whatdoes %1! , %2! , %3! , %4! ",  @SPID , @outputtype, @count, @showusage
        print ""
end


/*
# ----------------------------------------------------------------------------
# If no SPID, run sp_dba_whodoes
*/

if @SPID is null
begin
	exec sp_dba_whodoes null,0,0
	return 0
end
 

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

select * into #tmp_monSysSQLText 	from master.dbo.monSysSQLText
select * into #tmp_monSysStatement 	from master.dbo.monSysStatement

/*
# ----------------------------------------------------------------------------
# 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  b.SPID, 
	b.KPID , 
	b.BatchID ,
	sum(b.CpuTime) 						'CpuTime', 
	sum(b.WaitTime) 					'WaitTime',  
	sum(b.MemUsageKB) 					'MemUsageKB', 
	sum(b.PhysicalReads) 					'PhysicalReads', 
	sum(b.LogicalReads) 					'LogicalReads', 
	sum(b.RowsAffected) 					'RowsAffected', 
	max(b.ErrorStatus) 					'ErrorStatus', 
	max( case DBID when 2 then NULL else b.DBName end ) 	'DBName', 
	min(b.StartTime) 					'StartTime', 
	max(b.EndTime) 						'EndTime'
into    #aggregate
from    tempdb.dbo.dba_mon_monSysStatement b 
where   b.SPID=@SPID
group   by b.SPID, 
	b.KPID , 
	b.BatchID
order   by b.StartTime desc , b.KPID,b.BatchID


/*
# ----------------------------------------------------------------------------
# OUTPUT TYPE 0 = SQL text 
*/

if @outputtype = 0
begin
	set rowcount  @count

	select 		'text'=rtrim(ltrim(str_replace(str_replace(a.SQLText, char(10),' '),char(13),' '))) 
	from    	#aggregate 			 b
	join    	tempdb.dbo.dba_mon_monSysSQLText a on a.SPID=b.SPID and a.KPID=b.KPID and a.BatchID=b.BatchID
	order by 	b.StartTime ,
			a.BatchID,
			SequenceInBatch

	set rowcount 0 
end 


/*
# ----------------------------------------------------------------------------
# OUTPUT TYPE 1 = small screen
*/

if @outputtype = 1
begin
	set rowcount  @count
	select 		distinct 
			a.SPID,
			a.BatchID 								'Bat', 
			a.SequenceInBatch 							'Seq',
       		     	case a.SequenceInBatch when 1 then b.CpuTime else null end 		'CPU', 
       		     	case a.SequenceInBatch when 1 then b.WaitTime else null end 		'Wait',  
       		     	case a.SequenceInBatch when 1 then b.MemUsageKB else null end 		'MemKb', 
       		     	case a.SequenceInBatch when 1 then b.PhysicalReads else null end 	'Reads_P',  
       		     	case a.SequenceInBatch when 1 then b.LogicalReads else null end 	'Reads_L',  
       		     	case a.SequenceInBatch when 1 then b.RowsAffected else null end 	'Rws',  
       		     	case a.SequenceInBatch when 1 then b.ErrorStatus else null end 		'Err',  
       	 	    	case a.SequenceInBatch when 1 then b.DBName else null end 		'Db',  
       		     	case a.SequenceInBatch when 1 then convert(varchar(8),b.StartTime,108) else null end 'Start',  
       		     	case a.SequenceInBatch when 1 then datediff(ms,b.StartTime, b.EndTime ) else null end 'Dur',  
       	     		convert(varchar(50),rtrim(ltrim(str_replace(str_replace(a.SQLText, char(10),' '),char(13),' ')))) 'text'
	into 		#result2
	from    	#aggregate b
	join 		tempdb.dbo.dba_mon_monSysSQLText a on a.SPID=b.SPID and a.KPID=b.KPID and a.BatchID=b.BatchID
	and a.SequenceInBatch = 1
	order by 	b.StartTime desc, 
			a.BatchID, 
			SequenceInBatch

	set rowcount 0 

	exec sp_autoformat #result2
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_whatdoes to public
go

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

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

