/*
 * SP_MDA_COLLECT.SQL 
 *
 *
 * Description
 * ===========
 * A very simple procedure to collect rows from the MDA history tables. 
 * Retrieves rows from master..monSysSQLText etc. and inserts these into
 * identically named tables in database 'mon_db'. A unique index with ignore_dup_key
 * is created for each table to filter out duplicates. Unfortunately, duplicates cannot 
 * always be avoided for some tables (see comments in source code below)
 *
 *
 * Installation
 * ============
 * Installation steps:
 *   1. create a database named 'mon_db'
 *      (you may also use a different name, but you must then change all occurrences
 *       of 'mon_db' in this script accordingly)
 *   2. in that database, run this script
 *   3. run the proc mon_db..sp_mda_create_archive_tables
 *
 *
 * Operation
 * =========
 * To start collecting data:
 *   - run this proc: 'sp_mda_collect start'
 *     (this procedure will not return, and tie up a session, until data collection
 *      is stopped as described below)
 *
 * To stop collecting data:
 *   - run this proc from a different session: 'sp_mda_collect stop'
 *
 * To see how much has been collected:
 *   - run the proc: 'sp_mda_collect status'
 *
 *
 * Notes
 * =====
 *  - These procedures are pretty much experimental, and their main purpose is
 *    to provide a simple way to colelct MDA data into a permanent storage location.
 *    Please change/extend as neeed.
 *
 *
 * Revision History
 * ================
 * Version 1.0    Jul-2004  First version
 * Version 1.1    Nov-2004  Use 'like' for checking sysprocesses.clientname
 * Version 1.2    Nov-2004  Add sampling of monOpenObjectActivity (non-history table; 
 *                          the corresponding archive table has an additional datetime 
 *                          column named 'dt')
 * Version 1.3    Jun-2005  Fixed @debug parameter and wait interval test
 * 
 *
 * Copyright Note & Disclaimer :
 * =============================
 * This software is provided "as is"; there is no warranty of any kind.
 * While this software is believed to work accurately, it may not work 
 * correctly and/or reliably in a production environment. In no event shall  
 * Rob Verschoor and/or Sypron B.V. be liable for any damages resulting 
 * from the use of this software. 
 * You are allowed to use this software free of charge for your own 
 * professional, non-commercial purposes. 
 * You are not allowed to sell or bundle this software or use it for any 
 * other commercial purpose without prior written permission from 
 * Rob Verschoor/Sypron B.V.
 * You may (re)distribute only unaltered copies of this software, which 
 * must include this copyright note, as well as the copyright note in 
 * the header of each stored procedure.
 *
 * Note: All trademarks are acknowledged.
 *
 * Please send any comments, bugs, suggestions etc. to the below email
 * address.
 *
 * Copyright (c) 2004-2005 Rob Verschoor/Sypron B.V.
 *                         P.O.Box 10695
 *                         2501 HR Den Haag
 *                         The Netherlands
 *
 *                         Email: sypron@sypron.nl
 *                         WWW  : http://www.sypron.nl/
 *---------------------------------------------------------------------------- 
 */

set nocount on
go
set flushmessage on
go

-- check we have sa_role 
if charindex("sa_role", show_role()) = 0
begin
   print ""
   print ""
   print " This script requires 'sa_role'."
   print " Aborting..."
   print " "
   print " "
   print ""
   set background on  -- terminate this script now
end
go

-- We need to be in 12.5.0.3+ 
-- First test for 12.0 to avoid a syntax error on license_enabled()
if isnull(object_id("master.dbo.sysqueryplans"),99) >= 99  
begin
   print ""
   print ""
   print " This script requires ASE 12.5.0.3 or later."
   print " Aborting..."
   print " "
   print " "
   print ""
   set background on  -- terminate this script now
end
go

-- We need to be in 12.5.0.3+ 
if license_enabled('ASE_XRAY') = NULL
begin
   print ""
   print ""
   print " This script requires ASE 12.5.0.3 or later."
   print " Aborting..."
   print " "
   print " "
   print ""
   select syb_quit()  -- terminate this script now
