The default data cache and the procedure cache for Adaptive Server are specified using an absolute value. The first step in planning cache configuration and implementing caches is to set the max memory configuration parameter. After you set max memory, determine how much space you want to allocate for data caches on your server. The size of a data cache is limited only by access to memory on the system; however, max memory should be larger than total logical memory. You must specify an absolute value for the size of the default data cache and all other user-defined caches. For an overview of Adaptive Server memory usage, see Chapter 18, “Configuring Memory.”
You can configure data caches in two ways:
Interactively, using sp_cacheconfig and sp_poolconfig
By editing your configuration file
The following sections describe how to use sp_cacheconfig and sp_poolconfig. See “Configuring data caches with the configuration file” for information about using the configuration file.
Each time you execute sp_cacheconfig or sp_poolconfig, Adaptive Server writes the new cache or pool information into the configuration file and copies the old version of the file to a backup file. A message giving the backup file name is sent to the error log.
The syntax to create a new cache is:
sp_cacheconfig cache_name, "size[P|K|M|G]"
Size units can be specified with:
P – pages (Adaptive Server logical page size)
K – kilobytes (default)
M – megabytes
G – gigabytes
Maximum data cache size is limited only by the amount of memory available on your system.
This command configures a 10MB cache named pubs_cache:
sp_cacheconfig pubs_cache, "10M"
This command makes changes in the system tables and writes the new values to the configuration file, but does not activate the cache. You must restart Adaptive Server for the changes to take effect.
Using sp_cacheconfig to see the configuration before a restart shows different “Config” and “Run” values:
sp_cacheconfig pubs_cache
Cache Name Status Type Config Value Run Value ------------------------- --------- -------- ------------ ------------ pubs_cache Pend/Act Mixed 10.00 Mb 0.00 Mb ------------ ------------ Total 10.00 Mb 0.00 Mb
The status “Pend/Act” for pubs_cache shows that the configuration of this cache is pending, waiting for a restart. “Config Value” displays 10MB, and “Run Value” displays 0. Run values and configuration values are also different when you delete caches and when you change their size.
The section of output that provides detail about pools does not print for caches that are not active.
After a restart of Adaptive Server, sp_cacheconfig reports:
sp_cacheconfig
Cache Name Status Type Config Value Run Value ------------------------- --------- -------- ------------ ------------ default data cache Active Default 0.00 Mb 49.37 Mb pubs_cache Active Mixed 10.00 Mb 10.00 Mb ------------ ------------ Total 10.00 Mb 59.37 Mb ====================================================================== Cache: default data cache, Status: Active, Type: Default Config Size: 0.00 Mb, Run Size: 49.37 Mb Config Replacement: strict LRU, Run Replacement: strict LRU Config Partition: 1, Run Partition: 1 IO Size Wash Size Config Size Run Size APF Percent -------- --------- ------------ ------------ ----------- 2 Kb 10110 Kb 0.00 Mb 49.37 Mb 10 ====================================================================== Cache: pubs_cache, Status: Active, Type: Mixed Config Size: 10.00 Mb, Run Size: 10.00 Mb Config Replacement: strict LRU, Run Replacement: strict LRU Config Partition: 1, Run Partition: 1 IO Size Wash Size Config Size Run Size APF Percent -------- --------- ------------ ------------ ----------- 2 Kb 2048 Kb 0.00 Mb 10.00 Mb 10
The pubs_cache is now active, and all the space is assigned to the pool. The size of the default cache has been reduced by 10MB. The remainder of the difference in the size of the default cache and the total amount of cache available is due to changing overhead values.
Here is the same example on a 16K server:
1> sp_cacheconfig pubs_cache Cache Name Status Type Config Value Run Value ------------------------------ --------- -------- ------------ ------------ pubs_cache Active Mixed 10.00 Mb 10.00 Mb ------------ ------------ Total 10.00 Mb 10.00 Mb ========================================================================== Cache: pubs_cache, Status: Active, Type: Mixed Config Size: 10.00 Mb, Run Size: 10.00 Mb Config Replacement: strict LRU, Run Replacement: strict LRU Config Partition: 1, Run Partition: 1 IO Size Wash Size Config Size Run Size APF Percent -------- --------- ------------ ------------ ----------- 16 Kb 2048 Kb 0.00 Mb 10.00 Mb 10
See “How overhead affects total cache space” for examples.
You can create as many caches as you want before restarting Adaptive Server. You must restart Adaptive Server before you can configure pools or bind objects to newly created caches.
