DatabaseProcApplicationCreatedLinks
sybsystemprocssp_do_poolconfig  14 déc. 14Defects Propagation Dependencies

1     
2     /* This stored procedure is for configuring buffer pools. This is an internal
3     ** stored procedure calle by either sp_poolconfig or from recovery process
4     ** directly.
5     **
6     **	Messages for sp_do_poolconfig
7     **
8     ** 17260, "Can't run %1 from within a transaction."
9     **
10    ** 18076, "Could not set curwrite to object level. Set your maxwrite 
11    **	   label correctly."
12    **
13    ** 18095, "The destination buffer pool size must be a minimum of %1!
14    **	   kilobytes."
15    **
16    ** 18138, "A cache name must be supplied."
17    **
18    ** 18135, "The specified named cache (%1) does not exist."
19    **
20    ** 18141, "Syntax error encountered. Specification of the wash size must be 
21    **	   of the form 'wash = %d[PKMG]'
22    **
23    ** 18142, "I/O size of the memory pool is expected as the third argument."
24    **
25    ** 18143, "The source pool (%1k buffers, total size %2k) is not large enough 
26    **	   to satisfy the request to move %3k of memory.
27    **
28    ** 18144, "Source pool (%1k) and destination pool (%2k) are the same pool.  
29    **	   The source and destination pools must be different."
30    **
31    ** 18147, "Invalid buffer size of %1! encountered. Valid buffer sizes are 
32    **	   powers of two between %2! and %3! kilobytes inclusive."
33    **
34    ** 18148, "The specified pool (%1!) does not exist in named cache '%2!'."
35    **
36    ** 18558, "Unable to delete the '%1!' pool in cache '%2!'."
37    **
38    ** 18559, "This pool has been marked unavailable. You can
39    **	   remove it completely by retrying sp_poolconfig when
40    **	   the cache is not in use or by restarting the Server."
41    ** 
42    ** 18560, "Unable to move '%1!' from the '%2!' pool to the '%3!' pool in
43    **	   cache '%4!'. Only '%5!' moved."
44    ** 
45    ** 18339, "The '%1!' pool has been marked as being unavailable since it is
46    **	   smaller than the minimum pool size of %2! kilobytes."
47    ** 
48    ** 18562, "You can size the pools correctly by retrying sp_poolconfig 
49    **	   when the cache is not in use or by restarting the Server."
50    ** 
51    ** 19111, "Cannot change the pool configurations in the '%1!' when the server is
52    **	   in recovery."
53    **
54    ** 19595, "Instance '%1!' does not exist."
55    **
56    ** 19973, "Configuring large I/O pools for in-memory storage cache '%1' is
57    **	  not allowed." 
58    */
59    
60    create or replace procedure sp_do_poolconfig
61        @cachename varchar(255) = NULL,
62        @parm1 varchar(30) = NULL,
63        @parm2 varchar(40) = NULL,
64        @parm3 varchar(30) = NULL,
65        @is_in_recovery varchar(30) = NULL,
66        @parm4 varchar(255) = NULL
67    as
68    
69        declare @equal_sign_loc int /* for parsing parms */
70        declare @wash_or_apf_val_loc int /* for parsing parms */
71        declare @wash_or_apf_size int /* user supplied wash size or '
72        ** apf percent
73        */
74        declare @wash_delta_size int /* delta of the wash size */
75        declare @src_buf_size int /* size of bufs in src pool */
76        declare @dest_buf_size int /* size of bufs in dest pool */
77        declare @config_num_src int /* config # for src pool */
78        declare @config_num_dest int /* config # for dest pool */
79        declare @temp_size int /* temporary variable */
80        declare @requested_size int /* requested total size of dest pool */
81        declare @existing_pool_size int /* exisiting size of dest pool */
82        declare @curr_pool_size int /* current exisiting size of dest pool */
83        declare @stat int /* status of cache from sysconfigures */
84        declare @stat1 int /* status of cache from sysconfigures */
85        declare @stat2 int /* status of cache from sysconfigures */
86        declare @mem_to_move int /* amount of mem to move */
87        declare @inst_mem_to_move int /* amount of mem to move at an instance */
88        declare @mem_moved int /* Memory that should have been moved */
89        declare @src_pool_size int /* amount of mem in src pool */
90        declare @dest_pool_size int /* amount of mem in dest pool */
91        declare @ret int /* return status from sp_aux_getsize */
92        declare @result_size int /* resulting size of dest pool after 
93        ** config_admin call.
94        */
95        declare @delete_pool int /* 1 if src pool 2 if dest pool */
96        declare @temp_float float /* for temp float calculations */
97        declare @temp_value int /* for temp int calculations */
98        declare @temp_pool_size int /* for temp pool size value */
99        declare @dest_buf_sz_str varchar(30) /* size of bufs in dest pool */
100       declare @src_buf_sz_str varchar(30) /* size of bufs in src pool */
101       declare @dest_pool_sz_str varchar(30) /* total mem in dest pool */
102       declare @res_sz_str varchar(30) /* str value of result size */
103       declare @req_sz_str varchar(30) /* str value of requested size */
104       declare @temp_str varchar(30) /* scratch pad */
105       declare @wash_or_apf_str varchar(255) /* wash size or apf percentage */
106       declare @msg varchar(255)
107       declare @config_file_name varchar(255)
108       declare @min_buf_size int
109       declare @max_buf_size int
110       declare @rec_state varchar(30)
111       declare @instanceid int
112       declare @instancename varchar(255)
113       declare @cur_instanceid int
114       declare @cur_instancename varchar(255)
115       declare @instance_count int
116       declare @failure_count int
117       declare @total_pool_size int
118       declare @cache_size int
119       declare @i int /* iterator */
120       declare @nullarg char(1)
121       declare @dummy int
122       declare @status int /* Status of role or permission */
123       declare @gp_enabled int /* Is granular permissions enabled. */
124       declare @cha_area varchar(10) /* for configuration history auditing */
125       declare @cha_type varchar(30)
126       declare @cha_target varchar(30)
127       declare @cha_element varchar(255)
128       declare @cha_oldvalue varchar(255)
129       declare @cha_newvalue varchar(255)
130       declare @cha_mode varchar(10)
131       declare @cha_instanceid int
132       declare @cha_ret int
133       declare @current_wash_size int
134       declare @new_wash_size int
135       declare @current_apf_percent int
136       declare @new_apf_percent int
137   
138   
139       /* declare and init the config_admin() commands used in this sproc */
140       declare @cmdbindingcheck int
141           , @cmdsetwashsize int
142           , @cmdpoolconfig int
143           , @cmdsetapfsize int
144           , @cmdupdateconfigfile int
145   
146       select @cmdbindingcheck = 9
147           , @cmdsetwashsize = 10
148           , @cmdpoolconfig = 11
149           , @cmdsetapfsize = 17
150           , @cmdupdateconfigfile = 32
151   
152       /* declare and init the config options used in this sproc */
153       declare @cfguserdefinedcache int
154           , @cfgbuffer2kpoolgrp int
155           , @cfgcfgfilename int
156   
157       select @cfguserdefinedcache = 19
158           , @cfgbuffer2kpoolgrp = 20
159           , @cfgcfgfilename = 114
160   
161       select @wash_or_apf_str = NULL
162   
163       if @@trancount > 0
164       begin
165           raiserror 17260, "sp_poolconfig"
166           return 1
167       end
168       else
169       begin
170           set chained off
171       end
172   
173       set transaction isolation level 1
174       set nocount on
175   
176       if @cachename is NULL
177       begin
178           raiserror 18138
179           return 1
180       end
181   
182       /*
183       **  If the cache name is the only parameter that's supplied just pass it 
184       **  along to sp_cacheconfig and have it print out all of the configured pools
185       **  for the cache.
186       */
187       if (@parm1 is NULL) and (@parm2 is NULL) and (@parm3 is NULL)
188       begin
189           exec sp_cacheconfig @cachename
190           return 0
191       end
192       else if (patindex("instance %", @parm1) != 0) and (@parm2 is NULL) and (@parm3 is NULL)
193       begin
194           exec sp_cacheconfig @cachename, @parm1
195           return 0
196       end
197   
198       /* 
199       ** If granular permissions is not enabled then sa_role is required.
200       ** If granular permissions is enabled then the permission 'manage data cache' is
201       ** required.  proc_role and proc_auditperm will also do auditing
202       ** if required. Both will also print error message if required.
203       */
204   
205       select @nullarg = NULL
206       execute @status = sp_aux_checkroleperm "sa_role", "manage data cache",
207           @nullarg, @gp_enabled output
208   
209       /* For Auditing */
210       if (@gp_enabled = 0)
211       begin
212           if (proc_role("sa_role") = 0)
213               return (1)
214       end
215       else
216       begin
217           select @dummy = proc_auditperm("manage data cache", @status)
218       end
219   
220       if (@status != 0)
221           return (1)
222   
223       /*
224       ** If we are in the middle of boot time recovery, and not called by recovery,
225       ** changing configuration in default data cache is not allowed.
226       */
227       select @rec_state = @@recovery_state
228       if ((@rec_state not like "NOT_IN_RECOVERY%")
229               and (@is_in_recovery not in ("TRUE", "true")))
230       begin
231           if (patindex("%default data cache%", @cachename) != 0)
232           begin
233               raiserror 19111, @cachename
234               return 1
235           end
236       end
237   
238       select @instancename = NULL
239       select @instanceid = NULL
240   
241   
242   
243       select @dest_pool_sz_str = @parm1
244       select @min_buf_size = @@maxpagesize / 1024
245       select @max_buf_size = @min_buf_size * 8
246   
247       /*
248       **  Parm2 may be of the form "wash = %d" or %d [K | M | G]  
249       **  Or
250       **  Parm2 may be of the form "local async prefetch limit=%d" 
251       */
252       if (patindex("%wash%", @parm2) != 0) or (patindex("%local async%", @parm2) != 0)
253       begin -- {
254           /*
255           **  Translate destination buffer pool into units of k
256           */
257           exec @ret = sp_aux_getsize @dest_pool_sz_str, @dest_buf_size output
258   
259           if @ret = 0
260           begin
261               raiserror 18147, @dest_pool_sz_str, @min_buf_size, @max_buf_size
262               return 1
263           end
264   
265           select @equal_sign_loc = 0
266           select @equal_sign_loc = patindex("%=%", @parm2)
267           /*
268           **  proper syntax for specifying the wash is "wash = %d". Make sure 
269           **  the string follows this syntax.
270           */
271           if @equal_sign_loc = 0
272           begin
273               raiserror 18141
274               return 1
275           end
276   
277           select @wash_or_apf_val_loc = patindex("%[0-9]%", @parm2)
278           select @wash_or_apf_str = convert(varchar(255), substring(@parm2,
279                   @wash_or_apf_val_loc,
280                   char_length(@parm2) -
281                   @wash_or_apf_val_loc + 1))
282   
283           /* sp_aux_get_size will take string of the form "100 [kmpg]" */
284           exec @ret = sp_aux_getsize @wash_or_apf_str, @wash_or_apf_size output
285           if @ret = 0
286           begin
287               raiserror 18141
288               return 1
289           end
290   
291           /*
292           ** First sanity check the destination buffer size to make sure it's 
293           ** within the legal range i.e., powers of 2 between a logical page 
294           ** to 8 logical pages.
295           */
296           if (@dest_buf_size < @min_buf_size) or (@dest_buf_size > @max_buf_size)
297           begin
298               raiserror 18147, @dest_pool_sz_str, @min_buf_size, @max_buf_size
299               return 1
300           end
301   
302           select @temp_size = 2
303           while @temp_size < @dest_buf_size
304               select @temp_size = @temp_size * 2
305           if @temp_size != @dest_buf_size
306           begin
307               raiserror 18147, @dest_pool_sz_str, @min_buf_size, @max_buf_size
308               return 1
309           end
310   
311           /*
312           **  Config numbers in sysconfigures start at 20 for the 2k buffer pool,
313           **  21 for the 4k buffer pool, 22 for the 8k buffer pool and so on.
314           **  Basically if we add log base 2 of the destination buffer size to
315           **  the base of 20 and subtract one we should get the correct config 
316           **  number to index into sysconfigures.
317           */
318           select @config_num_dest
319               = @cfgbuffer2kpoolgrp
320               + (log10(@dest_buf_size * 1024 / @@pagesize) / log10(2))
321   
322           /* Parm3 may contain instance specific information */
323           if (charindex("instance ", @parm3) = 1)
324           begin
325               select @instancename = substring(@parm3, 10,
326                       char_length(@parm3) - 9)
327               select @instanceid = instance_id(@instancename)
328   
329               if @instanceid is NULL
330               begin
331                   raiserror 19595, @instancename
332                   return 1
333               end
334           end
335   
336           /* Check to see if the cache exists */
337           select @stat = - 1
338           select @stat = status
339           from master.dbo.sysconfigures
340           where name = @cachename
341               and parent = @cfguserdefinedcache
342               and config = @cfguserdefinedcache
343               and status & 128 != 128
344   
345   
346           if @stat = - 1
347           begin
348               raiserror 18135, @cachename
349               return 1
350           end
351   
352           /* Disallow configuring large I/O pool for in-memory storage cache. */
353           if (@stat & 65536 = 65536)
354           begin
355               raiserror 19973, @cachename
356               return 1
357           end
358   
359   
360   
361           begin
362               select @instance_count = 1
363           end
364   
365           select @failure_count = 0
366   
367   
368   
369           select @i = @instance_count
370           while (@i > 0)
371           begin -- {
372   
373   
374               select @temp_size = memory_used
375               from master.dbo.syscurconfigs
376               where config = @config_num_dest
377                   and comment = @cachename
378   
379   
380               /*
381               ** Check if there is an instance specific config for instance
382               ** @cur_instanceid
383               */
384               select @stat = - 1
385               select @stat = status
386               from master.dbo.sysconfigures
387               where name = @cachename
388                   and parent = @cfguserdefinedcache
389                   and config = @cfguserdefinedcache
390   
391   
392               /* 
393               ** Skip the instance if there exists an instance specific
394               ** configuration and the requested operation is global
395               */
396               if (@instanceid is NULL) and (@stat != 1) and (@@instanceid != NULL)
397               begin
398                   select @i = @i - 1
399                   continue
400               end
401   
402               /* If the pool doesn't exist it's an error. */
403               if @temp_size = 0
404               begin
405   
406                   raiserror 18148, @dest_pool_sz_str, @cachename
407                   return 1
408               end
409               select @i = @i - 1
410           end -- }
411   
412   
413   
414           /* if parm2 is wash related then do the following */
415           if patindex("%wash%", @parm2) != 0
416           begin -- {
417               if @@clustermode != 'shared disk cluster'
418               begin
419                   select @wash_delta_size = @wash_or_apf_size - @temp_size
420               end
421   
422               /*
423               ** In syscurconfigs, we obtain the wash size in Kilobytes.
424               ** However, we store the wash size, in sysconfigures, in
425               ** kilobytes/Buffer pool size.
426               ** Note: we are reusing the variable 'temp_size' here.
427               */
428               select @temp_size = @wash_or_apf_size / @dest_buf_size
429   
430               /*
431               ** If due to round off error, we get a wash size of 0,
432               ** even if it was not specified as the input, we will pass a 
433               ** non-zero invalid value to the update. This will be 
434               ** caught by the config manager and this update will 
435               ** rolled back later.This can happen, if the wash size
436               ** specified is not zero, but gets rounded of to 
437               ** zero. The wash size 0 is a special value.
438               */
439               if @temp_size = 0 and @wash_or_apf_size != 0
440                   select @temp_size = - 1
441   
442               /*
443               ** Save the current wash size for later configuration history
444               ** auditing.
445               */
446               select @current_wash_size = value3
447               from master.dbo.sysconfigures
448               where config = @config_num_dest
449                   and name = @cachename
450   
451   
452               begin tran set_wash
453   
454               update master.dbo.sysconfigures
455               set value3 = @temp_size
456               where config = @config_num_dest
457                   and name = @cachename
458   
459   
460               /*
461               ** In SDC, we consider named cache creation as success even if
462               ** some instance allocated memory partially only. Due to this
463               ** pools may have different run sizes and hence wash sizes.
464               ** Hence, we can't determine a unique value to change at stored
465               ** procedure level. Hence, we pass total wash value for SDC.
466               **
467               ** For SMP we need to pass 'delta' value of wash size.
468               */
469   
470               select @stat = config_admin(@cmdsetwashsize,
471                       @dest_buf_size,
472                       @wash_delta_size, 0,
473                       @instancename, @cachename)
474   
475               if @stat = 0
476               begin
477                   print "Failed to set '%1!' to '%2!' for '%3!' pool in cache '%4!'. Check the error logs for more information.", 'wash size',
478   
479                       @wash_delta_size
480   
481                       , @dest_pool_sz_str, @cachename
482                   rollback tran set_wash
483                   return 1
484               end
485   
486               commit tran set_wash
487   
488               /*
489               ** The effective wash size may be rounded, and not equal to the
490               ** config size exactly. Thus, we retrieve the effective wash
491               ** size from sysconfigures for configuration history auditing.
492               */
493               select @new_wash_size = value3
494               from master.dbo.sysconfigures
495               where config = @config_num_dest
496                   and name = @cachename
497   
498               if @new_wash_size != @current_wash_size
499               begin
500                   select @cha_area = "CACHE"
501                   select @cha_type = "sp_poolconfig"
502                   select @cha_target = @cachename
503                   select @cha_element = "config pool: " +
504                       @dest_pool_sz_str + ", option: wash size"
505                   select @cha_oldvalue = convert(varchar(255),
506                       @current_wash_size)
507                   select @cha_newvalue = convert(varchar(255),
508                       @new_wash_size)
509                   select @cha_mode = NULL
510                   select @cha_instanceid = @instanceid
511   
512                   select @cha_ret = audit_config_history(
513                           @cha_area,
514                           @cha_type,
515                           @cha_target,
516                           @cha_element,
517                           @cha_oldvalue,
518                           @cha_newvalue,
519                           @cha_mode,
520                           @cha_instanceid)
521               end
522   
523               return 0
524           end -- }
525   
526           /* if parm2 is apf related do the following */
527           if patindex("%local async%", @parm2) != 0
528           begin
529               /*
530               ** Save the current apf percent for later configuration history
531               ** auditing.
532               */
533               select @current_apf_percent = value4
534               from master.dbo.sysconfigures
535               where config = @config_num_dest
536                   and name = @cachename
537   
538   
539               begin tran set_apf
540   
541               update master.dbo.sysconfigures
542               set value4 = @wash_or_apf_size
543               where config = @config_num_dest
544                   and name = @cachename
545   
546               select @stat = config_admin(@cmdsetapfsize, @dest_buf_size,
547                       @wash_or_apf_size, 0, @instancename,
548                       @cachename)
549   
550               if @stat = 0
551               begin
552                   print "Failed to set '%1!' to '%2!' for '%3!' pool in cache '%4!'. Check the error logs for more information.", 'APF size', @wash_or_apf_size, @dest_pool_sz_str, @cachename
553                   rollback tran set_apf
554                   return 1
555               end
556   
557               commit tran set_apf
558   
559               /*
560               ** Like the wash size, we retrieve the effective apf percent
561               ** from the sysconfigures for configuration history auditing.
562               */
563               select @new_apf_percent = value4
564               from master.dbo.sysconfigures
565               where config = @config_num_dest
566                   and name = @cachename
567   
568               if @new_apf_percent != @current_apf_percent
569               begin
570                   select @cha_area = "CACHE"
571                   select @cha_type = "sp_poolconfig"
572                   select @cha_target = @cachename
573                   select @cha_element = "config pool: " +
574                       @dest_pool_sz_str +
575                       ", option: local async prefetch limit"
576                   select @cha_oldvalue = convert(varchar(255),
577                       @current_apf_percent)
578                   select @cha_newvalue = convert(varchar(255),
579                       @new_apf_percent)
580                   select @cha_mode = NULL
581                   select @cha_instanceid = @instanceid
582   
583                   select @cha_ret = audit_config_history(
584                           @cha_area,
585                           @cha_type,
586                           @cha_target,
587                           @cha_element,
588                           @cha_oldvalue,
589                           @cha_newvalue,
590                           @cha_mode,
591                           @cha_instanceid)
592               end
593   
594               return 0
595           end
596       end -- }
597   
598       /*
599       **  If we make it here then the command must be of the form:
600       **		sp_poolconfig "foo", "100M", "16k", "instance "
601       **  We'll check against that syntax here.
602       */
603       if @parm2 is NULL
604       begin
605           raiserror 18142
606           return 1
607       end
608   
609       select @dest_buf_sz_str = @parm2
610   
611       if @parm3 is NULL
612       begin
613           select @src_buf_sz_str = "1p"
614           select @instancename = NULL
615           select @instanceid = NULL
616       end
617       else
618       begin -- {
619           /* @parm3 may contain instance specific information. */
620           if (charindex("instance ", @parm3) = 1)
621           begin
622               select @instancename = substring(@parm3, 10,
623                       char_length(@parm3) - 9)
624               select @instanceid = instance_id(@instancename)
625   
626               if @instanceid is NULL
627               begin
628                   raiserror 19595, @instancename
629                   return 1
630               end
631   
632               select @src_buf_sz_str = "1p"
633           end
634           else
635           begin
636               if (charindex("instance ", @parm4) = 1)
637               begin
638                   select @instancename = substring(@parm4, 10,
639                           char_length(@parm4) - 9)
640                   select @instanceid = instance_id(@instancename)
641   
642                   if @instanceid is NULL
643                   begin
644                       raiserror 19595, @instancename
645                       return 1
646                   end
647               end
648               else
649               begin
650                   select @instancename = NULL
651                   select @instanceid = NULL
652               end
653   
654               select @src_buf_sz_str = @parm3
655           end
656       end -- }
657   
658       /* Check to see if the cache exists. */
659       select @stat = - 1
660       select @stat = status
661       from master.dbo.sysconfigures
662       where name = @cachename
663           and parent = @cfguserdefinedcache
664           and config = @cfguserdefinedcache
665           and status & 128 != 128
666   
667   
668       if @stat = - 1
669       begin
670           raiserror 18135, @cachename
671           return 1
672       end
673   
674       /* Disallow configuring large I/O pool for in-memory storage cache. */
675       if (@stat & 65536 = 65536)
676       begin
677           raiserror 19973, @cachename
678           return 1
679       end
680   
681       /* Convert the destination pool size to units of kilobytes.*/
682       exec @ret = sp_aux_getsize @dest_pool_sz_str, @requested_size output
683       if @ret = 0
684       begin
685           raiserror 18147, @dest_pool_sz_str, @min_buf_size, @max_buf_size
686           return 1
687       end
688   
689       /*
690       **  First sanity check the destination pool size to make sure
691       **  it is at least 256 * pagesize big.
692       */
693       if (@requested_size != 0 and @requested_size < @@min_poolsize)
694       begin
695           raiserror 18095, @@min_poolsize
696           return 1
697       end
698   
699       /*
700       **  Translate the size in K of the destination and src pools to a 
701       **  config value that we can use to look up the pool in sysconfigures.
702       */
703       exec @ret = sp_aux_getsize @dest_buf_sz_str, @dest_buf_size output
704       if @ret = 0
705       begin
706           raiserror 18147, @dest_buf_sz_str, @min_buf_size, @max_buf_size
707           return 1
708       end
709   
710       exec @ret = sp_aux_getsize @src_buf_sz_str, @src_buf_size output
711       if @ret = 0
712       begin
713           raiserror 18147, @src_buf_sz_str, @min_buf_size, @max_buf_size
714           return 1
715       end
716   
717       /* The source and destination pools cannot be the same.*/
718       if @dest_buf_size = @src_buf_size
719       begin
720           raiserror 18144, @src_buf_sz_str, @dest_buf_sz_str
721           return 1
722       end
723   
724       /*
725       **  Sanity check the destination and source buffer sizes to make sure it's 
726       **  within the legal range (i.e. powers of 2 between a logical page and 
727       **  8 logical pages inclusive.
728       */
729       if (@dest_buf_size < @min_buf_size or @dest_buf_size > @max_buf_size)
730       begin
731           raiserror 18147, @dest_buf_sz_str, @min_buf_size, @max_buf_size
732           return 1
733       end
734   
735       if (@src_buf_size < @min_buf_size or @src_buf_size > @max_buf_size)
736       begin
737           raiserror 18147, @src_buf_sz_str, @min_buf_size, @max_buf_size
738           return 1
739       end
740   
741       select @temp_size = 2
742       while @temp_size < @dest_buf_size
743           select @temp_size = @temp_size * 2
744       if @temp_size != @dest_buf_size
745       begin
746           raiserror 18147, @dest_buf_sz_str, @min_buf_size, @max_buf_size
747           return 1
748       end
749   
750       select @temp_size = 2
751       while @temp_size < @src_buf_size
752           select @temp_size = @temp_size * 2
753       if @temp_size != @src_buf_size
754       begin
755           raiserror 18147, @src_buf_sz_str, @min_buf_size, @max_buf_size
756           return 1
757       end
758   
759       /*
760       **  Translate buffer sizes into config numbers to index into sysconfigures
761       **  and syscurconfigs with.
762       */
763       select @config_num_src
764           = @cfgbuffer2kpoolgrp + (log10(@src_buf_size * 1024 / @@pagesize) / log10(2))
765       select @config_num_dest
766           = @cfgbuffer2kpoolgrp + (log10(@dest_buf_size * 1024 / @@pagesize) / log10(2))
767   
768       /* Get the list of instances where the operations needs to be run. */
769   
770   
771   
772   
773       /*
774       **  Find out how much memory is already in the destination pool.  It could
775       **  turn out that this request is for making the pool smaller.  In that case
776       **  turn around the arguments (i.e. destination pool becomes source and source
777       **  pool becomes destination) and give the extra memory to the source
778       **  pool. We need to consider the config value for configuring the pools as
779       **  different instances can different run values.
780       */
781       select @delete_pool = 0
782       select @existing_pool_size = 0
783       select @existing_pool_size = value
784       from master.dbo.sysconfigures
785       where name = @cachename
786           and config = @config_num_dest
787   
788   
789       select @mem_to_move = @requested_size - @existing_pool_size
790   
791       /*
792       **  If there is nothing to move just return. No need to work 
793       **  without a good reason.
794       */
795       if @mem_to_move = 0
796       begin
797           return 0
798       end
799   
800       begin tran pool_config
801   
802       /*
803       **  If the memory to move is positive, we're moving memory from the source
804       **  pool (parm3) to the destination pool (parm2).  If it is negative then
805       **  we're moving memory from the destination pool (parm2) to the source
806       **  pool (parm3). Negative memory indicates that we're making a pool smaller.
807       */
808       if @mem_to_move > 0
809       begin -- {
810           /*
811           **  Check to see if the source pool has enough memory in it to
812           **  satisfy the request.
813           */
814           select @src_pool_size = 0
815           select @src_pool_size = value
816           from master.dbo.sysconfigures
817           where config = @config_num_src
818               and name = @cachename
819   
820   
821           /*
822           ** If @src_pool_size = 0 means it has default pool size.
823           ** We need to calculate the config size this pool by
824           ** reading other pool information and cache size
825           */
826           if @src_pool_size = 0
827           begin
828               select @total_pool_size = sum(value)
829               from master.dbo.sysconfigures
830               where parent = @cfguserdefinedcache
831                   and name = @cachename
832   
833                   and config != @cfguserdefinedcache
834   
835               select @cache_size = value
836               from master.dbo.sysconfigures
837               where parent = @cfguserdefinedcache
838                   and name = @cachename
839   
840                   and config = @cfguserdefinedcache
841   
842               if @cache_size = 0
843               begin
844                   select @cache_size = value
845                   from master.dbo.syscurconfigs
846                   where config = @cfguserdefinedcache
847                       and comment = @cachename
848               end
849   
850               select @src_pool_size = @cache_size - @total_pool_size
851           end
852   
853           select @temp_size = @src_pool_size - @mem_to_move
854           if (@temp_size != 0 and @temp_size < (256 * @@maxpagesize / 1024))
855           begin
856               /* Use wash_or_apf_str to covert mem_to_move to string. */
857               select @wash_or_apf_str
858                   = rtrim(convert(varchar(20), @mem_to_move)) + "Kb"
859               raiserror 18143, @src_buf_sz_str, @src_pool_size,
860                   @wash_or_apf_str
861   
862               /*
863               ** We are not using a rollback tran here as it will
864               ** complain that their is nothing to rollback. The
865               ** reason being we have not done any work in this
866               ** transaction so far.
867               */
868               commit tran poolconfig
869               return 1
870           end
871   
872           if exists (select *
873                   from master.dbo.sysconfigures
874                   where name = @cachename
875                       and config = @config_num_dest
876   
877                   )
878           begin
879               /*
880               ** @temp_str contains the string equivalent of @requested_size
881               ** in @dest_pool_sz_str format.
882               */
883               exec sp_aux_getsize @dest_pool_sz_str, @temp_pool_size output,
884                   @requested_size, @temp_str output
885   
886               update master.dbo.sysconfigures
887               set value = @requested_size,
888                   value2 = @temp_str
889               where config = @config_num_dest
890                   and name = @cachename
891   
892           end
893           else
894           begin
895               select @stat = status
896               from master.dbo.sysconfigures
897               where name = @cachename
898                   and config = @cfguserdefinedcache
899   
900   
901               select @msg = rtrim(convert(varchar(255), @dest_buf_size))
902                   + "K I/O Buffer Pool"
903   
904               exec sp_aux_getsize @dest_pool_sz_str, @temp_pool_size output,
905                   @requested_size, @temp_str output
906   
907               /*
908               ** Note the last 2 values for the insert below is '0'and (-1). 
909               ** This corresponds to the wash size and default apf percentage
910               ** of the newly created buffer pool. 
911               ** Newly created pools get a wash size of zero which 
912               ** corresponds to default wash size.
913               */
914               insert into master.dbo.sysconfigures
915               values (@config_num_dest, @requested_size, @msg, @stat,
916                   @cachename, @cfguserdefinedcache, @temp_str, 0, (- 1)
917   
918               )
919           end
920   
921           if @temp_size = 0
922           begin
923               /* Flag that we should delete the source buffer pool later. */
924               select @delete_pool = 1
925           end
926   
927           /* 
928           ** Update the source pool size after transfer.
929           ** If the updated value would become negative then
930           ** set it to zero.  @temp_str contains the string equivalent 
931           ** of the value field in @src_pool_sz_str format.
932           */
933           select @temp_size = value - @mem_to_move
934           from master.dbo.sysconfigures
935           where config = @config_num_src
936               and name = @cachename
937   
938   
939           if @temp_size < 0
940           begin
941               exec sp_aux_getsize @dest_pool_sz_str, @temp_pool_size output,
942                   0, @temp_str output
943               update master.dbo.sysconfigures
944               set value = 0,
945                   value2 = @temp_str
946               where config = @config_num_src
947                   and name = @cachename
948   
949           end
950           else
951           begin
952               exec sp_aux_getsize @dest_pool_sz_str, @temp_pool_size output,
953                   @temp_size, @temp_str output
954               update master.dbo.sysconfigures
955               set value = @temp_size,
956                   value2 = @temp_str
957               where config = @config_num_src
958                   and name = @cachename
959   
960           end
961       end -- }
962       else
963       begin -- {
964           /*
965           **  We're actually removing memory from the destination pool since
966           **  @mem_to_move is negative.  If we've deleted this pool then delete
967           **  the entry from sysconfigures.  Otherwise just subtract (by adding
968           **  -@mem_to_move from the value already in sysconfigures).
969           */
970           /*
971           ** Check to see if we are shrinking default pool
972           ** as we don't allow this
973           */
974           if (@dest_buf_size = @min_buf_size)
975           begin
976               /*
977               ** We are not using a rollback tran here as it will
978               ** complain that their is nothing to rollback. The
979               ** reason being we have not done any work in this
980               ** transaction so far.
981               */
982               print "You cannot make the size of the default pool of cache '%1!' smaller.", @cachename
983               commit tran poolconfig
984               return 1
985           end
986   
987           /* Check to see whether we have enough space in the dest pool */
988           select @dest_pool_size = 0
989           select @dest_pool_size = value
990           from master.dbo.sysconfigures
991           where config = @config_num_dest
992               and name = @cachename
993   
994   
995           select @temp_size = @dest_pool_size + @mem_to_move
996           if (@temp_size != 0 and @temp_size < (256 * @@maxpagesize / 1024))
997           begin
998               /* Use wash_or_apf_str to covert mem_to_move to string.	*/
999               select @wash_or_apf_str
1000                  = rtrim(convert(varchar(20), @mem_to_move)) + "Kb"
1001              raiserror 18143, @dest_buf_sz_str, @dest_pool_size,
1002                  @wash_or_apf_str
1003  
1004              /*
1005              ** We are not using a rollback tran here as it will
1006              ** complain that their is nothing to rollback. The
1007              ** reason being we have not done any work in this
1008              ** transaction so far.
1009              */
1010              commit tran poolconfig
1011              return 1
1012          end
1013  
1014          if exists (select *
1015                  from master.dbo.sysconfigures
1016                  where name = @cachename
1017                      and config = @config_num_src
1018  
1019                  )
1020          begin -- {
1021              /*
1022              ** @temp_str contains the string equivalent of value field 
1023              ** in @src_pool_sz_str format.
1024              */
1025              select @temp_value = value
1026              from master.dbo.sysconfigures
1027              where config = @config_num_src
1028                  and name = @cachename
1029  
1030  
1031              /*
1032              ** If we are updating the default pool and 
1033              ** the size of the default pool is zero then don't add
1034              ** @mem_to_move to 'value'.
1035              */
1036              if (@config_num_src !=
1037                      (@cfgbuffer2kpoolgrp
1038                      + (log10(@@maxpagesize / @@pagesize) / log10(2))))
1039              begin
1040                  select @temp_value = @temp_value + abs(@mem_to_move)
1041              end
1042              else
1043              begin
1044                  if (@temp_value != 0)
1045                  begin
1046                      select @temp_value = @temp_value + abs(@mem_to_move)
1047                  end
1048              end
1049  
1050              exec sp_aux_getsize @dest_pool_sz_str, @temp_pool_size output,
1051                  @temp_value, @temp_str output
1052              update master.dbo.sysconfigures
1053              set value = @temp_value,
1054                  value2 = @temp_str
1055              where config = @config_num_src
1056                  and name = @cachename
1057  
1058  
1059          end -- }
1060          else
1061          begin -- {
1062              select @stat = status
1063              from master.dbo.sysconfigures
1064              where name = @cachename
1065                  and config = @cfguserdefinedcache
1066  
1067  
1068              select @msg = rtrim(convert(varchar(255), @src_buf_size))
1069                  + "K I/O Buffer Pool"
1070              select @temp_value = abs(@mem_to_move)
1071              exec sp_aux_getsize @dest_pool_sz_str, @temp_pool_size output,
1072                  @temp_value, @temp_str output
1073              /*
1074              ** Note the last 2 values for the insert below is '0'and (-1). 
1075              ** This corresponds to the wash size and default apf percentage
1076              ** of the newly created buffer pool. 
1077              ** Newly created pools get a wash size of zero which 
1078              ** corresponds to default wash size.
1079              */
1080              insert into master.dbo.sysconfigures
1081              values (@config_num_src, @temp_value, @msg, @stat, @cachename,
1082                  @cfguserdefinedcache, @temp_str, 0, (- 1)
1083  
1084              )
1085          end -- }
1086  
1087          if @temp_size = 0
1088          begin
1089              /* 
1090              ** Flag that we should delete the destination buffer pool 
1091              ** later. 
1092              */
1093              select @delete_pool = 2
1094          end
1095  
1096          /* 
1097          ** Update the destination pool size after transfer.
1098          ** @temp_str contains the string equivalent
1099          ** of the value field @dest_pool_sz_str format.
1100          */
1101          exec sp_aux_getsize @dest_pool_sz_str, @temp_pool_size output,
1102              @requested_size, @temp_str output
1103          update master.dbo.sysconfigures
1104          set value = @requested_size,
1105              value2 = @temp_str
1106          where config = @config_num_dest
1107              and name = @cachename
1108  
1109  
1110      end -- }
1111  
1112  
1113  
1114  
1115      select @instance_count = 1
1116  
1117  
1118  
1119      select @failure_count = 0
1120  
1121  
1122  
1123      select @i = @instance_count
1124      while (@i > 0)
1125      begin -- {
1126  
1127  
1128  
1129          /*
1130          ** We need to calculate the amount of memory to be moved
1131          ** separately for each instance and use it for poolconfig
1132          ** The reason for this is, there is a possibility of having
1133          ** different run values at different instances.
1134          */
1135  
1136          select @curr_pool_size = 0
1137          select @curr_pool_size = value
1138          from master.dbo.syscurconfigs
1139          where comment = @cachename
1140              and config = @config_num_dest
1141  
1142          select @inst_mem_to_move = @requested_size - @curr_pool_size
1143  
1144          /*
1145          ** The amount of memory to be moved may be negative some instance which
1146          ** means not enough memory
1147          */
1148          if (@inst_mem_to_move = 0)
1149          begin
1150              select @i = @i - 1
1151              continue
1152          end
1153  
1154  
1155          select @cur_instancename = NULL
1156  
1157  
1158  
1159          begin
1160              select @stat = config_admin(@cmdpoolconfig, @src_buf_size,
1161                      @inst_mem_to_move, @dest_buf_size,
1162                      @cur_instancename, @cachename)
1163          end
1164  
1165          if @stat = 0
1166          begin
1167              select @failure_count = @failure_count + 1
1168          end
1169  
1170          select @i = @i - 1
1171      end -- }
1172  
1173  
1174  
1175  
1176      /*
1177      ** We rollback the transanction only if the operation is failed
1178      ** at all nodes.
1179      */
1180      if @failure_count = @instance_count
1181      begin
1182          rollback tran pool_config
1183          return 1
1184      end
1185  
1186  
1187      /*
1188      ** We need to check the result size at each instance and check
1189      ** whether we satisfied the requirement at all nodes before we 
1190      ** delete a pool entry if needed.
1191      */
1192      select @result_size = value
1193      from master.dbo.sysconfigures
1194      where config = @config_num_dest
1195          and name = @cachename
1196  
1197  
1198      /*
1199      ** ASE cannot guarentee that all of the requested buffers can be
1200      ** moved from one pool to another. Determine if the correct number of 
1201      ** buffers were moved.
1202      **
1203      ** When moving memory, ASE cannot move fractions of MASSes. Therefore
1204      ** the actual amount of memory moved may be rounded down to a multiple
1205      ** of the greater of the source and destination MASS size. Take this
1206      ** into account when determining if the request has been fulfilled.
1207      ** For example, if a request is made to move 28K from the 2K to the 8K
1208      ** pool, then the server will only attempt to move 24K, since half a
1209      ** MASS (the remaining 4K) has no meaning to the 8K pool.
1210      **
1211      ** NOTE: This fact creates a dependency between the server's behaviour and
1212      ** the behaviour of sp_do_poolconfig, and care should be taken in future not
1213      ** to break this. These sorts of dependencies will continue to exist until
1214      ** such time as config_admin() is modified to return good status 
1215      ** information. The stored procedure needs to know whether the memory move
1216      ** succeeded or not, and should not be left to duplicate the server's 
1217      ** behaviour in order to work this out.
1218      ** 
1219      ** Calculate this rounded down value and save it in @mem_moved.
1220      */
1221      if @src_buf_size > @dest_buf_size
1222      begin
1223          select @temp_value = @mem_to_move / @src_buf_size
1224          select @mem_moved = @temp_value * @src_buf_size
1225      end
1226      else
1227      begin
1228          select @temp_value = @mem_to_move / @dest_buf_size
1229          select @mem_moved = @temp_value * @dest_buf_size
1230      end
1231  
1232      /*
1233      ** If the requisite number of buffers were moved successfully between 
1234      ** pools, remove corresponding entries from sysconfigures if this was a 
1235      ** request to delete a pool.
1236      */
1237      if @result_size = (@mem_moved + @existing_pool_size)
1238      begin -- {
1239          if @delete_pool = 1
1240          begin
1241              delete from master.dbo.sysconfigures
1242              where config = @config_num_src
1243                  and parent = @cfguserdefinedcache
1244                  and name = @cachename
1245  
1246  
1247              select @stat = config_admin(@cmdbindingcheck, 5,
1248                      @src_buf_size * 1024, 0, @instancename,
1249                      @cachename)
1250  
1251              if @stat = 0
1252              begin
1253                  select "Warning: Failed to verify whether log I/O size
1254  				was set for the deleted pool."
1255              end
1256          end
1257          if @delete_pool = 2
1258          begin
1259              delete from master.dbo.sysconfigures
1260              where config = @config_num_dest
1261                  and parent = @cfguserdefinedcache
1262                  and name = @cachename
1263  
1264  
1265              select @stat = config_admin(@cmdbindingcheck, 5,
1266                      @dest_buf_size * 1024, 0, @instancename,
1267                      @cachename)
1268              if @stat = 0
1269              begin
1270                  select "Warning: Failed to verify whether log I/O size
1271  				was set for the deleted pool."
1272              end
1273          end
1274      end -- }
1275      else
1276      begin -- {
1277          /*
1278          ** Unable to move the requested number of buffers. If this was an
1279          ** attempt to delete a pool then generate a message indicating
1280          ** the failure to fully do do. The pool will have been marked
1281          ** as unavailable by the server in such cases.
1282          */
1283          if (@delete_pool > 0)
1284          begin
1285              if (@delete_pool = 1)
1286              begin
1287                  raiserror 18558, @src_buf_sz_str, @cachename
1288              end
1289              else
1290              begin
1291                  raiserror 18558, @dest_buf_sz_str, @cachename
1292              end
1293  
1294              raiserror 18559
1295          end
1296          else
1297          begin -- {
1298              /*
1299              ** Unable to move the correct number of buffers, but there
1300              ** was no attempt to delete either source or destination
1301              ** pool.
1302              */
1303              select @res_sz_str =
1304                  rtrim(convert(varchar(30), abs(@mem_moved)))
1305              select @req_sz_str =
1306                  rtrim(convert(varchar(30), abs(@mem_to_move)))
1307  
1308              if (@mem_to_move > 0)
1309              begin
1310                  raiserror 18560, @req_sz_str, @src_buf_sz_str,
1311                      @dest_buf_sz_str, @cachename, @res_sz_str
1312              end
1313              else
1314              begin
1315                  raiserror 18560, @req_sz_str, @dest_buf_sz_str,
1316                      @src_buf_sz_str, @cachename, @res_sz_str
1317              end
1318  
1319              /*
1320              ** If the destination pool has been left with a size of
1321              ** less than 256 * (logical pagesize), then the server
1322              ** will have marked this pool as unuseable.
1323              ** Print a warning in such cases.
1324              */
1325              if (@result_size != 0 and @result_size < @@min_poolsize)
1326              begin
1327                  raiserror 18339, @dest_buf_sz_str, @@min_poolsize
1328              end
1329  
1330              /*
1331              ** If the source pool has been left with a size of
1332              ** less than 256 * (logical pagesize), then the server
1333              ** will have marked this pool as unuseable. Print a
1334              ** warning in such cases.
1335              */
1336              select @result_size = value
1337              from master.dbo.sysconfigures
1338              where config = @config_num_src
1339                  and name = @cachename
1340  
1341  
1342              if (@result_size != 0 and @result_size < @@min_poolsize)
1343              begin
1344                  raiserror 18339, @src_buf_sz_str, @@min_poolsize
1345              end
1346  
1347              /*
1348              ** Print a general message as to how to correctly resize
1349              ** the pools.
1350              */
1351              raiserror 18562
1352          end -- }
1353      end -- }
1354  
1355      /*
1356      ** Update the wash size for source and destination buffer pools if required.
1357      ** We do this at the end because, here the actual buffer movement
1358      ** has already been done and we will have the new wash size available
1359      ** for the affected buffer pools.
1360      **
1361      ** NOTE: In syscurconfigs, we obtain the wash size in Kilobytes.
1362      ** However, we store the wash size, in sysconfigures, in
1363      ** kilobytes/Buffer pool size.
1364      */
1365  
1366      /*
1367      ** There are two system tables which has all the config information.
1368      ** syscurconfigs maintains current running configuration and sysconfigures
1369      ** contains the configuration user has asked through config file and
1370      ** using config releated commands. The values in these two system catalogs
1371      ** may be different because server might calculate some values for example
1372      ** if wash size is DEFAULT (i.e value3=0 in sysconfigures) server will find
1373      ** out what is the optimal wash size and set that in syscurconfigs where as
1374      ** sysconfigures will have 0 only. This is useful because when memory is
1375      ** moved between pools and if user give wash size as DEFAULT server adjusts
1376      ** to optimal value otherwise wash size might become more than the pool
1377      ** hence memory may not be allowed to move. 
1378      */
1379  
1380      /* For source pool, get the current wash size from sysconfigures. */
1381      select @temp_value = 0
1382      select @temp_value = value3
1383      from master.dbo.sysconfigures
1384      where config = @config_num_src
1385          and name = @cachename
1386  
1387  
1388      /*
1389      ** At this point user has created or modified a pool. We should not update the
1390      ** wash size here because it was not specified as a parameter.
1391      ** We should leave wash size as 0 which indicates a default value.
1392      ** @temp_value will not be zero if user has explicitly changed the wash size 
1393      ** at some point of the time, in such cases we should go update the
1394      ** value to that of running value because server might give a diffrent wash
1395      ** size than we asked for.
1396      */
1397      if (@temp_value != 0)
1398      begin
1399          select @wash_or_apf_size = memory_used
1400          from master.dbo.syscurconfigs
1401          where config = @config_num_src
1402              and comment = @cachename
1403  
1404  
1405          update master.dbo.sysconfigures
1406          set value3 = @wash_or_apf_size / @src_buf_size
1407          where config = @config_num_src
1408              and name = @cachename
1409  
1410      end
1411  
1412      /* Now, do the aame for destination pool.*/
1413      select @temp_value = 0
1414      select @temp_value = value3
1415      from master.dbo.sysconfigures
1416      where config = @config_num_dest
1417          and name = @cachename
1418  
1419  
1420      if (@temp_value != 0)
1421      begin
1422          select @wash_or_apf_size = memory_used
1423          from master.dbo.syscurconfigs
1424          where config = @config_num_dest
1425              and comment = @cachename
1426  
1427          update master.dbo.sysconfigures
1428          set value3 = @wash_or_apf_size / @dest_buf_size
1429          where config = @config_num_dest
1430              and name = @cachename
1431  
1432      end
1433  
1434      /*
1435      ** Write the changes to the config file. We do not want to do this when called
1436      ** by recovery during runtime buffer tuning because in case of private install,
1437      ** when the cluster is started, the first instance writing this will prevent 
1438      ** the rest of the instance from joining the cluster in first attempt.
1439      */
1440      if (@is_in_recovery not in ("TRUE", "true"))
1441      begin
1442          /*
1443          ** For SMP server the instanceid is set to NULL. In SMP we only have one
1444          ** configuration file and hence a special where clause of instanceid is 
1445          ** not required. 
1446          */
1447          if (@@instanceid = NULL)
1448          begin
1449              select @config_file_name = value2
1450              from master.dbo.syscurconfigs
1451              where config = @cfgcfgfilename
1452          end
1453          else
1454          begin
1455              select @config_file_name = value2
1456              from master.dbo.syscurconfigs where
1457  
1458                  config = @cfgcfgfilename
1459          end
1460  
1461          select @stat = - 1
1462          select @stat = config_admin(@cmdupdateconfigfile, 0, 0, 0,
1463                  @instancename, @config_file_name)
1464  
1465          if @stat = 0
1466          begin
1467              print "Failed to update the config file. Changes to the pool configuration for cache '%1!' cannot be undone.", @cachename
1468              rollback tran pool_config
1469              return 1
1470          end
1471      end
1472      commit tran pool_config
1473  
1474      /*
1475      ** Record the pool configure change for configuration history auditing.
1476      */
1477      select @cha_area = "CACHE"
1478      select @cha_type = "sp_poolconfig"
1479      select @cha_target = @cachename
1480      select @cha_element = "config pool: " + @dest_buf_sz_str +
1481          case when @src_buf_sz_str != "1p" then ", affected pool: " end +
1482          case when @src_buf_sz_str != "1p" then @src_buf_sz_str end
1483      select @cha_oldvalue = convert(varchar(255), @existing_pool_size)
1484      select @cha_newvalue = convert(varchar(255), @requested_size)
1485      select @cha_mode = NULL
1486      select @cha_instanceid = @instanceid
1487  
1488      select @cha_ret = audit_config_history(
1489              @cha_area,
1490              @cha_type,
1491              @cha_target,
1492              @cha_element,
1493              @cha_oldvalue,
1494              @cha_newvalue,
1495              @cha_mode,
1496              @cha_instanceid)
1497      return 0
1498  


