DatabaseProcApplicationCreatedLinks
sybsystemprocssp_engine  14 déc. 14Defects Propagation Dependencies

1     
2     
3     /*
4     ** Messages for "sp_engine"
5     **
6     ** 17260, "Can't run %1! from within a transaction."
7     ** 18366, "WARNING ! : %1! has been deprecated for threaded kernel mode and will be removed from a subsequent release. Use '%2!' to achieve the same functionality in threaded kernel mode."
8     ** 18899, "Invalid value supplied to parameter %1!."
9     ** 19461, "You can take this engine offline."
10    */
11    
12    /*
13    ** Syntax:
14    **	sp_engine "online | offline | can_offline | shutdown
15    **		| q_online | q_offline | q_can_offline | q_shutdown " [,]
16    **
17    **	online		- online an engine
18    **	offline		- offline an engine with engid being passed in.
19    **	can_offline	- checks to see if the engine can be offlined.
20    **	shutdown	- shutdown the engine
21    **	q_online	- online a Q engine
22    **	q_offline	- offline a Q engine with engid being passed in.
23    **	q_can_offline	- checks to see if the Q engine can be offlined.
24    **	q_shutdown	- shutdown the Q engine
25    **
26    **	Onlines or offlines an engine. This stored procedure can be 
27    **	used to online an additional engine if the number of engines 
28    **	currently in the server is less than 'max online engines' 
29    **	config parameter value. This stored procedure can also be used
30    **	to offline an engine, if the number of engines active is more
31    **	than one.
32    **
33    ** Returns:
34    **	1 - if error.
35    **	0 - if no error.
36    */
37    create or replace procedure sp_engine
38        @option varchar(30),
39        @engid int = - 1
40    as
41    
42        declare @nullarg varchar(1)
43        declare @dummy int
44        declare @status int
45        declare @gp_enabled int
46    
47    
48    
49    
50        declare @old_thread_count int
51        declare @new_thread_count int
52    
53        if @@trancount > 0
54        begin
55            /* 17260, "Can't run %1! from within a transaction." */
56            raiserror 17260, "sp_engine"
57            return (1)
58        end
59        else
60        begin
61            set chained off
62        end
63    
64        set transaction isolation level 1
65        set nocount on
66    
67        /* 
68        ** If granular permissions is not enabled then sa_role is required.
69        ** If granular permissions is enabled then the permission manage server is 
70        ** required. This stored procedure is related to config.
71        */
72        select @nullarg = NULL
73        execute @status = sp_aux_checkroleperm "sa_role",
74            "manage server", @nullarg, @gp_enabled output
75    
76        /* For Auditing */
77        if (@gp_enabled = 0)
78        begin
79            if (proc_role("sa_role") = 0)
80                return 1
81        end
82        else
83        begin
84            select @dummy = proc_auditperm("manage server", @status)
85        end
86    
87        if (@status != 0)
88            return 1
89    
90    
91        if (@@kernelmode != 'process')
92        begin
93            /* 18366, "WARNING ! : %1! has been deprecated for threaded kernel mode and will be removed from a subsequent release. Use '%2!' to achieve the same functionality in threaded kernel mode." */
94    
95            raiserror 18366, "sp_engine", "alter thread pool syb_default_pool with thread count = new_eng_count"
96    
97            /* Get the current value for number of threads for syb_default_pool */
98            select @old_thread_count = Size from master.dbo.monThreadPool
99            where ThreadPoolName = "syb_default_pool"
100   
101           if (@option = "can_offline")
102           begin
103               if (@engid >= @old_thread_count OR @old_thread_count < 2)
104               begin
105                   raiserror 18899, "engid"
106                   return (1)
107               end
108               else
109               begin
110                   raiserror 19461
111                   return (0)
112               end
113           end
114   
115           if (@option = "online")
116           begin
117               select @new_thread_count = @old_thread_count + 1
118           end
119           else if (@option = "offline" OR @option = "shutdown")
120           begin
121               select @new_thread_count = @old_thread_count - 1
122           end
123   
124           execute ("alter thread pool syb_default_pool with thread count
125   		= @new_thread_count")
126   
127           return (0)
128       end
129   
130       if (@option = "offline")
131       begin
132           /* Offline an engine */
133           if (@engid = - 1)
134           begin
135               dbcc engine(@option)
136           end
137           else
138           begin
139               dbcc engine(@option, @engid)
140           end
141       end
142       else if (@option = "can_offline")
143       begin
144           /* Can the engine be offlined? */
145           if (@engid = - 1)
146           begin
147               dbcc engine(@option)
148           end
149           else
150           begin
151               dbcc engine(@option, @engid)
152           end
153           if (@@error = 0)
154           begin
155               raiserror 19461
156               return (0)
157           end
158           else
159           begin
160               return (1)
161           end
162       end
163       else if (@option = "online")
164       begin
165           /* Online an engine */
166           dbcc engine(@option)
167       end
168       else if (@option = "shutdown")
169       begin
170           /* Can the engine be shutdown? */
171           if (@engid = - 1)
172           begin
173               /* Shutdown always expects engid to be supplied */
174               raiserror 18899, "engid"
175               return (1)
176           end
177           else
178           begin
179               dbcc engine(@option, @engid)
180           end
181       end
182       else if (@option = "q_offline")
183       begin
184           /* engid is required. */
185           if (@engid = - 1)
186           begin
187               raiserror 18899, "engid"
188               return (1)
189           end
190   
191           dbcc engine(@option, @engid)
192       end
193       else if (@option = "q_can_offline")
194       begin
195           /* engid is required. */
196           if (@engid = - 1)
197           begin
198               raiserror 18899, "engid"
199               return (1)
200           end
201   
202           dbcc engine(@option, @engid)
203       end
204       else if (@option = "q_online")
205       begin
206           dbcc engine(@option)
207       end
208       else if (@option = "q_shutdown")
209       begin
210           /* engid is required. */
211           if (@engid = - 1)
212           begin
213               raiserror 18899, "engid"
214               return (1)
215           end
216   
217           dbcc engine(@option, @engid)
218       end
219       else
220       begin
221           /* 18899, Invalid value supplied to parameter %1!. */
222           raiserror 18899, "option"
223           return (1)
224       end
225   
226       if (@@error != 0)
227           return (1)
228       else
229           return (0)
230   


exec sp_procxmode 'sp_engine', 'AnyMode'
go

Grant Execute on sp_engine to public
go
DEFECTS
 TNOI 4 Table with no index master..monThreadPool master..monThreadPool
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 37
 MGTP 3 Grant to public master..monThreadPool  
 MGTP 3 Grant to public sybsystemprocs..sp_engine  
 MUCO 3 Useless Code Useless Brackets 57
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 79
 MUCO 3 Useless Code Useless Brackets 87
 MUCO 3 Useless Code Useless Brackets 91
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 106
 MUCO 3 Useless Code Useless Brackets 111
 MUCO 3 Useless Code Useless Brackets 115
 MUCO 3 Useless Code Useless Brackets 119
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 130
 MUCO 3 Useless Code Useless Brackets 133
 MUCO 3 Useless Code Useless Brackets 142
 MUCO 3 Useless Code Useless Brackets 145
 MUCO 3 Useless Code Useless Brackets 153
 MUCO 3 Useless Code Useless Brackets 156
 MUCO 3 Useless Code Useless Brackets 160
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 168
 MUCO 3 Useless Code Useless Brackets 171
 MUCO 3 Useless Code Useless Brackets 175
 MUCO 3 Useless Code Useless Brackets 182
 MUCO 3 Useless Code Useless Brackets 185
 MUCO 3 Useless Code Useless Brackets 188
 MUCO 3 Useless Code Useless Brackets 193
 MUCO 3 Useless Code Useless Brackets 196
 MUCO 3 Useless Code Useless Brackets 199
 MUCO 3 Useless Code Useless Brackets 204
 MUCO 3 Useless Code Useless Brackets 208
 MUCO 3 Useless Code Useless Brackets 211
 MUCO 3 Useless Code Useless Brackets 214
 MUCO 3 Useless Code Useless Brackets 223
 MUCO 3 Useless Code Useless Brackets 226
 MUCO 3 Useless Code Useless Brackets 227
 MUCO 3 Useless Code Useless Brackets 229
 QAFM 3 Var Assignment from potentially many rows 98
 QAPT 3 Access to Proxy Table master..monThreadPool 98
 QISO 3 Set isolation level 64
 VNRD 3 Variable is not read @dummy 84
 VNRD 3 Variable is not read @new_thread_count 121
 MDYS 2 Dynamic SQL Marker 124
 MTR1 2 Metrics: Comments Ratio Comments: 40% 37
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 17 = 28dec - 13exi + 2 37
 MTR3 2 Metrics: Query Complexity Complexity: 106 37

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