| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_addsegment | | 31 Aug 14 | Defects 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
| 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) |