end
go

-- Check whether the user has already installed the MDA tables
if object_id('master..monTables') = NULL
begin
   print ""
   print ""
   print " This script requires the MDA tables to be installed."
   print " Among other things, this involves executing the 'installmontables'"
   print " script."
   print " See the ASE documentation, or www.sypron.nl/mda, for more "
   print " information."
   print " "
   print " Aborting..."
   print " "
   print " "
   print ""
   select syb_quit()  -- terminate this script now
end
go

---------------------------------------------------------------------------- 

-- Check whether the database 'mon_db' exists
if db_id('mon_db') = NULL
begin
   print ""
   print ""
   print " This script requires that a database named 'mon_db' exists."
   print " Please create this database and re-run this script."
   print " "
   print " Aborting..."
   print " "
   print " "
   print ""
   select syb_quit()  -- terminate this script now
end
go

---------------------------------------------------------------------------- 

print ""
print " Setting database options for 'mon_db' ..."
print ""
print ""
go

use master 
go
-- enable 'trunc log on chkpt' and 'select into/bulkcopy/pllsort'
sp_dboption mon_db, trunc, true
go
sp_dboption mon_db, selec, true
go

use mon_db
go
checkpoint
go

---------------------------------------------------------------------------- 

--
-- Do not drop this table once it exists!
-- It is needed when starting/stopping data collection
--
if object_id ('mon_db_control_tab') != NULL
 drop table mon_db_control_tab
go
create table mon_db_control_tab (name varchar(15), status varchar(15), spid int, dt datetime)
go

---------------------------------------------------------------------------- 

if object_id ('sp_mda_create_archtable_sub') != NULL
   drop procedure sp_mda_create_archtable_sub
go
create procedure sp_mda_create_archtable_sub
/* Copyright (c) 2004-2005 Rob Verschoor/Sypron B.V. */
   @dbname varchar(30),
   @tabname varchar(30),
   @ixcols varchar(100),
   @option1 varchar(30) = NULL,
   @debug int = 0
as
begin
declare @cmd varchar(500)
declare @db_tabname varchar(100)
declare @add_timestamp varchar(30)

    select @add_timestamp = ''
	if @tabname in ('monOpenObjectActivity')
	begin
	   select @add_timestamp = 'dt = getdate(),'
	end


	select @db_tabname  = @dbname + '..' + @tabname

    if object_id (@db_tabname) != NULL
	begin
	   if lower(@option1) = 'force'
	   begin
	      select @cmd = 'drop table ' + @db_tabname
		  if @debug > 0 print 'cmd=[%1!]', @cmd
		  exec (@cmd)
	   end
	   else
	   begin
	      print 'Table %1! already exists (use "force" to recreate)', @db_tabname
		  return
	   end
	end

	-- copy the schema from the MDA table
    select @cmd = 'select ' + @add_timestamp + ' * into ' + @db_tabname + ' from master..' + @tabname
	if @debug > 0 print 'cmd=[%1!]', @cmd
	exec(@cmd)

    -- Let's skip the unique index for 'monSysStatement' until the issue with possible duplicate rows
	-- has been sorted out.
    if object_id (@tabname) != NULL and @tabname != 'monSysStatement'
	begin
	      print 'Table %1! created as copy of master..%2!', @db_tabname, @tabname

		  -- create the index
		  select @cmd = 'create unique index ix1 on ' + @db_tabname + '('+ @ixcols + ') with ignore_dup_key'
		  if @debug > 0 print 'cmd=[%1!]', @cmd
		  exec(@cmd)
	end
	else
	begin
	      print 'Error: table %1! not created', @db_tabname
	end
end
go

---------------------------------------------------------------------------- 

print ""
print " Installing 'sp_mda_create_archive_tables' ..."
go

if object_id ('sp_mda_create_archive_tables') != NULL
   drop procedure sp_mda_create_archive_tables
go
create procedure sp_mda_create_archive_tables
/* Copyright (c) 2004-2005 Rob Verschoor/Sypron B.V. */
   @option1 varchar(30) = NULL