exec sp_procxmode 'sp_do_poolconfig', 'AnyMode'
go

Grant Execute on sp_do_poolconfig to public
go
RESULT SETS
sp_do_poolconfig_rset_002
sp_do_poolconfig_rset_001

DEFECTS
 MCTR 4 Conditional Begin Tran or Commit Tran 452
 MCTR 4 Conditional Begin Tran or Commit Tran 486
 MCTR 4 Conditional Begin Tran or Commit Tran 539
 MCTR 4 Conditional Begin Tran or Commit Tran 557
 MCTR 4 Conditional Begin Tran or Commit Tran 868
 MCTR 4 Conditional Begin Tran or Commit Tran 983
 MCTR 4 Conditional Begin Tran or Commit Tran 1010
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 MTYP 4 Assignment type mismatch @rec_state: varchar(30) = int 227
 MTYP 4 Assignment type mismatch @size_str: varchar(30) = varchar(255) 284
 MTYP 4 Assignment type mismatch @cha_target: varchar(30) = varchar(255) 502
 MTYP 4 Assignment type mismatch @cha_target: varchar(30) = varchar(255) 572
 MTYP 4 Assignment type mismatch @dest_buf_sz_str: varchar(30) = varchar(40) 609
 MTYP 4 Assignment type mismatch config: smallint = int 915
 MTYP 4 Assignment type mismatch parent: smallint = int 916
 MTYP 4 Assignment type mismatch config: smallint = int 1081
 MTYP 4 Assignment type mismatch parent: smallint = int 1082
 MTYP 4 Assignment type mismatch @cha_target: varchar(30) = varchar(255) 1479
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 341
 QTYP 4 Comparison type mismatch smallint = int 341
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 342
 QTYP 4 Comparison type mismatch smallint = int 342
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 376
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 388
 QTYP 4 Comparison type mismatch smallint = int 388
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 389
 QTYP 4 Comparison type mismatch smallint = int 389
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 448
 QTYP 4 Comparison type mismatch smallint = int 448
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 456
 QTYP 4 Comparison type mismatch smallint = int 456
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 495
 QTYP 4 Comparison type mismatch smallint = int 495
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 535
 QTYP 4 Comparison type mismatch smallint = int 535
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 543
 QTYP 4 Comparison type mismatch smallint = int 543
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 565
 QTYP 4 Comparison type mismatch smallint = int 565
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 663
 QTYP 4 Comparison type mismatch smallint = int 663
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 664
 QTYP 4 Comparison type mismatch smallint = int 664
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 786
 QTYP 4 Comparison type mismatch smallint = int 786
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 817
 QTYP 4 Comparison type mismatch smallint = int 817
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 830
 QTYP 4 Comparison type mismatch smallint = int 830
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 833
 QTYP 4 Comparison type mismatch smallint = int 833
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 837
 QTYP 4 Comparison type mismatch smallint = int 837
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 840
 QTYP 4 Comparison type mismatch smallint = int 840
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 846
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 875
 QTYP 4 Comparison type mismatch smallint = int 875
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 889
 QTYP 4 Comparison type mismatch smallint = int 889
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 898
 QTYP 4 Comparison type mismatch smallint = int 898
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 935
 QTYP 4 Comparison type mismatch smallint = int 935
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 946
 QTYP 4 Comparison type mismatch smallint = int 946
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 957
 QTYP 4 Comparison type mismatch smallint = int 957
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 991
 QTYP 4 Comparison type mismatch smallint = int 991
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1017
 QTYP 4 Comparison type mismatch smallint = int 1017
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1027
 QTYP 4 Comparison type mismatch smallint = int 1027
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1055
 QTYP 4 Comparison type mismatch smallint = int 1055
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1065
 QTYP 4 Comparison type mismatch smallint = int 1065
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1106
 QTYP 4 Comparison type mismatch smallint = int 1106
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1140
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1194
 QTYP 4 Comparison type mismatch smallint = int 1194
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1242
 QTYP 4 Comparison type mismatch smallint = int 1242
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1243
 QTYP 4 Comparison type mismatch smallint = int 1243
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1260
 QTYP 4 Comparison type mismatch smallint = int 1260
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1261
 QTYP 4 Comparison type mismatch smallint = int 1261
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1338
 QTYP 4 Comparison type mismatch smallint = int 1338
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1384
 QTYP 4 Comparison type mismatch smallint = int 1384
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1401
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1407
 QTYP 4 Comparison type mismatch smallint = int 1407
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1416
 QTYP 4 Comparison type mismatch smallint = int 1416
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1424
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1429
 QTYP 4 Comparison type mismatch smallint = int 1429
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1451
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1458
 TNOI 4 Table with no index master..syscurconfigs master..syscurconfigs
 MGTP 3 Grant to public master..sysconfigures  
 MGTP 3 Grant to public master..syscurconfigs  
 MGTP 3 Grant to public sybsystemprocs..sp_do_poolconfig  
 MNAC 3 Not using ANSI 'is null' 396
 MNAC 3 Not using ANSI 'is null' 1447
 MNER 3 No Error Check should check return value of exec 189
 MNER 3 No Error Check should check return value of exec 194
 MNER 3 No Error Check should check @@error after update 454
 MNER 3 No Error Check should check @@error after update 541
 MNER 3 No Error Check should check return value of exec 883
 MNER 3 No Error Check should check @@error after update 886
 MNER 3 No Error Check should check return value of exec 904
 MNER 3 No Error Check should check @@error after insert 914
 MNER 3 No Error Check should check return value of exec 941
 MNER 3 No Error Check should check @@error after update 943
 MNER 3 No Error Check should check return value of exec 952
 MNER 3 No Error Check should check @@error after update 954
 MNER 3 No Error Check should check return value of exec 1050
 MNER 3 No Error Check should check @@error after update 1052
 MNER 3 No Error Check should check return value of exec 1071
 MNER 3 No Error Check should check @@error after insert 1080
 MNER 3 No Error Check should check return value of exec 1101
 MNER 3 No Error Check should check @@error after update 1103
 MNER 3 No Error Check should check @@error after delete 1241
 MNER 3 No Error Check should check @@error after delete 1259
 MNER 3 No Error Check should check @@error after update 1405
 MNER 3 No Error Check should check @@error after update 1427
 MUBC 3 Unbalanced begin tran/commit tran 1472
 MUCO 3 Useless Code Useless Brackets 210
 MUCO 3 Useless Code Useless Brackets 212
 MUCO 3 Useless Code Useless Brackets 213
 MUCO 3 Useless Code Useless Brackets 220
 MUCO 3 Useless Code Useless Brackets 221
 MUCO 3 Useless Code Useless Brackets 228
 MUCO 3 Useless Code Useless Brackets 231
 MUCO 3 Useless Code Useless Brackets 320
 MUCO 3 Useless Code Useless Brackets 323
 MUCO 3 Useless Code Useless Brackets 353
 MUCO 3 Useless Code Useless Brackets 370
 MUCO 3 Useless Code Useless Brackets 620
 MUCO 3 Useless Code Useless Brackets 636
 MUCO 3 Useless Code Useless Brackets 675
 MUCO 3 Useless Code Useless Brackets 693
 MUCO 3 Useless Code Useless Brackets 729
 MUCO 3 Useless Code Useless Brackets 735
 MUCO 3 Useless Code Useless Brackets 764
 MUCO 3 Useless Code Useless Brackets 766
 MUCO 3 Useless Code Useless Brackets 854
 MUCO 3 Useless Code Useless Brackets 916
 MUCO 3 Useless Code Useless Brackets 974
 MUCO 3 Useless Code Useless Brackets 996
 MUCO 3 Useless Code Useless Brackets 1036
 MUCO 3 Useless Code Useless Brackets 1038
 MUCO 3 Useless Code Useless Brackets 1044
 MUCO 3 Useless Code Useless Brackets 1082
 MUCO 3 Useless Code Useless Brackets 1124
 MUCO 3 Useless Code Useless Brackets 1148
 MUCO 3 Useless Code Useless Brackets 1283
 MUCO 3 Useless Code Useless Brackets 1285
 MUCO 3 Useless Code Useless Brackets 1308
 MUCO 3 Useless Code Useless Brackets 1325
 MUCO 3 Useless Code Useless Brackets 1342
 MUCO 3 Useless Code Useless Brackets 1397
 MUCO 3 Useless Code Useless Brackets 1420
 MUCO 3 Useless Code Useless Brackets 1440
 MUCO 3 Useless Code Useless Brackets 1447
 MUOT 3 Updates outside transaction 954
 QAFM 3 Var Assignment from potentially many rows 374
 QAFM 3 Var Assignment from potentially many rows 446
 QAFM 3 Var Assignment from potentially many rows 493
 QAFM 3 Var Assignment from potentially many rows 533
 QAFM 3 Var Assignment from potentially many rows 563
 QAFM 3 Var Assignment from potentially many rows 783
 QAFM 3 Var Assignment from potentially many rows 815
 QAFM 3 Var Assignment from potentially many rows 844
 QAFM 3 Var Assignment from potentially many rows 895
 QAFM 3 Var Assignment from potentially many rows 933
 QAFM 3 Var Assignment from potentially many rows 989
 QAFM 3 Var Assignment from potentially many rows 1025
 QAFM 3 Var Assignment from potentially many rows 1062
 QAFM 3 Var Assignment from potentially many rows 1137
 QAFM 3 Var Assignment from potentially many rows 1192
 QAFM 3 Var Assignment from potentially many rows 1336
 QAFM 3 Var Assignment from potentially many rows 1382
 QAFM 3 Var Assignment from potentially many rows 1399
 QAFM 3 Var Assignment from potentially many rows 1414
 QAFM 3 Var Assignment from potentially many rows 1422
 QAFM 3 Var Assignment from potentially many rows 1449
 QAFM 3 Var Assignment from potentially many rows 1455
 QCRS 3 Conditional Result Set 1253
 QCRS 3 Conditional Result Set 1270
 QISO 3 Set isolation level 173
 QNAM 3 Select expression has no name "Warning: Failed to verify whether log I/O size was set for the deleted pool." 1253
 QNAM 3 Select expression has no name "Warning: Failed to verify whether log I/O size was set for the deleted pool." 1270
 QSWV 3 Sarg with variable @config_num_dest, Candidate Index: sysconfigures.nc2sysconfigures(config) S 448
 QSWV 3 Sarg with variable @config_num_dest, Candidate Index: sysconfigures.nc2sysconfigures(config) S 456
 QSWV 3 Sarg with variable @config_num_dest, Candidate Index: sysconfigures.nc2sysconfigures(config) S 495
 QSWV 3 Sarg with variable @config_num_dest, Candidate Index: sysconfigures.nc2sysconfigures(config) S 535
 QSWV 3 Sarg with variable @config_num_dest, Candidate Index: sysconfigures.nc2sysconfigures(config) S 543
 QSWV 3 Sarg with variable @config_num_dest, Candidate Index: sysconfigures.nc2sysconfigures(config) S 565
 QSWV 3 Sarg with variable @config_num_dest, Candidate Index: sysconfigures.nc2sysconfigures(config) S 786
 QSWV 3 Sarg with variable @config_num_src, Candidate Index: sysconfigures.nc2sysconfigures(config) S 817
 QSWV 3 Sarg with variable @config_num_dest, Candidate Index: sysconfigures.nc2sysconfigures(config) S 875
 QSWV 3 Sarg with variable @config_num_dest, Candidate Index: sysconfigures.nc2sysconfigures(config) S 889
 QSWV 3 Sarg with variable @cfguserdefinedcache, Candidate Index: sysconfigures.nc2sysconfigures(config) S 898
 QSWV 3 Sarg with variable @config_num_src, Candidate Index: sysconfigures.nc2sysconfigures(config) S 935
 QSWV 3 Sarg with variable @config_num_src, Candidate Index: sysconfigures.nc2sysconfigures(config) S 946
 QSWV 3 Sarg with variable @config_num_src, Candidate Index: sysconfigures.nc2sysconfigures(config) S 957
 QSWV 3 Sarg with variable @config_num_dest, Candidate Index: sysconfigures.nc2sysconfigures(config) S 991
 QSWV 3 Sarg with variable @config_num_src, Candidate Index: sysconfigures.nc2sysconfigures(config) S 1017
 QSWV 3 Sarg with variable @config_num_src, Candidate Index: sysconfigures.nc2sysconfigures(config) S 1027
 QSWV 3 Sarg with variable @config_num_src, Candidate Index: sysconfigures.nc2sysconfigures(config) S 1055
 QSWV 3 Sarg with variable @cfguserdefinedcache, Candidate Index: sysconfigures.nc2sysconfigures(config) S 1065
 QSWV 3 Sarg with variable @config_num_dest, Candidate Index: sysconfigures.nc2sysconfigures(config) S 1106
 QSWV 3 Sarg with variable @config_num_dest, Candidate Index: sysconfigures.nc2sysconfigures(config) S 1194
 QSWV 3 Sarg with variable @config_num_src, Candidate Index: sysconfigures.nc2sysconfigures(config) S 1338
 QSWV 3 Sarg with variable @config_num_src, Candidate Index: sysconfigures.nc2sysconfigures(config) S 1384
 QSWV 3 Sarg with variable @config_num_src, Candidate Index: sysconfigures.nc2sysconfigures(config) S 1407
 QSWV 3 Sarg with variable @config_num_dest, Candidate Index: sysconfigures.nc2sysconfigures(config) S 1416
 QSWV 3 Sarg with variable @config_num_dest, Candidate Index: sysconfigures.nc2sysconfigures(config) S 1429
 VNRD 3 Variable is not read @dummy 217
 VNRD 3 Variable is not read @temp_pool_size 1101
 VNRD 3 Variable is not read @cha_ret 1488
 VUNU 3 Variable is not used @stat1 84
 VUNU 3 Variable is not used @stat2 85
 VUNU 3 Variable is not used @temp_float 96
 VUNU 3 Variable is not used @cur_instanceid 113
 MRST 2 Result Set Marker 1253
 MRST 2 Result Set Marker 1270
 MSUB 2 Subquery Marker 872
 MSUB 2 Subquery Marker 1014
 MTR1 2 Metrics: Comments Ratio Comments: 41% 60
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 98 = 136dec - 40exi + 2 60
 MTR3 2 Metrics: Query Complexity Complexity: 656 60

