Jason L. Froebe – Tech tips and How Tos for Fellow Techies

Yesterday I was scratching my head on how to send an email to myself when an application failed or a certain error occurred. You see, I wanted to be able to read an email on my crackberry (Blackberry) that showed that a problem occurred and more importantly, I wanted the error messages and the snippet of code where the problem occurred.

The first part of sending the email was very simple by using Mail::Sendmail, but I wanted to extend it a bit by sending highlighted code. The problem is that the documentation of Syntax::Highlight::Engine::Kate isn’t too clear on how to use it.

The Syntax::Highlight::Engine::Kate is surprisingly easy to use once you understand that Kate just parses whatever string you give it for tokens and lets you handle how you want each type of token highlighted. In truth, you could easily forsake HTML and have it spit out XML or something else if you so desired.

I wrote a wrapper subroutine for Syntax::Highlight::Engine::Kate so I can just call any_to_html(‘parsing template’, $string) and it will return HTML code.

The language option (a parsing template) and format_table option (token into html code) are required but the substitutions option is optional but I strongly recommend using it else the output will look odd.

The following is the output of $SYBASE/$SYBASE_ASE/scripts/installmontables using our little application. Note that we are reading the batches one by one (just because we can) even though it isn’t necessary for this example.

print “It is no longer necessary to run this script to install the Monitoring Tables.”
print “Monitoring Tables are now installed by the installmaster script.”
print “”
print “This installmontables script is provided as a sample that can be copied and”
print “modified to support remote access of Monitoring Tables. To do so you need to:”
print “1) Replace all instances of @SERVER@ with the name of the remote ASE from which”
print “   monitoring data is to be obtained. Note that each remote ASE to be monitored”
print “   must be added to the local ASE’s sysservers table using sp_addserver.”
print “2) Create a database with the same name as the remote ASE. This database need ”
print “   only be of the minimum size as these tables do not store any data.”
print “3) Remove this header (i.e. these first 21 lines).”
print “4) Run the script against the local ASE using the isql utility as follows:”
print “   isql -Usa -P<password> -S<server name> -i<script name>”
print “5) Retrieve remote monitoring data. E.g. to obtain monEngine information for an”
print “   ASE named REMASE you would execute the following SQL:”
print “        use REMASE”
print “        go”
print “        select * from monEngine”
print “        go”
go

quit
/*
Monitoring Tables/15.0.2/14331/P/Linux Intel/Linux 2.4.21-47.ELsmp i686/ase1502/2486/32-bit/OPT/Thu May 24 03:55:23 2007

Confidential property of Sybase, Inc.
Copyright 2001, 2007
Sybase, Inc.  All rights reserved.
Unpublished rights reserved under U.S. copyright laws.

This software contains confidential and trade secret information of Sybase,
Inc.   Use,  duplication or disclosure of the software and documentation by
the  U.S.  Government  is  subject  to  restrictions set forth in a license
agreement  between  the  Government  and  Sybase,  Inc.  or  other  written
agreement  specifying  the  Government’s rights to use the software and any
applicable FAR provisions, for example, FAR 52.227-19.
Sybase, Inc. One Sybase Drive, Dublin, CA 94568, USA
*/

/* Use a different database to obtain monitoring information for each ASE */
use @SERVER@
go

/*
** monTables definition
*/
if (exists (select * from sysobjects
where name = ‘monTables’
and type = ‘U’))
drop table monTables
go

print “Creating monTables”
go

create existing table monTables (
TableID                         int,
Columns tinyint,
Parameters tinyint,
Indicators                      int,
Size int,
TableName                       varchar(30) NULL,
Description                     varchar(368) NULL,
)
external procedure
at “@SERVER@…$monTables”
go

grant select on monTables to mon_role
go

/*
** monTableParameters definition
*/
if (exists (select * from sysobjects
where name = ‘monTableParameters’
and type = ‘U’))
drop table monTableParameters
go

print “Creating monTableParameters”
go

create existing table monTableParameters (
TableID                         int,
ParameterID                     int,
TypeID                          int,
Precision tinyint,
Scale                           tinyint,
Length smallint,
TableName                       varchar(30) NULL,
ParameterName                   varchar(30) NULL,
TypeName                        varchar(20) NULL,
Description                     varchar(255) NULL,
)
external procedure
at “@SERVER@…$monTableParameters”
go

