DatabaseProcApplicationCreatedLinks
sybsystemprocssp_monitor_deadlock_usage  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** sp_monitor_deadlock_usage
4     **
5     **	Print the usage help information for monitoring deadlocks.
6     **
7     ** Parameters:
8     **	@output_type		- 'terse' or 'verbose'
9     **	@output_count_by_date
10    **	@output_count_by_appln
11    **	@output_count_by_object
12    **	@output_count_by_date_object
13    **	@opt_verbose
14    **	@opt_pagdiag
15    **				- Output mode phrases that are recognized as
16    **				  valid and used to report in usage messages.
17    {
18    */
19    create or replace procedure sp_monitor_deadlock_usage(
20        @output_type varchar(8)
21        , @output_count_by_date varchar(30)
22        , @output_count_by_appln varchar(30)
23        , @output_count_by_object varchar(30)
24        , @output_count_by_date_object varchar(30)
25        , @opt_verbose varchar(7)
26        , @opt_pagediag varchar(8)
27    ) as
28        begin
29            declare @dlk_table varchar(30)
30                , @deadlock varchar(8)
31                , @deadlock_table varchar(30)
32                , @mdlk_table varchar(30)
33                , @arch_dbname varchar(10)
34                , @prefix varchar(10)
35                , @verb_pagediag varchar(30)
36                , @msg varchar(200)
37                , @sp_command varchar(80)
38                , @dlk_pipe_max_msgs_cfg varchar(30)
39                , @dlk_pipe_max_msgs_value int
40                , @newline char(1)
41                , @tabchar char(1)
42                , @indent varchar(5)
43    
44            select @dlk_table = 'daily_monDeadLock'
45                , @deadlock = 'deadlock'
46                , @deadlock_table = 'monDeadLock'
47                , @mdlk_table = 'master.dbo.monDeadLock'
48                , @arch_dbname = 'mondb'
49                , @prefix = 'daily_'
50                , @verb_pagediag = @opt_verbose + "," + @opt_pagediag
51                , @dlk_pipe_max_msgs_cfg = "deadlock pipe max messages"
52                , @dlk_pipe_max_msgs_value = 200
53                , @newline = char(10)
54                , @tabchar = char(9)
55                , @indent = "  -- "
56    
57            print " "
58            exec sp_getmessage 18954, @msg output
59            select @sp_command = @msg
60                + " sp_monitor '%1!' [ [, '@filters' ] [, '@output_modes' ] ]"
61            print @sp_command, @deadlock
62    
63            select @sp_command = @msg + " sp_monitor '%1!' "
64            print @sp_command, @deadlock
65            print "		[  [, '<deadlockID>' | '<for Date>' ]"
66            print "		   [, { '%1!' | '%2!' }"
67                , @opt_verbose, @opt_pagediag
68    
69            -- reuse sp_command to build print format string w/newlines embedded.
70            --
71            select @sp_command = @tabchar + @tabchar + "     |"
72                + "{   '%1!' "
73                + @newline + @tabchar + @tabchar + @tabchar
74                + "| '%2!' "
75                + @newline + @tabchar + @tabchar + @tabchar
76                + "| '%3!' "
77                + @newline + @tabchar + @tabchar + @tabchar
78                + "| '%4!' "
79                + @newline + @tabchar + @tabchar
80                + "      } ]"
81            -- print "		     | {   '%1!' | '%2!' | '%3!' | '%4!' } ]"
82            print @sp_command
83                , @output_count_by_date
84                , @output_count_by_appln
85                , @output_count_by_object
86                , @output_count_by_date_object
87            print "		]"
88    
89            if (@output_type = 'terse')
90            begin
91                return 0
92            end
93    
94            print " "
95    
96            -- Reuse @verb_pagediag to store term for msg.
97            exec sp_getmessage 19494, @verb_pagediag output
98            select @sp_command = "sp_monitor 'enable', " + "'" + @deadlock + "'"
99            print @verb_pagediag, @sp_command
100   
101           exec sp_getmessage 19497, @msg output
102           print @msg
103               , @dlk_pipe_max_msgs_cfg, @dlk_pipe_max_msgs_value
104   
105           select @sp_command = "sp_configure '" + @dlk_pipe_max_msgs_cfg + "'"
106               + ", " + convert(varchar, @dlk_pipe_max_msgs_value)
107   
108           print @verb_pagediag, @sp_command
109   
110           print " "
111           exec sp_getmessage 19498, @msg output
112           print @msg, @mdlk_table
113   
114           print " "
115           exec sp_getmessage 19499, @msg output
116           print @msg, @indent
117           print "	sp_monitor 'deadlock'"
118   
119           print " "
120           exec sp_getmessage 19500, @msg output
121           print @msg, @indent
122           print "	sp_monitor 'deadlock', '10'"
123   
124           print " "
125           exec sp_getmessage 19501, @msg output
126           print @msg, @indent
127           print "	sp_monitor 'deadlock', '10', '%1!'"
128               , @opt_verbose
129   
130           print " "
131           exec sp_getmessage 19502, @msg output
132           print @msg, @indent
133           print "	sp_monitor 'deadlock', '10', '%1!'"
134               , @verb_pagediag
135   
136           print " "
137           exec sp_getmessage 19503, @msg output
138           print @msg, @indent
139           print "	sp_monitor 'deadlock', NULL, '%1!'"
140               , @output_count_by_appln
141   
142           print " "
143           exec sp_getmessage 19504, @msg output
144           print @msg, @indent
145           print "	sp_monitor 'deadlock', NULL, '%1!'"
146               , @output_count_by_object
147   
148           print " "
149           exec sp_getmessage 19505, @msg output
150           print @msg, @deadlock_table
151   
152           print " "
153           exec sp_getmessage 19506, @msg output
154           print @msg, @indent, @deadlock_table, @arch_dbname
155           print "	sp_monitor 'archive', 'deadlock'"
156   
157           print " "
158           exec sp_getmessage 19507, @msg output
159           print @msg, @indent, @arch_dbname, @deadlock_table
160           print "	%1!..sp_monitor 'report', 'deadlock'"
161               , @arch_dbname
162               , @deadlock_table
163   
164           print " "
165           exec sp_getmessage 19508, @msg output
166           print @msg, @indent, @deadlock_table
167           print "	sp_monitor 'report', 'deadlock', '10'"
168   
169           print " "
170           exec sp_getmessage 19509, @msg output
171           print @msg, @indent, @deadlock_table, @arch_dbname
172           print "	sp_monitor 'report', 'deadlock', '10', '%1!'"
173               , @opt_verbose
174   
175           print " "
176           exec sp_getmessage 19510, @msg output
177           print @msg, @indent, @deadlock_table
178           print "	sp_monitor 'report', 'deadlock', 'Mar 16 2006'"
179   
180           print " "
181           exec sp_getmessage 19511, @msg output
182           print @msg, @indent
183           print "	sp_monitor 'report', 'deadlock', NULL, '%1!'"
184               , @output_count_by_date
185   
186           print " "
187           exec sp_getmessage 19512, @msg output
188           print @msg, @indent
189           print "	sp_monitor 'report', 'deadlock', NULL, '%1!'"
190               , @output_count_by_appln
191   
192           print " "
193           exec sp_getmessage 19513, @msg output
194           print @msg, @indent
195           print "	sp_monitor 'report', 'deadlock', NULL, '%1!'"
196               , @output_count_by_object
197   
198           print " "
199           exec sp_getmessage 19514, @msg output
200           print @msg
201   
202           print " "
203           exec sp_getmessage 19515, @msg output
204           print @msg, @indent
205               , @prefix, @deadlock_table, @arch_dbname
206           print "	sp_monitor 'archive, prefix=%1!', 'deadlock'"
207               , @prefix
208   
209           print " "
210           exec sp_getmessage 19516, @msg output
211           print @msg, @indent
212               , @prefix, @deadlock_table, @arch_dbname
213           print "	sp_monitor 'report, prefix=%1!', 'deadlock'"
214               , @prefix
215   
216       end -- }
217   

