DatabaseProcApplicationCreatedLinks
sybsystemprocssp_extendsegment  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "@(#) generic/sproc/src/%M% %I% %G%" */
3     /*	5.0	14.2	11/12/91	sproc/src/extendsegment */
4     
5     /*
6     ** Messages for "sp_extendsegment"      17550
7     **
8     ** 17260, "Can't run %1! from within a transaction."
9     ** 17520, "There is no such segment as '%1!'."
10    ** 17281, "The specified device is not used by the database."
11    ** 17283, "'%1!' is reserved exclusively as a log device."
12    ** 17280, "No such device exists -- run sp_helpdb to list the devices for the current database."
13    ** 17550, "Segment extended."
14    ** 17551, "Device '%1!' is now exclusively used by '%2!'"
15    ** 17715, "The last-chance threshold for database %1! is now %2! pages."
16    ** 17716, "Could not update the last-chance threshold for database %1!"
17    ** 17552, "This command has been ignored.  Extending the log segment on device '%1!' would leave no space for creating objects in database '%2!'."
18    ** 17590, "The specified database does not exist." 
19    ** 17288, "You must execute this procedure from the database 
20    ** 	  in which you wish to %1! a segment.  Please execute 
21    **	  'use %2!' and try again."
22    ** 17289, "Set your curwrite to the hurdle of current database."
23    ** 19572, "A segment with a virtually hashed table exists of device %1!."
24    ** 19571, "You cannot extend a segment with a virtually hashed table on device %1!, because this device has other segments."
25    */
26    
27    create or replace procedure sp_extendsegment
28        @segname varchar(30), /* segment name */
29        @dbname varchar(30), /* database name */
30        @devname varchar(30) /* device name to put segment on */
31    as
32    
33        declare @dbid smallint /* id of the database */
34        declare @segbit int /* this is the bit to turn on in sysusages */
35        declare @lct int /* log new last-chance threshold value */
36        declare @msg varchar(1024)
37        declare @new_msg varchar(100)
38        declare @returncode int
39        declare @procval int
40        declare @bitdesc varchar(30) /* bit description for the db */
41        declare @tempdb_mask int /* All database status bit for a tempdb */
42        declare @isatempdb int /* Is this a temp. db ? */
43        declare @isamaster int
44        declare @segmap int /* segment map */
45        declare @tmp_segmap int
46        declare @segid int /* id of the segment */
47        declare @status int /* status of the segment */
48        declare @nullarg char(1)
49        declare @dummy int
50        declare @gp_enabled int
51    
52    
53    
54        /*
55        **  If we're in a transaction, disallow this since it might make recovery
56        **  impossible.
57        */
58        if @@trancount > 0
59        begin
60            /*
61            ** 17260, "Can't run %1! from within a transaction."
62            */
63            raiserror 17260, "sp_extendsegment"
64            return (1)
65        end
66        else
67        begin
68            set chained off
69        end
70    
71        set transaction isolation level 1
72    
73    
74        /*
75        **  Make sure the database exists
76        */
77        if not exists (select * from master.dbo.sysdatabases
78                where name = @dbname)
79        begin
80            /* 17590, "The specified database does not exist." */
81            raiserror 17590
82            return (1)
83        end
84    
85        /*
86        **  Make sure that we are in the database specified
87        **  by @dbname. 
88        */
89        if @dbname != db_name()
90        begin
91            /* 
92            ** 13233, "update"
93            ** 17288, "You must execute this procedure from the database 
94            ** 	  in which you wish to %1! a segment.  Please execute 
95            **	  'use %2!' and try again."
96            */
97            declare @action varchar(30)
98            select @action = description
99            from master.dbo.sysmessages
100           where error = 13233 and langid = @@langid
101           if @action is null
102               select @action = description
103               from master.dbo.sysmessages
104               where error = 13233 and langid is null
105           if @action is null select @action = "update"
106           raiserror 17288, @action, @dbname
107           return (1)
108       end
109   
110       select @dbid = db_id(@dbname)
111   
112       /*
113       **  Check to see if the segment exists.
114       */
115       if not exists (select *
116               from syssegments
117               where name = @segname)
118       begin
119           /*
120           ** 17520, "There is no such segment as '%1!'."
121           */
122           raiserror 17520, @segname
123           return (1)
124       end
125   
126       /*
127       **  See if the device exists.
128       */
129       if not exists (select *
130               from master.dbo.sysdevices
131               where name like @devname)
132       begin
133           /*
134           ** 17280, "No such device exists -- run sp_helpdb to list the devices for the current database."
135   
136           */
137           raiserror 17280
138   
139   
140   
141           return (1)
142       end
143   
144       /*
145       **  Now see if the @dbname uses the @devname
146       */
147   
148       if not exists (select 1
149               from master.dbo.sysusages u, master.dbo.sysdevices d
150               where d.name = @devname
151                   and u.vdevno = d.vdevno
152                   and u.dbid = @dbid)
153       begin
154           /*
155           ** 17281, "The specified device is not used by the database."
156           */
157           raiserror 17281
158           return (1)
159       end
160   
161       /*
162       **  Check to see if the device is used as a log device and we are 
163       **  trying to extend a segment different from the log segment on it.
164       **  If so, print an error.
165       */
166       if @segname != "logsegment"
167           and exists (select 1
168               from master.dbo.sysusages u, master.dbo.sysdevices d
169               where d.name = @devname
170                   and u.vdevno = d.vdevno
171                   and u.dbid = @dbid
172                   and segmap = 4)
173       begin
174           /*
175           ** 17283, "'%1!' is reserved exclusively as a log device."
176           */
177           raiserror 17283, @devname
178           return (1)
179       end
180   
181       /*
182       **  If granular permissions is not enabled then only the Database Owner (DBO) or
183       **  accounts with SA role can execute it.
184       **  if user  had SA role he would be the dbo hence check only
185       **  whether user is DBO.
186       **  If granular permissions is enabled then the permission 'manage database' is
187       **  required.  proc_role and proc_auditperm will also do auditing
188       **  if required. Both will also print error message if required.
189       */
190   
191       select @nullarg = NULL
192       execute @status = sp_aux_checkroleperm "dbo", "manage database",
193           @dbname, @gp_enabled output
194   
195       /* For Auditing */
196       if (@gp_enabled = 0)
197       begin
198           if (@status = 0)
199           begin
200               /* Audit sa_role if dbo is due to having sa_role */
201               execute @procval = sp_aux_checkroleperm "sa_role", @nullarg,
202                   @nullarg, @gp_enabled output
203               if (@procval = 0)
204                   select @procval = proc_role("sa_role")
205           end
206           else
207           begin
208               select @procval = proc_role("sa_role")
209           end
210       end
211       else
212       begin
213           select @dummy = proc_auditperm("manage database", @status, @dbname)
214       end
215   
216       if (@status != 0)
217           return (1)
218   
219       /*
220       ** Get segment map of device on which segment is extended.
221       */
222       select @segmap = segmap
223       from master.dbo.sysusages u, master.dbo.sysdevices d
224       where d.name = @devname
225           and u.vstart between d.low and d.high
226           and u.dbid = db_id(@dbname)
227       select @status = status
228       from syssegments where name = @segname
229   
230       /*
231       ** Keep only the status bit regarding whether
232       ** vhash table is there or not on this segment.
233       */
234       select @status = @status & 2
235   
236       if @status = 2
237       begin
238           /*
239           ** vhash table is there on this segment. This is an
240           ** exclusive segment. It can only be extended on a 
241           ** device having no other segment. Thus, segmap 
242           ** should be 0.
243           */
244           if @segmap > 0
245           begin
246               /*
247               ** 19571, You cannot extend a segment with a virtually hashed table on device %1!,
248               ** because this device has other segments.
249               */
250               raiserror 19571, @devname
251               return (1)
252           end
253       end
254       else if @segmap > 0
255       begin
256           /*
257           ** This is not an exclusive segment. Also there
258           ** are other segments on the device. Thus, we
259           ** need to check that none of these is an
260           ** exclusive segment.
261           */
262           select @tmp_segmap = @segmap - 1
263           select @tmp_segmap = @segmap & @tmp_segmap
264           if @tmp_segmap = 0
265           begin
266               /*
267               ** Only one bit is set in segmap.
268               ** Thus, there is only one segment
269               ** existing on device. Now we need
270               ** to check if that segment has vhash
271               ** table. As if it has a vhash table
272               ** it will be a exclusive segment and
273               ** another segment cannot be created
274               ** on this device.
275               */
276               select @segid = log(@segmap) / log(2)
277               if exists (select * from syssegments
278                       where segment = @segid and 2 = status & 2)
279               begin
280                   /*
281                   ** 19572, A segment with a virtually hashed table exists of device %1!.
282                   */
283                   raiserror 19572, @devname
284                   return (1)
285               end
286           end
287       end
288       /*
289       **  Get the segment number for @segname.
290       */
291       select @segbit = segment
292       from syssegments
293       where name = @segname
294   
295       /*
296       ** Determine if we are dealing with a temporary database.
297       */
298       select @tempdb_mask = number
299       from master.dbo.spt_values
300       where type = "D3" and name = "TEMPDB STATUS MASK"
301   
302       if (@dbid = 2) or exists (select * from master.dbo.sysdatabases
303               where dbid = @dbid
304                   and (status3 & @tempdb_mask) != 0)
305       begin
306           select @isatempdb = 1
307       end
308       else
309       begin
310           select @isatempdb = 0
311       end
312   
313       select @isamaster = 0
314       if ((@dbid = db_id("master"))
315               or (@dbid = db_id("master_companion")))
316       begin
317           select @isamaster = 1
318       end
319   
320       /* Encapsulate sysusages/anchor update in a transaction */
321       if (@isamaster = 1)
322           begin transaction sysusg_upd
323   
324       /*
325       **  If @segbit is 2 then it's the logsegment and the logsegment
326       **  doesn't share devices with other segments.  In this case,
327       **  don't OR the @segbit in but just set segmap to 4.
328       **  Also, if we are extending the log space and this is not 
329       **  a temporary database ...
330       */
331       if @segbit = 2 and @isatempdb = 0
332       begin
333           /* 
334           ** ... check single user bit (4096).
335           ** Database must be in single user mode so that no log allocation
336           ** or deallocation take place while recalculing the free space counter
337           */
338           select @bitdesc = null
339           select @bitdesc = v.name
340           from master.dbo.spt_values v, master.dbo.sysdatabases d
341           where d.dbid = @dbid
342               and v.type = "D"
343               and d.status & v.number = 4096
344           if @bitdesc is null
345           begin
346               /*
347               ** 17793, "System Administrator (SA) must set database '%1!' to single-user mode with sp_dboption before using '%2!'."
348               */
349               raiserror 17793, @dbname, "sp_extendsegment"
350               return (1)
351           end
352   
353           /*
354           ** Extending the logsegment means we won't be able to
355           ** create any more objects on this device. (See comment above.)
356           ** Do it only if non-log space is available on other devices.
357           */
358           if not exists (select *
359                   from master.dbo.sysdevices d,
360                       master.dbo.sysusages u
361                   where u.vdevno = d.vdevno
362                       and u.dbid = @dbid
363                       and u.segmap != 4
364                       and d.name != @devname
365                       and d.cntrltype = 0)
366           begin
367               /*
368               ** 17552, "This command has been ignored.  Extending the log
369               ** segment on device '%1!' would leave no space for creating
370               ** objects in database '%2!'."
371               */
372               raiserror 17552, @devname, @dbname
373               return (1)
374           end
375   
376           update master.dbo.sysusages
377           set segmap = 4
378           from master.dbo.sysusages u,
379               master.dbo.sysdevices d
380           where d.name = @devname
381               and u.vdevno = d.vdevno
382               and u.dbid = @dbid
383       end
384   
385       else
386       begin
387           if (@segbit < 31)
388               select @segbit = power(2, @segbit)
389           else
390               /*
391               **  Since this is segment 31, power(2, 31) will
392               **  overflow since segmap is an int.  We'll grab the
393               **  machine-dependent bit mask from spt_values to set
394               **  the right bit.
395               */
396               select @segbit = low
397               from master.dbo.spt_values
398               where type = "E"
399                   and number = 2
400           /*
401           **  Add the segment to @devname in sysusages.
402           */
403           update master.dbo.sysusages
404           set segmap = segmap | @segbit
405           from master.dbo.sysusages u,
406               master.dbo.sysdevices d
407           where d.name = @devname
408               and u.vdevno = d.vdevno
409               and u.dbid = @dbid
410       end
411   
412       /* End the transaction */
413       if (@isamaster = 1)
414       begin
415           if (@@error != 0)
416           begin
417               rollback transaction sysusg_upd
418               return (1)
419           end
420   
421           dbcc dbrepair(@dbname, "upd_usg")
422           if (@@error != 0)
423           begin
424               rollback transaction sysusg_upd
425               return (1)
426           end
427   
428           commit transaction sysusg_upd
429       end
430   
431       /*
432       **  Get the segment number for @segname.
433       */
434       select @segbit = segment
435       from syssegments
436       where name = @segname
437   
438       /*
439       **  Now we need to activate the new segment map.
440       */
441       dbcc dbrepair(@dbname, remap, NULL, - 1, @segname)
442   
443       /*
444       ** If we are adding log space, we might be going onto a segment that
445       ** used to contain user data, so we need to look for stranded objects.
446       */
447       if @segbit = 2
448       begin
449           dbcc dbrepair(@dbname, findstranded, NULL, @isatempdb)
450   
451           /* 
452           ** Fix log free space counters, as we may have
453           ** added new log space.
454           */
455           dbcc dbrepair(@dbname, fixlogfreespace, scanlogchain, 1)
456   
457           /*
458           ** Now that dbrepair has remapped the database, we can
459           ** recalculate the log's last-chance threshold.
460           */
461           select @lct = lct_admin("lastchance", @dbid)
462           if @lct > 0
463           begin
464               exec sp_getmessage 17715, @msg out
465               print @msg, @dbname, @lct
466           end
467           else
468           begin
469               raiserror 17716, @dbname, @lct
470           end
471       end
472   
473       /*
474       ** 17550, "Segment extended."
475       */
476       exec sp_getmessage 17550, @msg output
477       print @msg
478   
479       /*
480       **  If we are extending the logsegment, print out a message about what it
481       **  means to do so.
482       */
483       if @segbit = 2
484       begin
485           /*
486           ** 17551, "Device '%1!' is now exclusively used by '%2!'"
487           */
488           exec sp_getmessage 17551, @msg output
489           print @msg, @devname, @segname
490       end
491   
492       return (0)
493   


