| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_logiosize | | 31 Aug 14 | Defects Dependencies |
1 2 /* Stored procedure for setting the log I/O size. */ 3 4 /* 5 ** Messages for sp_logiosize 6 ** 7 ** 17260, "Can't run %1! from with in a transaction." 8 ** 17289, "Set your curwrite to the hurdle of current database." 9 ** 18126, "Log I/O size must be a power of 2, and is between a 10 ** logical pagesize to size of 8 logical pages. For example, 11 ** if the logical pagesize is %1!K, the log I/O size should be 12 ** %2!, %3!, %4!, or %5![k|K]." 13 ** 18127, "You must have System Administrator (SA) role to 14 ** execute this stored procedure." 15 ** 18128, "Unable to change the log I/O size. The memory pool 16 ** for the specified log I/O size does not exist." 17 ** 18129, "Log I/O size is set to %1! Kbytes." 18 ** 18130, "sp_logiosize failed. Please contact Sybase Technical 19 ** Support to help you resolve the problem." 20 ** 18131, "Log I/O Size value '%1!' is illegal." 21 ** 18132, "The transaction log for database '%1!' will use I/O size of %2! 22 ** Kbytes." 23 ** 19979, "Cannot run '%1!' for in-memory database '%2!'." 24 */ 25 26 /* 27 ** Syntax: 28 ** 29 ** sp_logiosize ["default" | "size" | "[k|K]" | "all"] 30 ** 31 ** Internal variables used: 32 ** 33 ** @old_value - The log I/O size from the sysattributes if it exists. 34 ** @new_value - The log I/O size specified through sp_logiosize. 35 ** @use_value - The log I/O size server would use based on available 36 ** resources. 37 ** @action - Denotes add, change, drop or fetch an I/O size value. 38 ** 39 ** Handle the following 3 cases for @new_value 40 ** case NULL 41 ** case @new_value > 0 42 ** case @new_value = "default" 43 */ 44 45 create procedure sp_logiosize 46 @size varchar(30) = NULL 47 as 48 declare @new_value int 49 , @objid int 50 , @old_value int 51 , @action int 52 , @use_value int 53 , @tempiosize int 54 , @retstat int 55 , @row_count int 56 , @cstat int 57 , @unit_loc int 58 , @size_len int 59 , @logicalpgsz1 int 60 , @logicalpgsz2 int 61 , @logicalpgsz3 int 62 , @logicalpgsz4 int 63 , @msg varchar(1024) 64 , @tempstr varchar(30) 65 , @dbname varchar(255) 66 , @name varchar(255) /* cache name from sysconfigures */ 67 , @imdbstat int 68 , @adbstat int 69 , @status3 int 70 , @status4 int 71 72 /* 73 ** Extract the I/O size value from the input. 74 */ 75 select @tempstr = lower(ltrim(rtrim(@size))) 76 77 if @tempstr is NULL 78 begin 79 select @new_value = NULL 80 end 81 else 82 begin 83 84 /* 85 ** Check whether the input value has correct unit k or K. 86 ** Parse the input string. 87 */ 88 select @unit_loc = patindex("%[kK]%", @size) 89 90 /* If the unit is specified, check if it is the last char 91 ** in the string, input like "2kk" should raise an error. 92 ** If the input is correct, grap the digit string and put 93 ** it into @tempstr. 94 */ 95 if @unit_loc != 0 96 begin 97 select @size_len = char_length(@size) 98 if @size_len = @unit_loc 99 begin 100 select @tempstr = substring(@size, 1, @unit_loc - 1) 101 end 102 103 /* 104 ** 18131, "Log I/O Size value '%1!' is illegal." 105 */ 106 else 107 begin 108 raiserror 18131, @size 109 return (1) 110 end 111 end 112 113 /* Check whether the input value is an integer */ 114 if @tempstr like "[0-9]%" 115 begin 116 select @new_value = convert(int, @tempstr) 117 /* 118 ** Make sure the log I/O size is a power of 2 and is 119 ** between a logical pagesize to size of 8 logical pages. 120 */ 121 122 select @tempiosize = 2 123 while @tempiosize < @new_value 124 select @tempiosize = @tempiosize * 2 125 if @tempiosize != @new_value 126 or @tempiosize > 8 * @@maxpagesize / 1024 127 or @tempiosize < @@maxpagesize / 1024 128 begin 129 /* 18126, "Log I/O size must be a power of 2, and is 130 ** between a logical pagesize to size of 8 logical 131 ** pages. For example, if the logical pagesize is 132 ** %1!K, the log I/O size should be %2!, %3!, %4!, 133 ** or %5![k|K]." 134 */ 135 select @logicalpgsz1 = @@maxpagesize / 1024 136 select @logicalpgsz2 = 2 * @logicalpgsz1 137 select @logicalpgsz3 = 4 * @logicalpgsz1 138 select @logicalpgsz4 = 8 * @logicalpgsz1 139 raiserror 18126, @logicalpgsz1, @logicalpgsz1, 140 @logicalpgsz2, @logicalpgsz3, @logicalpgsz4 141 return (1) 142 end 143 end 144 else 145 /* Process the value as a character string */ 146 begin 147 /* Check whether the input value is "default" */ 148 if @tempstr = "default" 149 begin 150 select @new_value = 0 151 end 152 else if @tempstr = "all" 153 begin 154 /* 155 ** There is no sanctity about this value. We just 156 ** want to differentiate this value from other 157 ** valid values. This indicates that user wants 158 ** log I/O size listing for all data bases. 159 */ 160 select @new_value = 99 161 end 162 else 163 begin 164 /* 165 ** 18131, "Log I/O Size value '%1!' is illegal." 166 */ 167 raiserror 18131, @size 168 return (1) 169 end 170 end 171 end 172 173 /* 174 ** Don't allow this in a transaction because we can't undo what 175 ** we have done through the attr_notify() built-in. 176 */ 177 if @@trancount > 0 178 begin 179 /* 180 ** 17260, "Can't run %1! from with in a transaction." 181 */ 182 raiserror 17260, "sp_logiosize" 183 return (1) 184 end 185 else 186 begin 187 set transaction isolation level 1 188 set chained off 189 end 190 191 select @objid = object_id("syslogs") 192 select @dbname = db_name() 193 select @use_value = 0 194 195 /* 196 ** If the input value is NULL, print the log I/O size set for the current 197 ** database. 198 */ 199 if @new_value is NULL 200 begin 201 /* 202 ** Print the log I/O size set for the current database 203 */ 204 select @action = 4 /* Denotes fetching a value */ 205 select @use_value = attrib_notify(2, 0, "T", @objid, NULL, NULL, 206 NULL, NULL, @new_value, NULL, NULL, 207 NULL, NULL, @action) 208 end 209 else if @new_value = 99 210 begin 211 /* List log I/O size values for all the data bases */ 212 select name into #syscacheconfig from master.dbo.sysconfigures 213 where parent = 19 and config = 19 214 215 /* 216 ** Find out the number of rows we want to look at. 217 */ 218 select @row_count = count(*) from #syscacheconfig 219 220 declare logiosize_cursor cursor 221 for select name from #syscacheconfig 222 223 open logiosize_cursor 224 225 while (@row_count > 0) 226 begin 227 fetch logiosize_cursor into @name 228 print "Cache name: %1!", @name 229 print "Data base Log I/O Size" 230 print "------------------------------ ------------" 231 select @cstat = config_admin(9, 4, 0, 0, NULL, @name) 232 print " " 233 select @row_count = @row_count - 1 234 end 235 236 close logiosize_cursor 237 return (0) 238 end 239 /* 240 ** Process other input values. Look for a matching row in the 241 ** sysattributes table for the Log I/O Size attribute. 242 */ 243 else 244 begin 245 246 select @status4 = status4, @status3 = status3 247 from master..sysdatabases 248 where dbid = db_id() 249 250 /* Don't let sp_logiosize to be run on an in-memory database. */ 251 select @imdbstat = number from master..spt_values 252 where name = "in-memory database" and type = "D4" 253 254 if (@status4 & @imdbstat != 0) 255 begin 256 raiserror 19979, "sp_logiosize", @dbname 257 return (1) 258 end 259 260 select @adbstat = number from master.dbo.spt_values 261 where name = "archive database" and type = "D3" 262 263 /* Do not allow sp_logiosize to be run on an archive database. */ 264 if (@status3 & @adbstat != 0) 265 begin 266 /* Cannot run '%1!' on an archive database." */ 267 raiserror 19424, "sp_logiosize" 268 return (1) 269 end 270 271 /* 272 ** Check if user has "sa" role. proc_role() also does auditing 273 ** if required. proc_role() will also print error message if 274 ** required. 275 */ 276 if (proc_role("sa_role") = 0) 277 begin 278 /* 279 ** 18127, "You must have System Administrator (SA) role 280 ** to execute this stored procedure." 281 */ 282 raiserror 18127 283 return (1) 284 end 285 286 select @old_value = int_value from sysattributes 287 where class = 2 and 288 attribute = 0 and 289 object = @objid 290 291 /* We found a matching entry in sysattributes */ 292 293 if @old_value != 0 and @old_value is not NULL 294 begin 295 if @new_value > 0 296 begin 297 298 select @action = 2 /* Denotes a change */ 299 300 /* If the previous Log I/O Size value is same as the 301 ** requested value do not do any thing. Otherwise update 302 ** the value. 303 */ 304 305 if @new_value = @old_value 306 begin 307 select @use_value = @old_value 308 end 309 else 310 begin 311 select @use_value = attrib_notify(2, 0, "T", 312 @objid, NULL, NULL, NULL, NULL, @new_value, 313 NULL, NULL, NULL, NULL, @action) 314 if @use_value = @new_value 315 begin 316 update sysattributes 317 set int_value = @new_value 318 where class = 2 and 319 attribute = 0 and 320 object = @objid 321 end 322 end 323 end 324 325 if @new_value = 0 /* Handles "default" value */ 326 begin 327 select @action = 3 /* Denotes a drop */ 328 select @use_value = attrib_notify(2, 0, "T", 329 @objid, NULL, NULL, NULL, NULL, @new_value, NULL, 330 NULL, NULL, NULL, @action) 331 /* Delete the entry from sysattributes */ 332 delete sysattributes 333 where class = 2 and 334 attribute = 0 and 335 object = @objid 336 end 337 338 end 339 340 else 341 342 /* There are no entries in the sysattributes table. */ 343 begin 344 /* We did not find a matching entry. So insert a new entry */ 345 if @new_value > 0 346 begin 347 /* 348 ** First check whether specified buffer pool exists. 349 ** It it does, then insert the row. 350 */ 351 352 select @action = 1 /* Denotes an addition of a row */ 353 select @use_value = attrib_notify(2, 0, "T", 354 @objid, NULL, NULL, NULL, NULL, @new_value, NULL, 355 NULL, NULL, NULL, @action) 356 if @use_value = @new_value 357 begin 358 /* Insert an entry in sysattributes */ 359 insert sysattributes(class, attribute, 360 object_type, object, int_value) 361 values (2, 0, 'T', @objid, @new_value) 362 end 363 end 364 365 if @new_value = 0 /* Handles "default" value */ 366 begin 367 /* 368 ** The action taken is same as @action = 3 above 369 ** except we dont have any row to delete. 370 */ 371 select @action = 3 372 select @use_value = attrib_notify(2, 0, "T", 373 @objid, NULL, NULL, NULL, NULL, @new_value, NULL, 374 NULL, NULL, NULL, @action) 375 end 376 end 377 end 378 379 /* 380 ** Process the return values from the attrib_notify built-in function. 381 ** @use_value = 0 denotes that sysattributes notification has failed. 382 */ 383 384 if (@use_value = 0) 385 begin 386 /* 387 ** 18130, "sp_logiosize failed. Please contact Sybase 388 ** Technical Support to help you resolve the problem." 389 */ 390 raiserror 18130 391 return (1) 392 end 393 394 /* 395 ** @action = 1 Qualified for an addition of row in sysattributes 396 ** @action = 2 Qualified for a change of row in sysattributes 397 ** @action = 3 Qualified for a deletion of row in sysattributes 398 ** @action = 4 Fetched a log I/O size value 399 */ 400 401 if (@action = 1) or (@action = 2) 402 begin 403 if @use_value = @new_value 404 begin 405 /* 406 ** 18129, "Log I/O size is set to %1! Kbytes." 407 ** 18132, "The transaction log for database '%1!' will use 408 ** I/O size of %2! Kbytes." 409 */ 410 exec sp_getmessage 18129, @msg output 411 print @msg, @new_value 412 exec sp_getmessage 18132, @msg output 413 print @msg, @dbname, @use_value 414 end 415 else 416 begin 417 /* 418 ** 18128, "Unable to change the log I/O size. 419 ** The buffer pool for the specified log I/O size 420 ** does not exist." 421 */ 422 raiserror 18128 423 return (1) 424 end 425 end 426 else if (@action = 3) 427 begin 428 /* 429 ** 18132, "The transaction log for database '%1!' will use 430 ** I/O size of %2! Kbytes." 431 */ 432 exec sp_getmessage 18132, @msg output 433 print @msg, @dbname, @use_value 434 end 435 else if (@action = 4) 436 begin 437 /* 438 ** 18129, "Log I/O size is set to %1! Kbytes." 439 */ 440 if @old_value != 0 and @old_value is not NULL 441 begin 442 /* 443 ** 18129, "Log I/O size is set to %1! Kbytes." 444 */ 445 exec sp_getmessage 18129, @msg output 446 print @msg, @old_value 447 end 448 /* 449 ** 18132, "The transaction log for database '%1!' will use 450 ** I/O size of %2! Kbytes." 451 */ 452 exec sp_getmessage 18132, @msg output 453 print @msg, @dbname, @use_value 454 end 455 else /* sp_logiosize failed */ 456 begin 457 /* 458 ** 18130, "sp_logiosize failed. Please contact Sybase 459 ** Technical Support to help you resolve the problem." 460 */ 461 raiserror 18130 462 return (1) 463 end 464
exec sp_procxmode 'sp_logiosize', 'AnyMode' go Grant Execute on sp_logiosize to public go
| DEFECTS | |
 MINU 4 Unique Index with nullable columns master..sysconfigures | master..sysconfigures |