grant select on monTableParameters to mon_role
go

/*
** monTableColumns definition
*/
if (exists (select * from sysobjects
where name = ‘monTableColumns’
and type = ‘U’))
drop table monTableColumns
go

print “Creating monTableColumns”
go

create existing table monTableColumns (
TableID                         int,
ColumnID                        int,
TypeID                          int,
Precision tinyint,
Scale                           tinyint,
Length smallint,
Indicators                      int,
TableName                       varchar(30) NULL,
ColumnName                      varchar(30) NULL,
TypeName                        varchar(20) NULL,
Description                     varchar(255) NULL,
)
external procedure
at “@SERVER@…$monTableColumns”
go

grant select on monTableColumns to mon_role
go

/*
** monState definition
*/
if (exists (select * from sysobjects
where name = ‘monState’
and type = ‘U’))
drop table monState
go

print “Creating monState”
go

create existing table monState (
LockWaitThreshold               int,
LockWaits                       int,
DaysRunning                     int,
CheckPoints                     int,
NumDeadlocks                    int,
DiagnosticDumps                 int,
Connections                     int,
MaxRecovery                     int,
StartDate                       datetime NULL,
CountersCleared                 datetime NULL,
)
external procedure
at “@SERVER@…$monState”
go

grant select on monState to mon_role
go

/*
** monEngine definition
*/
if (exists (select * from sysobjects
where name = ‘monEngine’
and type = ‘U’))
drop table monEngine
go

print “Creating monEngine”
go

create existing table monEngine (
EngineNumber                    smallint,
CurrentKPID                     int,
PreviousKPID                    int,
CPUTime                         int,
SystemCPUTime                   int,
UserCPUTime                     int,
IdleCPUTime                     int,
Yields                          int,
Connections                     int,
DiskIOChecks                    int,
DiskIOPolled                    int,
DiskIOCompleted                 int,
ProcessesAffinitied             int,
ContextSwitches                 int,
HkgcMaxQSize                    int,
HkgcPendingItems                int,
HkgcHWMItems                    int,
HkgcOverflows                   int,
Status                          varchar(20) NULL,
StartTime                       datetime NULL,
StopTime                        datetime NULL,
AffinitiedToCPU                 int NULL,
OSPID                           int NULL,
)
external procedure
at “@SERVER@…$monEngine”
go

grant select on monEngine to mon_role
go

/*
** monDataCache definition
*/
if (exists (select * from sysobjects
where name = ‘monDataCache’
and type = ‘U’))
drop table monDataCache
go

print “Creating monDataCache”
go

create existing table monDataCache (
CacheID                         int,
RelaxedReplacement              int,
BufferPools                     int,
CacheSearches                   int,
PhysicalReads                   int,
LogicalReads                    int,
PhysicalWrites                  int,
Stalls                          int,
CachePartitions                 smallint,
CacheName                       varchar(30) NULL,
)
external procedure
at “@SERVER@…$monDataCache”
go

grant select on monDataCache to mon_role
go

/*
** monProcedureCache definition
*/
if (exists (select * from sysobjects
where name = ‘monProcedureCache’
and type = ‘U’))
drop table monProcedureCache
go

print “Creating monProcedureCache”
go

create existing table monProcedureCache (
Requests                        int,
Loads                           int,
Writes                          int,
Stalls                          int,
)
external procedure
at “@SERVER@…$monProcedureCache”
go

grant select on monProcedureCache to mon_role
go

/*
** monProcedureCacheMemoryUsage definition
*/
if (exists (select * from sysobjects
where name = ‘monProcedureCacheMemoryUsage’
and type = ‘U’))
drop table monProcedureCacheMemoryUsage
go

print “Creating monProcedureCacheMemoryUsage”
go

create existing table monProcedureCacheMemoryUsage (
AllocatorID                     int,
ModuleID                        int,
Active                          int,
HWM                             int,
ChunkHWM                        int,
NumReuseCaused                  int,
AllocatorName                   varchar(30) NULL,
)
external procedure
at “@SERVER@…$monProcedureCacheMemoryUsage”
go

grant select on monProcedureCacheMemoryUsage to mon_role
go