DATA PROPAGATION detailed
ColumnWritten To
@cachenamesysconfigures.name   sp_configure_rset_001.Parameter Name sp_configure_rset_002.Parameter Name sp_configure_rset_003.Parameter Name sp_configure_rset_004.sqlbNoName4 sp_configure_rset_005.Parameter Name sp_configure_rset_006.Parameter Name sp_configure_rset_001.Parameter Name sp_configure_rset_002.Parameter Name sp_configure_rset_003.Parameter Name
sp_configure_rset_004.sqlbNoName30 sp_configure_rset_005.Parameter Name sp_configure_rset_006.Parameter Name sp_countmetadata_rset_001.option name sp_exec_SQL_rset_001.sqlbNoName57 sp_monitorconfig_rset_001.Name sp_monitorconfig_rset_002.Name sp_validateconfigname_rset_001.option_name
@parm1sysattributes.object_info1   °.object_info2   °.char_value   sysconfigures.config   °.value   °.comment   °.value2   °.value3   °.value4   sysattributes.object_info1  
°.object_info2   °.int_value   °.char_value   sp_addconf_rset_001.sqlbNoName0 sp_configure_rset_001.Config Value sp_configure_rset_002.Config Value sp_configure_rset_005.Config Value sp_configure_rset_006.Config Value sp_dropdevice_rset_001.device sp_autoformat_rset_002._clid#af
sp_configure_rset_001.Config Value sp_configure_rset_002.Config Value sp_configure_rset_005.Config Value sp_configure_rset_006.Config Value sp_countmetadata_rset_001.metadata count °.memory required sp_displayroles_rset_001.Role Name sp_displayroles_rset_002.Role Name sp_displayroles_rset_003.Role Name sp_displayroles_rset_004.Role Name
sp_dropdevice_rset_001.device sp_exec_SQL_rset_001.sqlbNoName57 sp_forceonline_object_rset_001.Object °.Index °.status sp_forceonline_object_rset_002.Object °.Index °.status sp_forceonline_page_rset_001.status sp_forceonline_page_rset_002.status
sp_listsuspect_object_rset_001.Object °.Index °.Access sp_listsuspect_page_rset_001.Object °.Index °.Access sp_makesuspect_obj_rset_001.Obj °.Indid °.LogType °.PageType
°.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_002.Obj °.Indid °.LogType °.PageType °.ErrType °.Delay °.TotalNum
sp_makesuspect_obj_rset_003.Obj °.Indid °.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_memlog_rset_001.dumps_per_file sp_monitorconfig_rset_001.Configure Value °.System Value
°.Run Value °.Num_free °.Num_active sp_monitorconfig_rset_002.Num_active °.Pct_act °.Max_Used °.Reuse_cnt °.Instance_Name sp_passwordpolicy_rset_001.message sp_passwordpolicy_rset_002.value
sp_passwordpolicy_rset_003.value sp_passwordpolicy_rset_004.Policy_option sp_rjs_retrieve_rset_001.host_name sp_setsuspect_granularity_rset_001.Online mode sp_setsuspect_threshold_rset_001.Cfg. Suspect threshold sp_setsuspect_threshold_rset_002.Cfg. Suspect Threshold sp_ssladmin_rset_001.certificate_path sp_ssladmin_rset_002.Cipher Suite Name °.Preference sp_validateconfigname_rset_001.config_value
@parm2sysconfigures.config   °.comment   °.value3   °.value4   sp_countmetadata_rset_001.metadata count °.memory required sp_exec_SQL_rset_001.sqlbNoName57 sp_monitorconfig_rset_001.Configure Value °.System Value °.Run Value
°.Num_free °.Num_active sp_monitorconfig_rset_002.Num_active °.Pct_act °.Max_Used °.Reuse_cnt °.Instance_Name
@parm3sysconfigures.config   °.comment   °.value3   sp_countmetadata_rset_001.metadata count °.memory required sp_exec_SQL_rset_001.sqlbNoName57 sp_monitorconfig_rset_001.Configure Value °.System Value °.Run Value °.Num_free
°.Num_active sp_monitorconfig_rset_002.Num_active °.Pct_act °.Max_Used °.Reuse_cnt °.Instance_Name

