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