DatabaseProcApplicationCreatedLinks
sybsystemprocssp_addthreshold  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_addthreshold"               17870
6     **
7     ** 17240, "'%1!' is not a valid name."
8     ** 17870, "Table '%1!' does not exist in database '%2!' -- cannot add
9     **	  thresholds."
10    ** 17871, "There is no segment named '%1!'."
11    ** 17872, "This threshold is too close to one or more existing thresholds. 
12    ** 		Thresholds must be no closer than 128 pages to each other."
13    ** 17873, "Adding threshold for segment '%1!' at '%2!' pages."
14    ** 17874, "A threshold at %1! pages is logically impossible for segment '%2!'.
15    **		Choose a value between %3! and %4! pages."
16    ** 17875, "Only the DBO of database '%1!' or a user with System Administrator
17    **	   (SA) authorization may add, delete, or modify thresholds in that
18    **	   database."
19    ** 17289, "Set your curwrite to the hurdle of current database."
20    ** 18084, "A threshold at %1! pages for segment '%2!' is too close to the 
21    **	   maximum size of the database. Because of this, the threshold will 
22    **	   not fire more than once per SQL server re-boot. Modify the value 
23    **	   using sp_modifythreshold to be between %3! and %4! pages."
24    ** 17876, "Warning: The specified threshold level is greater than the 
25    **         existing number of free pages on this segment."
26    */
27    
28    create or replace procedure sp_addthreshold
29        @dbname varchar(255), /* current database name */
30        @segname varchar(255), /* segment name		*/
31        @free_space bigint, /* threshold level	*/
32        @proc_name varchar(255) /* threshold procedure	*/
33    as
34    
35        declare @segno int,
36            @segmap int,
37            @pagect unsigned int,
38            @curr_free_space unsigned int,
39            @hyst unsigned int,
40            @maxthpgct unsigned int,
41            @msg varchar(1024),
42            @maxseg int,
43            @retval int,
44            @fs unsigned int,
45            @fs_low unsigned int,
46            @dummy int,
47            @status int,
48            @status1 int,
49            @nullarg char(1),
50            @gp_enabled int
51    
52    
53        /*
54        ** If we are under a user defined xact, disallow this since we may
55        ** leave the dbtable's threshold cache out-of-sync if the end user
56        ** rollbacks its xact.
57        */
58        if @@trancount > 0
59        begin
60            /*
61            ** 17260, "Can't run %1! from within a transaction."
62            */
63            raiserror 17260, "sp_addthreshold"
64            return (1)
65        end
66    
67        set transaction isolation level 1
68        set chained off
69    
70        /*
71        ** Make sure we are in the right database
72        */
73        if (@dbname != db_name())
74        begin
75            /*
76            ** 18031, "You are in the wrong database.  Say 'USE %1!', then run
77            **         this procedure again."
78            */
79            raiserror 18031, @dbname
80            return (5)
81        end
82    
83        /*
84        ** If granular permissions is not enabled make sure the user (is the DBO) 
85        ** or (has "sa_role").  If granular permissions is enabled make certain the 
86        ** user has 'manage database' permission.
87        */
88    
89        select @nullarg = NULL
90        execute @status = sp_aux_checkroleperm "dbo",
91            "manage database", @dbname, @gp_enabled output
92    
93        if (@status != 0)
94        begin
95            /*
96            ** Send apropriate audit records.
97            */
98            if (@gp_enabled = 0)
99            begin
100               execute @status1 = sp_aux_checkroleperm "sa_role",
101                   @nullarg, @nullarg, @gp_enabled output
102               if (@status1 != 0)
103               begin
104                   /* To Audit. */
105                   select @dummy = proc_role("sa_role")
106                   /*
107                   ** 17875, "Only the DBO of database %1! or a 
108                   ** user with System Administrator (SA) 
109                   ** authorization may add, delete, or modify 
110                   ** thresholds in that database.
111                   */
112                   raiserror 17875, @dbname
113                   return (1)
114               end
115           end
116           else
117           begin
118               select @dummy = proc_auditperm("manage database",
119                       @status, @dbname)
120               return (1)
121           end
122       end
123   
124       /* For Auditing */
125       if (@gp_enabled = 0)
126       begin
127           if (@status1 = 0)
128           begin
129               if (proc_role("sa_role") = 0)
130                   return 1
131           end
132       end
133       else
134       begin
135           select @dummy = proc_auditperm("manage database", @status,
136                   @dbname)
137       end
138   
139       /*
140       ** Make sure the segment name is valid.
141       */
142       select @segno = segment from syssegments where name = @segname
143       if @segno is NULL
144       begin
145           /*
146           ** 17871, "There is no segment named '%1!'."
147           */
148           raiserror 17871, @segname
149           return (2)
150       end
151   
152       /*
153       ** Make sure the threshold fits within the segment
154       */
155       select @maxseg = low
156       from master.dbo.spt_values
157       where type = "E"
158           and number = 2
159   
160       if (@segno < 31)
161           select @segmap = power(2, @segno)
162       else
163           select @segmap = @maxseg
164   
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       begin
170           /*
171           ** 17874, "A threshold at %1! pages is logically impossible for
172           ** segment '%2!'.  Choose a value between %3! and %4! pages."
173           */
174           raiserror 17874, @free_space, @segname, 0, @pagect
175           return (4)
176       end
177   
178       /*
179       ** Make sure there is no existing threshold too close to this new one
180       */
181       select @hyst = (2 * @@thresh_hysteresis) - 1,
182           @fs = @free_space
183       if (@fs <= @hyst)
184           select @fs_low = 0
185       else
186           select @fs_low = (@fs - @hyst)
187       if exists (select free_space from systhresholds
188               where segment = @segno
189                   and free_space between @fs_low and
190                   (@fs + @hyst))
191       begin
192           /*
193           ** 17872, "This threshold is too close to one or more existing
194           **	  thresholds.  Thresholds must be no closer than 128 pages
195           **	  to each other."
196           */
197           raiserror 17872
198   
199           select @segname as 'segment_name', free_space into #systhresholds1
200           from systhresholds
201           where segment = @segno
202               and free_space between (@free_space - @hyst) and
203               (@free_space + @hyst)
204           order by free_space
205   
206           exec @retval = sp_autoformat #systhresholds1
207           drop table #systhresholds1
208           if (@retval != 0)
209               return 1
210   
211           return (3)
212       end
213   
214       /*
215       ** Make sure that the threshold is not too close to the maximum size of
216       ** the database segment. If threshold value + @@thresh_hysteresis greater
217       ** than maximum size of the database segment then the threshold will fire
218       ** only once.
219       */
220       select @maxthpgct = @pagect - @@thresh_hysteresis - 1
221       if (@pagect <= (@free_space + @@thresh_hysteresis))
222       begin
223           /*
224           ** 18084, "A threshold at %1! pages for segment '%2!' is too 
225           ** close to the maximum size of the database. Because of this,
226           ** the threshold will not fire more than once per SQL server 
227           ** re-boot. Modify the value using sp_modifythreshold to be 
228           ** between %3! and %4! pages."
229           */
230           exec sp_getmessage 18084, @msg output
231           print @msg, @free_space, @segname, 0, @maxthpgct
232   
233       /* This is not an error, so raise a message and continue.  */
234   
235       end
236   
237       /*
238       ** We do not validate the procedure name, since it might be in a different
239       ** database or even in a remote server.  Thus, we have done all the tests
240       ** we can.  Add the threshold.
241       */
242   
243       /*
244       ** 17873, "Adding threshold for segment '%1!' at '%2!' pages."
245       */
246       exec sp_getmessage 17873, @msg output
247       print @msg, @segname, @free_space
248   
249       begin transaction insert_threshold
250   
251       insert systhresholds values (@segno, @free_space, 0, @proc_name,
252           suser_id(), current_roles())
253   
254       if @@error != 0
255       begin
256           rollback transaction
257           /*
258           ** 17877, "Insert of systhresholds row failed. "
259           */
260           raiserror 17877
261           return (1)
262       end
263   
264       /*
265       ** Last, rebuild the database threshold table
266       */
267       dbcc dbrepair(@dbname, "newthreshold", @segname)
268   
269       if @@error != 0
270       begin
271           rollback transaction
272           /*
273           ** 17878, "Rebuild of the database threshold table failed.
274           */
275           raiserror 17878
276           return (1)
277       end
278       /*
279       ** If the specified threshold level(@free_space) is greater than the
280       ** the existing number of free pages on the segment, the user should 
281       ** be warned about this
282       */
283   
284       if (@segmap = 4)
285           select @curr_free_space = lct_admin("logsegment_freepages", db_id())
286       else
287           select @curr_free_space = sum(curunreservedpgs(dbid, lstart, unreservedpgs))
288           from master.dbo.sysusages u, syssegments s
289           where u.dbid = db_id()
290               and (u.segmap & case when s.segment < 31
291                   then power(2, s.segment)
292                   else @maxseg
293               end) != 0
294               and s.name = @segname
295       if (@curr_free_space < @free_space)
296       begin
297           /* 17876, "Warning: The specified threshold level is greater 
298           ** than the existing number of free pages on this segment."   
299           */
300           exec sp_getmessage 17876, @msg output
301           print @msg
302   
303       /* This is a warning, not an error. Hence raise a message
304       and continue.*/
305   
306       end
307   
308       commit transaction
309       return (0)
310   


