DatabaseProcApplicationCreatedLinks
sybsystemprocssp_modifythreshold  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_modifythreshold"            17870
6     **
7     ** 17870, "Table '%1!' does not exist in database '%2!' -- cannot add
8     **	  thresholds."
9     ** 17871, "There is no segment named '%1!'."
10    ** 17872, "This threshold is too close to one or more existing thresholds.
11    **	   Thresholds must be no closer than 128 pages to each other."
12    ** 17874, "A threshold at %1! pages is logically impossible for
13    **	   segment '%2!'.  Choose a value between %3! and %4! pages."
14    ** 17875, "Only the DBO of database '%1!' or a user with System Administrator
15    **	   (SA) authorization may add, delete, or modify thresholds in that
16    **	   database."
17    ** 17904, "Segment '%1!' does not have a threshold at '%2!' pages."
18    ** 18031, "This procedure can only affect thresholds in the current database.
19    **	   Say 'USE %1!', then run this procedure again."
20    ** 18032, "You may not alter the free space or segment name of the
21    **	   log's last-chance threshold."
22    ** 17289, "Set your curwrite to the hurdle of current database."
23    ** 18084, "A threshold at %1! pages for segment '%2!' is too close to the 
24    **	   maximum size of the database. Because of this, the threshold will 
25    **	   not fire more than once per SQL server re-boot. Modify the value 
26    **	   using sp_modifythreshold to be between %3! and %4! pages."
27    */
28    
29    create or replace procedure sp_modifythreshold
30        @dbname varchar(255), /* this database	*/
31        @segname varchar(255), /* segment name		*/
32        @free_space bigint, /* threshold level	*/
33        @new_proc_name varchar(255) = NULL, /* threshold procedure	*/
34        @new_free_space bigint = NULL, /* new threshold level	*/
35        @new_segname varchar(255) = NULL /* new segment name	*/
36    as
37    
38        declare @segno int,
39            @segmap int,
40            @status int,
41            @status1 int,
42            @newsegno int,
43            @pagect bigint,
44            @proxim bigint,
45            @maxthpgct bigint,
46            @suid int,
47            @roles varbinary(255),
48            @proc varchar(255),
49            @msg varchar(1024),
50            @dummy int,
51            @nullarg char(1),
52            @gp_enabled int,
53            @fs_new unsigned int
54    
55        /*
56        ** If we are under a user defined xact, disallow this since we may 
57        ** leave the dbtable's threshold cache out-of-sync if the end user 
58        ** rollbacks its xact.
59        */
60        if @@trancount > 0
61        begin
62            /*
63            ** 17260, "Can't run %1! from within a transaction."
64            */
65            raiserror 17260, "sp_modifythreshold"
66            return (1)
67    
68        end
69    
70        set transaction isolation level 1
71        set chained off
72    
73        /*
74        ** Make sure we are in the right database
75        */
76        if (@dbname != db_name())
77        begin
78            /*
79            ** 18031, "You are in the wrong database.  Say 'USE %1!', then run
80            **	   this procedure again."
81            */
82            raiserror 18031, @dbname
83            return (1)
84    
85        end
86    
87        /*
88        ** If granular permissions is not enabled make sure the user (is the DBO) 
89        ** or (has "sa_role").  If granular permissions is enabled make certain the 
90        ** user has 'manage database' permission.
91        */
92        select @nullarg = NULL
93        execute @status = sp_aux_checkroleperm "dbo",
94            "manage database", @dbname, @gp_enabled output
95    
96        if (@status != 0)
97        begin
98            /*
99            ** Send apropriate audit records.
100           */
101           if (@gp_enabled = 0)
102           begin
103               execute @status1 = sp_aux_checkroleperm "sa_role",
104                   @nullarg, @nullarg, @gp_enabled output
105               if (@status1 != 0)
106               begin
107                   /* To Audit. */
108                   select @dummy = proc_role("sa_role")
109                   /*
110                   ** 17875, "Only the DBO of database %1! or a 
111                   ** user with System Administrator (SA) 
112                   ** authorization may add, delete, or modify 
113                   ** thresholds in that database.
114                   */
115                   raiserror 17875, @dbname
116                   return (1)
117               end
118           end
119           else
120           begin
121               select @dummy = proc_auditperm("manage database",
122                       @status, @dbname)
123               return (1)
124           end
125       end
126   
127       /* For Auditing */
128       if (@gp_enabled = 0)
129       begin
130           if (@status1 = 0)
131           begin
132               if (proc_role("sa_role") = 0)
133                   return 1
134           end
135       end
136       else
137       begin
138           select @dummy = proc_auditperm("manage database", @status,
139                   @dbname)
140       end
141   
142       /*
143       ** Make sure the segment name is valid.
144       */
145       select @segno = segment from syssegments where name = @segname
146       if @segno is NULL
147       begin
148           /*
149           ** 17871, "There is no segment named '%1!'."
150           */
151           raiserror 17871, @segname
152           return (2)
153       end
154   
155       /*
156       ** Make sure the threshold exists
157       */
158       if (@segno < 31)
159           select @segmap = power(2, @segno)
160       else
161           select @segmap = low
162           from master.dbo.spt_values
163           where type = "E"
164               and number = 2
165       select @pagect = sum(size) from master..sysusages
166       where dbid = db_id()
167           and segmap & @segmap = @segmap
168       if (@free_space < 0) or (@free_space > @pagect)
169           select @proc = null
170       else
171       begin
172           select @proc = proc_name, @status = status
173           from systhresholds
174           where segment = @segno
175               and free_space = @free_space
176       end
177       if (@proc is null)
178       begin
179           /*
180           ** 17904, "Segment '%1!' does not have a threshold at '%2!' pages."
181           */
182           raiserror 17904, @segname, @free_space
183           select segment_name = @segname, free_space
184           from systhresholds
185           where segment = @segno
186   
187           return (1)
188       end
189   
190       /*
191       ** Establish values for new threshold items
192       */
193       if (@new_free_space is NULL)
194           select @new_free_space = @free_space
195       if (@new_segname is NULL)
196           select @new_segname = @segname
197       if (@new_proc_name is NULL)
198           select @new_proc_name = @proc
199       select @suid = suser_id()
200       select @roles = current_roles()
201   
202       /*
203       ** May not alter free space and segment name on the logsegment
204       ** last-chance threshold
205       */
206       if ((@segno = 2) and
207               ((@status & 1) = 1) and
208               ((@new_free_space != @free_space) or
209                   (@new_segname != @segname)))
210       begin
211           /*
212           ** 18032, "You may not alter the free space or segment name of the
213           **	   log's last-chance threshold."
214           */
215           raiserror 18032
216           return (1)
217       end
218   
219       /* If the user provided a new segment name ... */
220       if @new_segname != @segname
221       begin
222           /* ... then make sure that it is a valid one ... */
223           select @newsegno = segment from syssegments where name = @new_segname
224           if @newsegno is NULL
225           begin
226               /*
227               ** 17871, "There is no segment named '%1!'."
228               */
229               raiserror 17871, @new_segname
230               return (2)
231           end
232       end
233       else
234       begin
235           /* 
236           ** ... otherwise, do the threshold freespace size 
237           ** validation tests using the original segment.
238           */
239           select @newsegno = @segno
240       end
241   
242       /*
243       ** Make sure the threshold fits within the segment
244       */
245       if (@newsegno < 31)
246           select @segmap = power(2, @newsegno)
247       else
248           select @segmap = low
249           from master.dbo.spt_values
250           where type = "E"
251               and number = 2
252   
253       select @pagect = sum(size) from master..sysusages
254       where dbid = db_id()
255           and segmap & @segmap = @segmap
256       if (@new_free_space < 0) or (@new_free_space > @pagect)
257       begin
258           /*
259           ** 17874, "A threshold at %1! pages is logically impossible for
260           ** segment '%2!'.  Choose a value between %3! and %4! pages."
261           */
262           raiserror 17874, @new_free_space, @new_segname, 0, @pagect
263           return (1)
264       end
265   
266       /*
267       ** Make sure there is no existing threshold too close to this new one
268       */
269       select @proxim = (2 * @@thresh_hysteresis) - 1
270       if exists (select free_space from systhresholds
271               where segment = @newsegno
272                   and free_space between (@new_free_space - @proxim)
273                   and (@new_free_space + @proxim)
274                   and free_space != @free_space)
275       begin
276           /*
277           ** 17872, "This threshold is too close to one or more existing
278           **	  thresholds.  Thresholds must be no closer than 128 pages
279           **	  to each other."
280           */
281           raiserror 17872
282           select segment_name = @new_segname, free_space
283           from systhresholds
284           where segment = @newsegno
285               and free_space between (@new_free_space - @proxim)
286               and (@new_free_space + @proxim)
287               and free_space != @free_space
288   
289           return (1)
290       end
291   
292       /*
293       ** Make sure that the threshold is not too close to the maximum size of
294       ** the database segment. If threshold value + @@thresh_hysteresis greater
295       ** than maximum size of the database segment then threshold will fire only
296       ** once.  Signal this condition to the user.
297       */
298       select @maxthpgct = @pagect - @@thresh_hysteresis - 1
299       if (@pagect <= (@new_free_space + @@thresh_hysteresis))
300       begin
301           /*
302           ** 18084, "A threshold at %1! pages for segment '%2!' is too
303           ** close to the maximum size of the database. Because of this,
304           ** the threshold will not fire more than once per SQL server re-boot.
305           ** Modify the value using sp_modifythreshold to be
306           ** between %3! and %4! pages."
307           */
308           exec sp_getmessage 18084, @msg output
309           print @msg, @new_free_space, @new_segname, 0, @maxthpgct
310   
311       /* This is not an error, so raise a message and continue.  */
312       end
313   
314       /*
315       ** We do not validate the procedure name, since it might be in a different
316       ** database or even in a remote server.  Thus, we have done all the tests
317       ** we can.  Modify the threshold.
318       */
319   
320       /*
321       ** 17906, "Dropping threshold for segment '%1!' at '%2!' pages."
322       */
323       exec sp_getmessage 17906, @msg output
324       print @msg, @segname, @free_space
325   
326   
327       begin transaction modify_threshold
328   
329       delete systhresholds
330       where segment = @segno
331           and free_space = @free_space
332   
333       if (@@error != 0)
334       begin
335           rollback transaction
336           /*
337           ** 17907, "Delete of systhresholds row failed."
338           */
339           raiserror 17907
340           return (1)
341       end
342   
343       /*
344       ** 17873, "Adding threshold for segment '%1!' at '%2!' pages."
345       */
346       exec sp_getmessage 17873, @msg output
347       print @msg, @new_segname, @new_free_space
348   
349   
350       select @fs_new = @new_free_space
351       insert systhresholds values (@newsegno, @fs_new, @status,
352           @new_proc_name, @suid, @roles)
353       if (@@error != 0)
354       begin
355           rollback transaction
356           /*
357           ** 17877, "Insert of systhresholds row failed."
358           */
359           raiserror 17877
360           return (1)
361       end
362   
363   
364       /*
365       ** Last, rebuild the database threshold table
366       */
367       dbcc dbrepair(@dbname, "newthreshold", @segname, 0, @new_segname)
368   
369       if (@@error != 0)
370       begin
371           rollback transaction
372           /*
373           ** 17878, "Rebuild of the database threshold table failed."
374           */
375           raiserror 17878
376           return (1)
377       end
378   
379       commit transaction
380   
381       return (0)
382   


