Creating a New Cache

Use sp_cacheconfig to create a new cache.

See the Reference Manual: Procedures.

Maximum data cache size is limited only by the amount of memory available on your system. The amount of SAP ASE global memory dictates the memory required to create the new cache. When the cache is created:
  • It has a default wash size.

  • The asynchronous prefetch size is set to the value of global async prefetch limit.

  • It has only the default buffer pool.

Use sp_poolconfig to reset these values.

To create 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. The cache is immediately active. Running sp_cacheconfig now yields:
sp_cacheconfigCache Name                     Status    Type     Config Value Run Value
------------------------------ --------- -------- ------------ ------------
default data cache             Active    Default       0.00 Mb      8.00 Mb
pubs_cache                     Active    Mixed        10.00 Mb     10.00 Mb
------------ ------------
Total      10.00 Mb     18.00 Mb
==========================================================================
Cache: default data cache,   Status: Active,   Type: Default
Config Size: 0.00 Mb,   Run Size: 8.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
-------- --------- ------------ ------------ -----------
4 Kb   1636 Kb      0.00 Mb      8.00 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
-------- --------- ------------ ------------ -----------
4 Kb   2048 Kb      0.00 Mb     10.00 Mb     10

The pubs_cache is now active, and all space is assigned to the smallest pool.

Note: When you create a new cache, the additional memory you specify is validated against max memory. If the sum of total logical memory and additional memory requested is greater than max memory, then SAP ASE issues an error and does not perform the changes.

You can create as many caches as you want without restarting SAP ASE.