DEFECTS
 MNER 3 No Error Check should check return value of exec 58
 MNER 3 No Error Check should check return value of exec 97
 MNER 3 No Error Check should check return value of exec 101
 MNER 3 No Error Check should check return value of exec 111
 MNER 3 No Error Check should check return value of exec 115
 MNER 3 No Error Check should check return value of exec 120
 MNER 3 No Error Check should check return value of exec 125
 MNER 3 No Error Check should check return value of exec 131
 MNER 3 No Error Check should check return value of exec 137
 MNER 3 No Error Check should check return value of exec 143
 MNER 3 No Error Check should check return value of exec 149
 MNER 3 No Error Check should check return value of exec 153
 MNER 3 No Error Check should check return value of exec 158
 MNER 3 No Error Check should check return value of exec 165
 MNER 3 No Error Check should check return value of exec 170
 MNER 3 No Error Check should check return value of exec 176
 MNER 3 No Error Check should check return value of exec 181
 MNER 3 No Error Check should check return value of exec 187
 MNER 3 No Error Check should check return value of exec 193
 MNER 3 No Error Check should check return value of exec 199
 MNER 3 No Error Check should check return value of exec 203
 MNER 3 No Error Check should check return value of exec 210
 MUCO 3 Useless Code Useless Brackets in create proc 19
 MUCO 3 Useless Code Useless Begin-End Pair 28
 MUCO 3 Useless Code Useless Brackets 89
 VNRD 3 Variable is not read @dlk_table 44
 MTR1 2 Metrics: Comments Ratio Comments: 9% 19
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 2 = 2dec - 2exi + 2 19
 MTR3 2 Metrics: Query Complexity Complexity: 98 19

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  

CALLERS
called by proc sybsystemprocs..sp_monitor_deadlock  
   called by proc sybsystemprocs..sp_monitor