exec sp_procxmode 'sp_extendsegment', 'AnyMode'
go

Grant Execute on sp_extendsegment to public
go
DEFECTS
 MRIT 5 Return in Transaction trancount is 1 350
 MRIT 5 Return in Transaction trancount is 1 373
 QJWI 5 Join or Sarg Without Index 225
 QJWI 5 Join or Sarg Without Index 343
 MCTR 4 Conditional Begin Tran or Commit Tran 322
 MCTR 4 Conditional Begin Tran or Commit Tran 428
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 MTYP 4 Assignment type mismatch @action: varchar(30) = varchar(1024) 98
 MTYP 4 Assignment type mismatch @action: varchar(30) = varchar(1024) 102
 MTYP 4 Assignment type mismatch @bitdesc: varchar(30) = varchar(255) 339
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
300
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
342
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 100
 QTYP 4 Comparison type mismatch smallint = int 100
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 226
 QTYP 4 Comparison type mismatch smallint = int 226
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 278
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 365
 TNOI 4 Table with no index sybsystemprocs..syssegments sybsystemprocs..syssegments
 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..sysdatabases  
 MGTP 3 Grant to public master..sysdevices  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public master..sysusages  
 MGTP 3 Grant to public sybsystemprocs..sp_extendsegment  
 MGTP 3 Grant to public sybsystemprocs..syssegments  
 MNER 3 No Error Check should check @@error after update 376
 MNER 3 No Error Check should check @@error after update 403
 MNER 3 No Error Check should check return value of exec 464
 MNER 3 No Error Check should check return value of exec 476
 MNER 3 No Error Check should check return value of exec 488
 MUCO 3 Useless Code Useless Brackets 64
 MUCO 3 Useless Code Useless Brackets 82
 MUCO 3 Useless Code Useless Brackets 107
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 141
 MUCO 3 Useless Code Useless Brackets 158
 MUCO 3 Useless Code Useless Brackets 178
 MUCO 3 Useless Code Useless Brackets 196
 MUCO 3 Useless Code Useless Brackets 198
 MUCO 3 Useless Code Useless Brackets 203
 MUCO 3 Useless Code Useless Brackets 216
 MUCO 3 Useless Code Useless Brackets 217
 MUCO 3 Useless Code Useless Brackets 251
 MUCO 3 Useless Code Useless Brackets 284
 MUCO 3 Useless Code Useless Brackets 314
 MUCO 3 Useless Code Useless Brackets 321
 MUCO 3 Useless Code Useless Brackets 350
 MUCO 3 Useless Code Useless Brackets 373
 MUCO 3 Useless Code Useless Brackets 387
 MUCO 3 Useless Code Useless Brackets 413
 MUCO 3 Useless Code Useless Brackets 415
 MUCO 3 Useless Code Useless Brackets 418
 MUCO 3 Useless Code Useless Brackets 422
 MUCO 3 Useless Code Useless Brackets 425
 MUCO 3 Useless Code Useless Brackets 492
 QAFM 3 Var Assignment from potentially many rows 98
 QAFM 3 Var Assignment from potentially many rows 102
 QAFM 3 Var Assignment from potentially many rows 227
 QAFM 3 Var Assignment from potentially many rows 291
 QAFM 3 Var Assignment from potentially many rows 298
 QAFM 3 Var Assignment from potentially many rows 396
 QAFM 3 Var Assignment from potentially many rows 434
 QISO 3 Set isolation level 71
 QNAJ 3 Not using ANSI Inner Join 149
 QNAJ 3 Not using ANSI Inner Join 168
 QNAJ 3 Not using ANSI Inner Join 223
 QNAJ 3 Not using ANSI Inner Join 340
 QNAJ 3 Not using ANSI Inner Join 359
 QNAJ 3 Not using ANSI Inner Join 378
 QNAJ 3 Not using ANSI Inner Join 405
 QNUA 3 Should use Alias: Column segmap should use alias u 172
 QNUA 3 Should use Alias: Column segmap should use alias u 222
 QNUA 3 Should use Alias: Column segmap should use alias u 404
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
100
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
104
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
152
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
171
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
226
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
362
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
382
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
409
 VNRD 3 Variable is not read @gp_enabled 202
 VNRD 3 Variable is not read @procval 208
 VNRD 3 Variable is not read @dummy 213
 VUNU 3 Variable is not used @new_msg 37
 VUNU 3 Variable is not used @returncode 38
 MSUB 2 Subquery Marker 77
 MSUB 2 Subquery Marker 115
 MSUB 2 Subquery Marker 129
 MSUB 2 Subquery Marker 148
 MSUB 2 Subquery Marker 167
 MSUB 2 Subquery Marker 277
 MSUB 2 Subquery Marker 302
 MSUB 2 Subquery Marker 358
 MTR1 2 Metrics: Comments Ratio Comments: 50% 27
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 44 = 56dec - 14exi + 2 27
 MTR3 2 Metrics: Query Complexity Complexity: 229 27
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 148
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 167
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 358
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 376
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 403

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
read_writes table master..sysusages (1)  
reads table master..sysmessages (1)  
reads table master..sysdatabases (1)  
reads table master..sysdevices (1)  
reads table master..spt_values (1)  
reads table sybsystemprocs..syssegments  
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)