/*
** monProcedureCacheModuleUsage definition
*/
if (exists (select * from sysobjects
where name = ‘monProcedureCacheModuleUsage’
and type = ‘U’))
drop table monProcedureCacheModuleUsage
go

print “Creating monProcedureCacheModuleUsage”
go

create existing table monProcedureCacheModuleUsage (
ModuleID                        int,
Active                          int,
HWM                             int,
NumPagesReused                  int,
ModuleName                      varchar(30) NULL,
)
external procedure
at “@SERVER@…$monProcedureCacheModuleUsage”
go

grant select on monProcedureCacheModuleUsage to mon_role
go

/*
** monOpenDatabases definition
*/
if (exists (select * from sysobjects
where name = ‘monOpenDatabases’
and type = ‘U’))
drop table monOpenDatabases
go

print “Creating monOpenDatabases”
go

create existing table monOpenDatabases (
DBID                            int,
BackupInProgress                int,
LastBackupFailed                int,
TransactionLogFull              int,
SuspendedProcesses              int,
AppendLogRequests               int,
AppendLogWaits                  int,
DBName                          varchar(30) NULL,
BackupStartTime                 datetime NULL,
QuiesceTag                      varchar(30) NULL,
)
external procedure
at “@SERVER@…$monOpenDatabases”
go

grant select on monOpenDatabases to mon_role
go

/*
** monSysWorkerThread definition
*/
if (exists (select * from sysobjects
where name = ‘monSysWorkerThread’
and type = ‘U’))
drop table monSysWorkerThread
go

print “Creating monSysWorkerThread”
go

create existing table monSysWorkerThread (
ThreadsActive                   int,
TotalWorkerThreads              int,
HighWater                       int,
ParallelQueries                 int,
PlansAltered                    int,
WorkerMemory                    int,
TotalWorkerMemory               int,
WorkerMemoryHWM                 int,
MaxParallelDegree               int,
MaxScanParallelDegree           int,
)
external procedure
at “@SERVER@…$monSysWorkerThread”
go

grant select on monSysWorkerThread to mon_role
go

/*
** monNetworkIO definition
*/
if (exists (select * from sysobjects
where name = ‘monNetworkIO’
and type = ‘U’))
drop table monNetworkIO
go

print “Creating monNetworkIO”
go

create existing table monNetworkIO (
PacketsSent                     int,
PacketsReceived                 int,
BytesSent                       int,
BytesReceived                   int,
)
external procedure
at “@SERVER@…$monNetworkIO”
go

grant select on monNetworkIO to mon_role
go

/*
** monErrorLog definition
*/
if (exists (select * from sysobjects
where name = ‘monErrorLog’
and type = ‘U’))
drop table monErrorLog
go

print “Creating monErrorLog”
go

create existing table monErrorLog (
SPID                            smallint,
KPID                            int,
FamilyID                        smallint,
EngineNumber                    smallint,
ErrorNumber                     int,
Severity                        int,
State                           int,
Time datetime,
ErrorMessage                    varchar(512) NULL,
)
external procedure
at “@SERVER@…$monErrorLog”
go

grant select on monErrorLog to mon_role
go

/*
** monLocks definition
*/
if (exists (select * from sysobjects
where name = ‘monLocks’
and type = ‘U’))
drop table monLocks
go

print “Creating monLocks”
go

create existing table monLocks (
SPID                            smallint,
KPID                            int,
DBID                            int,
ParentSPID                      smallint,
LockID                          int,
Context int,
ObjectID                        int NULL,
LockState                       varchar(20) NULL,
LockType                        varchar(20) NULL,
LockLevel                       varchar(30) NULL,
WaitTime                        int NULL,
PageNumber                      int NULL,
RowNumber                       int NULL,
BlockedState                    varchar(64) NULL,
BlockedBy                       int NULL,
SourceCodeID                    varchar(30) NULL,
)
external procedure
at “@SERVER@…$monLocks”
go

grant select on monLocks to mon_role
go

/*
** monDeadLock definition
*/
if (exists (select * from sysobjects
where name = ‘monDeadLock’
and type = ‘U’))
drop table monDeadLock
go

print “Creating monDeadLock”
go