| MINU 4 Unique Index with nullable columns sybsystemprocs..sysattributes | sybsystemprocs..sysattributes |
| QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered (number, type) Intersection: {type} | 252 |
| QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered (number, type) Intersection: {type} | 261 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 213 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 248 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 287 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 288 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 318 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 319 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 333 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 334 |
| TNOU 4 Table with no unique index master..spt_values | master..spt_values |
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause logiosize_cursor | 221 |
| MGTP 3 Grant to public master..spt_values | |
| MGTP 3 Grant to public master..sysconfigures | |
| MGTP 3 Grant to public master..sysdatabases | |
| MGTP 3 Grant to public sybsystemprocs..sp_logiosize | |
| MGTP 3 Grant to public sybsystemprocs..sysattributes | |
| MNER 3 No Error Check should check @@error after select into | 212 |
| MNER 3 No Error Check should check @@error after update | 316 |
| MNER 3 No Error Check should check @@error after delete | 332 |
| MNER 3 No Error Check should check @@error after insert | 359 |
| MNER 3 No Error Check should check return value of exec | 410 |
| MNER 3 No Error Check should check return value of exec | 412 |
| MNER 3 No Error Check should check return value of exec | 432 |
| MNER 3 No Error Check should check return value of exec | 445 |
| MNER 3 No Error Check should check return value of exec | 452 |
| MUCO 3 Useless Code Useless Brackets | 109 |
| MUCO 3 Useless Code Useless Brackets | 141 |
| MUCO 3 Useless Code Useless Brackets | 168 |
| MUCO 3 Useless Code Useless Brackets | 183 |
| MUCO 3 Useless Code Useless Brackets | 225 |
| MUCO 3 Useless Code Useless Brackets | 237 |
| MUCO 3 Useless Code Useless Brackets | 254 |
| MUCO 3 Useless Code Useless Brackets | 257 |
| MUCO 3 Useless Code Useless Brackets | 264 |
| MUCO 3 Useless Code Useless Brackets | 268 |
| MUCO 3 Useless Code Useless Brackets | 276 |
| MUCO 3 Useless Code Useless Brackets | 283 |
| MUCO 3 Useless Code Useless Brackets | 384 |
| MUCO 3 Useless Code Useless Brackets | 391 |
| MUCO 3 Useless Code Useless Brackets | 423 |
| MUCO 3 Useless Code Useless Brackets | 426 |
| MUCO 3 Useless Code Useless Brackets | 435 |
| MUCO 3 Useless Code Useless Brackets | 462 |
| MUOT 3 Updates outside transaction | 359 |
| QAFM 3 Var Assignment from potentially many rows | 251 |
| QAFM 3 Var Assignment from potentially many rows | 260 |
| QAFM 3 Var Assignment from potentially many rows | 286 |
| QCTC 3 Conditional Table Creation | 212 |
| QISO 3 Set isolation level | 187 |
| QIWC 3 Insert with not all columns specified missing 10 columns out of 15 | 359 |
| QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object, class, attribute} | 287 |
| QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object, class, attribute} | 318 |
| QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object, class, attribute} | 333 |
| VUNU 3 Variable is not used @retstat | 54 |
 CUPD 2 Updatable Cursor Marker (updatable by default) | 221 |
| MTR1 2 Metrics: Comments Ratio Comments: 50% | 45 |
| MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 29 = 39dec - 12exi + 2 | 45 |
| MTR3 2 Metrics: Query Complexity Complexity: 172 | 45 |
| DEPENDENCIES |
| PROCS AND TABLES USED read_writes table tempdb..#syscacheconfig (1) reads table master..spt_values (1)  calls proc sybsystemprocs..sp_getmessage  reads table sybsystemprocs..sysusermessages reads table master..sysmessages (1) reads table master..syslanguages (1) calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) read_writes table sybsystemprocs..sysattributes reads table master..sysdatabases (1) reads table master..sysconfigures (1) |