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


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 405
 MCTR 4 Conditional Begin Tran or Commit Tran 439
 MCTR 4 Conditional Begin Tran or Commit Tran 446
 MCTR 4 Conditional Begin Tran or Commit Tran 464
 MCTR 4 Conditional Begin Tran or Commit Tran 739
 MCTR 4 Conditional Begin Tran or Commit Tran 854
 MCTR 4 Conditional Begin Tran or Commit Tran 881
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 MTYP 4 Assignment type mismatch @rec_state: varchar(30) = int 190
 MTYP 4 Assignment type mismatch @size_str: varchar(30) = varchar(255) 247
 MTYP 4 Assignment type mismatch @dest_buf_sz_str: varchar(30) = varchar(40) 480
 MTYP 4 Assignment type mismatch config: smallint = int 786
 MTYP 4 Assignment type mismatch parent: smallint = int 787
 MTYP 4 Assignment type mismatch config: smallint = int 952
 MTYP 4 Assignment type mismatch parent: smallint = int 953
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 304
 QTYP 4 Comparison type mismatch smallint = int 304
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 305
 QTYP 4 Comparison type mismatch smallint = int 305
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 339
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 351
 QTYP 4 Comparison type mismatch smallint = int 351
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 352
 QTYP 4 Comparison type mismatch smallint = int 352
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 409
 QTYP 4 Comparison type mismatch smallint = int 409
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 450
 QTYP 4 Comparison type mismatch smallint = int 450
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 534
 QTYP 4 Comparison type mismatch smallint = int 534
 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 657
 QTYP 4 Comparison type mismatch smallint = int 657
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 688
 QTYP 4 Comparison type mismatch smallint = int 688
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 701
 QTYP 4 Comparison type mismatch smallint = int 701
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 704
 QTYP 4 Comparison type mismatch smallint = int 704
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 708
 QTYP 4 Comparison type mismatch smallint = int 708
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 711
 QTYP 4 Comparison type mismatch smallint = int 711
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 717
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 746
 QTYP 4 Comparison type mismatch smallint = int 746
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 760
 QTYP 4 Comparison type mismatch smallint = int 760
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 769
 QTYP 4 Comparison type mismatch smallint = int 769
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 806
 QTYP 4 Comparison type mismatch smallint = int 806
 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 828
 QTYP 4 Comparison type mismatch smallint = int 828
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 862
 QTYP 4 Comparison type mismatch smallint = int 862
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 888
 QTYP 4 Comparison type mismatch smallint = int 888
 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 926
 QTYP 4 Comparison type mismatch smallint = int 926
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 936
 QTYP 4 Comparison type mismatch smallint = int 936
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 977
 QTYP 4 Comparison type mismatch smallint = int 977
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1011
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1066
 QTYP 4 Comparison type mismatch smallint = int 1066
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1114
 QTYP 4 Comparison type mismatch smallint = int 1114
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1115
 QTYP 4 Comparison type mismatch smallint = int 1115
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1132
 QTYP 4 Comparison type mismatch smallint = int 1132
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1133
 QTYP 4 Comparison type mismatch smallint = int 1133
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1210
 QTYP 4 Comparison type mismatch smallint = int 1210
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1256
 QTYP 4 Comparison type mismatch smallint = int 1256
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1273
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1279
 QTYP 4 Comparison type mismatch smallint = int 1279
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1288
 QTYP 4 Comparison type mismatch smallint = int 1288
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1296
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1301
 QTYP 4 Comparison type mismatch smallint = int 1301
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1323
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1330
 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' 359
 MNAC 3 Not using ANSI 'is null' 1319
 MNER 3 No Error Check should check return value of exec 170
 MNER 3 No Error Check should check return value of exec 175
 MNER 3 No Error Check should check @@error after update 407
 MNER 3 No Error Check should check @@error after update 448
 MNER 3 No Error Check should check return value of exec 754
 MNER 3 No Error Check should check @@error after update 757
 MNER 3 No Error Check should check return value of exec 775
 MNER 3 No Error Check should check @@error after insert 785
 MNER 3 No Error Check should check return value of exec 812
 MNER 3 No Error Check should check @@error after update 814
 MNER 3 No Error Check should check return value of exec 823
 MNER 3 No Error Check should check @@error after update 825
 MNER 3 No Error Check should check return value of exec 921
 MNER 3 No Error Check should check @@error after update 923
 MNER 3 No Error Check should check return value of exec 942
 MNER 3 No Error Check should check @@error after insert 951
 MNER 3 No Error Check should check return value of exec 972
 MNER 3 No Error Check should check @@error after update 974
 MNER 3 No Error Check should check @@error after delete 1113
 MNER 3 No Error Check should check @@error after delete 1131
 MNER 3 No Error Check should check @@error after update 1277
 MNER 3 No Error Check should check @@error after update 1299
 MUBC 3 Unbalanced begin tran/commit tran 1344
 MUCO 3 Useless Code Useless Brackets 183
 MUCO 3 Useless Code Useless Brackets 191
 MUCO 3 Useless Code Useless Brackets 194
 MUCO 3 Useless Code Useless Brackets 283
 MUCO 3 Useless Code Useless Brackets 286
 MUCO 3 Useless Code Useless Brackets 316
 MUCO 3 Useless Code Useless Brackets 333
 MUCO 3 Useless Code Useless Brackets 491
 MUCO 3 Useless Code Useless Brackets 507
 MUCO 3 Useless Code Useless Brackets 546
 MUCO 3 Useless Code Useless Brackets 564
 MUCO 3 Useless Code Useless Brackets 600
 MUCO 3 Useless Code Useless Brackets 606
 MUCO 3 Useless Code Useless Brackets 635
 MUCO 3 Useless Code Useless Brackets 637
 MUCO 3 Useless Code Useless Brackets 725
 MUCO 3 Useless Code Useless Brackets 787
 MUCO 3 Useless Code Useless Brackets 845
 MUCO 3 Useless Code Useless Brackets 867
 MUCO 3 Useless Code Useless Brackets 907
 MUCO 3 Useless Code Useless Brackets 909
 MUCO 3 Useless Code Useless Brackets 915
 MUCO 3 Useless Code Useless Brackets 953
 MUCO 3 Useless Code Useless Brackets 995
 MUCO 3 Useless Code Useless Brackets 1019
 MUCO 3 Useless Code Useless Brackets 1155
 MUCO 3 Useless Code Useless Brackets 1157
 MUCO 3 Useless Code Useless Brackets 1180
 MUCO 3 Useless Code Useless Brackets 1197
 MUCO 3 Useless Code Useless Brackets 1214
 MUCO 3 Useless Code Useless Brackets 1269
 MUCO 3 Useless Code Useless Brackets 1292
 MUCO 3 Useless Code Useless Brackets 1312
 MUCO 3 Useless Code Useless Brackets 1319
 MUOT 3 Updates outside transaction 825
 QAFM 3 Var Assignment from potentially many rows 337
 QAFM 3 Var Assignment from potentially many rows 654
 QAFM 3 Var Assignment from potentially many rows 686
 QAFM 3 Var Assignment from potentially many rows 715
 QAFM 3 Var Assignment from potentially many rows 766
 QAFM 3 Var Assignment from potentially many rows 804
 QAFM 3 Var Assignment from potentially many rows 860
 QAFM 3 Var Assignment from potentially many rows 896
 QAFM 3 Var Assignment from potentially many rows 933
 QAFM 3 Var Assignment from potentially many rows 1008
 QAFM 3 Var Assignment from potentially many rows 1064
 QAFM 3 Var Assignment from potentially many rows 1208
 QAFM 3 Var Assignment from potentially many rows 1254
 QAFM 3 Var Assignment from potentially many rows 1271
 QAFM 3 Var Assignment from potentially many rows 1286
 QAFM 3 Var Assignment from potentially many rows 1294
 QAFM 3 Var Assignment from potentially many rows 1321
 QAFM 3 Var Assignment from potentially many rows 1327
 QCRS 3 Conditional Result Set 1125
 QCRS 3 Conditional Result Set 1142
 QISO 3 Set isolation level 155
 QNAM 3 Select expression has no name "Warning: Failed to verify whether log I/O size was set for the deleted pool." 1125
 QNAM 3 Select expression has no name "Warning: Failed to verify whether log I/O size was set for the deleted pool." 1142
 QSWV 3 Sarg with variable @config_num_dest, Candidate Index: sysconfigures.nc2sysconfigures(config) S 409
 QSWV 3 Sarg with variable @config_num_dest, Candidate Index: sysconfigures.nc2sysconfigures(config) S 450
 QSWV 3 Sarg with variable @config_num_dest, Candidate Index: sysconfigures.nc2sysconfigures(config) S 657
 QSWV 3 Sarg with variable @config_num_src, Candidate Index: sysconfigures.nc2sysconfigures(config) S 688
 QSWV 3 Sarg with variable @config_num_dest, Candidate Index: sysconfigures.nc2sysconfigures(config) S 746
 QSWV 3 Sarg with variable @config_num_dest, Candidate Index: sysconfigures.nc2sysconfigures(config) S 760
 QSWV 3 Sarg with variable @cfguserdefinedcache, Candidate Index: sysconfigures.nc2sysconfigures(config) S 769
 QSWV 3 Sarg with variable @config_num_src, Candidate Index: sysconfigures.nc2sysconfigures(config) S 806
 QSWV 3 Sarg with variable @config_num_src, Candidate Index: sysconfigures.nc2sysconfigures(config) S 817
 QSWV 3 Sarg with variable @config_num_src, Candidate Index: sysconfigures.nc2sysconfigures(config) S 828
 QSWV 3 Sarg with variable @config_num_dest, Candidate Index: sysconfigures.nc2sysconfigures(config) S 862
 QSWV 3 Sarg with variable @config_num_src, Candidate Index: sysconfigures.nc2sysconfigures(config) S 888
 QSWV 3 Sarg with variable @config_num_src, Candidate Index: sysconfigures.nc2sysconfigures(config) S 898
 QSWV 3 Sarg with variable @config_num_src, Candidate Index: sysconfigures.nc2sysconfigures(config) S 926
 QSWV 3 Sarg with variable @cfguserdefinedcache, Candidate Index: sysconfigures.nc2sysconfigures(config) S 936
 QSWV 3 Sarg with variable @config_num_dest, Candidate Index: sysconfigures.nc2sysconfigures(config) S 977
 QSWV 3 Sarg with variable @config_num_dest, Candidate Index: sysconfigures.nc2sysconfigures(config) S 1066
 QSWV 3 Sarg with variable @config_num_src, Candidate Index: sysconfigures.nc2sysconfigures(config) S 1210
 QSWV 3 Sarg with variable @config_num_src, Candidate Index: sysconfigures.nc2sysconfigures(config) S 1256
 QSWV 3 Sarg with variable @config_num_src, Candidate Index: sysconfigures.nc2sysconfigures(config) S 1279
 QSWV 3 Sarg with variable @config_num_dest, Candidate Index: sysconfigures.nc2sysconfigures(config) S 1288
 QSWV 3 Sarg with variable @config_num_dest, Candidate Index: sysconfigures.nc2sysconfigures(config) S 1301
 VNRD 3 Variable is not read @instanceid 522
 VNRD 3 Variable is not read @temp_pool_size 972
 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 1125
 MRST 2 Result Set Marker 1142
 MSUB 2 Subquery Marker 743
 MSUB 2 Subquery Marker 885
 MTR1 2 Metrics: Comments Ratio Comments: 43% 60
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 90 = 127dec - 39exi + 2 60
 MTR3 2 Metrics: Query Complexity Complexity: 582 60

DEPENDENCIES
PROCS AND TABLES USED
read_writes table master..sysconfigures (1)  
calls proc sybsystemprocs..sp_aux_getsize  
calls proc sybsystemprocs..sp_cacheconfig  
   calls proc sybsystemprocs..sp_autoformat  
      reads table master..systypes (1)  
      reads table tempdb..syscolumns (1)  
      read_writes table tempdb..#colinfo_af (1) 
      calls proc sybsystemprocs..sp_autoformat  
      reads table master..syscolumns (1)  
      reads table tempdb..systypes (1)  
      calls proc sybsystemprocs..sp_namecrack  
   read_writes table tempdb..#cache_info (1) 
   read_writes table tempdb..#pool_detail (1) 
   reads table master..syscurconfigs (1)  
   read_writes table tempdb..#syscacheconfig (1) 
   calls proc sybsystemprocs..sp_aux_getsize  
   read_writes table master..sysconfigures (1)  
reads table master..syscurconfigs (1)  

CALLERS
called by proc sybsystemprocs..sp_poolconfig