Database | Proc | Application | Created | Links |
sybsystemprocs | sp_cachestrategy ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** 17756, "The execution of the stored procedure '%1!' in database 4 ** '%2!' was aborted because there was an error in writing the 5 ** replication log record." 6 ** 17763, "The execution of the stored procedure '%1!' in database '%2!' was aborted because there 7 ** was an error in updating the schemacnt column in sysobjects." 8 ** 18173, "Only the System Administrator (SA) or the Object Owner 9 ** may execute this stored Procedure." 10 ** 18176, "Usage: sp_cachestrategy dbname [, [ownername.]tablename 11 ** [, indexname | `text only' | `table only' 12 ** [, prefetch | mru, `on' | `off']]]" 13 ** 18077, "Set curwrite to the level of index." 14 ** 18079, "Set curwrite to the level of the object in sysobjects." 15 ** 18076, "Could not set curwrite to object level. Set your maxwrite label correctly." 16 ** 18333, "Cache configuration failed. Your curread must dominate the sensitivity level of the object." 17 ** 19979, "Cannot run '%1!' for in-memory database '%2!'." 18 */ 19 20 /* 21 ** This procedure sets a default prefetch strategy for a given object. 22 ** For non-clustered index it only affects its index pages. 23 ** For clustered indexes it affects the data pages as well. 24 ** 25 ** All updates to sysindexes are done by forcing the index access via 26 ** 'csysindexes' so that it's clear that only one row is being updated. 27 ** (Multi-row updates to sysindexes are prohibited.) 28 */ 29 CREATE PROCEDURE sp_cachestrategy 30 @dbname varchar(255), 31 @tablename varchar(255), 32 @indexname varchar(255) = NULL, 33 @strategy varchar(30) = NULL, 34 @onoff varchar(4) = NULL 35 AS 36 37 DECLARE @stat2 int 38 DECLARE @dbid int 39 DECLARE @ownerid int 40 DECLARE @tabid int 41 DECLARE @indid smallint 42 DECLARE @entitytype char(1) 43 DECLARE @status int 44 DECLARE @updated int 45 DECLARE @indname varchar(255) 46 DECLARE @largeio varchar(8) 47 DECLARE @bufstr varchar(8) 48 49 50 51 52 IF @@trancount = 0 53 BEGIN 54 SET chained off 55 END 56 57 SET transaction isolation level 1 58 59 SELECT @stat2 = 0 60 61 IF @dbname IS NULL 62 BEGIN 63 /* 64 ** The database name is required for this stored procedure 65 ** 18176, "Usage: sp_cachestrategy dbname [, [ownername.]tablename 66 ** [, indexname | `text only' | `table only' 67 ** [, prefetch | mru, `on' | `off']]]" 68 */ 69 raiserror 18176 70 RETURN (1) 71 END 72 73 /* 74 ** Retrieve the id's required to identify the object affected 75 */ 76 SELECT @status = 0 77 SELECT @dbid = NULL 78 SELECT @ownerid = NULL 79 SELECT @tabid = NULL 80 SELECT @indid = NULL 81 SELECT @entitytype = NULL 82 EXEC @status = sp_aux_parse_entity @dbname, @tablename, @indexname, 83 @dbid OUTPUT, @ownerid OUTPUT, 84 @tabid OUTPUT, @indid OUTPUT, 85 @entitytype OUTPUT 86 87 88 IF @status = 2 89 BEGIN 90 /* 91 ** A syntax error was encountered. Raise a usage message. 92 ** 18176, "Usage: sp_cachestrategy dbname [, [ownername.]tablename 93 ** [, indexname | `text only' | `table only' 94 ** [, prefetch | mru, `on' | `off']]]" 95 */ 96 raiserror 18176 97 RETURN (1) 98 END 99 ELSE IF @status != 0 100 BEGIN 101 RETURN (@status) 102 END 103 104 /* Do not allow sp_cachestrategy to be run on in-memory database. */ 105 if exists (select * from master.dbo.sysdatabases 106 where dbid = @dbid and (status4 & 4096 = 4096)) 107 begin 108 raiserror 19979, "sp_cachestrategy", @dbname 109 return (1) 110 end 111 112 /* 113 ** Perform permission checks. Basically, only the System Administrator 114 ** can change the cache strategy for objects he/she doesn't own. 115 */ 116 IF @ownerid != user_id() 117 BEGIN 118 /* 119 ** Check if the user has the SA role. 120 */ 121 IF proc_role("sa_role") = 0 122 BEGIN 123 /* 124 ** proc_role already raised an error message but we want 125 ** to supply an addendum saying that you need to be the 126 ** owner of the object in order to perform this operation. 127 */ 128 raiserror 18173 129 RETURN (1) 130 END 131 132 END 133 134 135 /* 136 ** Fix the index id if the entity is a table. 137 */ 138 IF (@entitytype = 'T') 139 BEGIN 140 /* 141 ** Leaf level of clustered index, if it exists, 142 ** contain the data pages 143 */ 144 SELECT @indid = 1 145 146 IF NOT EXISTS (SELECT * 147 FROM sysindexes 148 WHERE id = @tabid AND indid = 1) 149 BEGIN 150 SELECT @indid = 0 151 END 152 END 153 154 /* 155 ** If @strategy is entered, but it is neither "prefetch" nor "mru" 156 ** we should report an error. If @strategy is correct entered, 157 ** and @onoff is entered, but @onoff is neither "on" nor "off" 158 ** we should report an error. However, if @onoff is not entered, 159 ** we would not report error to keep the existing behavior. 160 */ 161 IF (@strategy != NULL) 162 BEGIN 163 164 IF ((lower(@strategy) NOT IN ("prefetch", "mru")) 165 OR ((@onoff != NULL) AND (lower(@onoff) NOT IN ("on", "off")))) 166 BEGIN 167 /* 168 ** A syntax error was encountered. Raise a usage message. 169 ** 18176, "Usage: sp_cachestrategy dbname 170 ** [, [ownername.]tablename 171 ** [, indexname | `text only' | `table only' 172 ** [, prefetch | mru, `on' | `off']]]" 173 */ 174 raiserror 18176 175 RETURN (1) 176 END 177 END 178 179 /* 180 ** Initialize some variables. 181 */ 182 SELECT @stat2 = status2, @indname = name 183 FROM sysindexes 184 WHERE id = @tabid AND indid = @indid 185 186 SELECT @updated = 0 187 188 /* 189 ** Check if invoking the precedure in informational mode 190 ** In this case the user may not specify any strategy. 191 */ 192 IF (@onoff = NULL) 193 BEGIN 194 /* Print the SHOWPLAN messages here as well */ 195 IF (@stat2 & 16 = 16) 196 SELECT @largeio = "OFF" 197 ELSE 198 SELECT @largeio = "ON" 199 200 IF (@stat2 & 32 = 32) 201 SELECT @bufstr = "OFF" 202 ELSE 203 SELECT @bufstr = "ON" 204 205 SELECT "object name" = user_name(@ownerid) + "." + 206 object_name(@tabid), 207 "index name" = @indname, 208 "large IO" = @largeio, 209 "MRU" = @bufstr 210 211 RETURN (0) 212 END 213 214 215 216 begin tran updatesysind 217 218 /* 219 ** Turn the sysindexes.status2 field for IND2_NOPREFETCH 220 */ 221 IF lower(@strategy) = "prefetch" 222 BEGIN 223 IF lower(@onoff) = "off" 224 BEGIN 225 IF (@stat2 & 16) = 0 226 BEGIN 227 UPDATE sysindexes 228 SET status2 = status2 | 16 229 WHERE id = @tabid AND indid = @indid 230 plan "(update (i_scan csysindexes sysindexes))" 231 232 /* 233 ** We updated a sysindexes row. We also need to update 234 ** the in-core structure representing this sysindexes 235 ** row as the sysindexes rows cache is not a write thru 236 ** cache. 237 */ 238 dbcc refreshides(@dbid, @tabid, @indid, "cachestrategy") 239 240 /* 241 ** In case of error, dbcc refreshides will 242 ** print the error message. 243 */ 244 if @@error != 0 245 begin 246 rollback tran updatesysind 247 RETURN (1) 248 end 249 250 SELECT @updated = 1 251 END 252 END 253 ELSE 254 BEGIN 255 IF (@stat2 & 16) != 0 256 BEGIN 257 UPDATE sysindexes 258 SET status2 = status2 & - 17 259 WHERE id = @tabid AND indid = @indid 260 plan "(update (i_scan csysindexes sysindexes))" 261 262 /* 263 ** We updated a sysindexes row. We also need to update 264 ** the in-core structure representing this sysindexes 265 ** row as the sysindexes rows cache is not a write thru 266 ** cache. 267 */ 268 dbcc refreshides(@dbid, @tabid, @indid, "cachestrategy") 269 270 /* 271 ** In case of error, dbcc refreshides will 272 ** print the error message. 273 */ 274 if @@error != 0 275 begin 276 rollback tran updatesysind 277 RETURN (1) 278 end 279 280 SELECT @updated = 1 281 END 282 END 283 END 284 ELSE /* Already checked for all legal values, must be MRU now */ 285 BEGIN 286 IF lower(@onoff) = "off" 287 BEGIN 288 IF (@stat2 & 32) = 0 289 BEGIN 290 UPDATE sysindexes 291 SET status2 = status2 | 32 292 WHERE id = @tabid AND indid = @indid 293 plan "(update (i_scan csysindexes sysindexes))" 294 295 /* 296 ** We updated a sysindexes row. We also need to update 297 ** the in-core structure representing this sysindexes 298 ** row as the sysindexes rows cache is not a write thru 299 ** cache. 300 */ 301 dbcc refreshides(@dbid, @tabid, @indid, "cachestrategy") 302 303 /* 304 ** In case of error, dbcc refreshides will 305 ** print the error message. 306 */ 307 if @@error != 0 308 begin 309 rollback tran updatesysind 310 RETURN (1) 311 end 312 313 SELECT @updated = 1 314 END 315 END 316 ELSE 317 BEGIN 318 IF (@stat2 & 32) != 0 319 BEGIN 320 UPDATE sysindexes 321 SET status2 = status2 & - 33 322 WHERE id = @tabid AND indid = @indid 323 plan "(update (i_scan csysindexes sysindexes))" 324 325 /* 326 ** We updated a sysindexes row. We also need to update 327 ** the in-core structure representing this sysindexes 328 ** row as the sysindexes rows cache is not a write thru 329 ** cache. 330 */ 331 dbcc refreshides(@dbid, @tabid, @indid, "cachestrategy") 332 333 /* 334 ** In case of error, dbcc refreshides will 335 ** print the error message. 336 */ 337 if @@error != 0 338 begin 339 rollback tran updatesysind 340 RETURN (1) 341 end 342 343 SELECT @updated = 1 344 END 345 END 346 END 347 348 commit tran updatesysind 349 350 /* 351 ** We need to increment the object's schema count if we changed 352 ** its cache strategy. 353 */ 354 IF @updated = 1 355 BEGIN 356 /* 357 ** This transaction also writes a log record for replicating the 358 ** invocation of this procedure. If logexec() fails, the transaction 359 ** is aborted. 360 ** 361 ** IMPORTANT: The name rs_logexec is significant and is used by 362 ** Replication Server. 363 */ 364 BEGIN TRANSACTION rs_logexec 365 366 IF (schema_inc(object_id(@tablename), 0) != 1) 367 BEGIN 368 /* 369 ** 17763, "The execution of the stored procedure '%1!' 370 ** in database '%2!' was aborted because there 371 ** was an error in updating the column 372 ** schemacnt in sysobjects." 373 */ 374 SELECT @dbname = db_name() 375 RAISERROR 17763, "sp_cachestrategy", @dbname 376 ROLLBACK TRANSACTION rs_logexec 377 RETURN (1) 378 END 379 380 IF (logexec() != 1) 381 BEGIN 382 /* 383 ** 17756, "The execution of the stored procedure '%1!' 384 ** in database '%2!' was aborted because there 385 ** was an error in writing the replication log 386 ** record." 387 */ 388 SELECT @dbname = db_name() 389 RAISERROR 17756, "sp_bindefault", @dbname 390 391 ROLLBACK TRANSACTION rs_logexec 392 RETURN (1) 393 END 394 COMMIT TRANSACTION 395 END 396 397 RETURN (0) 398
exec sp_procxmode 'sp_cachestrategy', 'AnyMode' go Grant Execute on sp_cachestrategy to public go
RESULT SETS | |
sp_cachestrategy_rset_001 |
DEPENDENCIES |
PROCS AND TABLES USED calls proc sybsystemprocs..sp_aux_parse_entity ![]() reads table sybsystemprocs..sysindexes ![]() reads table sybsystemprocs..sysobjects ![]() reads table master..sysdatabases (1) ![]() read_writes table sybsystemprocs..sysindexes ![]() |