exec sp_procxmode 'sp_addthreshold', 'AnyMode'
go

Grant Execute on sp_addthreshold to public
go
DEFECTS
 QJWI 5 Join or Sarg Without Index 290
 MTYP 4 Assignment type mismatch segment: smallint = int 251
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 206
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 166
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 188
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 201
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 289
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 290
 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_addthreshold  
 MGTP 3 Grant to public sybsystemprocs..syssegments  
 MGTP 3 Grant to public sybsystemprocs..systhresholds  
 MNER 3 No Error Check should check @@error after select into 199
 MNER 3 No Error Check should check return value of exec 206
 MNER 3 No Error Check should check return value of exec 230
 MNER 3 No Error Check should check return value of exec 246
 MNER 3 No Error Check should check return value of exec 300
 MUCO 3 Useless Code Useless Brackets 64
 MUCO 3 Useless Code Useless Brackets 73
 MUCO 3 Useless Code Useless Brackets 80
 MUCO 3 Useless Code Useless Brackets 93
 MUCO 3 Useless Code Useless Brackets 98
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 120
 MUCO 3 Useless Code Useless Brackets 125
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 129
 MUCO 3 Useless Code Useless Brackets 149
 MUCO 3 Useless Code Useless Brackets 160
 MUCO 3 Useless Code Useless Brackets 175
 MUCO 3 Useless Code Useless Brackets 181
 MUCO 3 Useless Code Useless Brackets 183
 MUCO 3 Useless Code Useless Brackets 208
 MUCO 3 Useless Code Useless Brackets 211
 MUCO 3 Useless Code Useless Brackets 221
 MUCO 3 Useless Code Useless Brackets 261
 MUCO 3 Useless Code Useless Brackets 276
 MUCO 3 Useless Code Useless Brackets 284
 MUCO 3 Useless Code Useless Brackets 295
 MUCO 3 Useless Code Useless Brackets 309
 QAFM 3 Var Assignment from potentially many rows 142
 QAFM 3 Var Assignment from potentially many rows 155
 QCTC 3 Conditional Table Creation 199
 QISO 3 Set isolation level 67
 QNAJ 3 Not using ANSI Inner Join 288
 QNUA 3 Should use Alias: Column dbid should use alias u 287
 QNUA 3 Should use Alias: Column lstart should use alias u 287
 QNUA 3 Should use Alias: Column unreservedpgs should use alias u 287
 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}
289
 VNRD 3 Variable is not read @dummy 135
 MSUB 2 Subquery Marker 187
 MTR1 2 Metrics: Comments Ratio Comments: 51% 28
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 19 = 29dec - 12exi + 2 28
 MTR3 2 Metrics: Query Complexity Complexity: 117 28

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
@proc_namesysthresholds.proc_name  

DEPENDENCIES
PROCS AND TABLES USED
reads table master..spt_values (1)  
writes table tempdb..#systhresholds1 (1) 
calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_002 
   reads table master..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_004 
   writes table sybsystemprocs..sp_autoformat_rset_003 
   reads table tempdb..systypes (1)  
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   read_writes table tempdb..#colinfo_af (1) 
   writes table sybsystemprocs..sp_autoformat_rset_001 
   reads table tempdb..syscolumns (1)  
   calls proc sybsystemprocs..sp_namecrack  
   writes table sybsystemprocs..sp_autoformat_rset_005 
reads table sybsystemprocs..syssegments  
reads table master..sysusages (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
read_writes table sybsystemprocs..systhresholds