DatabaseProcApplicationCreatedLinks
sybsystemprocssp_monitor_disable  14 déc. 14Defects Propagation 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 or replace 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    
44            exec @rtnstatus = sp_monitor_verify_setup
45            if (@rtnstatus != 0)
46                return @rtnstatus
47    
48            -- User might have entered: sp_monitor disable, 'procstack monitoring'
49            -- If so, strip out the term 'monitoring', and get the base monitoring type
50            -- which would be one of terms like 'all', 'connection', 'procstack' etc.
51            --
52            if (@montype LIKE "% monitoring")
53            begin
54                -- Locate start of ' monitoring' phrase.
55                select @char_index = charindex(' ', @montype)
56                if (@char_index > 0)
57                begin
58                    select @montype = ltrim(rtrim(substring(@montype, 1,
59                                    (@char_index - 1))))
60                end
61            end
62    
63            -- Validate argument for monitoring type and error out if it's invalid.
64            --
65            if (@montype IS NOT NULL) and (@montype NOT IN ('all'
66                        , 'connection'
67                        , 'statement'
68                        , 'event'
69                        , 'procedure'
70                        , 'deadlock'
71                        , 'procstack'
72                    ))
73            begin
74                raiserror 19260, "sp_monitor 'help', 'disable'"
75                return 1
76            end
77    
78            -- Silently return if there is no work to do; i.e. the control table is
79            -- not to be found.
80            --
81            if (object_id('tempdb.dbo.mon_config') IS NULL)
82            begin
83                return 0
84            end
85    
86            exec sp_getmessage 19479, @printmsg_type output
87            exec sp_getmessage 17778, @printmsg_cfg output
88    
89            if (@montype IS NULL) or (@montype = 'all')
90            begin
91                if (object_id('tempdb.dbo.mon_config') IS NOT NULL)
92                begin
93                    print @printmsg_type, "all"
94    
95                    -- Turn OFF monitoring for all options that you find set ON in the
96                    -- control table. Eliminate duplicate rows, as certain config options
97                    -- are common across monitoring types and will appear more than once
98                    -- in the control table.
99                    --
100                   select distinct confignum, configname
101                   into #mon_configs_on
102                   from tempdb.dbo.mon_config
103                   where configval = 0
104                       and confignum > 0 -- eliminate non-config entry  rows.
105   
106                   -- Report which config options are being turned OFF.
107                   declare mon_configs_on_cur cursor for
108                   select confignum, configname
109                   from #mon_configs_on
110                   for read only
111   
112                   open mon_configs_on_cur
113                   while (1 = 1)
114                   begin
115                       fetch mon_configs_on_cur into @cfgnum, @cfgname
116                       if (@@sqlstatus != 0)
117                           break
118   
119                       print @printmsg_cfg, @cfgname
120                   end
121   
122                   close mon_configs_on_cur
123                   deallocate cursor mon_configs_on_cur
124   
125                   select @val = config_admin(23, confignum, 0, 0, NULL, NULL)
126                   from #mon_configs_on
127   
128                   drop table #mon_configs_on
129                   drop table tempdb.dbo.mon_config
130   
131                   select @val = mdaconfig('disable_lwp', 'end')
132               end
133           end
134   
135           else
136           begin
137               print @printmsg_type, @montype
138   
139               -- Turn OFF config options for individual monitoring items.
140               -- Some of these config options are common between different monitoring
141               -- types. So, if are we turning OFF, say, monitoring for 'statement',
142               -- and we still want to retain monitoring for 'connection', we don't
143               -- want to turn OFF the config options that are common between them.
144               -- The way we handle this situation is:
145   
146               --   . Find the config options for this monitoring type that are 0.
147               --     (If the option was recorded as 1, it means that when monitoring
148               --      was enabled for the type being disabled, that config option
149               --      was already ON, turned ON by the user using sp_configure.)
150   
151               --   . For each such option, find out if the option is ON for some
152               --     other monitoring type. If so, leave it alone.
153   
154               --   . Otherwise, turn OFF this config option. This will ensure that
155               --     we only affect those config options that are needed by this
156               --     monitoring type and are currently unused by any other type.
157               --
158               declare cfg_option_cur cursor for
159               select m1.confignum, m1.configname
160               from tempdb.dbo.mon_config m1
161               where m1.monitor = @montype
162                   and not exists (select 1
163                       from tempdb.dbo.mon_config m2
164                       where m2.monitor != @montype
165                           and m2.confignum = m1.confignum)
166   
167                   and (m1.configval = 0) -- cfg was not ON before monitoring started
168                   and m1.confignum > 0 -- eliminate non-config entry rows.
169               for read only
170   
171               open cfg_option_cur
172   
173               while (1 = 1)
174               begin
175                   fetch cfg_option_cur into @cfgnum, @cfgname
176                   if (@@sqlstatus != 0)
177                       break
178   
179                   print @printmsg_cfg, @cfgname
180                   select @val = config_admin(23, @cfgnum, 0, 0, NULL, NULL)
181               end
182   
183               close cfg_option_cur
184               deallocate cursor cfg_option_cur
185   
186               -- Delete all rows to indicate that monitoring is disabled now.
187               delete tempdb.dbo.mon_config where monitor = @montype
188           end
189   
190           return (@rtnstatus)
191       end -- }
192   

DEFECTS
 PERR 6 Parsing Error Could not find definition for table tempdb..mon_config 102
 PERR 6 Parsing Error Could not find definition for table tempdb..mon_config 160
 PERR 6 Parsing Error Could not find definition for table tempdb..mon_config 187
 MULT 4 Using literal database 'tempdb' tempdb..mon_config 102
 MULT 4 Using literal database 'tempdb' tempdb..mon_config 160
 MULT 4 Using literal database 'tempdb' tempdb..mon_config 163
 MULT 4 Using literal database 'tempdb' tempdb..mon_config 187
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 103
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 104
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(30) 161
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(30) 164
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 167
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 168
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(30) 187
 MNER 3 No Error Check should check return value of exec 86
 MNER 3 No Error Check should check return value of exec 87
 MNER 3 No Error Check should check @@error after select into 100
 MNER 3 No Error Check should check @@error after delete 187
 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 45
 MUCO 3 Useless Code Useless Brackets 52
 MUCO 3 Useless Code Useless Brackets 56
 MUCO 3 Useless Code Useless Brackets 81
 MUCO 3 Useless Code Useless Brackets 91
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 173
 MUCO 3 Useless Code Useless Brackets 176
 MUCO 3 Useless Code Useless Brackets 190
 MZMB 3 Zombie: use of non-existent object tempdb..mon_config 102
 MZMB 3 Zombie: use of non-existent object tempdb..mon_config 160
 MZMB 3 Zombie: use of non-existent object tempdb..mon_config 163
 MZMB 3 Zombie: use of non-existent object tempdb..mon_config 187
 QAFM 3 Var Assignment from potentially many rows 125
 VNRD 3 Variable is not read @whoami 42
 CRDO 2 Read Only Cursor Marker (has for read only clause) 108
 MTR1 2 Metrics: Comments Ratio Comments: 51% 30
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 18 = 19dec - 3exi + 2 30
 MTR3 2 Metrics: Query Complexity Complexity: 77 30

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