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


exec sp_procxmode 'sp_cachestrategy', 'AnyMode'
go

Grant Execute on sp_cachestrategy to public
go
RESULT SETS
sp_cachestrategy_rset_001

DEFECTS
 MCTR 4 Conditional Begin Tran or Commit Tran 380
 MCTR 4 Conditional Begin Tran or Commit Tran 410
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 108
 QTYP 4 Comparison type mismatch smallint = int 108
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 164
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 164
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 200
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 245
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 275
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 308
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 338
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public sybsystemprocs..sp_cachestrategy  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MNAC 3 Not using ANSI 'is null' 177
 MNAC 3 Not using ANSI 'is null' 181
 MNAC 3 Not using ANSI 'is null' 208
 MNER 3 No Error Check should check @@error after update 243
 MNER 3 No Error Check should check @@error after update 273
 MNER 3 No Error Check should check @@error after update 306
 MNER 3 No Error Check should check @@error after update 336
 MUCO 3 Useless Code Useless Brackets 72
 MUCO 3 Useless Code Useless Brackets 99
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 111
 MUCO 3 Useless Code Useless Brackets 130
 MUCO 3 Useless Code Useless Brackets 139
 MUCO 3 Useless Code Useless Brackets 145
 MUCO 3 Useless Code Useless Brackets 146
 MUCO 3 Useless Code Useless Brackets 154
 MUCO 3 Useless Code Useless Brackets 177
 MUCO 3 Useless Code Useless Brackets 180
 MUCO 3 Useless Code Useless Brackets 191
 MUCO 3 Useless Code Useless Brackets 208
 MUCO 3 Useless Code Useless Brackets 211
 MUCO 3 Useless Code Useless Brackets 216
 MUCO 3 Useless Code Useless Brackets 227
 MUCO 3 Useless Code Useless Brackets 263
 MUCO 3 Useless Code Useless Brackets 293
 MUCO 3 Useless Code Useless Brackets 326
 MUCO 3 Useless Code Useless Brackets 356
 MUCO 3 Useless Code Useless Brackets 382
 MUCO 3 Useless Code Useless Brackets 393
 MUCO 3 Useless Code Useless Brackets 396
 MUCO 3 Useless Code Useless Brackets 408
 MUCO 3 Useless Code Useless Brackets 413
 QCRS 3 Conditional Result Set 221
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 243
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 273
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 306
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 336
 QISO 3 Set isolation level 59
 VNRD 3 Variable is not read @dummy 144
 MRST 2 Result Set Marker 221
 MSUB 2 Subquery Marker 107
 MSUB 2 Subquery Marker 162
 MTR1 2 Metrics: Comments Ratio Comments: 51% 29
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 24 = 37dec - 15exi + 2 29
 MTR3 2 Metrics: Query Complexity Complexity: 167 29

DATA PROPAGATION detailed
ColumnWritten To
@dbnamesp_cachestrategy_rset_001.object name
@tablenamesp_cachestrategy_rset_001.object name

DEPENDENCIES
PROCS AND TABLES USED
writes table sybsystemprocs..sp_cachestrategy_rset_001 
calls proc sybsystemprocs..sp_aux_parse_entity  
   reads table sybsystemprocs..sysindexes  
   reads table sybsystemprocs..sysobjects  
read_writes table sybsystemprocs..sysindexes  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
reads table master..sysdatabases (1)