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


exec sp_procxmode 'sp_addsegment', 'AnyMode'
go

Grant Execute on sp_addsegment to public
go
DEFECTS
 MCTR 4 Conditional Begin Tran or Commit Tran 355
 MCTR 4 Conditional Begin Tran or Commit Tran 380
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 MTYP 4 Assignment type mismatch @action: varchar(30) = varchar(1024) 101
 MTYP 4 Assignment type mismatch @action: varchar(30) = varchar(1024) 105
 MTYP 4 Assignment type mismatch name: sysname = varchar(255) 335
 MTYP 4 Assignment type mismatch segment: smallint = int 335
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 103
 QTYP 4 Comparison type mismatch smallint = int 103
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 136
 QTYP 4 Comparison type mismatch smallint = int 136
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 153
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 215
 QTYP 4 Comparison type mismatch smallint = int 215
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 235
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 253
 QTYP 4 Comparison type mismatch smallint = int 253
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 310
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 363
 QTYP 4 Comparison type mismatch smallint = int 363
 TNOI 4 Table with no index sybsystemprocs..syssegments sybsystemprocs..syssegments
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 266
 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 334
 MNER 3 No Error Check should check return value of exec 390
 MUCO 3 Useless Code Useless Brackets 61
 MUCO 3 Useless Code Useless Brackets 70
 MUCO 3 Useless Code Useless Brackets 85
 MUCO 3 Useless Code Useless Brackets 110
 MUCO 3 Useless Code Useless Brackets 125
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 173
 MUCO 3 Useless Code Useless Brackets 178
 MUCO 3 Useless Code Useless Brackets 182
 MUCO 3 Useless Code Useless Brackets 185
 MUCO 3 Useless Code Useless Brackets 193
 MUCO 3 Useless Code Useless Brackets 200
 MUCO 3 Useless Code Useless Brackets 202
 MUCO 3 Useless Code Useless Brackets 242
 MUCO 3 Useless Code Useless Brackets 259
 MUCO 3 Useless Code Useless Brackets 274
 MUCO 3 Useless Code Useless Brackets 295
 MUCO 3 Useless Code Useless Brackets 328
 MUCO 3 Useless Code Useless Brackets 340
 MUCO 3 Useless Code Useless Brackets 354
 MUCO 3 Useless Code Useless Brackets 364
 MUCO 3 Useless Code Useless Brackets 366
 MUCO 3 Useless Code Useless Brackets 369
 MUCO 3 Useless Code Useless Brackets 374
 MUCO 3 Useless Code Useless Brackets 377
 MUCO 3 Useless Code Useless Brackets 392
 QAFM 3 Var Assignment from potentially many rows 101
 QAFM 3 Var Assignment from potentially many rows 105
 QAFM 3 Var Assignment from potentially many rows 265
 QAFM 3 Var Assignment from potentially many rows 348
 QISO 3 Set isolation level 67
 QNAJ 3 Not using ANSI Inner Join 133
 QNAJ 3 Not using ANSI Inner Join 150
 QNAJ 3 Not using ANSI Inner Join 212
 QNAJ 3 Not using ANSI Inner Join 250
 QNAJ 3 Not using ANSI Inner Join 359
 QNUA 3 Should use Alias: Column segmap should use alias u 137
 QNUA 3 Should use Alias: Column segmap should use alias u 211
 QNUA 3 Should use Alias: Column segmap should use alias u 358
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
103
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
107
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
136
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
215
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
253
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
266
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
363
 VNRD 3 Variable is not read @procval 184
 VNRD 3 Variable is not read @status1 207
 MSUB 2 Subquery Marker 80
 MSUB 2 Subquery Marker 116
 MSUB 2 Subquery Marker 132
 MSUB 2 Subquery Marker 149
 MSUB 2 Subquery Marker 234
 MSUB 2 Subquery Marker 249
 MSUB 2 Subquery Marker 287
 MSUB 2 Subquery Marker 308
 MTR1 2 Metrics: Comments Ratio Comments: 44% 25
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 32 = 46dec - 16exi + 2 25
 MTR3 2 Metrics: Query Complexity Complexity: 190 25
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 132
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 149
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 211
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 249
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 357

DATA PROPAGATION detailed
ColumnWritten To
@segnamesyssegments.name   sp_checkreswords_rset_009.Reserved Word User Names sp_dropalias_rset_001.name sp_dropuser_rset_001.name

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