DatabaseProcApplicationCreatedLinks
sybsystemprocssp_monitor_verify_setup  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** This stored procedure verifies if everything is kosher to run the monitoring
4     ** stored procedure. If any of the parameters are not satisfied, 
5     ** an error is raised. If a particular monitoring type is being done (e.g.
6     ** 'deadlock') then the appropriate configuration option setting up stuff
7     ** like pipe buffers will be checked, and an error will be reported if the
8     ** right configuration is missing.
9     **
10    ** This routine does double-duty as both a verify-and-fail sproc, as well as
11    ** as a verify-and-return-value sproc. The latter mode is used by the 'list'
12    ** command. If the verification fails, and the caller wants an error, then an
13    ** error is raised, returning 1 for failure. If the caller does not want an
14    ** error, then the config item that is not properly statically configured
15    ** is returned to the caller.
16    **
17    ** Parameters:
18    **	@montype	- Type of entity being monitored whose setup
19    **			  and required cfg options' value is validated.
20    **	@raiserror	- Boolean to control whether to raise errors, or to
21    **			  only execute this code to collect and return config
22    **			  numnber (i.e. caller is in verify mode).
23    **
24    ** Returns:
25    **	0 in case of sucess.
26    **	1 in case of failure, and caller is ok with raising an error.
27    **	Config num, in case of failure, and caller only wants to verify.
28    {
29    */
30    create or replace procedure sp_monitor_verify_setup(
31        @montype varchar(30) = NULL
32        , @raiserror int = 1
33    
34    ) as
35        begin
36            declare @cfg_value int
37                , @cfg_item varchar(100)
38                , @cfg_cmd varchar(100)
39                , @cfg_num int -- matching cfg_item
40                , @procval int
41    
42            if (@raiserror = 1)
43            begin
44                /*
45                ** Indicate that there is a problem if some of the key monitoring tables
46                ** are absent.
47                */
48                if not exists (select 1 from master.dbo.sysobjects
49                        where name IN ('monSysStatement'
50                                , 'monProcessSQLText'
51                                , 'monProcessActivity'
52                                , 'monDeadLock'
53                            ))
54                begin
55                    raiserror 19122, "MDA", "installmaster", "(mon_role)"
56                    return (1)
57                end
58            end
59    
60            -- In the following section, we check for individual config options that
61            -- need a value-setting (not boolean) for the particular type of monitoring
62            -- to be enabled. If the config option is NOT set to some non-zero value,
63            -- raise an error.
64            --
65            if (@montype IN ('connection', 'statement'))
66            begin
67                select @cfg_item = "max SQL text monitored"
68    
69                exec sp_monitor_getcfgval @cfg_item, @cfg_value output, 'static'
70    
71                if (@cfg_value = 0)
72                begin
73                    if (@raiserror = 1)
74                    begin
75                        raiserror 19262, "sp_monitor", @montype,
76                            @cfg_item, "1024", "1024"
77                        return 1
78                    end
79    
80                    exec sp_monitor_getcfgnum @cfg_item, @cfg_num output
81                    return (@cfg_num)
82                end
83            end
84    
85            else if (@montype IN ('procedure'))
86            begin
87                select @cfg_item = "statement pipe max messages"
88    
89                exec sp_monitor_getcfgval @cfg_item, @cfg_value output
90    
91                if (@cfg_value = 0)
92                begin
93                    if (@raiserror = 1)
94                    begin
95                        select @cfg_value = 1024
96                        select @cfg_cmd = "sp_configure '"
97                            + @cfg_item + "', "
98                            + convert(varchar, @cfg_value)
99                        raiserror 19480, "sp_monitor", @montype, @cfg_item
100                           , @cfg_value, @cfg_cmd
101                       return 1
102                   end
103   
104                   exec sp_monitor_getcfgnum @cfg_item, @cfg_num output
105                   return (@cfg_num)
106               end
107           end
108   
109           else if (@montype IN ('deadlock'))
110           begin
111               select @cfg_item = "deadlock pipe max messages"
112   
113               exec sp_monitor_getcfgval @cfg_item, @cfg_value output
114   
115               if (@cfg_value = 0)
116               begin
117                   if (@raiserror = 1)
118                   begin
119                       select @cfg_value = 200
120                       select @cfg_cmd = "sp_configure '"
121                           + @cfg_item + "', "
122                           + convert(varchar, @cfg_value)
123                       raiserror 19480, "sp_monitor", @montype, @cfg_item
124                           , @cfg_value, @cfg_cmd
125                       return 1
126                   end
127   
128                   exec sp_monitor_getcfgnum @cfg_item, @cfg_num output
129                   return (@cfg_num)
130               end
131           end
132   
133           return (0)
134       end -- }
135   

DEFECTS
 MGTP 3 Grant to public master..sysobjects  
 MNER 3 No Error Check should check return value of exec 69
 MNER 3 No Error Check should check return value of exec 80
 MNER 3 No Error Check should check return value of exec 89
 MNER 3 No Error Check should check return value of exec 104
 MNER 3 No Error Check should check return value of exec 113
 MNER 3 No Error Check should check return value of exec 128
 MUCO 3 Useless Code Useless Brackets in create proc 30
 MUCO 3 Useless Code Useless Begin-End Pair 35
 MUCO 3 Useless Code Useless Brackets 42
 MUCO 3 Useless Code Useless Brackets 56
 MUCO 3 Useless Code Useless Brackets 65
 MUCO 3 Useless Code Useless Brackets 71
 MUCO 3 Useless Code Useless Brackets 73
 MUCO 3 Useless Code Useless Brackets 81
 MUCO 3 Useless Code Useless Brackets 85
 MUCO 3 Useless Code Useless Brackets 91
 MUCO 3 Useless Code Useless Brackets 93
 MUCO 3 Useless Code Useless Brackets 105
 MUCO 3 Useless Code Useless Brackets 109
 MUCO 3 Useless Code Useless Brackets 115
 MUCO 3 Useless Code Useless Brackets 117
 MUCO 3 Useless Code Useless Brackets 129
 MUCO 3 Useless Code Useless Brackets 133
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
49
 VUNU 3 Variable is not used @procval 40
 MSUB 2 Subquery Marker 48
 MTR1 2 Metrics: Comments Ratio Comments: 43% 30
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 6 = 12dec - 8exi + 2 30
 MTR3 2 Metrics: Query Complexity Complexity: 51 30

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

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