DatabaseProcApplicationCreatedLinks
sybsystemprocssp_addsegment  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "@(#) generic/sproc/src/%M% %I% %G%" */
3     /*	4.1	1.1	06/14/90	sproc/src/addsegment */
4     /*
5     ** Messages for "sp_addsegment"         17280
6     **
7     ** 17260, "Can't run %1! from within a transaction." 
8     ** 17280, "No such device exists -- run sp_helpdb to list the
9     ** 	  devices for the current database."
10    ** 17281, "The specified device is not used by the database."
11    ** 17282, "The specified device is not a database device."
12    ** 17283, "'%1!' is reserved exclusively as a log device."
13    ** 17284, "'%1!' is not a valid identifier."
14    ** 17285, "There is already a segment named '%1!'."
15    ** 17286, "The maximum number of segments for the current database are already defined."
16    ** 17287, "Segment created."
17    ** 17288, "You must execute this procedure from the database 
18    ** 	  in which you wish to %1! a segment.  Please execute 
19    **	  'use %2!' and try again."
20    ** 17590, "The specified database does not exist." 
21    ** 17289, "Set your curwrite to the hurdle of current database."
22    ** 18072, "Setting curwrite to data_low for updating syssegments failed."
23    ** 19572, "A segment with a virtually hashed table exists on device %1!."
24    */
25    create procedure sp_addsegment
26        @segname varchar(255), /* segment name */
27        @dbname varchar(255), /* database name */
28        @devname varchar(255) /* device name to put segment on */
29    as
30    
31        declare @segbit int /* this is the bit to turn on in sysusages */
32        declare @msg varchar(1024)
33        declare @returncode int
34        declare @name varchar(255)
35        declare @procval int
36        declare @maxlen int
37        declare @isamaster int
38        declare @dbid smallint
39        declare @segmap int /* segment map of device */
40        declare @tmp_segmap int
41        declare @segid int /* id of the segment */
42    
43        select @procval = 0
44        select @dbid = db_id(@dbname)
45    
46        /*
47        **  If we're in a transaction, disallow this since it might make recovery
48        **  impossible.
49        */
50        if @@trancount > 0
51        begin
52            /*
53            ** 17260, "Can't run %1! from within a transaction." 
54            */
55            select @name = "sp_addsegment"
56            raiserror 17260, @name
57            return (1)
58        end
59        else
60        begin
61            set chained off
62        end
63        set transaction isolation level 1
64    
65        select @isamaster = 0
66        if ((@dbid = db_id("master"))
67                or (@dbid = db_id("master_companion")))
68        begin
69            select @isamaster = 1
70        end
71    
72        /*
73        **  Only the Database Owner (DBO) or
74        **  Accounts with SA role can execute it.
75        **  if user  had SA role he would be the dbo hence check only
76        **  whether user is DBO.
77        **  Call proc_role() with the required SA role.
78        */
79        if (user_id() = 1)
80        begin
81            /* If user has sa role audit this as a successful sa
82            ** command execution.
83            ** If in the future we add new role with "sa" as 
84            ** substring or allow roles to be added the following could
85            ** result in an incorrect (extra) audit record being sent.
86            */
87            if charindex("sa_role", show_role()) > 0
88                select @procval = proc_role("sa_role")
89        end
90        else
91        begin
92            /* user_id() is not DBO hence user does not have SA role
93            ** audit this as a failed sa command execution.
94            */
95            select @procval = proc_role("sa_role")
96            return (1)
97        end
98    
99        /*
100       **  Make sure the database exists
101       */
102       if not exists (select * from master.dbo.sysdatabases
103               where name = @dbname)
104       begin
105           /* 17590, "The specified database does not exist." */
106           raiserror 17590
107           return (1)
108       end
109   
110       /*
111       **  Make sure that we are in the database specified
112       **  by @dbname. 
113       */
114       if @dbname != db_name()
115       begin
116           /* 
117           ** 13231, "add"
118           ** 17288, "You must execute this procedure from the database 
119           ** 	  in which you wish to %1! a segment.  Please execute 
120           **	  'use %2!' and try again."
121           */
122           declare @action varchar(30)
123           select @action = description
124           from master.dbo.sysmessages
125           where error = 13231 and langid = @@langid
126           if @action is null
127               select @action = description
128               from master.dbo.sysmessages
129               where error = 13231 and langid is null
130           if @action is null select @action = "add"
131           raiserror 17288, @action, @dbname
132           return (1)
133       end
134   
135       /*
136       **  See if the device exists.
137       */
138       if not exists (select *
139               from master.dbo.sysdevices
140               where name like @devname)
141       begin
142           /*
143           ** 17280, "No such device exists -- run sp_helpdb to list the
144           ** 	  devices for the current database."
145           */
146           raiserror 17280
147           return (1)
148       end
149   
150       /*
151       **  Check to see if the device is marked as a log device.
152       **  If so, raiserror.
153       */
154       if exists (select *
155               from master.dbo.sysusages u, master.dbo.sysdevices d
156               where d.name = @devname
157                   and u.vdevno = d.vdevno
158                   and u.dbid = db_id(@dbname)
159                   and segmap = 4)
160       begin
161           /*
162           ** 17283, "'%1!' is reserved exclusively as a log device."
163           */
164           raiserror 17283, @devname
165           return (1)
166       end
167   
168       /*
169       **  Now see if the @devname is a proper database device
170       */
171       if not exists (select *
172               from master.dbo.sysusages u, master.dbo.sysdevices d
173               where d.name = @devname
174                   and u.vdevno = d.vdevno
175                   and d.cntrltype = 0)
176       begin
177           /*
178           ** 17282, "The specified device is not a database device."
179           */
180           raiserror 17282
181           return (1)
182       end
183   
184       select @segmap = segmap
185       from master.dbo.sysusages u, master.dbo.sysdevices d
186       where d.name = @devname
187           and u.vstart between d.low and d.high
188           and u.dbid = db_id(@dbname)
189           and u.vdevno = d.vdevno
190       if @segmap > 0
191       begin
192           select @tmp_segmap = @segmap - 1
193           select @tmp_segmap = @segmap & @tmp_segmap
194           if @tmp_segmap = 0
195           begin
196               /*
197               ** Only one bit is set in segmap.
198               ** Thus, there is only one segment
199               ** existing on device. Now we need 
200               ** to check if it has vhash table.
201               ** As if it has a vhash table it
202               ** will be an exclusive segment and
203               ** another segment cannot be created
204               ** on this device.
205               */
206               select @segid = log(@segmap) / log(2)
207               if exists (select * from syssegments
208                       where segment = @segid and 2 = status & 2)
209               begin
210                   /*
211                   ** 19572, "A segment with a virtually hashed table
212                   ** exists on device %1!."
213                   */
214                   raiserror 19572, @devname
215                   return (1)
216               end
217           end
218       end
219       /*
220       **  Now see if the @dbname uses the @devname
221       */
222       if not exists (select *
223               from master.dbo.sysusages u, master.dbo.sysdevices d
224               where d.name = @devname
225                   and u.vdevno = d.vdevno
226                   and u.dbid = db_id(@dbname))
227       begin
228           /*
229           ** 17281, "The specified device is not used by the database."
230           */
231           raiserror 17281
232           return (1)
233       end
234   
235       /*
236       ** Check for valid identifier.
237       */
238       select @maxlen = length from syscolumns
239       where id = object_id("syssegments") and name = "name"
240   
241       if valid_name(@segname, @maxlen) = 0
242       begin
243           /*
244           ** 17284, "'%1!' is not a valid identifier."
245           */
246           raiserror 17284, @segname
247           return (1)
248       end
249   
250       /*
251       **  Now go ahead and define the new segment and add it to the segmap
252       **  of sysusages.
253       **  NOTE: Don't update master..sysusages and syssegments as a xact since
254       **  	it could cause problems for recovery.
255       */
256   
257       /*
258       **  Check that @segname doesn't already exist.
259       */
260       if exists (select *
261               from syssegments holdlock
262               where name = @segname)
263       begin
264           /*
265           ** 17285, "There is already a segment named '%1!'."
266           */
267           raiserror 17285, @segname
268           return (1)
269       end
270   
271       /*
272       **  Figure out the next segment number to use.
273       **  Segment number may be 0-31.
274       */
275       select @segbit = 3
276       while @segbit < 32
277       begin
278           /*
279           **  Did we find one?
280           */
281           if exists (select *
282                   from syssegments
283                   where segment = @segbit)
284           begin
285               select @segbit = @segbit + 1
286           end
287   
288           /*
289           **  We found an opening so break out.
290           */
291           else break
292   
293       end
294   
295       if @segbit >= 32
296       begin
297           /*
298           ** 17286, "The maximum number of segments for the current database are already defined."
299           */
300           raiserror 17286
301           return (1)
302       end
303   
304   
305   
306       /*
307       **  Add the new segment.
308       */
309       insert into syssegments(segment, name, status)
310       values (@segbit, @segname, 0)
311   
312   
313   
314       /*
315       **  Now set the segments on @devname sysusages.
316       */
317       if (@segbit < 31)
318           select @segbit = power(2, @segbit)
319       else
320           /*
321           **  Since this is segment 31, power(2, 31) will overflow
322           **  since segmap is an int.  We'll grab the machine-dependent
323           **  bit mask from spt_values to set the right bit.
324           */
325           select @segbit = low
326           from master.dbo.spt_values
327           where type = "E"
328               and number = 2
329   
330       /* Encapsulate sysusages/anchor update in a transaction */
331       if (@isamaster = 1)
332           begin tran sysusg_upd
333   
334       update master.dbo.sysusages
335       set segmap = segmap | @segbit
336       from master.dbo.sysusages u,
337           master.dbo.sysdevices d
338       where d.name = @devname
339           and u.vdevno = d.vdevno
340           and u.dbid = db_id(@dbname)
341       if (@isamaster = 1)
342       begin
343           if (@@error != 0)
344           begin
345               rollback transaction sysusg_upd
346               return (1)
347           end
348   
349           dbcc dbrepair(@dbname, "upd_usg")
350   
351           if (@@error != 0)
352           begin
353               rollback transaction sysusg_upd
354               return (1)
355           end
356   
357           commit transaction sysusg_upd
358       end
359   
360       /*
361       **  Now we need to activate the new segment map.
362       */
363       dbcc dbrepair(@dbname, remap, NULL, - 1, @segname)
364       /*
365       ** 17287, "Segment created."
366       */
367       exec sp_getmessage 17287, @msg output
368       print @msg
369       return (0)
370   


