DatabaseProcApplicationCreatedLinks
sybsystemprocssp_monitor_verify_cfgval  31 Aug 14Defects Dependencies

1     
2     create procedure sp_monitor_verify_cfgval(
3         @montype varchar(10)
4         , @raiserror int = 1
5     ) as
6         begin
7     
8             /* Config param values required so support this sproc */
9             declare @per_obj_stat int
10            declare @enable_monitoring int
11            declare @sql_batch_capture int
12            declare @wait_event_timing int
13            declare @process_wait_events int
14            declare @stmt_pipe_maxmsg int
15            declare @stmt_pipe_active int
16            declare @stmt_stat_active int
17                , @deadlock_pipe_max_msgs int
18                , @deadlock_pipe_active int
19                , @retval int -- return code from sproc.
20    
21    
22            /* Names of the configuration parameters */
23            declare @enable_monitoring_cfg varchar(100)
24            declare @sql_batch_capture_cfg varchar(100)
25            declare @per_obj_stat_cfg varchar(100)
26            declare @wait_event_cfg varchar(100)
27            declare @process_wait_events_cfg varchar(100)
28            declare @stmt_pipe_maxmsg_cfg varchar(100)
29            declare @stmt_pipe_active_cfg varchar(100)
30            declare @stmt_stat_active_cfg varchar(100)
31                , @deadlock_pipe_max_msgs_cfg varchar(100)
32                , @deadlock_pipe_active_cfg varchar(100)
33    
34    
35            /* Note down the configuration parameters of interest */
36            select @enable_monitoring_cfg = "enable monitoring"
37                , @sql_batch_capture_cfg = "SQL batch capture"
38                , @per_obj_stat_cfg = "per object statistics active"
39                , @wait_event_cfg = "wait event timing"
40                , @process_wait_events_cfg = "process wait events"
41                , @stmt_pipe_active_cfg = "statement pipe active"
42                , @stmt_pipe_maxmsg_cfg = "statement pipe max messages"
43                , @stmt_stat_active_cfg = "statement statistics active"
44                , @deadlock_pipe_active_cfg = "deadlock pipe active"
45    
46    
47            -- Assume there are no errors till we find otherwise.
48            select @retval = 0
49    
50            -- We only want to do this if this sproc is being called from run-time
51            -- when enable is being done, or if actual monitoring is being done.
52            -- If caller is simply executing verification or 'list' mode, no need
53            -- to further validate whether 'enable monitoring'is ON, or not. (It would
54            -- be ON, if some monitoring type was already enabled, anyway.)
55            --
56            if (@raiserror = 1)
57            begin
58                exec sp_monitor_getcfgval @enable_monitoring_cfg,
59                    @enable_monitoring output
60    
61                /* Verify if the 'enable monitoring' option is on */
62                select @retval = case @enable_monitoring when 1 then 0 else 1 end
63            end
64    
65            /*
66            ** Note that setup-type configuration (where memory has to be allocated for
67            ** stuff like, e.g., 'max SQL text monitored') has already been checked for
68            ** in sp_monitor_verify_setup sproc. Here we only check for on/off settings.
69            ** Check for config options depending on the type of monitoring.
70            */
71            if (@montype = 'connection')
72            begin
73                exec sp_monitor_getcfgval @sql_batch_capture_cfg
74                    , @sql_batch_capture output
75    
76                exec sp_monitor_getcfgval @wait_event_cfg
77                    , @wait_event_timing output
78    
79                exec sp_monitor_getcfgval @per_obj_stat_cfg
80                    , @per_obj_stat output
81    
82                if ((@sql_batch_capture = 0)
83                        or (@wait_event_timing = 0)
84                        or (@per_obj_stat = 0)
85                    )
86                begin
87                    select @retval = 1
88                end
89            end
90    
91            else if (@montype = 'statement')
92            begin
93                exec sp_monitor_getcfgval @sql_batch_capture_cfg
94                    , @sql_batch_capture output
95    
96                exec sp_monitor_getcfgval @stmt_stat_active_cfg
97                    , @stmt_stat_active output
98    
99                exec sp_monitor_getcfgval @per_obj_stat_cfg
100                   , @per_obj_stat output
101   
102               exec sp_monitor_getcfgval @wait_event_cfg
103                   , @wait_event_timing output
104   
105               if ((@sql_batch_capture = 0)
106                       or (@stmt_stat_active = 0)
107                       or (@per_obj_stat = 0)
108                       or (@wait_event_timing = 0)
109                   )
110               begin
111                   select @retval = 1
112               end
113           end
114   
115           else if (@montype = 'event')
116           begin
117               exec sp_monitor_getcfgval @wait_event_cfg
118                   , @wait_event_timing output
119   
120               exec sp_monitor_getcfgval @process_wait_events_cfg
121                   , @process_wait_events output
122   
123               if ((@wait_event_timing = 0)
124                       or (@process_wait_events = 0)
125                   )
126               begin
127                   select @retval = 1
128               end
129           end
130   
131           else if (@montype = 'procedure')
132           begin
133               exec sp_monitor_getcfgval @stmt_stat_active_cfg
134                   , @stmt_stat_active output
135   
136               exec sp_monitor_getcfgval @per_obj_stat_cfg
137                   , @per_obj_stat output
138   
139               exec sp_monitor_getcfgval @stmt_pipe_active_cfg
140                   , @stmt_pipe_active output
141   
142               if ((@stmt_stat_active = 0)
143                       or (@per_obj_stat = 0)
144                       or (@stmt_pipe_active = 0)
145                   )
146               begin
147                   select @retval = 1
148               end
149           end
150   
151           else if (@montype = 'deadlock')
152           begin
153               exec sp_monitor_getcfgval @deadlock_pipe_active_cfg
154                   , @deadlock_pipe_active output
155   
156               if ((@deadlock_pipe_active = 0)
157                   )
158               begin
159                   select @retval = 1
160               end
161           end
162   
163           if ((@retval = 1) and (@raiserror = 1))
164           begin
165               -- Build a string like 'enable,  monitoring', so that
166               -- it can be passed as 2nd arg to error message.
167               -- (Reuse variable, to save space.)
168               select @enable_monitoring_cfg = "'enable', '"
169                   + @montype
170                   + " monitoring'"
171   
172               raiserror 19261, "sp_monitor", @montype, "sp_monitor"
173                   , @enable_monitoring_cfg
174           end
175   
176           return @retval
177       end -- }
178   


