DatabaseProcApplicationCreatedLinks
sybsystemprocssp_cacheconfig  14 déc. 14Defects Propagation Dependencies

1     
2     /* This stored procedure is for configuring named caches.
3     **
4     **	Messages for sp_cacheconfig
5     **
6     ** 17260, "Can't run %1! from within a transaction."
7     ** 17325, "The length of input parameter '%1!' is longer than the 
8     **	  permissible %2! characters."
9     ** 18135, "The specified named cache (%1) does not exist"
10    **
11    ** 18136, "The cache type can only be specified once."
12    **
13    ** 18137, "Attempt to delete the default data cache.  The default cache may not
14    **	   be deleted."
15    **
16    ** 18140, "Cannot modify a cache type to be 'log only' when non-log objects 
17    **         are bound to it. Use sp_helpcache to print out bound objects 
18    **	   and sp_unbindcache to delete the cache bindings."
19    **
20    ** 18155, "The cache type can be 'logonly', 'mixed', or 'inmemory_storage' only. 
21    **	   Replacement policy can be 'strict', 'relaxed' or 'none' only."
22    **
23    ** 18177, "Usage: sp_cacheconfig [ cachename [, 'cache_size[K|P|M|G]' ] 
24    **	   [, logonly | mixed | inmemory_storage] [, strict | relaxed | none] 
25    **	   [, cache_partition=[1|2|4|8|16|32|64]]]"
26    **
27    ** 18420, "The replacement policy can only be specified once."
28    **
29    ** 18332, "You need to be able to set curwrite label to data_low.  
30    **	   This script will continue in spite of failure to set curwrite.
31    **	   Please rerun after setting your labels correctly."
32    **
33    ** 18611, "The specified cache partition number is invalid. A valid cache
34    **	   partition number should be power of 2 and between 1 to 128."
35    **
36    ** 18616, "The cache partition can only be specified once."
37    **
38    ** 19817, "Can not create another configuration for the cache '%1!' due to 
39    **	   existing cache bindings and an instance specific configuration.
40    **	   Please use 'sp_unbindcache_all' to remove cache bindings for
41    **	   cache '%2!'  to create another configuration."
42    **
43    ** 19976, "The specified cache replacement policy '%1!' for cache '%2!' is 
44    **	  invalid. Replacement policy 'none' is applicable to cache of type 
45    **	  type 'inmemory_storage' and replacement policy 'strict | relaxed'
46    **	  is applicable to cache of type 'logonly | mixed'."
47    **
48    ** 19977, "Changing the cache type for cache '%1!' from or to type '%2!' is not 
49    **	  allowed."
50    **        
51    ** 19978, "Changing the cache replacement policy between '%1!' and '%2!' is 
52    **	  not allowed."
53    **				
54    ** 19595, "Instance '%1!' does not exist."
55    **
56    ** 19656, "Deleting instance-specific configuration of 'default data cache' 
57    **	   is not dynamic; restart instance '%1!' to effect the changes."
58    **
59    ** 19981, "The cache type for the 'default data cache' cannot be modified."
60    **
61    ** 19982, "The cache type for '%1!' cannot be modified to '%2!' as it is 
62    **	   already configured as '%3!' on other instance(s). A cache can be
63    **	   configured to be either 'mixed', or 'logonly', but not both"
64    */
65    create or replace procedure sp_cacheconfig
66        @cachename varchar(255) = "%",
67        @parm1 varchar(133) = NULL,
68        @parm2 varchar(133) = NULL,
69        @parm3 varchar(133) = NULL,
70        @parm4 varchar(133) = NULL,
71        @parm5 varchar(255) = NULL
72    as
73        /*
74        **  The following variable are used to print out cache configuration 
75        **  information.
76        */
77        declare @instancename varchar(255)
78        declare @instanceid int
79        declare @print_str varchar(1024) /* for printing error msgs */
80        declare @sysconf_name varchar(255) /* cache name from sysconfigures */
81        declare @io_sz_comment char(255) /* io size string from sysconfigures */
82        declare @cache_type char(24) /* cache type from sysconfigures */
83        declare @stat_to_print char(10) /* status string from sysconfigures */
84        declare @pool_status char(21) /* status of pool */
85        declare @config_sz_str varchar(13) /* configured sz from sysconfigures */
86        declare @run_sz_str char(13) /* run size from sysconfigures */
87        declare @io_sz_str varchar(3) /* pool size (in KB) */
88        declare @maxnamelen int
89        declare @maxtypelen int
90        declare @maxstatuslen int
91        declare @maxlen int
92        declare @operation int /* update, insert, or delete */
93        declare @config int /* config value from sysconfigures */
94        declare @size int /* generic size of a cache or pool */
95        declare @row_count int /* # cache/pool rows in sysconfigures */
96        declare @cache_count int /* # cache rows in sysconfigures */
97        declare @retstat int /* return status from other sprocs called */
98        declare @wash_size int /* wash size of a pool */
99        declare @apf_value int /* apf percent of a pool */
100       declare @run_size int /* run_size of a pool or cache */
101       declare @config_size int /* config size of a pool or cache */
102       declare @total_cfg_sz float /* total, all configured cache mem */
103       declare @total_run_sz float /* total, all run cache mem */
104       declare @oldglobalstat int /* global status from sysconfigures  */
105       declare @stat int /* tmp var to collect status from sysconfigures */
106       declare @oldlocalstat int /* status from sysconfigures for an instance */
107       declare @c_part_stat int /* status for cache partition */
108       declare @old_stat int /* status before update of sysconf. */
109       declare @runstat int /* status from syscurconfigs */
110       declare @new_cache_type int /* modified value of cache type */
111       declare @old_repl int /* original replacement policy */
112       declare @new_repl int /* modified replacement policy */
113       declare @dflt_ctype int /* default cache type when unspecified */
114       declare @dflt_crepl int /* default cache repl when unspecified */
115       declare @mincachename varchar(255) /* for looping through temp table */
116       declare @pools_available int /* whether all pools are available */
117       declare @cfg_cache_repl varchar(11) /* to print cfg cache replacement */
118       declare @run_cache_repl varchar(11) /* to print run cache replacement */
119       declare @cfg_cache_partition int /* cache partition number	*/
120       declare @cache_partition_parm varchar(30) /* parameter for cache partition*/
121       declare @instance_parm varchar(133) /* parameter for instance name	*/
122       declare @cache_partition_loc int /* for parsing partition parms	*/
123       declare @cache_partition_str varchar(255) /* for parsing partition number */
124       declare @cache_part_temp int /* cache partition number	*/
125       declare @partition_number int /* cache partition number	*/
126       declare @dflt_cpart smallint /* default part # if unspecified */
127       declare @msg varchar(255) /* used to print new pool info */
128       declare @pool_config_id int /* new pool id */
129       declare @default_pool_size int /* config size for default pool */
130       declare @diff_config_run_size int /* difference in config and run size */
131       declare @config_file_name varchar(255) /* Name of the config file */
132       /* 
133       ** The following variables are required to copy pool config information 
134       ** from sysconfigures
135       */
136       declare @value int
137       declare @comment varchar(255)
138       declare @name varchar(255)
139       declare @parent smallint
140       declare @value2 varchar(255)
141       declare @value3 int
142       declare @value4 int
143       declare @total_config int
144       declare @nullarg varchar(1)
145       declare @dummy int
146       declare @gp_enabled int
147       declare @cha_area varchar(10) /* for configuration history auditing */
148       declare @cha_type varchar(30)
149       declare @cha_target varchar(30)
150       declare @cha_element varchar(255)
151       declare @cha_oldvalue varchar(255)
152       declare @cha_newvalue varchar(255)
153       declare @cha_mode varchar(10)
154       declare @cha_instanceid int
155       declare @cha_ret int
156       declare @oldsize int
157   
158   
159   
160       /* declare and init the config_admin() commands used in this sproc */
161       declare @cmdbindingcheck int
162           , @cmdupdateconfigfile int
163           , @cfgmaxcachevalue int
164   
165       select @cmdbindingcheck = 9
166           , @cmdupdateconfigfile = 32
167           , @cfgmaxcachevalue = 33
168   
169       /* declare and init the config options used in this sproc */
170       declare @cfgcacheinsert int
171           , @cfgcacheupdate int
172           , @cfgcachedelete int
173           , @cfguserdefinedcache int
174           , @cfgbuffer2kpoolgrp int
175           , @cfgcfgfilename int
176   
177   
178       select @cfgcacheinsert = 6
179           , @cfgcacheupdate = 7
180           , @cfgcachedelete = 8
181           , @cfguserdefinedcache = 19
182           , @cfgbuffer2kpoolgrp = 20
183           , @cfgcfgfilename = 114
184   
185       /* cache status bit constants used in this sproc */
186       declare @defaulttype int
187           , @mixedtype int
188           , @logonlytype int
189           , @hkignore int
190           , @activetype int
191           , @activependingtype int
192           , @deletependingtype int
193           , @relaxedlru int
194           , @strictlru int
195           , @cmpartspecified int
196           , @deletedtype int
197           , @inmemtype int
198           , @nonereplace int
199   
200       select @defaulttype = 1
201           , @mixedtype = 2
202           , @logonlytype = 4
203           , @hkignore = 16
204           , @activetype = 32
205           , @activependingtype = 64
206           , @deletependingtype = 128
207           , @relaxedlru = 256
208           , @strictlru = 512
209           , @cmpartspecified = 1024
210           , @deletedtype = 16384
211           , @inmemtype = 65536
212           , @nonereplace = 131072
213   
214   
215   
216       select @instancename = NULL
217       select @instanceid = NULL
218   
219   
220   
221       /* Dont allow sp_cacheconfig to run with in a transaction */
222       if @@trancount > 0
223       begin
224           raiserror 17260, "sp_cacheconfig"
225           return 1
226       end
227       else
228       begin
229           set transaction isolation level 1
230           set chained off
231           set nocount on
232       end
233   
234       /* Check to see that the @cachename is valid. */
235       if char_length(@cachename) > 30
236       begin
237           raiserror 17325, @cachename, 30
238           return 1
239       end
240   
241       /*
242       ** 	If sp_cacheconfig is excuted without any parameter or only cache 
243       **	name is provided, or instance is name is provided print the 
244       ** 	cache information. Otherwise, process the cache as indicated by
245       **	the input parameters.
246       */
247   
248       if ((@parm1 is NULL or charindex("instance ", @parm1) = 1) and @parm2 is NULL
249               and @parm3 is NULL and @parm4 is NULL and @parm5 is NULL)
250       begin -- {
251           /*
252           ** Create temp tables to store cache information
253           ** and pool information
254           */
255           create table #cache_info(
256               cache_name varchar(255),
257               Status varchar(9),
258               Type varchar(24),
259               ConfigReplacement varchar(11),
260               RunReplacement varchar(11),
261               configval char(12),
262               runval char(12),
263               ConfigPartition int,
264               RunPartition int,
265               instanceid tinyint null)
266           create table #pool_detail(
267               cache_name varchar(255),
268               io_sz varchar(8),
269               wash_size varchar(13),
270               configval char(12),
271               runval char(12),
272               pool_status char(21),
273               apf_value varchar(9),
274               instanceid tinyint null)
275   
276           if @parm1 is NULL
277           begin
278               /*
279               ** If parm1 is NULL then there are two cases here
280               ** case 1: @cachename is name of the cache to display
281               **	   information of cache
282               ** case 2: @cachename is an instance name to disply cache
283               **	   information of instance
284               */
285               if (charindex("instance ", @cachename) = 1)
286               begin
287   
288   
289                   if @instanceid is NULL
290                   begin
291                       raiserror 19595, @instancename
292                       return 1
293                   end
294   
295                   select @cachename = "%"
296               end
297               else
298               begin
299                   select @instancename = NULL
300                   select @instanceid = NULL
301               end
302           end
303   
304   
305           /*
306           **  Create temp table with just those rows from sysconfigures that have
307           **  to do with cache configuration.
308           */
309           select co.config, parent, co.name,
310               co.value config_size, co.status, cu.status runstat,
311               co.comment, cu.value run_size,
312               memory_used wash_size, apf_percent apf_value,
313               co.value4 cfg_cache_partition, co.value2
314   
315           into #syscacheconfig
316           from master.dbo.sysconfigures co, master.dbo.syscurconfigs cu
317           where 1 = 2
318   
319           /*
320           ** Get all global cache information first
321           */
322   
323           insert #syscacheconfig
324           select distinct co.config, parent, co.name,
325               co.value config_size, co.status, cu.status runstat,
326               co.comment, cu.value run_size,
327               memory_used wash_size, apf_percent apf_value,
328               co.value4 cfg_cache_partition, co.value2
329   
330           from master.dbo.sysconfigures co, master.dbo.syscurconfigs cu
331           where parent = @cfguserdefinedcache
332               and co.config = cu.config
333               and co.name = cu.comment
334               and name like "%" + @cachename + "%"
335   
336           order by name, config
337   
338   
339   
340   
341           /*
342           ** Determine the number of caches
343           */
344           select @cache_count = count(*)
345           from #syscacheconfig where config = @cfguserdefinedcache
346   
347           /*
348           ** 18135, "The specified named cache (%1) does not exist"
349           */
350           if @cache_count = 0
351           begin
352               select @cachename = rtrim(@cachename)
353               raiserror 18135, @cachename
354               return 1
355           end
356   
357           /*
358           ** Determine the total number of caches, and pools within caches.
359           */
360           select @row_count = count(*)
361           from #syscacheconfig where parent = @cfguserdefinedcache
362   
363           /*
364           **  Intialize total configured and run sizes.
365           */
366           select @total_cfg_sz = 0, @total_run_sz = 0
367   
368           /* Initialize the default pool size and difference */
369           select @default_pool_size = 0, @diff_config_run_size = 0
370   
371           /* Until determined otherwise, all defined pools are available */
372           select @pools_available = 1
373   
374           declare sysc_cursor cursor
375           for select config, config_size, name, status, runstat, comment,
376               run_size, wash_size, apf_value, cfg_cache_partition, value2
377   
378           from #syscacheconfig
379           order by name
380   
381           open sysc_cursor
382   
383           while (@row_count > 0)
384           begin
385               fetch sysc_cursor into @config, @config_size, @sysconf_name,
386                   @stat, @runstat, @io_sz_comment, @run_size,
387                   @wash_size, @apf_value, @cfg_cache_partition, @value2
388   
389               if @stat & @deletedtype = @deletedtype
390               begin
391                   /* Cache is deleted */
392                   select @row_count = @row_count - 1
393                   continue
394               end
395   
396               /*
397               ** Append the instance name to cache name in case the cache 
398               ** is local. This will make sure that the cache names are
399               ** used along with scope.
400               */
401   
402   
403               /*
404               **  A config value of 19 means this rows holds the total size
405               **  and type information for the whole cache.
406               */
407               if @config = @cfguserdefinedcache
408               begin
409                   /*
410                   ** Check to see if this cache is actively running, 
411                   ** has been created but the server has not been 
412                   ** re-booted so it hasn't been instantiated, or it's 
413                   ** been deleted and still occupying memory because 
414                   ** the server has not been rebooted yet.
415                   */
416                   if (@stat & @activetype = @activetype)
417                   begin
418                       select @stat_to_print = "Active"
419                   end
420                   if (@stat & @activependingtype = @activependingtype)
421                   begin
422                       select @stat_to_print = "Pend/Act"
423                   end
424                   if (@stat & @deletependingtype = @deletependingtype)
425                   begin
426                       select @stat_to_print = "Act/Del"
427                       select @config_size = 0
428                   end
429   
430                   /*
431                   ** Config size of cache is set to zero during startup
432                   ** if it is set to DEFAULT or is not configured.
433                   **
434                   ** If config size of default data cache is zero,
435                   ** set it equal to run size(default cache size).
436                   */
437                   if ((@config_size = 0) and
438                           (@sysconf_name like "default data cache"))
439                   begin
440                       select @config_size = @run_size
441                   end
442   
443                   /* 
444                   ** Save the difference in config value and run value
445                   ** of the cache size for default data cache.
446                   */
447                   if (@sysconf_name like "default data cache")
448                   begin
449                       select @diff_config_run_size =
450                           (@run_size - @config_size)
451                   end
452   
453                   /*
454                   **  Update totals for config and run size
455                   */
456                   select @total_cfg_sz = @total_cfg_sz +
457                       convert(float, @config_size)
458                   select @total_run_sz = @total_run_sz +
459                       convert(float, @run_size)
460   
461                   /*
462                   **  Convert run_size and config_size to megabyte 
463                   **  values stored as strings
464                   */
465                   select @run_sz_str = rtrim(str(convert(float, @run_size) / 1024, 9, 2))
466                       + " Mb"
467                   select @config_sz_str = rtrim(str(convert(float, @config_size) / 1024, 9, 2))
468                       + " Mb"
469   
470                   /*
471                   **  Extract the status and the type from the status 
472                   **  field since  both bit values are stuffed into 
473                   **  this one field.
474                   */
475                   if (@stat & @mixedtype = @mixedtype)
476                   begin
477                       if (@stat & @hkignore = @hkignore)
478                           select @cache_type = "Mixed, HK Ignore"
479                       else
480                           select @cache_type = "Mixed"
481                   end
482                   if (@stat & @logonlytype = @logonlytype)
483                   begin
484                       select @cache_type = "Log Only"
485                   end
486                   if (@stat & @defaulttype = @defaulttype)
487                   begin
488                       select @cache_type = "Default"
489                   end
490                   if (@stat & @inmemtype = @inmemtype)
491                   begin
492                       select @cache_type = "In-Memory Storage"
493                   end
494   
495   
496                   /* 
497                   **  Determine configured cache replacement policy
498                   */
499                   if (@stat & @relaxedlru = @relaxedlru)
500                   begin
501                       select @cfg_cache_repl = "relaxed LRU"
502                   end
503                   else if (@stat & @nonereplace = @nonereplace)
504                   begin
505                       select @cfg_cache_repl = "none"
506                   end
507                   else
508                   begin
509                       select @cfg_cache_repl = "strict LRU"
510                   end
511   
512                   /* 
513                   **  Determine running cache replacement policy
514                   */
515                   if (@runstat & @relaxedlru = @relaxedlru)
516                   begin
517                       select @run_cache_repl = "relaxed LRU"
518                   end
519                   else if (@runstat & @nonereplace = @nonereplace)
520                   begin
521                       select @run_cache_repl = "none"
522                   end
523                   else
524                   begin
525                       select @run_cache_repl = "strict LRU"
526                   end
527   
528                   if @instanceid is NULL
529                   begin
530                       select @instanceid = 0
531                   end
532                   /*
533                   **  Now store the line of output for this cache
534                   */
535                   insert #cache_info values (@sysconf_name,
536                       @stat_to_print, @cache_type,
537                       @cfg_cache_repl, @run_cache_repl,
538                       @config_sz_str, @run_sz_str,
539                       @cfg_cache_partition, @apf_value,
540                       @instanceid)
541               end
542               else
543               begin
544                   /*
545                   ** Row contains pool information. Select required
546                   ** information.
547                   */
548   
549                   /* Determine pool size (in KB) */
550                   select @io_sz_str = substring(@io_sz_comment, 1,
551                           charindex("K", @io_sz_comment) - 1)
552   
553                   /*
554                   ** If it is the default pool for default data cache 
555                   ** set the config size to be equal to its run size.
556                   */
557                   if ((@config_size = 0) and
558                           (@sysconf_name like "default data cache") and
559                           ((@io_sz_str = convert(varchar(3), @@maxpagesize / 1024))))
560                   begin
561                       select @config_size = @run_size
562                       select @default_pool_size = @run_size
563                   end
564   
565                   /*
566                   ** value2 = 'DEFAULT' means pool size is configured
567                   ** to default & config_size should be equal to run_size
568                   */
569                   if ((@config_size = 0) and ((@value2 is NULL) or
570                               (@value2 like "DEFAULT")))
571                   begin
572                       select @config_size = @run_size
573                   end
574   
575                   /* Determine the availability of the pool */
576                   if ((@config_size = 0) and
577                           (@run_size > 0) and
578                           (@io_sz_str != convert(varchar(3), @@maxpagesize / 1024)))
579                   begin
580                       /*
581                       ** Pools, other than the default pool, which
582                       ** have a configured size of 0, but a run size 
583                       ** greater than zero could not be fully 
584                       ** removed. Such pools will have been marked 
585                       ** as unavailable by the server.
586                       */
587                       select @pool_status = "Unavailable/deleted"
588                       select @pools_available = 0
589                   end
590                   else if ((@run_size < (@@maxpagesize * 256 / 1024)) and
591                           (@io_sz_str != convert(varchar(3), @@maxpagesize / 1024)))
592                   begin
593                       /*
594                       ** Pools, other than the default pool, which
595                       ** have a run size less than 256 *
596                       ** (logical pagesize) will have
597                       ** been marked as unavailable by the
598                       ** server, since they are too small
599                       ** to be used.
600                       */
601                       select @pool_status = "Unavailable/too small"
602                       select @pools_available = 0
603                   end
604                   else
605                   begin
606                       /* Pool is available */
607                       select @pool_status = ""
608                   end
609   
610                   insert #pool_detail values
611                   (@sysconf_name,
612                       replicate(" ", 6 -
613                           (charindex("K", @io_sz_comment))) +
614                       substring(@io_sz_comment, 1,
615                           charindex("K", @io_sz_comment) - 1) +
616                       " Kb ",
617                       str(@wash_size, 10) + " Kb",
618                       rtrim(str(convert(float, @config_size) /
619                               1024, 9, 2)) + " Mb",
620                       rtrim(str(convert(float, @run_size) /
621                               1024, 9, 2)) + " Mb",
622                       @pool_status,
623                       str(@apf_value, 6),
624                       @instanceid)
625               end
626               select @row_count = @row_count - 1
627           end
628   
629           close sysc_cursor
630   
631           /*
632           ** If difference in the config size and run size of default data cache 
633           ** is not zero, it means that the cache has been shrinked. The memory 
634           ** lost due to cache shrinking comes from the default pool. Hence, this
635           ** difference in run size and config size of cache should be subtracted 
636           ** from the config size of default pool.
637           **
638           ** Calculate the correct config size of default pool by subtracting
639           ** the difference in run value and config value of the cache size
640           ** and update the temp table.
641           */
642           if (@diff_config_run_size != 0)
643           begin
644               select @default_pool_size = @default_pool_size -
645                   @diff_config_run_size
646               update #pool_detail
647               set configval = (rtrim(str(convert(float, @default_pool_size) /
648                           1024, 9, 2)) + " Mb")
649               where cache_name like "default data cache" and
650                   io_sz like "%" + (convert(varchar(3), @@maxpagesize / 1024)) + "%"
651           end
652   
653           select @maxnamelen = max(char_length(cache_name)) from #cache_info
654           select @maxtypelen = max(char_length(Type)) from #cache_info
655           select @maxstatuslen = max(char_length(Status)) from #cache_info
656   
657           if (@maxnamelen <= 30)
658           begin
659               /* Print cache information. */
660               exec sp_autoformat @fulltabname = #cache_info,
661                   @selectlist = "'Cache Name' = cache_name, 'Status' = Status, 
662   	'Type' = Type,'Config Value' = configval, 'Run Value' = runval"
663   
664               /* 4 is for the four ' ' */
665               select @maxlen = @maxnamelen + @maxtypelen + @maxstatuslen + 4
666   
667               /*  Now print totals. */
668               select @run_sz_str = str(convert(float, @total_run_sz) / 1024, 7, 2) +
669                   " Mb"
670               select @config_sz_str = str(convert(float, @total_cfg_sz) / 1024, 7, 2) +
671                   " Mb"
672               select @print_str = replicate(' ', @maxlen) + "------------ ------------"
673               print @print_str
674               select @print_str = replicate(' ', @maxnamelen + @maxstatuslen + 4) + "Total    " +
675                   convert(char(13), @config_sz_str) + @run_sz_str
676               print @print_str
677           end
678           else
679           begin
680               /* Print cache information. */
681               select cache_name "Cache Name", Status, Type,
682                   configval "Config Value", runval "Run Value"
683               from #cache_info
684   
685               /*  Now print totals. */
686               select @run_sz_str = str(convert(float, @total_run_sz) / 1024, 7, 2) +
687                   " Mb"
688               select @config_sz_str = str(convert(float, @total_cfg_sz) / 1024, 7, 2) +
689                   " Mb"
690               select @print_str = convert(char(279), " ") + "------------ ------------"
691               print @print_str
692               select @print_str = convert(char(272), " ") + "Total    " +
693                   convert(char(13), @config_sz_str) + @run_sz_str
694               print @print_str
695           end
696   
697           /*
698           ** Now print cache/pool detail information 
699           ** A "Pend/Act" cache will not have pools configured
700           */
701           select @instanceid = min(instanceid)
702           from #cache_info
703           where Status != "Pend/Act"
704   
705           select @mincachename = min(cache_name)
706           from #cache_info
707           where Status != "Pend/Act" and instanceid = @instanceid
708   
709           while @mincachename is not NULL
710           begin
711   
712               /* 
713               ** Print information about the cache
714               */
715   
716               print '=========================================================================='
717               select @print_str = "Cache: " + @mincachename + ",   Status: " + Status +
718                   ",   Type: " + Type
719               from #cache_info
720               where cache_name = @mincachename
721               print @print_str
722               select @print_str = "      Config Size: " + ltrim(configval) +
723                   ",   Run Size: " + ltrim(runval)
724               from #cache_info
725               where cache_name = @mincachename
726               print @print_str
727               select @print_str = "      Config Replacement: " + ConfigReplacement +
728                   ",   Run Replacement: " + RunReplacement
729               from #cache_info
730               where cache_name = @mincachename
731               print @print_str
732               select @print_str = "      Config Partition:   " +
733                   str(convert(int, ConfigPartition)) +
734                   ",   Run Partition:   " +
735                   str(convert(int, RunPartition))
736               from #cache_info
737               where cache_name = @mincachename
738               print @print_str
739   
740               /*
741               ** Now print the pool information. Only print status information
742               ** if any pool is unavailable. This will happen so seldom, that for
743               ** the most part it is better to leave the output as documented in 
744               ** the user manuals to prevent unwanted Tech Support calls about
745               ** the meaning of this status field. 
746               */
747               if exists (select * from #pool_detail where cache_name = @mincachename)
748               begin
749                   if (@pools_available = 1)
750                   begin
751                       select io_sz "IO Size", wash_size "Wash Size",
752                           configval "Config Size", runval "Run Size",
753                           apf_value "APF Percent"
754                       from #pool_detail
755                       where cache_name = @mincachename
756                       order by io_sz
757                   end
758                   else
759                   begin
760                       select io_sz "IO Size", wash_size "Wash Size",
761                           configval "Config Size", runval "Run Size",
762                           apf_value "APF Percent",
763                           pool_status "Status"
764                       from #pool_detail
765                       where cache_name = @mincachename
766                       order by io_sz
767                   end
768               end
769               select @mincachename = min(cache_name)
770               from #cache_info
771               where cache_name > @mincachename
772                   and Status != "Pend/Act"
773                   and instanceid = @instanceid
774   
775               if @mincachename is NULL
776               begin
777                   select @instanceid = min(instanceid)
778                   from #cache_info
779                   where Status != "Pend/Act"
780                       and instanceid > @instanceid
781   
782                   select @mincachename = min(cache_name)
783                   from #cache_info
784                   where Status != "Pend/Act"
785                       and instanceid = @instanceid
786   
787                   if @mincachename is NULL
788                       break
789               end
790           end
791       end -- }
792       else
793       begin -- {
794           /* 
795           ** check if user has sa role, proc_role will also do auditing
796           ** if required. proc_role will also print error message if required.
797           */
798   
799           select @nullarg = NULL
800           execute @retstat = sp_aux_checkroleperm "sa_role",
801               "manage data cache", @nullarg, @gp_enabled output
802   
803           /* For Auditing */
804           if (@gp_enabled = 0)
805           begin
806               if (proc_role("sa_role") = 0)
807                   return 1
808           end
809           else
810           begin
811               select @dummy = proc_auditperm("manage data cache",
812                       @retstat)
813           end
814   
815           if (@retstat != 0)
816               return 1
817           /*
818           **  We actually have some real work to do; either insert, modify, or
819           **  delete a cache. Initialize configured size and new type.
820           */
821           select @config_sz_str = NULL
822           select @new_cache_type = 0
823           select @new_repl = 0
824           select @dflt_ctype = 0
825           select @dflt_crepl = 0
826           select @c_part_stat = 0
827           select @partition_number = 1
828           select @cache_partition_parm = NULL
829           select @instance_parm = NULL
830           select @dflt_cpart = 0
831           select @instancename = NULL
832           select @instanceid = NULL
833   
834           /*
835           ** Process the @parm1 
836           **
837           ** Here the @parm1 can be either a cache size, cache type,
838           ** replacement policy, cache partition number or instance name.  
839           **
840           ** First check whether this is a cache type.
841           */
842           if @parm1 IN ("logonly", "mixed", "inmemory_storage")
843           begin
844               if @parm1 = "logonly"
845                   select @new_cache_type = @logonlytype
846               else if @parm1 = "mixed"
847                   select @new_cache_type = 2
848               else
849                   select @new_cache_type = @inmemtype
850           end
851           /* Check whether this is a cache replacement policy.*/
852           else if @parm1 IN ("strict", "relaxed", "none")
853           begin
854               if @parm1 = "strict" or @parm1 = "relaxed"
855               begin
856                   if (@new_cache_type & @inmemtype = @inmemtype)
857                   begin
858                       /* 
859                       ** It is an error if cache type is 
860                       ** 'inmemory_storage' but replacement policy 
861                       ** is not 'none'.
862                       */
863                       raiserror 19976, @parm1, @cachename
864                       return 1
865                   end
866                   else
867                   begin
868                       if @parm1 = "strict"
869                           select @new_repl = 512
870                       else
871                           select @new_repl = 256
872                   end
873               end
874   
875               if @parm1 = "none"
876               begin
877                   if (@new_cache_type & @inmemtype != @inmemtype)
878                   begin
879                       /*
880                       ** It is an error if replacement policy is
881                       ** 'none' but cache type is not 
882                       ** 'inmemory_storage'.
883                       */
884                       raiserror 19976, @parm1, @cachename
885                       return 1
886                   end
887                   else
888                   begin
889                       select @new_repl = @nonereplace
890                   end
891               end
892           end
893           /* Check whether this is a cache partition number. */
894           else if (patindex("%cache_partition%", @parm1) != 0)
895           begin
896               /* Save the partition parameter string for later process.*/
897               select @cache_partition_parm = @parm1
898           end
899           else if (patindex("instance ", @parm1) != 0)
900           begin
901               /* Save the instance parameter string for later process.*/
902               select @instance_parm = @parm1
903           end
904           else
905           begin
906               /* 
907               ** Here @parm1 could contain a junk string value or a
908               ** legitimate cache size.
909               */
910               if patindex("%[a-z,A-Z]%", @parm1) = 1
911               begin
912                   raiserror 18155
913                   raiserror 18177
914                   return 1
915               end
916               else
917               begin
918                   /* Save the cache size parameter string.*/
919                   select @config_sz_str = @parm1
920               end
921           end
922   
923           /*
924           ** Process the @parm2.
925           **
926           ** Here the @parm2 can be either a cache type, replacement policy,
927           ** cache partition number or instance name.  
928           */
929           if @parm2 IN ("logonly", "mixed", "inmemory_storage")
930           begin
931               /*
932               **  It's a syntax error if the parm1 already had the cache
933               **  type in it.
934               */
935               if @new_cache_type != 0
936               begin
937   
938                   raiserror 18136
939                   raiserror 18177
940                   return 1
941               end
942   
943               /* It's 'log only' or 'mixed' or 'inmemory_storage'. */
944               if @parm2 = "logonly"
945                   select @new_cache_type = @logonlytype
946               else if @parm2 = 'mixed'
947                   select @new_cache_type = @mixedtype
948               else
949                   select @new_cache_type = @inmemtype
950           end
951           else if @parm2 IN ("strict", "relaxed", "none")
952           begin
953               /*
954               **  It's a syntax error if the parm1 already had the 
955               **  replacement policy in it.
956               */
957               if @new_repl != 0
958               begin
959   
960                   raiserror 18420
961                   raiserror 18177
962                   return 1
963               end
964   
965               if @parm2 = "strict" or @parm2 = "relaxed"
966               begin
967                   if (@new_cache_type & @inmemtype = @inmemtype)
968                   begin
969                       /*
970                       ** It is an error if cache type is 
971                       ** 'inmemory_storage' but replacement policy 
972                       ** is not 'none'.
973                       */
974                       raiserror 19976, @parm2, @cachename
975                       return 1
976                   end
977                   else
978                   begin
979                       if @parm2 = "strict"
980                           select @new_repl = 512
981                       else
982                           select @new_repl = 256
983                   end
984               end
985   
986               if @parm2 = "none"
987               begin
988                   if (@new_cache_type & @inmemtype != @inmemtype)
989                   begin
990                       /*
991                       ** It is an error if replacement policy is
992                       ** 'none' but cache type is not 
993                       ** 'inmemory_storage'.
994                       */
995                       raiserror 19976, @parm2, @cachename
996                       return 1
997                   end
998                   else
999                   begin
1000                      select @new_repl = @nonereplace
1001                  end
1002              end
1003          end
1004          else if (patindex("%cache_partition%", @parm2) != 0)
1005          begin
1006              /* It's a syntax error if partition already specified.*/
1007              if @cache_partition_parm is not NULL
1008              begin
1009                  raiserror 18616
1010                  raiserror 18177
1011                  return 1
1012              end
1013  
1014              select @cache_partition_parm = @parm2
1015          end
1016  
1017          else
1018          begin
1019              /* Issue error if @parm2 has junk string. */
1020              if @parm2 is not NULL
1021              begin
1022                  raiserror 18155
1023                  raiserror 18177
1024                  return 1
1025              end
1026          end
1027  
1028          /*
1029          ** Process the @parm3.
1030          **
1031          ** At this point, @parm3 is either a replacement policy, partition
1032          ** number, instance name, NULL, or a junk string.  
1033          */
1034          if @parm3 IN ("strict", "relaxed", "none")
1035          begin
1036              /*
1037              **  It's a syntax error if the parm1 or parm2 already had the
1038              **  replacement policy in it.
1039              */
1040              if @new_repl != 0
1041              begin
1042  
1043                  raiserror 18420
1044                  raiserror 18177
1045                  return 1
1046              end
1047  
1048              if @parm3 = "strict" or @parm3 = "relaxed"
1049              begin
1050                  if (@new_cache_type & @inmemtype = @inmemtype)
1051                  begin
1052                      /*
1053                      ** It is an error if cache type is 
1054                      ** 'inmemory_storage' but replacement policy 
1055                      ** is not 'none'.
1056                      */
1057                      raiserror 19976, @parm3, @cachename
1058                      return 1
1059                  end
1060                  else
1061                  begin
1062                      if @parm3 = "strict"
1063                          select @new_repl = 512
1064                      else
1065                          select @new_repl = 256
1066                  end
1067              end
1068  
1069              if @parm3 = "none"
1070              begin
1071                  if (@new_cache_type & @inmemtype != @inmemtype)
1072                  begin
1073                      /*
1074                      ** It is an error if replacement policy is
1075                      ** 'none' but cache type is not 
1076                      ** 'inmemory_storage'.
1077                      */
1078                      raiserror 19976, @parm3, @cachename
1079                      return 1
1080                  end
1081                  else
1082                  begin
1083                      select @new_repl = @nonereplace
1084                  end
1085              end
1086          end
1087          else if (patindex("%cache_partition%", @parm3) != 0)
1088          begin
1089              if @cache_partition_parm is not NULL
1090              begin
1091                  raiserror 18616
1092                  raiserror 18177
1093                  return 1
1094              end
1095  
1096              select @cache_partition_parm = @parm3
1097          end
1098  
1099          else
1100          begin
1101              if @parm3 is not NULL
1102              begin
1103                  raiserror 18155
1104                  raiserror 18177
1105                  return 1
1106              end
1107          end
1108  
1109          /*
1110          ** Process the @parm4.
1111          **
1112          ** At this point, @parm4 is either a partition number, instance name,
1113          ** NULL, or a junk string.
1114          */
1115          if (patindex("%cache_partition%", @parm4) != 0)
1116          begin
1117              if @cache_partition_parm is not NULL
1118              begin
1119                  raiserror 18616
1120                  raiserror 18177
1121                  return 1
1122              end
1123  
1124              select @cache_partition_parm = @parm4
1125          end
1126  
1127          else
1128          begin
1129              if @parm4 is not NULL
1130              begin
1131                  raiserror 18177
1132                  return 1
1133              end
1134          end
1135  
1136          /*
1137          ** Process the @parm5.
1138          **
1139          ** At this point, @parm5 is either a partition number, instance name,
1140          ** NULL, or a junk string.
1141          */
1142          if (patindex("%cache_partition%", @parm5) != 0)
1143          begin
1144              if @cache_partition_parm is not NULL
1145              begin
1146                  raiserror 18616
1147                  raiserror 18177
1148                  return 1
1149              end
1150  
1151              select @cache_partition_parm = @parm5
1152          end
1153  
1154          else
1155          begin
1156              if @parm5 is not NULL
1157              begin
1158                  raiserror 18177
1159                  return 1
1160              end
1161          end
1162  
1163          /* Extract and validate instance name from instance parameter. */
1164  
1165  
1166          /*
1167          **  If options are not explicitly given, we use the default value
1168          **  for cache creation.  For cache update case, we set flags to
1169          **  not overwrite the existing values. 
1170          */
1171          if @new_cache_type = 0
1172          begin
1173              /*
1174              ** If the cache type was not specified explicitly,
1175              ** then default cache type to mixed. If the cache
1176              ** name is 'default data cache' then the cache type is
1177              ** 'default'. 
1178              ** We also indicate that the cache type was unspecified
1179              ** by setting @dflt_ctype to 1. So that later, if we are
1180              ** doing an update, we dont overwrite a logonly cache type
1181              ** with the default mixed type.
1182              */
1183              if (charindex("default data cache", @cachename) != 0)
1184              begin
1185                  select @new_cache_type = @defaulttype
1186              end
1187              else
1188              begin
1189                  select @new_cache_type = @mixedtype
1190              end
1191              select @dflt_ctype = 1 /* cache type was unspecified */
1192          end
1193  
1194          if @new_repl = 0
1195          begin
1196              /*
1197              ** If the cache replacement was not specified explicitly,
1198              ** Set dflt_crepl so that later on we don't overwrite the 
1199              ** replacement policy of a cache that is using the DEFAULT
1200              ** replacement policy.
1201              */
1202              if @new_cache_type = @inmemtype
1203              begin
1204                  /* The default replacement policy for 'inmemory_storage'
1205                  ** cache is 'none'.
1206                  */
1207                  select @new_repl = @nonereplace
1208              end
1209              else
1210              begin
1211                  select @dflt_crepl = 1
1212              end
1213          end
1214  
1215          /*
1216          ** If the cache partition was not specified explicitly,
1217          ** set @partition_number to 0. Also set flag dflt_cpart to 1
1218          ** so later on we don't overwrite the existing partition number.
1219          */
1220          if @cache_partition_parm is NULL
1221          begin
1222              select @dflt_cpart = 1
1223          end
1224          /* cache partition was specified, validate the partition number. */
1225          else
1226          begin -- {
1227  
1228              select @cache_partition_loc = patindex("%=%",
1229                      @cache_partition_parm)
1230  
1231              /* if there is no "=" sign then return an error */
1232              if (@cache_partition_loc = 0)
1233              begin
1234                  raiserror 18611
1235                  return 1
1236              end
1237  
1238              /* skip the "=" part */
1239              select @cache_partition_loc = @cache_partition_loc + 1
1240  
1241              select @cache_partition_str = convert(varchar(255),
1242                  substring(@cache_partition_parm,
1243                      @cache_partition_loc,
1244                      char_length(@cache_partition_parm) -
1245                      @cache_partition_loc + 1))
1246  
1247              if @cache_partition_str != "default"
1248              begin
1249                  /*
1250                  ** This is a new status to indicate that a valid cache
1251                  ** partition other than default is passed
1252                  */
1253                  select @c_part_stat = @cmpartspecified
1254  
1255                  select @partition_number =
1256                      convert(int, @cache_partition_str)
1257  
1258                  /*
1259                  **  The cache partition number needs to be 
1260                  **  within 1 to 256
1261                  */
1262                  if (@partition_number < 1) OR (@partition_number > 256)
1263                  begin
1264                      raiserror 18611
1265                      return 1
1266                  end
1267  
1268                  /* The cache partition number needs to be power of 2.*/
1269                  select @cache_part_temp = 2
1270                  while @cache_part_temp < @partition_number
1271                      select @cache_part_temp = @cache_part_temp * 2
1272                  if (@partition_number != 1)
1273                      AND (@cache_part_temp != @partition_number)
1274                  begin
1275                      raiserror 18611
1276                      return 1
1277                  end
1278              end
1279          end -- }
1280  
1281          /* Convert the specified size into kbytes. */
1282          select @size = 0
1283          if @config_sz_str is not NULL
1284          begin
1285              exec @retstat = sp_aux_getsize @config_sz_str, @size output
1286  
1287              if @retstat = 0
1288              begin
1289                  raiserror 18177
1290                  return 1
1291              end
1292          end
1293  
1294          /* fetch the current cache size, for configuration history auditing. */
1295          select @oldsize = value
1296          from master.dbo.sysconfigures
1297          where parent = @cfguserdefinedcache
1298              and config = @cfguserdefinedcache
1299              and status & @deletedtype != @deletedtype
1300              and name = @cachename
1301  
1302  
1303          /*
1304          **  Find out if we need to update, insert, or delete this cache
1305          **  from sysconfigures.
1306          */
1307          select @oldlocalstat = - 1
1308          select @oldglobalstat = - 1
1309  
1310          /* fetch the instance specific current cache status. */
1311  
1312          select @oldlocalstat = status
1313          from master.dbo.sysconfigures
1314          where parent = @cfguserdefinedcache
1315              and config = @cfguserdefinedcache
1316              and name = @cachename
1317  
1318          /* 
1319          ** fetch the current global cache status. Please note that for a global
1320          ** cache, it is same as @oldlocalstat fetched above.
1321          */
1322          select @oldglobalstat = status
1323          from master.dbo.sysconfigures
1324          where parent = @cfguserdefinedcache
1325              and config = @cfguserdefinedcache
1326              and name = @cachename
1327  
1328  
1329          /* If the cache exists then it's an update/delete.*/
1330          if (@oldlocalstat != - 1) and (@oldlocalstat & @deletedtype != @deletedtype)
1331          begin -- {
1332              /* If the specified size is 0, then delete the cache. */
1333              if @config_sz_str is not NULL AND @size = 0
1334              begin
1335                  if (@oldlocalstat & @defaulttype = @defaulttype)
1336  
1337                  begin
1338                      /* Can't delete the global default cache.*/
1339                      raiserror 18137
1340                      return 1
1341                  end
1342  
1343                  /*  This is a cache delete. */
1344                  select @operation = @cfgcachedelete
1345              end
1346              else
1347              begin
1348                  /*  This is a cache update. */
1349                  select @operation = @cfgcacheupdate
1350              end
1351          end -- }
1352          else
1353          begin -- {
1354              if (@config_sz_str is NULL or @size = 0)
1355              begin
1356                  /* Can't create a cache with 0 size.*/
1357                  raiserror 18135, @cachename
1358                  return 1
1359              end
1360  
1361              select @operation = @cfgcacheinsert
1362          end -- }
1363  
1364  
1365  
1366          /* 
1367          ** Prevent cache creation, or update, if it leads to incompatible status
1368          ** for the same cache on different instances. If no new status was
1369          ** explicitly provided by the user (@dflt_ctype = 1), we retain the old
1370          ** status during update and so do not need this check.
1371          */
1372          if (@operation = @cfgcacheinsert)
1373              OR ((@operation = @cfgcacheupdate) AND (@dflt_ctype != 1))
1374          begin -- {
1375  
1376              /* 
1377              ** Note, the sproc accepts only 'mixed'/'log-only' status as
1378              ** input. And for default data cache it should never be allowed.
1379              */
1380              if (charindex("default data cache", @cachename) != 0)
1381                  AND (@dflt_ctype != 1)
1382              begin
1383                  raiserror 19981
1384                  return 1
1385              end
1386  
1387  
1388          end -- } 
1389  
1390          /*
1391          **  Begin a transaction.  We'll be modifying rows in sysconfigures
1392          **  and then calling the built-in function config_admin to verify the
1393          **  new configuration.  If config_admin fails the verification phase
1394          **  we'll have to rollback the changes to sysconfigures.
1395          */
1396  
1397          begin tran cacheconfig
1398  
1399          if @operation = @cfgcacheinsert
1400          begin -- {
1401              /*
1402              ** This is a cache create. Insert a row for the new
1403              ** cache into sysconfigures. The "status" field
1404              ** is made up of several components:
1405              **	The cache type (eg "logonly" or "mixed" or "default"
1406              ** 	for instance specific default data cache)
1407              **	The initial cache status (set to "pending/active")
1408              **	The cache replacement policy ("strict" or "relaxed")
1409              **	Whether the cache was user-partitioned or not.
1410              ** 
1411              ** NOTE that the @oldlocalstat may be = -1 here in which case
1412              ** the following condition will be always true (-1 = 0xffff). 
1413              ** But it is harmless as we're only deleting entries marked 
1414              ** deleted.
1415              */
1416              if (@oldlocalstat & @deletedtype = @deletedtype)
1417              begin
1418                  /*
1419                  ** If there exists any cache entries with same name
1420                  ** and status as deleted then delete them.
1421                  */
1422                  delete from master.dbo.sysconfigures
1423                  where parent = @cfguserdefinedcache
1424                      and name = @cachename
1425                      and status = @deletedtype
1426              end
1427  
1428              insert into master.dbo.sysconfigures
1429              values (@cfguserdefinedcache, @size, "User Defined Cache",
1430                  (@new_cache_type | @activependingtype | @new_repl | @c_part_stat),
1431                  @cachename, @cfguserdefinedcache, @config_sz_str, 0,
1432                  @partition_number
1433  
1434              )
1435  
1436  
1437  
1438              /*
1439              ** If there is already a global configuration and the
1440              ** request is for instance specific configuration then
1441              ** we need to copy global pool configuration rows and 
1442              ** set the instnceid of rows to target instanceid.
1443              ** Otherwise, we insert only default pool information.
1444              */
1445              if (@oldglobalstat != - 1)
1446                  AND (@oldglobalstat & @deletedtype != @deletedtype)
1447              begin -- {
1448  
1449                  select @row_count = count(*)
1450                  from master.dbo.sysconfigures
1451                  where parent = @cfguserdefinedcache
1452                      and config != @cfguserdefinedcache
1453                      and name = @cachename
1454  
1455  
1456                  declare pool_info cursor for
1457                  select config, value, comment, name, parent,
1458                      value2, value3, value4
1459                  from master.dbo.sysconfigures
1460                  where parent = @cfguserdefinedcache
1461                      and config != @cfguserdefinedcache
1462                      and name = @cachename
1463  
1464  
1465                  open pool_info
1466  
1467                  while (@row_count > 0)
1468                  begin
1469                      fetch pool_info into @config, @value, @comment,
1470                          @name, @parent, @value2,
1471                          @value3, @value4
1472  
1473                      /* Insert pool information */
1474                      insert into master.dbo.sysconfigures
1475                      values (@config, @value, @comment,
1476                          (@new_cache_type | @activependingtype | @new_repl | @c_part_stat),
1477                          @name, @parent, @value2, @value3, @value4
1478  
1479                      )
1480  
1481                      select @row_count = @row_count - 1
1482                  end
1483  
1484                  close pool_info
1485              end -- }
1486              else
1487              begin -- {
1488                  /*
1489                  ** The newly-created cache must have at least a "pagesize"
1490                  ** buffer pool. Insert a row into sysconfigures to reflect
1491                  ** this. Like the cache, the pool is initially set to
1492                  ** the "pending/active" state. 
1493                  */
1494                  select @pool_config_id
1495                      = @cfgbuffer2kpoolgrp
1496                      + (log10(@@maxpagesize / @@pagesize) / log10(2))
1497                  select @msg = rtrim(convert(varchar(255), @@maxpagesize / 1024)) +
1498                      "K I/O Buffer Pool"
1499                  insert into master.dbo.sysconfigures
1500                  values (@pool_config_id, 0, @msg,
1501                      (@new_cache_type | @activependingtype | @new_repl | @c_part_stat),
1502                      @cachename, @cfguserdefinedcache, NULL, 0, (- 1)
1503  
1504                  )
1505  
1506  
1507              end -- }
1508          end -- }
1509          else if @operation = @cfgcachedelete
1510          begin -- {
1511              /*
1512              ** Retain the existing cache type(7=default+mixed+logonly) and
1513              ** replacement policy(768=strict+relaxed) and partition state
1514              ** (1024=cmpartspecified) and set the Pend/Del(128) bit.
1515              */
1516  
1517              update master.dbo.sysconfigures
1518              set status = ((status & (7 | 768 | 1024)) | @deletependingtype)
1519              where parent = @cfguserdefinedcache
1520                  and name = @cachename
1521  
1522          end -- }
1523          else if @operation = @cfgcacheupdate
1524          begin -- {
1525              /*
1526              **  If @dflt_ctype is set to 1, we dont update the
1527              **  existing cache type with the default mixed type.
1528              */
1529              if @new_cache_type != 0 and @dflt_ctype != 1
1530              begin -- {
1531                  /*
1532                  ** If we're making the cache a log only cache then
1533                  ** verify that there are no non-log objects bound to
1534                  ** the cache.
1535                  */
1536                  if (@new_cache_type & @logonlytype = @logonlytype)
1537                  begin
1538                      select @retstat = config_admin(@cmdbindingcheck, 1,
1539                              0, 0,
1540  
1541                              NULL,
1542  
1543                              @cachename)
1544                      if @retstat = 0
1545                      begin
1546                          raiserror 18140
1547                          rollback tran cacheconfig
1548                          return 1
1549                      end
1550                  end
1551  
1552  
1553                  select @old_stat = status
1554                  from master.dbo.sysconfigures
1555                  where parent = @cfguserdefinedcache
1556                      and config = @cfguserdefinedcache
1557                      and name = @cachename
1558  
1559  
1560                  /*
1561                  ** If the cache type is changing then we need to
1562                  ** turn off the old cache type bits in the status
1563                  ** word before or-ing the new value in.
1564                  */
1565                  if (@old_stat & @new_cache_type != @new_cache_type)
1566                  begin
1567                      if ((@old_stat & @inmemtype = @inmemtype) or
1568                              (@new_cache_type = @inmemtype))
1569                      begin
1570                          /*
1571                          ** Disallow changing the cache type
1572                          ** between 'inmemory_storage' and 
1573                          ** 'logonly | mixed'.
1574                          */
1575                          raiserror 19977, @cachename, 'inmemory_storage'
1576                          rollback tran cacheconfig
1577                          return 1
1578                      end
1579  
1580                      select @old_stat = @old_stat & ~ 6
1581                      select @old_stat = @old_stat & ~ @deletedtype
1582                      select @old_stat = @old_stat | @new_cache_type
1583  
1584  
1585                      update master.dbo.sysconfigures
1586                      set status = @old_stat
1587                      where parent = @cfguserdefinedcache
1588                          and name = @cachename
1589  
1590                  end
1591  
1592              end -- }
1593              else
1594              begin -- {
1595                  /*
1596                  ** Make sure @new_cache_type has the existing cache type
1597                  ** so we don't overwrite it with the default value when
1598                  ** we we call config_admin() down below.
1599                  */
1600                  if (@oldlocalstat & @defaulttype) = @defaulttype
1601                  begin
1602                      select @new_cache_type = @defaulttype
1603                  end
1604                  else if (@oldlocalstat & @mixedtype) = @mixedtype
1605                  begin
1606                      select @new_cache_type = @mixedtype
1607                  end
1608                  else if (@oldlocalstat & @logonlytype) = @logonlytype
1609                  begin
1610                      select @new_cache_type = @logonlytype
1611                  end
1612              end -- }
1613  
1614              /*
1615              ** If @dflt_crepl is set to 1, we dont update the existing
1616              ** cache replacement with the default replacement policy.
1617              end
1618              else if (@old_stat & @inmemtype) = @inmemtype
1619              begin
1620              select @new_cache_type = @inmemtype
1621              */
1622              if @new_repl != 0 and @dflt_crepl != 1
1623              begin -- {
1624  
1625  
1626                  select @old_stat = status
1627                  from master.dbo.sysconfigures
1628                  where parent = @cfguserdefinedcache
1629                      and config = @cfguserdefinedcache
1630                      and name = @cachename
1631  
1632  
1633  
1634                  /*
1635                  ** If the replacement policy is changing then we need to
1636                  ** turn off the old replacement policy
1637                  ** (768=strict+relaxed) bits in the status word before
1638                  ** or-ing the new value.
1639                  */
1640                  if (@old_stat & @new_repl != @new_repl)
1641                  begin
1642                      if ((@old_stat & @nonereplace = @nonereplace) or
1643                              (@new_repl = @nonereplace))
1644                      begin
1645                          /*
1646                          ** Disallow changing cache replacement
1647                          ** policy between 'none' and 'strict|
1648                          ** relaxed'.
1649                          */
1650                          raiserror 19978, 'none', 'strict|relaxed'
1651                          rollback tran cacheconfig
1652                          return 1
1653                      end
1654  
1655                      select @old_stat = @old_stat & ~ 768
1656                      select @old_stat = @old_stat | @new_repl
1657  
1658  
1659                      update master.dbo.sysconfigures
1660                      set status = @old_stat
1661                      where parent = @cfguserdefinedcache
1662                          and name = @cachename
1663  
1664                  end
1665              end -- }
1666  
1667              /*
1668              ** If @dflt_cpart is set to 1, this is the case where cache
1669              ** partition is not specified in sp_cacheconfig, then we do not
1670              ** update the existing cache partition number.
1671              */
1672              if @dflt_cpart != 1
1673              begin
1674  
1675                  select @old_stat = status
1676                  from master.dbo.sysconfigures
1677                  where parent = @cfguserdefinedcache
1678                      and config = @cfguserdefinedcache
1679                      and name = @cachename
1680  
1681  
1682                  select @old_stat = @old_stat & ~ @cmpartspecified
1683                  select @old_stat = @old_stat | @c_part_stat
1684  
1685  
1686                  update master.dbo.sysconfigures
1687                  set value4 = @partition_number, status = @old_stat
1688                  where config = @cfguserdefinedcache
1689                      and parent = @cfguserdefinedcache
1690                      and name = @cachename
1691  
1692              end
1693  
1694              if @config_sz_str is not NULL
1695              begin -- {
1696  
1697                  update master.dbo.sysconfigures
1698                  set value2 = @config_sz_str, value = @size
1699                  where config = @cfguserdefinedcache
1700                      and parent = @cfguserdefinedcache
1701                      and name = @cachename
1702  
1703  
1704                  /*
1705                  ** Retain the current cache type and set status to
1706                  ** Active or Pend/Act if the current status is Pend/Del.
1707                  */
1708  
1709                  select @old_stat = status
1710                  from master.dbo.sysconfigures
1711                  where parent = @cfguserdefinedcache
1712                      and config = @cfguserdefinedcache
1713                      and name = @cachename
1714  
1715  
1716                  if (@old_stat & @deletependingtype = @deletependingtype)
1717                  begin -- {
1718                      if exists (select value
1719                              from master.dbo.syscurconfigs
1720                              where config = @cfguserdefinedcache
1721                                  and comment = @cachename
1722  
1723                              )
1724                      begin
1725                          /* 
1726                          ** The cache is active prior to 
1727                          ** being deleted. 
1728                          */
1729                          select @old_stat = @activetype
1730                      end
1731                      else
1732                      begin
1733                          /* 
1734                          ** The cache is Pend/Act prior to
1735                          ** being deleted.
1736                          */
1737                          select @old_stat = @activependingtype
1738                      end
1739  
1740  
1741                      update master.dbo.sysconfigures
1742                      set status = ((status & (7 | 768 | 1024))
1743                          | @old_stat)
1744                      where parent = @cfguserdefinedcache
1745                          and name = @cachename
1746  
1747  
1748                  end -- }
1749              end -- }
1750          end -- }
1751  
1752          select @retstat = config_admin(@operation, @cfguserdefinedcache,
1753                  @partition_number, @new_cache_type,
1754  
1755                  NULL,
1756  
1757                  @cachename)
1758  
1759          /* If the status is '1' then operation is successful. Otherwise rollback */
1760          if @retstat != 1
1761          begin
1762              rollback tran cacheconfig
1763              return 1
1764          end
1765  
1766          /* Operation is successfull. */
1767          /* 
1768          ** The return values for successfull calls is in cachemgr.h
1769          ** under the enum ONL_CACHE_RET_STATUS.
1770          */
1771  
1772          if @operation = @cfgcacheinsert
1773          begin -- {
1774              /*
1775              ** The cache creation was successful so modify the status of the
1776              ** cache & the default pool from "pending/active" to "active".
1777              */
1778  
1779              update master.dbo.sysconfigures
1780              set status = (@new_cache_type | @activetype
1781                  | @new_repl | @c_part_stat)
1782              where parent = @cfguserdefinedcache
1783                  and name = @cachename
1784  
1785  
1786          end -- }
1787          else if @operation = @cfgcachedelete
1788          begin -- {
1789  
1790              delete from master.dbo.sysconfigures
1791              where parent = @cfguserdefinedcache
1792                  and name = @cachename
1793  
1794          end -- }
1795  
1796          /*
1797          **  If the type of this cache has changed then we need to make sure
1798          **  that if the new type is log only, there are no non-log objects
1799          **  bound to this cache.
1800          */
1801          if (@oldlocalstat & @new_cache_type != @new_cache_type)
1802              and @operation != @cfgcacheinsert
1803              and @operation != @cfgcachedelete
1804          begin
1805              if (@new_cache_type & @logonlytype = @logonlytype)
1806              begin
1807                  select @retstat = config_admin(@cmdbindingcheck, 1, 0,
1808                          0,
1809  
1810                          NULL,
1811  
1812                          @cachename)
1813                  if @retstat = 0
1814                  begin
1815                      raiserror 18140
1816                      rollback tran cacheconfig
1817                      return 1
1818                  end
1819              end
1820          end
1821  
1822  
1823  
1824          commit tran cacheconfig
1825  
1826          /*
1827          ** Record the cache configuration change for configuration history
1828          ** auditing.
1829          */
1830          select @cha_area = "CACHE"
1831          select @cha_type = "sp_cacheconfig"
1832          select @cha_target = @cachename
1833  
1834          if (@dflt_ctype = 0)
1835          begin
1836              select @cha_element = "cache type: " +
1837                  case @new_cache_type
1838                      when @logonlytype then "logonly"
1839                      when @mixedtype then "mixed"
1840                      when @inmemtype then "inmemory_storage"
1841                      else "unknown"
1842                  end
1843          end
1844  
1845          if (@dflt_crepl = 0)
1846          begin
1847              select @cha_element = @cha_element +
1848                  case when @cha_element != NULL then ", " end +
1849                  "replacement policy: " +
1850                  case @new_repl
1851                      when @strictlru then "strict"
1852                      when @relaxedlru then "relaxed"
1853                      else "unknown"
1854                  end
1855          end
1856  
1857          if (@dflt_cpart = 0)
1858          begin
1859              select @cha_element = @cha_element +
1860                  case when @cha_element != NULL then ", " end +
1861                  "partition number: " +
1862                  convert(varchar(10), @partition_number)
1863          end
1864  
1865          select @cha_oldvalue = convert(varchar(255), isnull(@oldsize, 0))
1866  
1867          if (@config_sz_str is NULL) or (@oldsize = @size)
1868              select @cha_newvalue = "not changed"
1869          else
1870              select @cha_newvalue = convert(varchar(255), @size)
1871  
1872          select @cha_mode = NULL
1873          select @cha_instanceid = @instanceid
1874  
1875          select @cha_ret = audit_config_history(
1876                  @cha_area,
1877                  @cha_type,
1878                  @cha_target,
1879                  @cha_element,
1880                  @cha_oldvalue,
1881                  @cha_newvalue,
1882                  @cha_mode,
1883                  @cha_instanceid)
1884          return 0
1885      end -- }
1886  