create existing table monDeadLock (
DeadlockID                      int,
VictimKPID                      int,
ResolveTime                     datetime,
ObjectDBID                      int,
PageNumber                      int,
RowNumber                       int,
HeldFamilyID                    smallint,
HeldSPID                        smallint,
HeldKPID                        int,
HeldProcDBID                    int,
HeldProcedureID                 int,
HeldBatchID                     int,
HeldContextID                   int,
HeldLineNumber                  int,
WaitFamilyID                    smallint,
WaitSPID                        smallint,
WaitKPID                        int,
WaitTime                        int,
ObjectName                      varchar(30) NULL,
HeldUserName                    varchar(30) NULL,
HeldApplName                    varchar(30) NULL,
HeldTranName                    varchar(255) NULL,
HeldLockType                    varchar(20) NULL,
HeldCommand                     varchar(30) NULL,
WaitUserName                    varchar(30) NULL,
WaitLockType                    varchar(20) NULL,
HeldSourceCodeID                varchar(30) NULL,
WaitSourceCodeID                varchar(30) NULL,
)
external procedure
at “@SERVER@…$monDeadLock”
go

grant select on monDeadLock to mon_role
go

/*
** monWaitClassInfo definition
*/
if (exists (select * from sysobjects
where name = ‘monWaitClassInfo’
and type = ‘U’))
drop table monWaitClassInfo
go

print “Creating monWaitClassInfo”
go

create existing table monWaitClassInfo (
WaitClassID                     smallint,
Description                     varchar(50) NULL,
)
external procedure
at “@SERVER@…$monWaitClassInfo”
go

grant select on monWaitClassInfo to mon_role
go

/*
** monWaitEventInfo definition
*/
if (exists (select * from sysobjects
where name = ‘monWaitEventInfo’
and type = ‘U’))
drop table monWaitEventInfo
go

print “Creating monWaitEventInfo”
go

create existing table monWaitEventInfo (
WaitEventID                     smallint,
WaitClassID                     smallint,
Description                     varchar(50) NULL,
)
external procedure
at “@SERVER@…$monWaitEventInfo”
go

grant select on monWaitEventInfo to mon_role
go

/*
** monCachedObject definition
*/
if (exists (select * from sysobjects
where name = ‘monCachedObject’
and type = ‘U’))
drop table monCachedObject
go

print “Creating monCachedObject”
go

create existing table monCachedObject (
CacheID                         int,
DBID                            int,
IndexID                         int,
PartitionID                     int,
CachedKB                        int,
CacheName                       varchar(30) NULL,
ObjectID                        int NULL,
DBName                          varchar(30) NULL,
OwnerUserID                     int NULL,
OwnerName                       varchar(30) NULL,
ObjectName                      varchar(30) NULL,
PartitionName                   varchar(30) NULL,
ObjectType                      varchar(30) NULL,
TotalSizeKB                     int NULL,
ProcessesAccessing              int NULL,
)
external procedure
at “@SERVER@…$monCachedObject”
go

grant select on monCachedObject to mon_role
go

/*
** monCachePool definition
*/
if (exists (select * from sysobjects
where name = ‘monCachePool’
and type = ‘U’))
drop table monCachePool
go

print “Creating monCachePool”
go

create existing table monCachePool (
CacheID                         int,
IOBufferSize                    int,
AllocatedKB                     int,
PhysicalReads                   int,
Stalls                          int,
PagesTouched                    int,
PagesRead                       int,
BuffersToMRU                    int,
BuffersToLRU                    int,
CacheName                       varchar(30) NULL,
)
external procedure
at “@SERVER@…$monCachePool”
go

grant select on monCachePool to mon_role
go

/*
** monOpenObjectActivity definition
*/
if (exists (select * from sysobjects
where name = ‘monOpenObjectActivity’
and type = ‘U’))
drop table monOpenObjectActivity
go

print “Creating monOpenObjectActivity”
go

