1
2 /*
3 ** sp_monitor is the wrapper for any stored procedure that chooses to
4 ** display data from monitoring tables.
5 **
6 ** A new subordinate stored procedure should be created for each specific
7 ** option to sp_monitor.
8 ** Consider the example for statement monitoring.
9 ** A new option "statement" is provided to sp_monitor and a sub-ordinate
10 ** stored procedure sp_monitor_statement gets invoked when this option
11 ** is passed in. Additional parameters could also be passed in for
12 ** specific options as seen fit.
13 {
14 */
15 create procedure sp_monitor @entity varchar(30) = NULL,
16 @dbname varchar(30) = NULL,
17 @OrderBy_OR_Procname varchar(30) = NULL,
18 @option varchar(30) = NULL
19 as
20 begin
21 declare @listall int
22 declare @u_entity varchar(30)
23 , @l_entity varchar(30)
24 , @entity_arg varchar(30) -- user supplied argument.
25 , @charindex int
26 declare @u_dbname varchar(30)
27 , @l_dbname varchar(30)
28 declare @u_option varchar(30)
29 declare @monprocname varchar(255)
30 , @crt_procname varchar(100) -- For sp_monitor_crt_mon_config
31 declare @rtnstatus int
32 declare @spid int
33 , @context int
34 , @valid_entity tinyint -- @entity is valid
35 , @valid_montype tinyint -- @dbname is a valid monitoring type
36
37 /*
38 ** If we're in a transaction, disallow this since it might make recovery
39 ** impossible.
40 */
41 if @@trancount > 0
42 begin
43 /* 17260, "Can't run %1! from within a transaction." */
44 raiserror 17260, "sp_monitor"
45 return (1)
46 end
47 else
48 begin
49 set chained off
50 end
51
52 set transaction isolation level 1
53
54 -- Trim off leading/trailing spaces to make parsing simpler.
55 --
56 select @entity = ltrim(rtrim(@entity))
57
58 -- Save off the user-input 1st arg, as we might reuse it for a substring.
59 --
60 select @entity_arg = @entity
61
62 -- If 1st arg is 'archive', or 'report', it might have a substring naming the
63 -- archive table's prefix, starting with ' using' clause. Extract out the
64 -- basic command first.
65 --
66 select @charindex = charindex(' ', @entity)
67 if (@charindex != 0)
68 begin
69 select @entity = substring(@entity, 1, (@charindex - 1))
70 end
71
72 -- Convert to upper and lower case, for future re-use
73 select @u_entity = upper(@entity)
74 , @l_entity = lower(@entity)
75 , @u_dbname = upper(@dbname)
76 , @l_dbname = lower(@dbname)
77 , @u_option = upper(@option)
78
79 -- Assume some error in control flow till we validate all arguments.
80 , @rtnstatus = 1
81
82 -- Assume invalid arguments till we know otherwise.
83 , @valid_entity = case when @entity IS NOT NULL then 0 else 1 end
84 , @valid_montype = case when @dbname IS NOT NULL then 0 else 1 end
85 , @context = NULL
86
87 -- Cache status of valid entity and monitoring type (for reuse).
88 --
89 if ((@u_entity is not NULL)
90 and (@u_entity IN ("ENABLE"
91 , "DISABLE"
92 , "PROCEDURE"
93 , "CONNECTION"
94 , "STATEMENT"
95 , "EVENT"
96 , "DEADLOCK"
97 , "PROCSTACK"
98 , "ARCHIVE"
99 , "REPORT"
100 , "HELP"
101 , "LIST"
102 , "TRACE" -- undocumented option; internal use
103 )
104 )
105 )
106 begin
107 select @valid_entity = 1
108 end
109
110 if ((@u_dbname is not NULL)
111 and (@u_dbname IN ("ENABLE"
112 , "DISABLE"
113 , "PROCEDURE"
114 , "CONNECTION"
115 , "STATEMENT"
116 , "EVENT"
117 , "DEADLOCK"
118 , "PROCSTACK"
119 , "ARCHIVE"
120 , "REPORT"
121 , "HELP" -- 2nd arg can be 'help'.
122 , "ALL" -- 2nd arg can be 'help', 'all'.
123 )
124 )
125 )
126 begin
127 select @valid_montype = 1
128 end
129
130 /*
131 ** Verify if the parameters passed in are valid. Note that it is not possible
132 ** to do all parameter passing here but effort is made to do as much of checking
133 ** as possible. In cases where decisions on parameters cannot be made, they are
134 ** deferred to the individual stored procedure.
135 */
136
137 if (
138 (@valid_entity = 0)
139 OR
140 ((@u_entity = "HELP") AND (@valid_montype = 0))
141 OR
142 (@u_entity in ("CONNECTION", "STATEMENT", "ENABLE", "DISABLE",
143 "EVENT", "HELP", "") AND
144 ((@OrderBy_OR_Procname is NOT NULL) OR (@option is NOT NULL)))
145 OR
146 (@u_entity = "PROCEDURE" AND ((@u_option not in ("SUMMARY", "DETAIL"))
147 AND (@option is NOT NULL)))
148 OR
149 (@u_entity in ("CONNECTION", "STATEMENT")
150 AND (@u_dbname not in ("CPU", "DISKIO", "ELAPSED TIME", ""))
151 AND (@u_dbname is not NULL)
152 )
153 )
154 begin
155 -- Reuse variable to report help message. In the error generate the
156 -- sp_monitor invocation to get the appropriate help, depending on the
157 -- monitoring type requested.
158 --
159 -- . If a valid monitoring type was requested, generate:
160 -- sp_monitor 'help', ''
161 --
162 -- . If an invalid monitoring type was requested, generate:
163 -- sp_monitor 'help'
164 --
165 -- . If 'help' was the command, generate:
166 -- sp_monitor 'help'
167 --
168 select @monprocname = "sp_monitor 'help'"
169 + case
170 when (@valid_entity = 1) and (@l_entity != 'help')
171 then ", '" + @l_entity + "'"
172 else NULL
173 end
174 if ((@valid_entity = 0) or (@valid_montype = 0))
175 begin
176 select @entity_arg = case
177 when @valid_entity = 0
178 then @entity
179 when @valid_montype = 0
180 then @dbname
181 end
182 raiserror 18889, @entity_arg
183 end
184 raiserror 19260, @monprocname
185 return (1)
186 end
187
188 -- Check if some of the common utility sprocs are available, and bail if
189 -- they are not. The sub-procs assume that these verification sprocs exist,
190 -- so check first and bail if they don't.
191 --
192 select @monprocname = "sybsystemprocs.dbo.sp_monitor_verify_setup"
193 if (not exists (select 1 from sybsystemprocs.dbo.sysobjects
194 where sysstat & 7 = 4
195 and id = object_id(@monprocname)))
196 begin
197 raiserror 18826, @monprocname
198 goto missing_proc
199 end
200
201 select @monprocname = "sybsystemprocs.dbo.sp_monitor_verify_cfgval"
202 if (not exists (select 1 from sybsystemprocs.dbo.sysobjects
203 where sysstat & 7 = 4
204 and id = object_id(@monprocname)))
205 begin
206 raiserror 18826, @monprocname
207 goto missing_proc
208 end
209
210 -- See if the monitoring-specific sub-proc is available, and setup a
211 -- variable to call it.
212 --
213 select @monprocname = "sybsystemprocs.dbo.sp_monitor_"
214
215 select @monprocname = @monprocname + case @u_entity
216 when NULL then 'server'
217 else @l_entity
218 end
219
220 if (not exists (select 1 from sybsystemprocs.dbo.sysobjects
221 where sysstat & 7 = 4
222 and id = object_id(@monprocname)))
223 begin
224 raiserror 18826, @monprocname
225 goto missing_proc
226 end
227
228 if @u_entity = "HELP"
229 begin
230 -- Note that @dbname is overloaded to provide detailed
231 -- help on the parameter passed along with help.
232 -- User can say: sp_monitor help, "all"
233 -- to get detailed help information on all supported monitoring types.
234 -- In that caes pass @listall as 2.
235 --
236 select @listall = case
237 WHEN @u_dbname IS NULL then 1
238 WHEN lower(@u_dbname) = "all" then 2
239 else 0
240 end
241 exec @rtnstatus = @monprocname @u_dbname, @listall
242 return @rtnstatus
243 end
244
245 -- By default monitor the server
246 if (@u_entity is NULL)
247 begin
248 exec @rtnstatus = @monprocname
249 return @rtnstatus
250 end
251
252 /*
253 ** From here on, we go into the land of monitoring individual entities via
254 ** MDA tables. This system needs a control table that might not exist from
255 ** various calling sequences. Ensure that it is created first before doing
256 ** any further work.
257 */
258 select @crt_procname = "sybsystemprocs.dbo.sp_monitor_crt_mon_config"
259 exec @rtnstatus = @crt_procname
260 if (@rtnstatus != 0)
261 return @rtnstatus
262
263 if (@u_entity = "ENABLE")
264 begin
265 -- Using lower-case monitoring type names...
266 exec @rtnstatus = @monprocname @l_dbname
267 end
268
269 else if (@u_entity = "DISABLE")
270 begin
271 -- Using lower-case monitoring type names...
272 exec @rtnstatus = @monprocname @l_dbname
273 end
274
275 else if @u_entity = "PROCEDURE"
276 begin
277 exec @rtnstatus = @monprocname @dbname, @OrderBy_OR_Procname, @option
278 end
279
280 else if @u_entity = "EVENT"
281 begin
282 select @spid = case WHEN (@u_dbname is not NULL)
283 THEN convert(int, @u_dbname)
284 ELSE 0
285 end
286 exec @rtnstatus = @monprocname @spid
287 end
288
289 else if @u_entity = "CONNECTION"
290 begin
291 /*
292 ** By default order the output for monitoring
293 ** connections in the decreasing order of elapsed time
294 */
295 if (@u_dbname is NULL)
296 begin
297 select @u_dbname = "ELAPSED TIME"
298 end
299
300 exec @rtnstatus = @monprocname @u_dbname, @OrderBy_OR_Procname
301 end
302
303 else if @u_entity = "STATEMENT"
304 begin
305 /* By default order by elapsed time. */
306 if (@u_dbname is NULL)
307 begin
308 select @u_dbname = "ELAPSED TIME"
309 end
310
311 exec @rtnstatus = @monprocname @u_dbname
312 end
313
314 else if @u_entity = "DEADLOCK"
315 begin
316 -- Call this directly from the current db, w/o prefacing the dbname,
317 -- so that if the user issues this from 'master', we can correctly
318 -- use master..monDeadLock w/o any further parsing fuss.
319 --
320 select @monprocname = "sp_monitor_deadlock"
321 exec @rtnstatus = @monprocname
322 @dbname
323 , @OrderBy_OR_Procname
324 , @option
325 end
326
327 else if @u_entity = "PROCSTACK"
328 begin
329 -- If no spid is provided, generate current task's procedure stack
330 if (@dbname IS NULL)
331 select @spid = @@spid
332
333 else if (patindex("%[^0-9]%", @dbname) = 0)
334 begin
335 select @spid = convert(int, @dbname)
336 end
337 else
338 begin
339 select @monprocname = "<spid> = '" + @dbname + "'"
340 raiserror 19060, '"sp_monitor procstack"', @monprocname
341 return 1
342 end
343
344 -- Validate this arg, as an int, and pass it as the contet block
345 -- to sub-proc. (These many lines of context will be generated when
346 -- calling sp_showtext underneath here._
347 --
348 if (@OrderBy_OR_Procname IS NOT NULL)
349 begin
350 if (patindex("%[^0-9]%", @OrderBy_OR_Procname) = 0)
351 begin
352 select @context = convert(int, @OrderBy_OR_Procname)
353 end
354 else
355 begin
356 select @monprocname = "<contextblock> = '"
357 + @OrderBy_OR_Procname + "'"
358 raiserror 19060, '"sp_monitor procstack"', @monprocname
359 return 1
360 end
361 end
362
363 -- Produce the procedural/stack trace for this spid.
364 exec @rtnstatus = @monprocname @spid, @context, @option
365 end
366
367 else if @u_entity = "ARCHIVE"
368 begin
369 -- Call the archival sproc for given monitoring type and archive.
370 select @monprocname = "dbo.sp_monitor_archive"
371 exec @rtnstatus = @monprocname @dbname, @entity_arg
372 end
373
374 else if @u_entity = "REPORT"
375 begin
376 -- Call the reporting sproc for given monitoring type and report.
377 select @monprocname = "dbo.sp_monitor_report"
378 exec @rtnstatus = @monprocname @dbname, @entity_arg
379 , @OrderBy_OR_Procname, @option
380 end
381
382 else if @u_entity = "LIST"
383 begin
384 exec @rtnstatus = @monprocname
385 end
386
387 else if @u_entity = "TRACE"
388 begin
389 exec @rtnstatus = @monprocname @dbname
390 end
391
392 -- Return whatever return code we found from callee.
393 return (@rtnstatus)
394
395 missing_proc:
396 raiserror 19122, "MDA", "installmontables", "(mon_role)"
397 return (1)
398 end -- }
399
exec sp_procxmode 'sp_monitor', 'AnyMode'
go
Grant Execute on sp_monitor to public
go