exec sp_procxmode 'sp_monitor_verify_cfgval', 'AnyMode'
go

Grant Execute on sp_monitor_verify_cfgval to public
go
DEFECTS
 MGTP 3 Grant to public sybsystemprocs..sp_monitor_verify_cfgval  
 MNER 3 No Error Check should check return value of exec 58
 MNER 3 No Error Check should check return value of exec 73
 MNER 3 No Error Check should check return value of exec 76
 MNER 3 No Error Check should check return value of exec 79
 MNER 3 No Error Check should check return value of exec 93
 MNER 3 No Error Check should check return value of exec 96
 MNER 3 No Error Check should check return value of exec 99
 MNER 3 No Error Check should check return value of exec 102
 MNER 3 No Error Check should check return value of exec 117
 MNER 3 No Error Check should check return value of exec 120
 MNER 3 No Error Check should check return value of exec 133
 MNER 3 No Error Check should check return value of exec 136
 MNER 3 No Error Check should check return value of exec 139
 MNER 3 No Error Check should check return value of exec 153
 MUCO 3 Useless Code Useless Brackets in create proc 2
 MUCO 3 Useless Code Useless Begin-End Pair 6
 MUCO 3 Useless Code Useless Brackets 56
 MUCO 3 Useless Code Useless Brackets 71
 MUCO 3 Useless Code Useless Brackets 82
 MUCO 3 Useless Code Useless Brackets 91
 MUCO 3 Useless Code Useless Brackets 105
 MUCO 3 Useless Code Useless Brackets 115
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 131
 MUCO 3 Useless Code Useless Brackets 142
 MUCO 3 Useless Code Useless Brackets 151
 MUCO 3 Useless Code Useless Brackets 156
 MUCO 3 Useless Code Useless Brackets 163
 VNRD 3 Variable is not read @stmt_pipe_maxmsg_cfg 42
 VUNU 3 Variable is not used @stmt_pipe_maxmsg 14
 VUNU 3 Variable is not used @deadlock_pipe_max_msgs 17
 VUNU 3 Variable is not used @deadlock_pipe_max_msgs_cfg 31
 MTR1 2 Metrics: Comments Ratio Comments: 22% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 22 = 21dec - 1exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 75 2

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_monitor_getcfgval  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  

CALLERS
called by proc sybsystemprocs..sp_monitor_event  
called by proc sybsystemprocs..sp_monitor_connection  
called by proc sybsystemprocs..sp_monitor_deadlock  
   called by proc sybsystemprocs..sp_monitor  
called by proc sybsystemprocs..sp_monitor_archive  
   called by proc sybsystemprocs..sp_monitor_deadlock  
called by proc sybsystemprocs..sp_monitor_statement  
called by proc sybsystemprocs..sp_monitor_procedure  
called by proc sybsystemprocs..sp_monitor_list_montypes  
   called by proc sybsystemprocs..sp_monitor_list