create existing table monOpenObjectActivity (
DBID                            int,
ObjectID                        int,
IndexID                         int,
DBName                          varchar(30) NULL,
ObjectName                      varchar(30) NULL,
LogicalReads                    int NULL,
PhysicalReads                   int NULL,
APFReads                        int NULL,
PagesRead                       int NULL,
PhysicalWrites                  int NULL,
PagesWritten                    int NULL,
RowsInserted                    int NULL,
RowsDeleted                     int NULL,
RowsUpdated                     int NULL,
Operations                      int NULL,
LockRequests                    int NULL,
LockWaits                       int NULL,
OptSelectCount                  int NULL,
LastOptSelectDate               datetime NULL,
UsedCount                       int NULL,
LastUsedDate                    datetime NULL,
HkgcRequests                    int NULL,
HkgcPending                     int NULL,
HkgcOverflows                   int NULL,
)
external procedure
at “@SERVER@…$monOpenObjectActivity”
go

grant select on monOpenObjectActivity to mon_role
go

/*
** monIOQueue definition
*/
if (exists (select * from sysobjects
where name = ‘monIOQueue’
and type = ‘U’))
drop table monIOQueue
go

print “Creating monIOQueue”
go

create existing table monIOQueue (
IOs                             int,
IOTime                          int,
LogicalName                     varchar(30) NULL,
IOType                          varchar(12) NULL,
)
external procedure
at “@SERVER@…$monIOQueue”
go

grant select on monIOQueue to mon_role
go

/*
** monDeviceIO definition
*/
if (exists (select * from sysobjects
where name = ‘monDeviceIO’
and type = ‘U’))
drop table monDeviceIO
go

print “Creating monDeviceIO”
go

create existing table monDeviceIO (
Reads int,
APFReads                        int,
Writes                          int,
DevSemaphoreRequests            int,
DevSemaphoreWaits               int,
IOTime                          int,
LogicalName                     varchar(30) NULL,
PhysicalName                    varchar(128) NULL,
)
external procedure
at “@SERVER@…$monDeviceIO”
go

grant select on monDeviceIO to mon_role
go

/*
** monSysWaits definition
*/
if (exists (select * from sysobjects
where name = ‘monSysWaits’
and type = ‘U’))
drop table monSysWaits
go

print “Creating monSysWaits”
go

create existing table monSysWaits (
WaitEventID                     smallint,
WaitTime                        int,
Waits                           int,
)
external procedure
at “@SERVER@…$monSysWaits”
go

grant select on monSysWaits to mon_role
go

/*
** monProcess definition
*/
if (exists (select * from sysobjects
where name = ‘monProcess’
and type = ‘U’))
drop table monProcess
go

print “Creating monProcess”
go

create existing table monProcess (
SPID                            smallint,
KPID                            int,
BatchID                         int,
ContextID                       int,
LineNumber                      int,
SecondsConnected                int,
DBID                            int,
EngineNumber                    smallint,
Priority                        int,
FamilyID                        smallint NULL,
Login                           varchar(30) NULL,
Application                     varchar(30) NULL,
Command                         varchar(30) NULL,
NumChildren                     int NULL,
SecondsWaiting                  int NULL,
WaitEventID                     smallint NULL,
BlockingSPID                    smallint NULL,
BlockingXLOID                   int NULL,
DBName                          varchar(30) NULL,
EngineGroupName                 varchar(30) NULL,
ExecutionClass                  varchar(30) NULL,
MasterTransactionID             varchar(255) NULL,
)
external procedure
at “@SERVER@…$monProcess”
go

grant select on monProcess to mon_role
go

/*
** monProcessLookup definition
*/
if (exists (select * from sysobjects
where name = ‘monProcessLookup’
and type = ‘U’))
drop table monProcessLookup
go

print “Creating monProcessLookup”
go

create existing table monProcessLookup (
SPID                            smallint,
KPID                            int,
Login                           varchar(30) NULL,
Application                     varchar(30) NULL,
ClientHost                      varchar(30) NULL,
ClientIP                        varchar(64) NULL,
ClientOSPID                     varchar(30) NULL,
)
external procedure
at “@SERVER@…$monProcessLookup”
go

grant select on monProcessLookup to mon_role
go

/*
** monProcessActivity definition
*/
if (exists (select * from sysobjects
where name = ‘monProcessActivity’
and type = ‘U’))
drop table monProcessActivity
go

print “Creating monProcessActivity”
go

