DatabaseProcApplicationCreatedLinks
sybsystemprocssp_monitor_disable  31 Aug 14Defects Dependencies

1     
2     /*
3     ** sp_monitor_disable
4     **
5     ** This stored procedure is a subordinate stored procedure that is invoked from
6     ** the parent stored procedure sp_monitor when "disable" is passed as the 
7     ** argument. This stored procedure provides a way to disable the configuration
8     ** parameters relevant to monitoring
9     **
10    ** sp_monitor "disable" should be run once the monitoring activities using
11    ** sp_monitor stored procedures that use monitoring tables are complete. 
12    ** This will ensure that all the configuration parameters doing monitoring
13    ** are turned off.
14    **
15    ** Monitoring can be disabled using 'all' mode where all the config options
16    ** related to all monitoring items are turned OFF. Optionally, individual
17    ** config options for individual monitoring types can be selectively
18    ** disabled. If 'all' monitoring types are disabled, the control table is then
19    ** dropped.
20    **
21    ** Parameters:
22    **      @montype	- Type of monitoring to be disabled.
23    **
24    ** Returns:
25    **      0       - upon sucessful completion
26    **      1       - If an error resulted.
27    {
28    */
29    
30    create procedure sp_monitor_disable(@montype varchar(30) = NULL
31    ) as
32        begin
33            declare @rtnstatus int
34            declare @val int
35                , @char_index int
36                , @cfgnum int
37                , @cfgname varchar(80)
38                , @printmsg_cfg varchar(80)
39                , @printmsg_type varchar(80)
40                , @whoami varchar(30)
41    
42            select @whoami = object_name(@@procid, db_id('sybsystemprocs'))
43            exec @rtnstatus = sp_monitor_check_permission @whoami
44            if (@rtnstatus != 0)
45                return @rtnstatus
46    
47            exec @rtnstatus = sp_monitor_verify_setup
48            if (@rtnstatus != 0)
49                return @rtnstatus
50    
51            -- User might have entered: sp_monitor disable, 'procstack monitoring'
52            -- If so, strip out the term 'monitoring', and get the base monitoring type
53            -- which would be one of terms like 'all', 'connection', 'procstack' etc.
54            --
55            if (@montype LIKE "% monitoring")
56            begin
57                -- Locate start of ' monitoring' phrase.
58                select @char_index = charindex(' ', @montype)
59                if (@char_index > 0)
60                begin
61                    select @montype = ltrim(rtrim(substring(@montype, 1,
62                                    (@char_index - 1))))
63                end
64            end
65    
66            -- Validate argument for monitoring type and error out if it's invalid.
67            --
68            if (@montype IS NOT NULL) and (@montype NOT IN ('all'
69                        , 'connection'
70                        , 'statement'
71                        , 'event'
72                        , 'procedure'
73                        , 'deadlock'
74                        , 'procstack'
75                    ))
76            begin
77                raiserror 19260, "sp_monitor 'help', 'disable'"
78                return 1
79            end
80    
81            -- Silently return if there is no work to do; i.e. the control table is
82            -- not to be found.
83            --
84            if (object_id('tempdb.dbo.mon_config') IS NULL)
85            begin
86                return 0
87            end
88    
89            exec sp_getmessage 19479, @printmsg_type output
90            exec sp_getmessage 17778, @printmsg_cfg output
91    
92            if (@montype IS NULL) or (@montype = 'all')
93            begin
94                if (object_id('tempdb.dbo.mon_config') IS NOT NULL)
95                begin
96                    print @printmsg_type, "all"
97    
98                    -- Turn OFF monitoring for all options that you find set ON in the
99                    -- control table. Eliminate duplicate rows, as certain config options
100                   -- are common across monitoring types and will appear more than once
101                   -- in the control table.
102                   --
103                   select distinct confignum, configname
104                   into #mon_configs_on
105                   from tempdb.dbo.mon_config
106                   where configval = 0
107                       and confignum > 0 -- eliminate non-config entry  rows.
108   
109                   -- Report which config options are being turned OFF.
110                   declare mon_configs_on_cur cursor for
111                   select confignum, configname
112                   from #mon_configs_on
113                   for read only
114   
115                   open mon_configs_on_cur
116                   while (1 = 1)
117                   begin
118                       fetch mon_configs_on_cur into @cfgnum, @cfgname
119                       if (@@sqlstatus != 0)
120                           break
121   
122                       print @printmsg_cfg, @cfgname
123                   end
124   
125                   close mon_configs_on_cur
126                   deallocate cursor mon_configs_on_cur
127   
128                   select @val = config_admin(23, confignum, 0, 0, NULL, NULL)
129                   from #mon_configs_on
130   
131                   drop table #mon_configs_on
132                   drop table tempdb.dbo.mon_config
133   
134                   select @val = mdaconfig('disable_lwp', 'end')
135               end
136           end
137   
138           else
139           begin
140               print @printmsg_type, @montype
141   
142               -- Turn OFF config options for individual monitoring items.
143               -- Some of these config options are common between different monitoring
144               -- types. So, if are we turning OFF, say, monitoring for 'statement',
145               -- and we still want to retain monitoring for 'connection', we don't
146               -- want to turn OFF the config options that are common between them.
147               -- The way we handle this situation is:
148   
149               --   . Find the config options for this monitoring type that are 0.
150               --     (If the option was recorded as 1, it means that when monitoring
151               --      was enabled for the type being disabled, that config option
152               --      was already ON, turned ON by the user using sp_configure.)
153   
154               --   . For each such option, find out if the option is ON for some
155               --     other monitoring type. If so, leave it alone.
156   
157               --   . Otherwise, turn OFF this config option. This will ensure that
158               --     we only affect those config options that are needed by this
159               --     monitoring type and are currently unused by any other type.
160               --
161               declare cfg_option_cur cursor for
162               select m1.confignum, m1.configname
163               from tempdb.dbo.mon_config m1
164               where m1.monitor = @montype
165                   and not exists (select 1
166                       from tempdb.dbo.mon_config m2
167                       where m2.monitor != @montype
168                           and m2.confignum = m1.confignum)
169   
170                   and (m1.configval = 0) -- cfg was not ON before monitoring started
171                   and m1.confignum > 0 -- eliminate non-config entry rows.
172               for read only
173   
174               open cfg_option_cur
175   
176               while (1 = 1)
177               begin
178                   fetch cfg_option_cur into @cfgnum, @cfgname
179                   if (@@sqlstatus != 0)
180                       break
181   
182                   print @printmsg_cfg, @cfgname
183                   select @val = config_admin(23, @cfgnum, 0, 0, NULL, NULL)
184               end
185   
186               close cfg_option_cur
187               deallocate cursor cfg_option_cur
188   
189               -- Delete all rows to indicate that monitoring is disabled now.
190               delete tempdb.dbo.mon_config where monitor = @montype
191           end
192   
193           return (@rtnstatus)
194       end -- }
195   