exec sp_procxmode 'sp_cacheconfig', 'AnyMode'
go

Grant Execute on sp_cacheconfig to public
go
RESULT SETS
sp_cacheconfig_rset_001
sp_cacheconfig_rset_003
sp_cacheconfig_rset_002

DEFECTS
 QCAR 6 Cartesian product between tables master..sysconfigures co and [master..syscurconfigs cu] 316
 MCTR 4 Conditional Begin Tran or Commit Tran 1397
 MCTR 4 Conditional Begin Tran or Commit Tran 1824
 MEST 4 Empty String will be replaced by Single Space 607
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 MTYP 4 Assignment type mismatch Status: varchar(9) = char(10) 536
 MTYP 4 Assignment type mismatch configval: char(12) = varchar(13) 538
 MTYP 4 Assignment type mismatch runval: char(12) = char(13) 538
 MTYP 4 Assignment type mismatch instanceid: tinyint = int 540
 MTYP 4 Assignment type mismatch instanceid: tinyint = int 624
 MTYP 4 Assignment type mismatch @cache_partition_parm: varchar(30) = varchar(133) 897
 MTYP 4 Assignment type mismatch @config_sz_str: varchar(13) = varchar(133) 919
 MTYP 4 Assignment type mismatch @cache_partition_parm: varchar(30) = varchar(133) 1014
 MTYP 4 Assignment type mismatch @cache_partition_parm: varchar(30) = varchar(133) 1096
 MTYP 4 Assignment type mismatch @cache_partition_parm: varchar(30) = varchar(133) 1124
 MTYP 4 Assignment type mismatch @cache_partition_parm: varchar(30) = varchar(255) 1151
 MTYP 4 Assignment type mismatch config: smallint = int 1429
 MTYP 4 Assignment type mismatch parent: smallint = int 1431
 MTYP 4 Assignment type mismatch config: smallint = int 1475
 MTYP 4 Assignment type mismatch config: smallint = int 1500
 MTYP 4 Assignment type mismatch parent: smallint = int 1502
 MTYP 4 Assignment type mismatch @cha_target: varchar(30) = varchar(255) 1832
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 660
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 331
 QTYP 4 Comparison type mismatch smallint = int 331
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 345
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 361
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 707
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 773
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 780
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 785
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1297
 QTYP 4 Comparison type mismatch smallint = int 1297
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1298
 QTYP 4 Comparison type mismatch smallint = int 1298
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1314
 QTYP 4 Comparison type mismatch smallint = int 1314
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1315
 QTYP 4 Comparison type mismatch smallint = int 1315
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1324
 QTYP 4 Comparison type mismatch smallint = int 1324
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1325
 QTYP 4 Comparison type mismatch smallint = int 1325
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1423
 QTYP 4 Comparison type mismatch smallint = int 1423
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1451
 QTYP 4 Comparison type mismatch smallint = int 1451
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1452
 QTYP 4 Comparison type mismatch smallint = int 1452
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1460
 QTYP 4 Comparison type mismatch smallint = int 1460
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1461
 QTYP 4 Comparison type mismatch smallint = int 1461
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1519
 QTYP 4 Comparison type mismatch smallint = int 1519
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1555
 QTYP 4 Comparison type mismatch smallint = int 1555
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1556
 QTYP 4 Comparison type mismatch smallint = int 1556
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1587
 QTYP 4 Comparison type mismatch smallint = int 1587
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1628
 QTYP 4 Comparison type mismatch smallint = int 1628
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1629
 QTYP 4 Comparison type mismatch smallint = int 1629
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1661
 QTYP 4 Comparison type mismatch smallint = int 1661
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1677
 QTYP 4 Comparison type mismatch smallint = int 1677
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1678
 QTYP 4 Comparison type mismatch smallint = int 1678
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1688
 QTYP 4 Comparison type mismatch smallint = int 1688
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1689
 QTYP 4 Comparison type mismatch smallint = int 1689
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1699
 QTYP 4 Comparison type mismatch smallint = int 1699
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1700
 QTYP 4 Comparison type mismatch smallint = int 1700
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1711
 QTYP 4 Comparison type mismatch smallint = int 1711
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1712
 QTYP 4 Comparison type mismatch smallint = int 1712
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1720
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1744
 QTYP 4 Comparison type mismatch smallint = int 1744
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1782
 QTYP 4 Comparison type mismatch smallint = int 1782
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1791
 QTYP 4 Comparison type mismatch smallint = int 1791
 TNOI 4 Table with no index master..syscurconfigs master..syscurconfigs
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause sysc_cursor 375
 CUSU 3 Cursor updated through 'searched update': risk of halloween rows pool_info 1474
 CUUP 3 Cursor updated: cursor should contain 'for update' clause pool_info 1474
 MGTP 3 Grant to public master..sysconfigures  
 MGTP 3 Grant to public master..syscurconfigs  
 MGTP 3 Grant to public sybsystemprocs..sp_cacheconfig  
 MLCH 3 Char type with length>30 char(255) 81
 MLCH 3 Char type with length>30 char(279) 690
 MLCH 3 Char type with length>30 char(272) 692
 MNAC 3 Not using ANSI 'is null' 1848
 MNAC 3 Not using ANSI 'is null' 1860
 MNER 3 No Error Check should check @@error after select into 309
 MNER 3 No Error Check should check @@error after insert 323
 MNER 3 No Error Check should check @@error after insert 535
 MNER 3 No Error Check should check @@error after insert 610
 MNER 3 No Error Check should check @@error after update 646
 MNER 3 No Error Check should check return value of exec 660
 MNER 3 No Error Check should check @@error after delete 1422
 MNER 3 No Error Check should check @@error after insert 1428
 MNER 3 No Error Check should check @@error after insert 1474
 MNER 3 No Error Check should check @@error after insert 1499
 MNER 3 No Error Check should check @@error after update 1517
 MNER 3 No Error Check should check @@error after update 1585
 MNER 3 No Error Check should check @@error after update 1659
 MNER 3 No Error Check should check @@error after update 1686
 MNER 3 No Error Check should check @@error after update 1697
 MNER 3 No Error Check should check @@error after update 1741
 MNER 3 No Error Check should check @@error after update 1779
 MNER 3 No Error Check should check @@error after delete 1790
 MUCO 3 Useless Code Useless Brackets 248
 MUCO 3 Useless Code Useless Brackets 285
 MUCO 3 Useless Code Useless Brackets 383
 MUCO 3 Useless Code Useless Brackets 416
 MUCO 3 Useless Code Useless Brackets 420
 MUCO 3 Useless Code Useless Brackets 424
 MUCO 3 Useless Code Useless Brackets 437
 MUCO 3 Useless Code Useless Brackets 447
 MUCO 3 Useless Code Useless Brackets 475
 MUCO 3 Useless Code Useless Brackets 477
 MUCO 3 Useless Code Useless Brackets 482
 MUCO 3 Useless Code Useless Brackets 486
 MUCO 3 Useless Code Useless Brackets 490
 MUCO 3 Useless Code Useless Brackets 499
 MUCO 3 Useless Code Useless Brackets 503
 MUCO 3 Useless Code Useless Brackets 515
 MUCO 3 Useless Code Useless Brackets 519
 MUCO 3 Useless Code Useless Brackets 557
 MUCO 3 Useless Code Useless Brackets 559
 MUCO 3 Useless Code Useless Brackets 569
 MUCO 3 Useless Code Useless Brackets 576
 MUCO 3 Useless Code Useless Brackets 590
 MUCO 3 Useless Code Useless Brackets 613
 MUCO 3 Useless Code Useless Brackets 642
 MUCO 3 Useless Code Useless Brackets 650
 MUCO 3 Useless Code Useless Brackets 657
 MUCO 3 Useless Code Useless Brackets 749
 MUCO 3 Useless Code Useless Brackets 804
 MUCO 3 Useless Code Useless Brackets 806
 MUCO 3 Useless Code Useless Brackets 815
 MUCO 3 Useless Code Useless Brackets 856
 MUCO 3 Useless Code Useless Brackets 877
 MUCO 3 Useless Code Useless Brackets 894
 MUCO 3 Useless Code Useless Brackets 899
 MUCO 3 Useless Code Useless Brackets 967
 MUCO 3 Useless Code Useless Brackets 988
 MUCO 3 Useless Code Useless Brackets 1004
 MUCO 3 Useless Code Useless Brackets 1050
 MUCO 3 Useless Code Useless Brackets 1071
 MUCO 3 Useless Code Useless Brackets 1087
 MUCO 3 Useless Code Useless Brackets 1115
 MUCO 3 Useless Code Useless Brackets 1142
 MUCO 3 Useless Code Useless Brackets 1183
 MUCO 3 Useless Code Useless Brackets 1232
 MUCO 3 Useless Code Useless Brackets 1335
 MUCO 3 Useless Code Useless Brackets 1354
 MUCO 3 Useless Code Useless Brackets 1416
 MUCO 3 Useless Code Useless Brackets 1467
 MUCO 3 Useless Code Useless Brackets 1496
 MUCO 3 Useless Code Useless Brackets 1502
 MUCO 3 Useless Code Useless Brackets 1536
 MUCO 3 Useless Code Useless Brackets 1565
 MUCO 3 Useless Code Useless Brackets 1567
 MUCO 3 Useless Code Useless Brackets 1640
 MUCO 3 Useless Code Useless Brackets 1642
 MUCO 3 Useless Code Useless Brackets 1716
 MUCO 3 Useless Code Useless Brackets 1805
 MUCO 3 Useless Code Useless Brackets 1834
 MUCO 3 Useless Code Useless Brackets 1845
 MUCO 3 Useless Code Useless Brackets 1857
 MUIN 3 Column created using implicit nullability 255
 MUIN 3 Column created using implicit nullability 266
 QAFM 3 Var Assignment from potentially many rows 717
 QAFM 3 Var Assignment from potentially many rows 722
 QAFM 3 Var Assignment from potentially many rows 727
 QAFM 3 Var Assignment from potentially many rows 732
 QCRS 3 Conditional Result Set 681
 QCRS 3 Conditional Result Set 751
 QCRS 3 Conditional Result Set 760
 QCTC 3 Conditional Table Creation 255
 QCTC 3 Conditional Table Creation 266
 QCTC 3 Conditional Table Creation 309
 QDIS 3 Check correct use of 'select distinct' 324
 QISO 3 Set isolation level 229
 QNAJ 3 Not using ANSI Inner Join 316
 QNAJ 3 Not using ANSI Inner Join 330
 QNUA 3 Should use Alias: Column parent should use alias co 309
 QNUA 3 Should use Alias: Column apf_percent should use alias cu 312
 QNUA 3 Should use Alias: Column memory_used should use alias cu 312
 QNUA 3 Should use Alias: Column parent should use alias co 324
 QNUA 3 Should use Alias: Column apf_percent should use alias cu 327
 QNUA 3 Should use Alias: Column memory_used should use alias cu 327
 QNUA 3 Should use Alias: Column parent should use alias co 331
 QNUA 3 Should use Alias: Column name should use alias co 334
 QPNC 3 No column in condition 317
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {parent, name}
331
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {parent, name}
1423
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {parent, name}
1519
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {parent, name}
1587
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {parent, name}
1661
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {parent, name}
1744
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {parent, name}
1782
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {parent, name}
1791
 VNRD 3 Variable is not read @cmdupdateconfigfile 166
 VNRD 3 Variable is not read @cfgmaxcachevalue 167
 VNRD 3 Variable is not read @cfgcfgfilename 183
 VNRD 3 Variable is not read @dummy 811
 VNRD 3 Variable is not read @instancename 831
 VNRD 3 Variable is not read @instance_parm 902
 VNRD 3 Variable is not read @cha_ret 1875
 VUNU 3 Variable is not used @old_repl 111
 VUNU 3 Variable is not used @config_file_name 131
 VUNU 3 Variable is not used @total_config 143
 CRDO 2 Read Only Cursor Marker (has an 'order by' clause) 375
 CUPD 2 Updatable Cursor Marker (updatable by default) 1457
 MRST 2 Result Set Marker 681
 MRST 2 Result Set Marker 751
 MRST 2 Result Set Marker 760
 MSUB 2 Subquery Marker 747
 MSUB 2 Subquery Marker 1718
 MTR1 2 Metrics: Comments Ratio Comments: 37% 65
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 152 = 188dec - 38exi + 2 65
 MTR3 2 Metrics: Query Complexity Complexity: 799 65
 PRED_QUERY_COLLECTION 2 {c=master..syscurconfigs, c2=master..sysconfigures} 0 324

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.value   °.value2   °.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_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_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_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.value4  
@parm3sysconfigures.value4  
@parm4sysconfigures.value4  
@parm5sysconfigures.value4  

DEPENDENCIES
PROCS AND TABLES USED
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 master..sysconfigures (1)  
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_001 
writes table sybsystemprocs..sp_cacheconfig_rset_002 
reads table master..syscurconfigs (1)  
read_writes table tempdb..#cache_info (1) 
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
calls proc sybsystemprocs..sp_aux_getsize  

CALLERS
called by proc sybsystemprocs..sp_do_poolconfig  
   called by proc sybsystemprocs..sp_poolconfig