create existing table monProcessActivity (
SPID                            smallint,
KPID                            int,
ServerUserID                    int,
CPUTime                         int,
WaitTime                        int,
PhysicalReads                   int,
LogicalReads                    int,
PagesRead                       int,
PhysicalWrites                  int,
PagesWritten                    int,
MemUsageKB                      int,
LocksHeld                       int,
TableAccesses                   int,
IndexAccesses                   int,
TempDbObjects                   int,
WorkTables                      int,
ULCBytesWritten                 int,
ULCFlushes                      int,
ULCFlushFull                    int,
ULCMaxUsage                     int,
ULCCurrentUsage                 int,
Transactions                    int,
Commits                         int,
Rollbacks                       int,
)
external procedure
at “@SERVER@…$monProcessActivity”
go

grant select on monProcessActivity to mon_role
go

/*
** monProcessWorkerThread definition
*/
if (exists (select * from sysobjects
where name = ‘monProcessWorkerThread’
and type = ‘U’))
drop table monProcessWorkerThread
go

print “Creating monProcessWorkerThread”
go

create existing table monProcessWorkerThread (
SPID                            smallint,
KPID                            int,
ThreadsActive                   int,
MaxParallelDegree               smallint,
MaxScanParallelDegree           smallint,
ParallelQueries                 int,
PlansAltered                    int,
FamilyID                        smallint NULL,
)
external procedure
at “@SERVER@…$monProcessWorkerThread”
go

grant select on monProcessWorkerThread to mon_role
go

/*
** monProcessNetIO definition
*/
if (exists (select * from sysobjects
where name = ‘monProcessNetIO’
and type = ‘U’))
drop table monProcessNetIO
go

print “Creating monProcessNetIO”
go

create existing table monProcessNetIO (
SPID                            smallint,
KPID                            int,
NetworkPacketSize               int,
PacketsSent                     int,
PacketsReceived                 int,
BytesSent                       int,
BytesReceived                   int,
)
external procedure
at “@SERVER@…$monProcessNetIO”
go

grant select on monProcessNetIO to mon_role
go

/*
** monProcessObject definition
*/
if (exists (select * from sysobjects
where name = ‘monProcessObject’
and type = ‘U’))
drop table monProcessObject
go

print “Creating monProcessObject”
go

create existing table monProcessObject (
SPID                            smallint,
KPID                            int,
DBID                            int,
ObjectID                        int,
PartitionID                     int,
IndexID                         int,
OwnerUserID                     int,
LogicalReads                    int,
PhysicalReads                   int,
PhysicalAPFReads                int,
DBName                          varchar(30) NULL,
ObjectName                      varchar(30) NULL,
PartitionName                   varchar(30) NULL,
ObjectType                      varchar(30) NULL,
PartitionSize                   int NULL,
)
external procedure
at “@SERVER@…$monProcessObject”
go

grant select on monProcessObject to mon_role
go

/*
** monProcessWaits definition
*/
if (exists (select * from sysobjects
where name = ‘monProcessWaits’
and type = ‘U’))
drop table monProcessWaits
go

print “Creating monProcessWaits”
go

create existing table monProcessWaits (
SPID                            smallint,
KPID                            int,
WaitEventID                     smallint,
Waits                           int,
WaitTime                        int,
)
external procedure
at “@SERVER@…$monProcessWaits”
go

grant select on monProcessWaits to mon_role
go

/*
** monProcessStatement definition
*/
if (exists (select * from sysobjects
where name = ‘monProcessStatement’
and type = ‘U’))
drop table monProcessStatement
go

print “Creating monProcessStatement”
go

create existing table monProcessStatement (
SPID                            smallint,
KPID                            int,
DBID                            int,
ProcedureID                     int,
PlanID                          int,
BatchID                         int,
ContextID                       int,
LineNumber                      int,
CpuTime                         int,
WaitTime                        int,
MemUsageKB                      int,
PhysicalReads                   int,
LogicalReads                    int,
PagesModified                   int,
PacketsSent                     int,
PacketsReceived                 int,
NetworkPacketSize               int,
PlansAltered                    int,
RowsAffected                    int,
StartTime                       datetime NULL,
)
external procedure
at “@SERVER@…$monProcessStatement”
go

grant select on monProcessStatement to mon_role
go