exec sp_procxmode 'sp_monitor_disable', 'AnyMode'
go

Grant Execute on sp_monitor_disable to public
go
DEFECTS
 PERR 6 Parsing Error Could not find definition for table tempdb..mon_config 105
 PERR 6 Parsing Error Could not find definition for table tempdb..mon_config 163
 PERR 6 Parsing Error Could not find definition for table tempdb..mon_config 190
 MULT 4 Using literal database 'tempdb' tempdb..mon_config 105
 MULT 4 Using literal database 'tempdb' tempdb..mon_config 163
 MULT 4 Using literal database 'tempdb' tempdb..mon_config 166
 MULT 4 Using literal database 'tempdb' tempdb..mon_config 190
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 106
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 107
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(30) 164
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(30) 167
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 170
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 171
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(30) 190
 MGTP 3 Grant to public sybsystemprocs..sp_monitor_disable  
 MNER 3 No Error Check should check return value of exec 89
 MNER 3 No Error Check should check return value of exec 90
 MNER 3 No Error Check should check @@error after select into 103
 MNER 3 No Error Check should check @@error after delete 190
 MUCO 3 Useless Code Useless Brackets in create proc 30
 MUCO 3 Useless Code Useless Begin-End Pair 32
 MUCO 3 Useless Code Useless Brackets 44
 MUCO 3 Useless Code Useless Brackets 48
 MUCO 3 Useless Code Useless Brackets 55
 MUCO 3 Useless Code Useless Brackets 59
 MUCO 3 Useless Code Useless Brackets 84
 MUCO 3 Useless Code Useless Brackets 94
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 119
 MUCO 3 Useless Code Useless Brackets 176
 MUCO 3 Useless Code Useless Brackets 179
 MUCO 3 Useless Code Useless Brackets 193
 MZMB 3 Zombie: use of non-existent object tempdb..mon_config 105
 MZMB 3 Zombie: use of non-existent object tempdb..mon_config 163
 MZMB 3 Zombie: use of non-existent object tempdb..mon_config 166
 MZMB 3 Zombie: use of non-existent object tempdb..mon_config 190
 QAFM 3 Var Assignment from potentially many rows 128
 CRDO 2 Read Only Cursor Marker (has for read only clause) 111
 MTR1 2 Metrics: Comments Ratio Comments: 49% 30
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 18 = 19dec - 3exi + 2 30
 MTR3 2 Metrics: Query Complexity Complexity: 81 30

DEPENDENCIES
PROCS AND TABLES USED
read_writes table tempdb..#mon_configs_on (1) 
calls proc sybsystemprocs..sp_monitor_check_permission  
calls proc sybsystemprocs..sp_monitor_verify_setup  
   reads table master..sysobjects (1)  
   calls proc sybsystemprocs..sp_monitor_getcfgnum  
      reads table master..sysconfigures (1)  
   calls proc sybsystemprocs..sp_monitor_getcfgval  
      reads table master..syscurconfigs (1)  
      reads table master..sysconfigures (1)  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..sysmessages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..syslanguages (1)