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


exec sp_procxmode 'sp_extendsegment', 'AnyMode'
go

Grant Execute on sp_extendsegment to public
go
DEFECTS
 MRIT 5 Return in Transaction trancount is 1 329
 MRIT 5 Return in Transaction trancount is 1 352
 QJWI 5 Join or Sarg Without Index 202
 QJWI 5 Join or Sarg Without Index 322
 MCTR 4 Conditional Begin Tran or Commit Tran 301
 MCTR 4 Conditional Begin Tran or Commit Tran 407
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 MTYP 4 Assignment type mismatch @action: varchar(30) = varchar(1024) 115
 MTYP 4 Assignment type mismatch @action: varchar(30) = varchar(1024) 119
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
277
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
321
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 117
 QTYP 4 Comparison type mismatch smallint = int 117
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 203
 QTYP 4 Comparison type mismatch smallint = int 203
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 255
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 344
 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 355
 MNER 3 No Error Check should check @@error after update 382
 MNER 3 No Error Check should check return value of exec 440
 MNER 3 No Error Check should check return value of exec 452
 MNER 3 No Error Check should check return value of exec 464
 MUCO 3 Useless Code Useless Brackets 59
 MUCO 3 Useless Code Useless Brackets 74
 MUCO 3 Useless Code Useless Brackets 88
 MUCO 3 Useless Code Useless Brackets 99
 MUCO 3 Useless Code Useless Brackets 124
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 158
 MUCO 3 Useless Code Useless Brackets 175
 MUCO 3 Useless Code Useless Brackets 193
 MUCO 3 Useless Code Useless Brackets 228
 MUCO 3 Useless Code Useless Brackets 261
 MUCO 3 Useless Code Useless Brackets 291
 MUCO 3 Useless Code Useless Brackets 300
 MUCO 3 Useless Code Useless Brackets 329
 MUCO 3 Useless Code Useless Brackets 352
 MUCO 3 Useless Code Useless Brackets 366
 MUCO 3 Useless Code Useless Brackets 392
 MUCO 3 Useless Code Useless Brackets 394
 MUCO 3 Useless Code Useless Brackets 397
 MUCO 3 Useless Code Useless Brackets 401
 MUCO 3 Useless Code Useless Brackets 404
 MUCO 3 Useless Code Useless Brackets 468
 QAFM 3 Var Assignment from potentially many rows 115
 QAFM 3 Var Assignment from potentially many rows 119
 QAFM 3 Var Assignment from potentially many rows 204
 QAFM 3 Var Assignment from potentially many rows 268
 QAFM 3 Var Assignment from potentially many rows 275
 QAFM 3 Var Assignment from potentially many rows 375
 QAFM 3 Var Assignment from potentially many rows 413
 QISO 3 Set isolation level 66
 QNAJ 3 Not using ANSI Inner Join 166
 QNAJ 3 Not using ANSI Inner Join 183
 QNAJ 3 Not using ANSI Inner Join 200
 QNAJ 3 Not using ANSI Inner Join 319
 QNAJ 3 Not using ANSI Inner Join 338
 QNAJ 3 Not using ANSI Inner Join 357
 QNAJ 3 Not using ANSI Inner Join 384
 QNUA 3 Should use Alias: Column segmap should use alias u 187
 QNUA 3 Should use Alias: Column segmap should use alias u 199
 QNUA 3 Should use Alias: Column segmap should use alias u 383
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
117
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
121
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
169
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
186
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
203
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
341
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
361
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
388
 VNRD 3 Variable is not read @procval 87
 VUNU 3 Variable is not used @new_msg 37
 VUNU 3 Variable is not used @returncode 38
 MSUB 2 Subquery Marker 94
 MSUB 2 Subquery Marker 132
 MSUB 2 Subquery Marker 146
 MSUB 2 Subquery Marker 165
 MSUB 2 Subquery Marker 182
 MSUB 2 Subquery Marker 254
 MSUB 2 Subquery Marker 279
 MSUB 2 Subquery Marker 337
 MTR1 2 Metrics: Comments Ratio Comments: 51% 27
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 40 = 53dec - 15exi + 2 27
 MTR3 2 Metrics: Query Complexity Complexity: 218 27
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 165
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 182
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 337
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 355
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 382

DEPENDENCIES
PROCS AND TABLES USED
read_writes table master..sysusages (1)  
reads table sybsystemprocs..syssegments  
calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
reads table master..sysdatabases (1)  
reads table master..spt_values (1)  
reads table master..sysdevices (1)  
reads table master..sysmessages (1)