exec sp_procxmode 'sp_modifythreshold', 'AnyMode'
go

Grant Execute on sp_modifythreshold to public
go
RESULT SETS
sp_modifythreshold_rset_002
sp_modifythreshold_rset_001

DEFECTS
 MTYP 4 Assignment type mismatch free_space: uint = int 351
 MTYP 4 Assignment type mismatch segment: smallint = int 351
 MTYP 4 Assignment type mismatch status: smallint = int 351
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 166
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 174
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 185
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 254
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 271
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 284
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 330
 TNOI 4 Table with no index sybsystemprocs..syssegments sybsystemprocs..syssegments
 TNOI 4 Table with no index sybsystemprocs..systhresholds sybsystemprocs..systhresholds
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysusages  
 MGTP 3 Grant to public sybsystemprocs..sp_modifythreshold  
 MGTP 3 Grant to public sybsystemprocs..syssegments  
 MGTP 3 Grant to public sybsystemprocs..systhresholds  
 MNER 3 No Error Check should check return value of exec 308
 MNER 3 No Error Check should check return value of exec 323
 MNER 3 No Error Check should check return value of exec 346
 MUCO 3 Useless Code Useless Brackets 66
 MUCO 3 Useless Code Useless Brackets 76
 MUCO 3 Useless Code Useless Brackets 83
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 105
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 128
 MUCO 3 Useless Code Useless Brackets 130
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 152
 MUCO 3 Useless Code Useless Brackets 158
 MUCO 3 Useless Code Useless Brackets 177
 MUCO 3 Useless Code Useless Brackets 187
 MUCO 3 Useless Code Useless Brackets 193
 MUCO 3 Useless Code Useless Brackets 195
 MUCO 3 Useless Code Useless Brackets 197
 MUCO 3 Useless Code Useless Brackets 206
 MUCO 3 Useless Code Useless Brackets 216
 MUCO 3 Useless Code Useless Brackets 230
 MUCO 3 Useless Code Useless Brackets 245
 MUCO 3 Useless Code Useless Brackets 263
 MUCO 3 Useless Code Useless Brackets 269
 MUCO 3 Useless Code Useless Brackets 289
 MUCO 3 Useless Code Useless Brackets 299
 MUCO 3 Useless Code Useless Brackets 333
 MUCO 3 Useless Code Useless Brackets 340
 MUCO 3 Useless Code Useless Brackets 353
 MUCO 3 Useless Code Useless Brackets 360
 MUCO 3 Useless Code Useless Brackets 369
 MUCO 3 Useless Code Useless Brackets 376
 MUCO 3 Useless Code Useless Brackets 381
 QAFM 3 Var Assignment from potentially many rows 145
 QAFM 3 Var Assignment from potentially many rows 161
 QAFM 3 Var Assignment from potentially many rows 172
 QAFM 3 Var Assignment from potentially many rows 223
 QAFM 3 Var Assignment from potentially many rows 248
 QCRS 3 Conditional Result Set 183
 QCRS 3 Conditional Result Set 282
 QISO 3 Set isolation level 70
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
166
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
254
 VNRD 3 Variable is not read @dummy 138
 MRST 2 Result Set Marker 183
 MRST 2 Result Set Marker 282
 MSUB 2 Subquery Marker 270
 MTR1 2 Metrics: Comments Ratio Comments: 48% 29
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 26 = 39dec - 15exi + 2 29
 MTR3 2 Metrics: Query Complexity Complexity: 160 29

DATA PROPAGATION detailed
ColumnWritten To
@free_spacesysthresholds.free_space   sp_dropalias_rset_001.type sp_dropthreshold_rset_001.free_space sp_dropuser_rset_001.type sp_modifythreshold_rset_001.free_space sp_modifythreshold_rset_002.free_space
@new_free_spacesysthresholds.free_space   sp_dropalias_rset_001.type sp_dropthreshold_rset_001.free_space sp_dropuser_rset_001.type sp_modifythreshold_rset_001.free_space sp_modifythreshold_rset_002.free_space
@new_proc_namesysthresholds.proc_name  
@new_segnamesp_modifythreshold_rset_001.segment_name
@segnamesp_modifythreshold_rset_001.segment_name sp_modifythreshold_rset_002.segment_name

DEPENDENCIES
PROCS AND TABLES USED
writes table sybsystemprocs..sp_modifythreshold_rset_002 
read_writes table sybsystemprocs..systhresholds  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
reads table master..sysusages (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
reads table sybsystemprocs..syssegments  
reads table master..spt_values (1)  
writes table sybsystemprocs..sp_modifythreshold_rset_001