Database | Proc | Application | Created | Links |
sybsystemprocs | sp_engine | 31 Aug 14 | Defects 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 procedure sp_engine 38 @option varchar(30), 39 @engid int = - 1 40 as 41 42 declare @old_thread_count int 43 declare @new_thread_count int 44 45 if @@trancount > 0 46 begin 47 /* 17260, "Can't run %1! from within a transaction." */ 48 raiserror 17260, "sp_engine" 49 return (1) 50 end 51 else 52 begin 53 set chained off 54 end 55 56 set transaction isolation level 1 57 set nocount on 58 59 /* Must have sa_role as this stored procedure is related to config */ 60 if (proc_role("sa_role") < 1) 61 return (1) 62 63 if (@@kernelmode != 'process') 64 begin 65 /* 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." */ 66 67 raiserror 18366, "sp_engine", "alter thread pool syb_default_pool with thread count = new_eng_count" 68 69 /* Get the current value for number of threads for syb_default_pool */ 70 select @old_thread_count = Size from master.dbo.monThreadPool 71 where ThreadPoolName = "syb_default_pool" 72 73 if (@option = "can_offline") 74 begin 75 if (@engid >= @old_thread_count OR @old_thread_count < 2) 76 begin 77 raiserror 18899, "engid" 78 return (1) 79 end 80 else 81 begin 82 raiserror 19461 83 return (0) 84 end 85 end 86 87 if (@option = "online") 88 begin 89 select @new_thread_count = @old_thread_count + 1 90 end 91 else if (@option = "offline" OR @option = "shutdown") 92 begin 93 select @new_thread_count = @old_thread_count - 1 94 end 95 96 execute ("alter thread pool syb_default_pool with thread count 97 = @new_thread_count") 98 99 return (0) 100 end 101 102 if (@option = "offline") 103 begin 104 /* Offline an engine */ 105 if (@engid = - 1) 106 begin 107 dbcc engine(@option) 108 end 109 else 110 begin 111 dbcc engine(@option, @engid) 112 end 113 end 114 else if (@option = "can_offline") 115 begin 116 /* Can the engine be offlined? */ 117 if (@engid = - 1) 118 begin 119 dbcc engine(@option) 120 end 121 else 122 begin 123 dbcc engine(@option, @engid) 124 end 125 if (@@error = 0) 126 begin 127 raiserror 19461 128 return (0) 129 end 130 else 131 begin 132 return (1) 133 end 134 end 135 else if (@option = "online") 136 begin 137 /* Online an engine */ 138 dbcc engine(@option) 139 end 140 else if (@option = "shutdown") 141 begin 142 /* Can the engine be shutdown? */ 143 if (@engid = - 1) 144 begin 145 /* Shutdown always expects engid to be supplied */ 146 raiserror 18899, "engid" 147 return (1) 148 end 149 else 150 begin 151 dbcc engine(@option, @engid) 152 end 153 end 154 else if (@option = "q_offline") 155 begin 156 /* engid is required. */ 157 if (@engid = - 1) 158 begin 159 raiserror 18899, "engid" 160 return (1) 161 end 162 163 dbcc engine(@option, @engid) 164 end 165 else if (@option = "q_can_offline") 166 begin 167 /* engid is required. */ 168 if (@engid = - 1) 169 begin 170 raiserror 18899, "engid" 171 return (1) 172 end 173 174 dbcc engine(@option, @engid) 175 end 176 else if (@option = "q_online") 177 begin 178 dbcc engine(@option) 179 end 180 else if (@option = "q_shutdown") 181 begin 182 /* engid is required. */ 183 if (@engid = - 1) 184 begin 185 raiserror 18899, "engid" 186 return (1) 187 end 188 189 dbcc engine(@option, @engid) 190 end 191 else 192 begin 193 /* 18899, Invalid value supplied to parameter %1!. */ 194 raiserror 18899, "option" 195 return (1) 196 end 197 198 if (@@error != 0) 199 return (1) 200 else 201 return (0) 202
exec sp_procxmode 'sp_engine', 'AnyMode' go Grant Execute on sp_engine to public go
DEPENDENCIES |
PROCS AND TABLES USED reads table master..monThreadPool (1) |