as
begin
   if @option1 = '?' 
   begin 
      print " "
      print " Usage: sp_mda_create_archive_tables [ 'force' ] "
      print ""
      print " This procedure creates permanent copies of various MDA monitoring tables."
      print " "
      print " Arguments:"
      print "   'force' - drops & recreates tables if they already exist"
      print ""
      print " Copyright (c) 2004-2005 Rob Verschoor/Sypron B.V."
      print " Visit www.sypron.nl/mda"
      print " "
      return 0
   end

    exec sp_mda_create_archtable_sub 'mon_db', 'monSysSQLText', 
	     'SPID, KPID, BatchID, SequenceInBatch', @option1

    exec sp_mda_create_archtable_sub 'mon_db', 'monSysStatement', 
	     'SPID, KPID, DBID, ProcedureID, PlanID, BatchID, ContextID, LineNumber, LogicalReads, PagesModified', @option1
		 -- note: uniqueness is not guaranteed with multiple statements on one line!

    exec sp_mda_create_archtable_sub 'mon_db', 'monSysPlanText', 
	     'PlanID, SPID, KPID, BatchID, ContextID, SequenceNumber, DBID, ProcedureID', @option1

    exec sp_mda_create_archtable_sub 'mon_db', 'monDeadLock', 
	     'DeadlockID, ResolveTime', @option1

    exec sp_mda_create_archtable_sub 'mon_db', 'monErrorLog', 
	     'SPID, KPID, Time, ErrorMessage', @option1
		 -- uniqueness not always guaranteed: for example, with stacktraces

    exec sp_mda_create_archtable_sub 'mon_db', 'monOpenObjectActivity', 
	     'dt, DBID, ObjectID, IndexID', @option1
end
go

---------------------------------------------------------------------------- 

if object_id ('sp_mda_collect_archtab_sub') != NULL
   drop procedure sp_mda_collect_archtab_sub
go
create procedure sp_mda_collect_archtab_sub
/* Copyright (c) 2004-2005 Rob Verschoor/Sypron B.V. */
   @dbname varchar(30),
   @tabname varchar(30),
   @whereclause varchar(100) = NULL,
   @option1 varchar(30) = NULL,
   @debug int = 0
as
begin
declare @cmd varchar(500)
declare @db_tabname varchar(100)
declare @rc int
declare @add_timestamp varchar(30)

	select @db_tabname  = @dbname + '..' + @tabname

    select @add_timestamp = ''
	if @tabname in ('monOpenObjectActivity')
	begin
	   select @add_timestamp = 'getdate(),'
	end

	-- copy the schema from the MDA table
    select @cmd = 'insert ' + @db_tabname + ' select ' + @add_timestamp + ' * from master..' + @tabname + ' ' + @whereclause
	if @debug > 0 print 'cmd=[%1!]', @cmd
	exec(@cmd)
	select @rc = @@rowcount
	print 'Inserted %1! rows into %2!', @rc, @db_tabname
end
go

dump tran mon_db with truncate_only
go

---------------------------------------------------------------------------- 

if object_id ('sp_mda_collect_archive_tables') != NULL
   drop procedure sp_mda_collect_archive_tables
go
create procedure sp_mda_collect_archive_tables
/* Copyright (c) 2004-2005 Rob Verschoor/Sypron B.V. */
   @option1 varchar(30) = NULL
as
begin
    exec sp_mda_collect_archtab_sub 'mon_db', 'monSysSQLText', 'where SPID != @@spid', @option1

    exec sp_mda_collect_archtab_sub 'mon_db', 'monSysStatement', 'where SPID != @@spid', @option1

    exec sp_mda_collect_archtab_sub 'mon_db', 'monSysPlanText', 'where SPID != @@spid', @option1

    exec sp_mda_collect_archtab_sub 'mon_db', 'monDeadLock', null, @option1

    exec sp_mda_collect_archtab_sub 'mon_db', 'monErrorLog', null, @option1

	exec sp_mda_collect_archtab_sub 'mon_db', 'monOpenObjectActivity', null, @option1
