DatabaseProcApplicationCreatedLinks
sybsystemprocssp_logiosize  14 déc. 14Defects Propagation 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 or replace 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            , @nullarg char(1)
72            , @dummy int
73            , @status int
74            , @gp_enabled int
75    
76    
77        /* 
78        ** Extract the I/O size value from the input.
79        */
80        select @tempstr = lower(ltrim(rtrim(@size)))
81    
82        if @tempstr is NULL
83        begin
84            select @new_value = NULL
85        end
86        else
87        begin
88    
89            /* 
90            ** Check whether the input value has correct unit k or K.
91            ** Parse the input string.
92            */
93            select @unit_loc = patindex("%[kK]%", @size)
94    
95            /* If the unit is specified, check if it is the last char
96            ** in the string, input like "2kk" should raise an error.
97            ** If the input is correct, grap the digit string and put
98            ** it into @tempstr.
99            */
100           if @unit_loc != 0
101           begin
102               select @size_len = char_length(@size)
103               if @size_len = @unit_loc
104               begin
105                   select @tempstr = substring(@size, 1, @unit_loc - 1)
106               end
107   
108               /* 
109               ** 18131, "Log I/O Size value '%1!' is illegal."
110               */
111               else
112               begin
113                   raiserror 18131, @size
114                   return (1)
115               end
116           end
117   
118           /* Check whether the input value is an integer */
119           if @tempstr like "[0-9]%"
120           begin
121               select @new_value = convert(int, @tempstr)
122               /* 
123               ** Make sure the log I/O size is a power of 2 and is 
124               ** between a logical pagesize to size of 8 logical pages.
125               */
126   
127               select @tempiosize = 2
128               while @tempiosize < @new_value
129                   select @tempiosize = @tempiosize * 2
130               if @tempiosize != @new_value
131                   or @tempiosize > 8 * @@maxpagesize / 1024
132                   or @tempiosize < @@maxpagesize / 1024
133               begin
134                   /* 18126, "Log I/O size must be a power of 2, and is
135                   ** between a logical pagesize to size of 8 logical 
136                   ** pages. For example, if the logical pagesize is
137                   ** %1!K, the log I/O size should be %2!, %3!, %4!,
138                   ** or %5![k|K]." 
139                   */
140                   select @logicalpgsz1 = @@maxpagesize / 1024
141                   select @logicalpgsz2 = 2 * @logicalpgsz1
142                   select @logicalpgsz3 = 4 * @logicalpgsz1
143                   select @logicalpgsz4 = 8 * @logicalpgsz1
144                   raiserror 18126, @logicalpgsz1, @logicalpgsz1,
145                       @logicalpgsz2, @logicalpgsz3, @logicalpgsz4
146                   return (1)
147               end
148           end
149           else
150           /* Process the value as a character string */
151           begin
152               /* Check whether the input value is "default" */
153               if @tempstr = "default"
154               begin
155                   select @new_value = 0
156               end
157               else if @tempstr = "all"
158               begin
159                   /* 
160                   ** There is no sanctity about this value. We just
161                   ** want to differentiate this value from other
162                   ** valid values. This indicates that user wants
163                   ** log I/O size listing for all data bases.
164                   */
165                   select @new_value = 99
166               end
167               else
168               begin
169                   /* 
170                   ** 18131, "Log I/O Size value '%1!' is illegal." 
171                   */
172                   raiserror 18131, @size
173                   return (1)
174               end
175           end
176       end
177   
178       /*
179       ** Don't allow this in a transaction because we can't undo what 
180       ** we have done through the attr_notify() built-in.
181       */
182       if @@trancount > 0
183       begin
184           /*
185           ** 17260, "Can't run %1! from with in a transaction."
186           */
187           raiserror 17260, "sp_logiosize"
188           return (1)
189       end
190       else
191       begin
192           set transaction isolation level 1
193           set chained off
194       end
195   
196       select @objid = object_id("syslogs")
197       select @dbname = db_name()
198       select @use_value = 0
199   
200       /* 
201       ** If the input value is NULL, print the log I/O size set for the current
202       ** database.
203       */
204       if @new_value is NULL
205       begin
206           /* 
207           ** Print the log I/O size set for the current database 
208           */
209           select @action = 4 /* Denotes fetching a value */
210           select @use_value = attrib_notify(2, 0, "T", @objid, NULL, NULL,
211                   NULL, NULL, @new_value, NULL, NULL,
212                   NULL, NULL, @action)
213       end
214       else if @new_value = 99
215       begin
216           /* List log I/O size values for all the data bases */
217           select name into #syscacheconfig from master.dbo.sysconfigures
218           where parent = 19 and config = 19
219   
220           /*
221           **  Find out the number of rows we want to look at.
222           */
223           select @row_count = count(*) from #syscacheconfig
224   
225           declare logiosize_cursor cursor
226           for select name from #syscacheconfig
227   
228           open logiosize_cursor
229   
230           while (@row_count > 0)
231           begin
232               fetch logiosize_cursor into @name
233               print "Cache name: %1!", @name
234               print "Data base                       Log I/O Size"
235               print "------------------------------  ------------"
236               select @cstat = config_admin(9, 4, 0, 0, NULL, @name)
237               print " "
238               select @row_count = @row_count - 1
239           end
240   
241           close logiosize_cursor
242           return (0)
243       end
244       /* 
245       ** Process other input values.  Look for a matching row in the 
246       ** sysattributes table for the Log I/O Size attribute.
247       */
248       else
249       begin
250   
251           select @status4 = status4, @status3 = status3
252           from master..sysdatabases
253           where dbid = db_id()
254   
255           /* Don't let sp_logiosize to be run on an in-memory database. */
256           select @imdbstat = number from master..spt_values
257           where name = "in-memory database" and type = "D4"
258   
259           if (@status4 & @imdbstat != 0)
260           begin
261               raiserror 19979, "sp_logiosize", @dbname
262               return (1)
263           end
264   
265           select @adbstat = number from master.dbo.spt_values
266           where name = "archive database" and type = "D3"
267   
268           /* Do not allow sp_logiosize to be run on an archive database. */
269           if (@status3 & @adbstat != 0)
270           begin
271               /* Cannot run '%1!' on an archive database." */
272               raiserror 19424, "sp_logiosize"
273               return (1)
274           end
275   
276           /* 
277           ** If granular permissions is not enabled then sa_role is required.
278           ** If granular permissions is enabled then the permission 
279           ** 'manage data cache' is required.  proc_role and proc_auditperm will 
280           ** also do auditing if required. Both will also print error message if 
281           ** required.
282           */
283   
284           select @nullarg = NULL
285           execute @status = sp_aux_checkroleperm "sa_role", "manage data cache",
286               @nullarg, @gp_enabled output
287   
288           /* For Auditing */
289           if (@gp_enabled = 0)
290           begin
291               if (proc_role("sa_role") = 0)
292                   return (1)
293           end
294           else
295           begin
296               select @dummy = proc_auditperm("manage data cache", @status)
297           end
298   
299           if (@status != 0)
300               return (1)
301   
302           select @old_value = int_value from sysattributes
303           where class = 2 and
304               attribute = 0 and
305               object = @objid
306   
307           /* We found a matching entry in sysattributes */
308   
309           if @old_value != 0 and @old_value is not NULL
310           begin
311               if @new_value > 0
312               begin
313   
314                   select @action = 2 /* Denotes a change */
315   
316                   /* If the previous Log I/O Size value is same as the 
317                   ** requested value do not do any thing. Otherwise update
318                   ** the value.
319                   */
320   
321                   if @new_value = @old_value
322                   begin
323                       select @use_value = @old_value
324                   end
325                   else
326                   begin
327                       select @use_value = attrib_notify(2, 0, "T",
328                               @objid, NULL, NULL, NULL, NULL, @new_value,
329                               NULL, NULL, NULL, NULL, @action)
330                       if @use_value = @new_value
331                       begin
332                           update sysattributes
333                           set int_value = @new_value
334                           where class = 2 and
335                               attribute = 0 and
336                               object = @objid
337                       end
338                   end
339               end
340   
341               if @new_value = 0 /* Handles "default" value */
342               begin
343                   select @action = 3 /* Denotes a drop */
344                   select @use_value = attrib_notify(2, 0, "T",
345                           @objid, NULL, NULL, NULL, NULL, @new_value, NULL,
346                           NULL, NULL, NULL, @action)
347                   /* Delete the entry from sysattributes */
348                   delete sysattributes
349                   where class = 2 and
350                       attribute = 0 and
351                       object = @objid
352               end
353   
354           end
355   
356           else
357   
358           /* There are no entries in the sysattributes table. */
359           begin
360               /* We did not find a matching entry.  So insert a new entry */
361               if @new_value > 0
362               begin
363                   /* 
364                   ** First check whether specified buffer pool exists.
365                   ** It it does, then insert the row.
366                   */
367   
368                   select @action = 1 /* Denotes an addition of a row */
369                   select @use_value = attrib_notify(2, 0, "T",
370                           @objid, NULL, NULL, NULL, NULL, @new_value, NULL,
371                           NULL, NULL, NULL, @action)
372                   if @use_value = @new_value
373                   begin
374                       /* Insert an entry in sysattributes */
375                       insert sysattributes(class, attribute,
376                           object_type, object, int_value)
377                       values (2, 0, 'T', @objid, @new_value)
378                   end
379               end
380   
381               if @new_value = 0 /* Handles "default" value */
382               begin
383                   /* 
384                   ** The action taken is same as @action = 3 above
385                   ** except we dont have any row to delete.
386                   */
387                   select @action = 3
388                   select @use_value = attrib_notify(2, 0, "T",
389                           @objid, NULL, NULL, NULL, NULL, @new_value, NULL,
390                           NULL, NULL, NULL, @action)
391               end
392           end
393       end
394   
395       /* 
396       ** Process the return values from the attrib_notify built-in function.
397       ** @use_value = 0 denotes that sysattributes notification has failed.
398       */
399   
400       if (@use_value = 0)
401       begin
402           /*
403           ** 18130, "sp_logiosize failed.  Please contact Sybase 
404           ** Technical Support to help you resolve the problem."
405           */
406           raiserror 18130
407           return (1)
408       end
409   
410       /*
411       ** @action = 1  Qualified for an addition of row in sysattributes 
412       ** @action = 2  Qualified for a change of row in sysattributes 
413       ** @action = 3  Qualified for a deletion of row in sysattributes 
414       ** @action = 4  Fetched a log I/O size value 
415       */
416   
417       if (@action = 1) or (@action = 2)
418       begin
419           if @use_value = @new_value
420           begin
421               /*
422               ** 18129, "Log I/O size is set to %1! Kbytes."
423               ** 18132, "The transaction log for database '%1!' will use 
424               ** 	   I/O size of %2! Kbytes."
425               */
426               exec sp_getmessage 18129, @msg output
427               print @msg, @new_value
428               exec sp_getmessage 18132, @msg output
429               print @msg, @dbname, @use_value
430           end
431           else
432           begin
433               /*
434               ** 18128, "Unable to change the log I/O size. 
435               ** The buffer pool for the specified log I/O size 
436               ** does not exist." 
437               */
438               raiserror 18128
439               return (1)
440           end
441       end
442       else if (@action = 3)
443       begin
444           /*
445           ** 18132, "The transaction log for database '%1!' will use 
446           **         I/O size of %2! Kbytes."
447           */
448           exec sp_getmessage 18132, @msg output
449           print @msg, @dbname, @use_value
450       end
451       else if (@action = 4)
452       begin
453           /*
454           ** 18129, "Log I/O size is set to %1! Kbytes."
455           */
456           if @old_value != 0 and @old_value is not NULL
457           begin
458               /*
459               ** 18129, "Log I/O size is set to %1! Kbytes."
460               */
461               exec sp_getmessage 18129, @msg output
462               print @msg, @old_value
463           end
464           /*
465           ** 18132, "The transaction log for database '%1!' will use 
466           **         I/O size of %2! Kbytes."
467           */
468           exec sp_getmessage 18132, @msg output
469           print @msg, @dbname, @use_value
470       end
471       else /* sp_logiosize failed */
472       begin
473           /*
474           ** 18130, "sp_logiosize failed.  Please contact Sybase 
475           ** Technical Support to help you resolve the problem."
476           */
477           raiserror 18130
478           return (1)
479       end
480   


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}
257
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
266
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 218
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 253
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 303
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 304
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 334
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 335
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 349
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 350
 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 226
 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 217
 MNER 3 No Error Check should check @@error after update 332
 MNER 3 No Error Check should check @@error after delete 348
 MNER 3 No Error Check should check @@error after insert 375
 MNER 3 No Error Check should check return value of exec 426
 MNER 3 No Error Check should check return value of exec 428
 MNER 3 No Error Check should check return value of exec 448
 MNER 3 No Error Check should check return value of exec 461
 MNER 3 No Error Check should check return value of exec 468
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 146
 MUCO 3 Useless Code Useless Brackets 173
 MUCO 3 Useless Code Useless Brackets 188
 MUCO 3 Useless Code Useless Brackets 230
 MUCO 3 Useless Code Useless Brackets 242
 MUCO 3 Useless Code Useless Brackets 259
 MUCO 3 Useless Code Useless Brackets 262
 MUCO 3 Useless Code Useless Brackets 269
 MUCO 3 Useless Code Useless Brackets 273
 MUCO 3 Useless Code Useless Brackets 289
 MUCO 3 Useless Code Useless Brackets 291
 MUCO 3 Useless Code Useless Brackets 292
 MUCO 3 Useless Code Useless Brackets 299
 MUCO 3 Useless Code Useless Brackets 300
 MUCO 3 Useless Code Useless Brackets 400
 MUCO 3 Useless Code Useless Brackets 407
 MUCO 3 Useless Code Useless Brackets 439
 MUCO 3 Useless Code Useless Brackets 442
 MUCO 3 Useless Code Useless Brackets 451
 MUCO 3 Useless Code Useless Brackets 478
 MUOT 3 Updates outside transaction 375
 QAFM 3 Var Assignment from potentially many rows 256
 QAFM 3 Var Assignment from potentially many rows 265
 QAFM 3 Var Assignment from potentially many rows 302
 QCTC 3 Conditional Table Creation 217
 QISO 3 Set isolation level 192
 QIWC 3 Insert with not all columns specified missing 10 columns out of 15 375
 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}
303
 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}
334
 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}
349
 VNRD 3 Variable is not read @dummy 296
 VUNU 3 Variable is not used @retstat 54
 CUPD 2 Updatable Cursor Marker (updatable by default) 226
 MTR1 2 Metrics: Comments Ratio Comments: 48% 45
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 31 = 42dec - 13exi + 2 45
 MTR3 2 Metrics: Query Complexity Complexity: 179 45

DATA PROPAGATION detailed
ColumnWritten To
@sizesysattributes.int_value   °.char_value   sp_passwordpolicy_rset_002.value sp_passwordpolicy_rset_003.value sp_passwordpolicy_rset_004.Policy_option sp_ssladmin_rset_001.certificate_path sp_ssladmin_rset_002.Cipher Suite Name °.Preference

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
reads table master..spt_values (1)  
reads table master..sysconfigures (1)  
read_writes table sybsystemprocs..sysattributes  
reads table master..sysdatabases (1)  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
read_writes table tempdb..#syscacheconfig (1)