/*
** monSysStatement definition
*/
if (exists (select * from sysobjects
where name = ‘monSysStatement’
and type = ‘U’))
drop table monSysStatement
go

print “Creating monSysStatement”
go

create existing table monSysStatement (
SPID                            smallint,
KPID                            int,
DBID                            int,
ProcedureID                     int,
PlanID                          int,
BatchID                         int,
ContextID                       int,
LineNumber                      int,
CpuTime                         int,
WaitTime                        int,
MemUsageKB                      int,
PhysicalReads                   int,
LogicalReads                    int,
PagesModified                   int,
PacketsSent                     int,
PacketsReceived                 int,
NetworkPacketSize               int,
PlansAltered                    int,
RowsAffected                    int,
ErrorStatus                     int,
HashKey                         int,
SsqlId                          int,
StartTime                       datetime NULL,
EndTime                         datetime NULL,
)
external procedure
at “@SERVER@…$monSysStatement”
go

grant select on monSysStatement to mon_role
go

/*
** monProcessSQLText definition
*/
if (exists (select * from sysobjects
where name = ‘monProcessSQLText’
and type = ‘U’))
drop table monProcessSQLText
go

print “Creating monProcessSQLText”
go

create existing table monProcessSQLText (
SPID                            smallint,
KPID                            int,
ServerUserID                    int,
BatchID                         int,
LineNumber                      int,
SequenceInLine                  int,
SQLText                         varchar(255) NULL,
)
external procedure
at “@SERVER@…$monProcessSQLText”
go

grant select on monProcessSQLText to mon_role
go

/*
** monSysSQLText definition
*/
if (exists (select * from sysobjects
where name = ‘monSysSQLText’
and type = ‘U’))
drop table monSysSQLText
go

print “Creating monSysSQLText”
go

create existing table monSysSQLText (
SPID                            smallint,
KPID                            int,
ServerUserID                    int,
BatchID                         int,
SequenceInBatch                 int,
SQLText                         varchar(255) NULL,
)
external procedure
at “@SERVER@…$monSysSQLText”
go

grant select on monSysSQLText to mon_role
go

/*
** monCachedProcedures definition
*/
if (exists (select * from sysobjects
where name = ‘monCachedProcedures’
and type = ‘U’))
drop table monCachedProcedures
go

print “Creating monCachedProcedures”
go

create existing table monCachedProcedures (
ObjectID                        int,
OwnerUID                        int,
DBID                            int,
PlanID                          int,
MemUsageKB                      int,
CompileDate                     datetime,
ObjectName                      varchar(30) NULL,
ObjectType                      varchar(32) NULL,
OwnerName                       varchar(30) NULL,
DBName                          varchar(30) NULL,
)
external procedure
at “@SERVER@…$monCachedProcedures”
go

grant select on monCachedProcedures to mon_role
go

/*
** monProcessProcedures definition
*/
if (exists (select * from sysobjects
where name = ‘monProcessProcedures’
and type = ‘U’))
drop table monProcessProcedures
go

print “Creating monProcessProcedures”
go

create existing table monProcessProcedures (
SPID                            smallint,
KPID                            int,
DBID                            int,
OwnerUID                        int,
ObjectID                        int,
PlanID                          int,
MemUsageKB                      int,
CompileDate                     datetime,
ContextID                       int,
LineNumber                      int,
DBName                          varchar(30) NULL,
OwnerName                       varchar(30) NULL,
ObjectName                      varchar(30) NULL,
ObjectType                      varchar(32) NULL,
)
external procedure
at “@SERVER@…$monProcessProcedures”
go

grant select on monProcessProcedures to mon_role
go

/*
** monSysPlanText definition
*/
if (exists (select * from sysobjects
where name = ‘monSysPlanText’
and type = ‘U’))
drop table monSysPlanText
go

print “Creating monSysPlanText”
go

create existing table monSysPlanText (
PlanID                          int,
SPID                            smallint,
KPID                            int,
BatchID                         int,
ContextID                       int,
SequenceNumber                  int,
DBID                            int,
ProcedureID                     int,
PlanText                        varchar(160) NULL,
)
external procedure
at “@SERVER@…$monSysPlanText”
go

grant select on monSysPlanText to mon_role
go