end
go

---------------------------------------------------------------------------- 

if object_id ('sp_mda_collect') != NULL
   drop procedure sp_mda_collect
go
print ""
print " Installing 'sp_mda_collect' ..."
go

create procedure sp_mda_collect
/* Copyright (c) 2004-2005 Rob Verschoor/Sypron B.V. */
   @option1 varchar(30) = NULL,
   @option2 varchar(30) = NULL,
   @debug int = 0
as
begin
declare @s varchar(15), @wait_interval varchar(8)
declare @rc int

   if @option1 = '?' 
   begin 
      print " "
      print " Usage: sp_mda_collect { 'start' | 'stop' | 'status' } [, 'hh:mm:ss' [, @debug=1]]"
      print ""
      print " This procedure starts or stops data collection MDA monitoring tables;"
      print " 'status' shows the amount of data currently collected."
      print " By default, the sample interval is 30 seconds, but a different interval"
      print " may be specified."
      print ""
      print " Copyright (c) 2004-2005 Rob Verschoor/Sypron B.V."
      print " Visit www.sypron.nl/mda"
      print " "
      return 0
   end

   set flushmessage on

   if lower(@option1) = 'stop'
   begin
       select * into #t from master..sysprocesses
	   where clientname like 'sp_mda_collect%'   -- apparently, "= 'sp_mda_collect' " does not work sometimes...
	   select @rc = @@rowcount
	   if @rc = 0 
	   begin
	      print "sp_mda_collect is not currently running"
	      return
	   end
	   select spid, loginname=suser_name(suid), clientname from #t

	   update mon_db_control_tab 
	   set status = 'STOP'
	   where name = 'COLLECT'

	   print 'Requested sp_mda_collect to stop...'
	   return
   end
   else if lower(@option1) = 'start'
   begin
       select @wait_interval = isnull(@option2, '00:00:30')
	   if @wait_interval not like "[0-2][0-9]:[0-5][0-9]:[0-5][0-9]"
	   begin
	      print "Invalid wait interval."
	      return
	   end
	   print " "
	   print "Starting data collection from MDA history tables."
	   print "Wait interval between samples: %1!", @wait_interval
	   print "To stop, run 'mon_db..sp_mda_collect stop' from a different session."
	   print " "

	   delete mon_db_control_tab 
	   where name = 'COLLECT'

	   insert mon_db_control_tab values ('COLLECT', 'RUNNING', @@spid, getdate())

	   set clientname 'sp_mda_collect'

       -- here we go into an endless loop until we're told to stop...
	   while 1=1
	   begin
	       select @s = ''
		   select @s = status
		   from mon_db_control_tab 
		   where name = 'COLLECT'

		   if @s != 'RUNNING'
		   begin
		      print "I've been requested to stop..."
		      break
		   end

		   waitfor delay @wait_interval
		   exec sp_mda_collect_archive_tables
	   end

	   set clientname ''
	   return
   end
   else if lower(@option1) = 'status'
   begin
       select * into #t2 from master..sysprocesses
	   where clientname like 'sp_mda_collect%'   -- apparently, "= 'sp_mda_collect' " does not work sometimes...
	   select @rc = @@rowcount
	   if @rc = 0 
	   begin
	      print "sp_mda_collect is not currently running"
	   end
	   select spid, loginname=suser_name(suid), clientname from #t2

       exec sp_spaceused monSysSQLText
       exec sp_spaceused monSysStatement
       exec sp_spaceused monSysPlanText
       exec sp_spaceused monDeadLock      
       exec sp_spaceused monErrorLog  
       exec sp_spaceused monOpenObjectActivity
	   return
   end
   else 
   begin
      print 'Invalid option - only "start", "stop" and "status" are allowed.'
	  return
   end
end
go

---------------------------------------------------------------------------- 

print ""
print " For on-line help information, run these procedures with '?' as "
print " the first parameter."
print " Ready."
print ""
print " Copyright (c) 2004-2005 Rob Verschoor/Sypron B.V."
go


--
-- end of file
--