DEPENDENCIES
PROCS AND TABLES USED
writes table sybsystemprocs..sp_do_poolconfig_rset_001 
writes table sybsystemprocs..sp_do_poolconfig_rset_002 
read_writes table master..sysconfigures (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
calls proc sybsystemprocs..sp_aux_getsize  
reads table master..syscurconfigs (1)  
calls proc sybsystemprocs..sp_cacheconfig  
   read_writes table master..sysconfigures (1)  
   calls proc sybsystemprocs..sp_autoformat  
      writes table sybsystemprocs..sp_autoformat_rset_004 
      writes table sybsystemprocs..sp_autoformat_rset_002 
      reads table master..syscolumns (1)  
      writes table sybsystemprocs..sp_autoformat_rset_005 
      reads table master..systypes (1)  
      calls proc sybsystemprocs..sp_autoformat  
      read_writes table tempdb..#colinfo_af (1) 
      reads table tempdb..systypes (1)  
      writes table sybsystemprocs..sp_autoformat_rset_001 
      reads table tempdb..syscolumns (1)  
      writes table sybsystemprocs..sp_autoformat_rset_003 
      calls proc sybsystemprocs..sp_namecrack  
   read_writes table tempdb..#pool_detail (1) 
   writes table sybsystemprocs..sp_cacheconfig_rset_003 
   read_writes table tempdb..#syscacheconfig (1) 
   writes table sybsystemprocs..sp_cacheconfig_rset_002 
   reads table master..syscurconfigs (1)  
   writes table sybsystemprocs..sp_cacheconfig_rset_001 
   read_writes table tempdb..#cache_info (1) 
   calls proc sybsystemprocs..sp_aux_checkroleperm  
   calls proc sybsystemprocs..sp_aux_getsize  

CALLERS
called by proc sybsystemprocs..sp_poolconfig