/*
** monOpenPartitionActivity definition
*/
if (exists (select * from sysobjects
where name = ‘monOpenPartitionActivity’
and type = ‘U’))
drop table monOpenPartitionActivity
go

print “Creating monOpenPartitionActivity”
go

create existing table monOpenPartitionActivity (
DBID                            int,
ObjectID                        int,
IndexID                         int,
PartitionID                     int,
DBName                          varchar(30) NULL,
ObjectName                      varchar(30) NULL,
PartitionName                   varchar(30) NULL,
LogicalReads                    int NULL,
PhysicalReads                   int NULL,
APFReads                        int NULL,
PagesRead                       int NULL,
PhysicalWrites                  int NULL,
PagesWritten                    int NULL,
RowsInserted                    int NULL,
RowsDeleted                     int NULL,
RowsUpdated                     int NULL,
OptSelectCount                  int NULL,
LastOptSelectDate               datetime NULL,
UsedCount                       int NULL,
LastUsedDate                    datetime NULL,
HkgcRequests                    int NULL,
HkgcPending                     int NULL,
HkgcOverflows                   int NULL,
)
external procedure
at “@SERVER@…$monOpenPartitionActivity”
go

grant select on monOpenPartitionActivity to mon_role
go

/*
** monLicense definition
*/
if (exists (select * from sysobjects
where name = ‘monLicense’
and type = ‘U’))
drop table monLicense
go

print “Creating monLicense”
go

create existing table monLicense (
Quantity                        int,
Name                            varchar(30) NULL,
Edition                         varchar(30) NULL,
Type varchar(64) NULL,
Version                         varchar(16) NULL,
Status                          varchar(30) NULL,
LicenseExpiry                   datetime NULL,
GraceExpiry                     datetime NULL,
LicenseID                       varchar(150) NULL,
Filter varchar(14) NULL,
Attributes varchar(64) NULL,
)
external procedure
at “@SERVER@…$monLicense”
go

grant select on monLicense to mon_role
go

/*
** monStatementCache definition
*/
if (exists (select * from sysobjects
where name = ‘monStatementCache’
and type = ‘U’))
drop table monStatementCache
go

print “Creating monStatementCache”
go

create existing table monStatementCache (
TotalSizeKB                     int,
UsedSizeKB                      int,
NumStatements                   int,
NumSearches                     int,
HitCount                        int,
NumInserts                      int,
NumRemovals                     int,
NumRecompilesSchemaChanges      int,
NumRecompilesPlanFlushes        int,
)
external procedure
at “@SERVER@…$monStatementCache”
go

grant select on monStatementCache to mon_role
go

/*
** monCachedStatement definition
*/
if (exists (select * from sysobjects
where name = ‘monCachedStatement’
and type = ‘U’))
drop table monCachedStatement
go

print “Creating monCachedStatement”
go

create existing table monCachedStatement (
SSQLID                          int,
Hashkey                         int,
UserID                          int,
SUserID                         int,
DBID                            smallint,
UseCount                        int,
StatementSize                   int,
MinPlanSizeKB                   int,
MaxPlanSizeKB                   int,
CurrentUsageCount               int,
MaxUsageCount                   int,
NumRecompilesSchemaChanges      int,
NumRecompilesPlanFlushes        int,
HasAutoParams                   tinyint,
ParallelDegree                  tinyint,
QuotedIdentifier                tinyint,
TableCount                      tinyint,
TransactionIsolationLevel       tinyint,
TransactionMode                 tinyint,
SAAuthorization                 tinyint,
SystemCatalogUpdate             tinyint,
MetricsCount                    int,
MinPIO                          int,
MaxPIO                          int,
AvgPIO                          int,
MinLIO                          int,
MaxLIO                          int,
AvgLIO                          int,
MinCpuTime                      int,
MaxCpuTime                      int,
AvgCpuTime                      int,
MinElapsedTime                  int,
MaxElapsedTime                  int,
AvgElapsedTime                  int,
CachedDate                      datetime NULL,
LastUsedDate                    datetime NULL,
LastRecompiledDate              datetime NULL,
)
external procedure
at “@SERVER@…$monCachedStatement”
go

grant select on monCachedStatement to mon_role
go

« ASE Shared Disk Cluster at TechWave Sending email with attachments from Perl – the easy way »