exec sp_procxmode 'sp_addsegment', 'AnyMode'
go

Grant Execute on sp_addsegment to public
go
DEFECTS
 MCTR 4 Conditional Begin Tran or Commit Tran 332
 MCTR 4 Conditional Begin Tran or Commit Tran 357
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 MTYP 4 Assignment type mismatch @action: varchar(30) = varchar(1024) 123
 MTYP 4 Assignment type mismatch @action: varchar(30) = varchar(1024) 127
 MTYP 4 Assignment type mismatch name: sysname = varchar(255) 310
 MTYP 4 Assignment type mismatch segment: smallint = int 310
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 125
 QTYP 4 Comparison type mismatch smallint = int 125
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 158
 QTYP 4 Comparison type mismatch smallint = int 158
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 175
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 188
 QTYP 4 Comparison type mismatch smallint = int 188
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 208
 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 283
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 340
 QTYP 4 Comparison type mismatch smallint = int 340
 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_addsegment  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..syssegments  
 MNER 3 No Error Check should check @@error after insert 309
 MNER 3 No Error Check should check return value of exec 367
 MUCO 3 Useless Code Useless Brackets 57
 MUCO 3 Useless Code Useless Brackets 66
 MUCO 3 Useless Code Useless Brackets 79
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 107
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 147
 MUCO 3 Useless Code Useless Brackets 165
 MUCO 3 Useless Code Useless Brackets 181
 MUCO 3 Useless Code Useless Brackets 215
 MUCO 3 Useless Code Useless Brackets 232
 MUCO 3 Useless Code Useless Brackets 247
 MUCO 3 Useless Code Useless Brackets 268
 MUCO 3 Useless Code Useless Brackets 301
 MUCO 3 Useless Code Useless Brackets 317
 MUCO 3 Useless Code Useless Brackets 331
 MUCO 3 Useless Code Useless Brackets 341
 MUCO 3 Useless Code Useless Brackets 343
 MUCO 3 Useless Code Useless Brackets 346
 MUCO 3 Useless Code Useless Brackets 351
 MUCO 3 Useless Code Useless Brackets 354
 MUCO 3 Useless Code Useless Brackets 369
 QAFM 3 Var Assignment from potentially many rows 123
 QAFM 3 Var Assignment from potentially many rows 127
 QAFM 3 Var Assignment from potentially many rows 238
 QAFM 3 Var Assignment from potentially many rows 325
 QISO 3 Set isolation level 63
 QNAJ 3 Not using ANSI Inner Join 155
 QNAJ 3 Not using ANSI Inner Join 172
 QNAJ 3 Not using ANSI Inner Join 185
 QNAJ 3 Not using ANSI Inner Join 223
 QNAJ 3 Not using ANSI Inner Join 336
 QNUA 3 Should use Alias: Column segmap should use alias u 159
 QNUA 3 Should use Alias: Column segmap should use alias u 184
 QNUA 3 Should use Alias: Column segmap should use alias u 335
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
125
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
129
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
158
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
188
 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: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
239
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
340
 VNRD 3 Variable is not read @procval 95
 VUNU 3 Variable is not used @returncode 33
 MSUB 2 Subquery Marker 102
 MSUB 2 Subquery Marker 138
 MSUB 2 Subquery Marker 154
 MSUB 2 Subquery Marker 171
 MSUB 2 Subquery Marker 207
 MSUB 2 Subquery Marker 222
 MSUB 2 Subquery Marker 260
 MSUB 2 Subquery Marker 281
 MTR1 2 Metrics: Comments Ratio Comments: 49% 25
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 29 = 42dec - 15exi + 2 25
 MTR3 2 Metrics: Query Complexity Complexity: 174 25
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 154
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 171
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 184
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 222
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 334

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