Skip to Content

Cache Dbspace for Direct-Attached Storage


Cache dbspace is available from SAP IQ 16 and above. Cache dbspace comprised of high speed directly attached solid-state disks (SSDs) that reduce bandwidth requirements of the shared san devices as well as provide a more cost-effective I/O bandwidth.  Similar to IQ_SYSTEM_MAIN, IQ_SYSTEM_TEMP,   IQ_SHARED_TEMP and RLV dbspaces , Cache dbspace is an independent dbspace.  Like  System and User dbspaces, Cache dbspace can have single dbfile or device or multiple dbfiles or devices.  Maximum of one cache dbspace per simplex or multiplex node.  Cache dbspace is local to each multiplex node.

For maximum performance benefits, cache dbspace should be large enough to contain node’s entire working set of pages.  Performance is increased with
data affinity feature of cache dbspace.  Data affinity is automatically enabled for multiplex databases. Data affinity accesses cached data to increase query performance in multiplex servers.

  • To create cache dbspace on simplex or multiplex,  use ‘ IQ CACHE  STORE’  keyword


          create dbspace <dbspace-name> using file  file_specifications IQ CACHE STORE

       File specification:

                    File <logical-file-name>


                    Size <file-size> [ KB | MB | GB | TB ]

         Requires MANAGE ANY DBSPACE system privilege.

  • To drop cache dbspace from a simplex or multiplex node


         drop dbspace <cache-dbspace-name>

         Requires MANAGE ANY DBSPACE system privilege.

  • To add file or raw device to cache dbspace on simplex or multiplex


        alter dbspace <cache-dbspace-name> add file <logical –file-name> <‘file-path’> size  <file-size> [ KB | MB | GB | TB ]

       Requires MANAGE ANY DBSPACE system privilege.

  • Drop dbfile/s from a cache dbspace on simplex or multiplex


      alter dbspace <cache-dbspace-name> drop file <logical-file-name>

      Requires MANAGE ANY DBSPACE system privilege.

  • To disable cache dbspace on simplex or multiplex


        alter dbspace <cache-dbspace-name> OFFLINE

  • To re-enable disabled cache dbspace on simplex or multiplex


       alter dbspace <cache-dbspace-name> ONLINE

      Requires MANAGE ANY DBSPACE system prvilege.

  • To change single dbfile status to Readonly in the cache dbspace


     alter dbspace <cache-dbspace-name> alter file <logical-file-name>  Readonly

  • To change single dbfile status to Readwrite in the cache dbspace


    alter dbspace <cache-dbspace-name> alter file <logical-file-name> Readwrite

  •   To make cache dbspace readonly


         alter dbspace <cache-dbspace-name> readonly

  • Monitoring Cache Dbspace

          sp_iqstatus: displays information about memory usage for cache dbspace and its dbfiles.

         (DBA)> select * from sp_iqstatus() where Name like '%Cache Dbspace%'



Number of Cache Dbspace Files:                                                                                                

Cache Dbspace Blocks: 1-25600                                                                                                                                                                              

Cache Dbspace Blocks: 1045440-1071039                                                                                                                                                                                                                        

Cache Dbspace IQ Blocks Used:                                                                        
of 51200, 0%=0Mb, Max Block#: 0                                                                     

(4 rows)

        sp_iqdbspace:  displays information about the cache dbspace, including read/write status and online/offline status.

       (DBA)> select * from sp_iqdbspace() where DBSpaceName='cachedsp'

      DBSpaceName                                                                                                                      DBSpaceType  Writable Online Usage TotalSize Reserve       NumFiles
NumRWFiles Stripingon StripeSize BlkTypes                                                                       


CACHE        F        T    
15    400M      0B                2           0 T          1K         2H,7616F                                                                                                          
                                                                                                                                            Y        (NULL)                                                                                                   

(1 rows)

          sp_iqfile:  displays information about each dbfile in cache dbspace:

         (DBA)> select * from sp_iqfile() where   DBSpaceName='cachedsp'

                                                                                                                                                                   SegmentType  RWMode Online Usage DBFileSize Reserve
StripeSize BlkTypes                                                                                                                                                                                                                                                                    FirstBlk              LastBlk OkToDrop servername                                                                                                                     

/work/demo0809/                                                                                                                                                                                                                                    CACHE       RO     T    
15    200M       0B    
1K         1H,3808F                                                                                                          
                                                                                                                                                               1                25600 Y        (NULL)                                                         

cachedsp                                                                                                                         iqcachedsp2                                                                                                                    
/work/demo0809/                                                                                                                                                                                                                                    CACHE        RO   
T      15    200M     
0B      1K         1H,3808F                                                                                
                                                                                                                                                                                   1045440              1071039 Y        (NULL)                                                                                                                           (NULL)                                                                                     

(2 rows)

  • Verifying cache Dbspace

        sp_iqcheckdb:  Run sp_iqcheckdb  system stored procedure to perform database verifications against the cache dbspace.


        sp_iqcheckdb ‘allocation  cache’

        sp_iqcheckdb ‘check cache’

        sp_iqcheckdb ‘verify cache’

        sp_iqcheckdb ‘dropleaks cache’

       .iqmsg  -- Database Open:

           Information about cache blks is logged in .iqmsg  at Database open

           Cache     Blks: U0/0%

  • Query Plan Changes

       Additonal information about cache dbspace I/O  is logged in leaf nodes ib query plans regarding cache dbspace I/O

        - projection Cache Dbspace Stats <servername>

        - Condition <x> Cache Dbspace Status <servername>

      Leaf Node specific IO Satistics:

         - HitRate:  The percentage of hit count in the total dbspace read count

         - Finds: The total cache dbspace read count

         - Pageouts: Count of pages written to the cache dbspace by the buffer manager

          - Ioms: Time in milliseconds that the buffer manager took to write to and to read from the cache dbspace

  • Monitor Debug:

         IQ utilities statement can be used to start monitor to collect buffer cache statistics in debug or non-debug mode.


    (DBA)> create table iqmondummy (c1 int)

    (DBA)> iq utilities main into iqmondummy start monitor '-debug -interval 20'

    (DBA)> iq utilities main  into iqmondummy stop monitor

    (DBA)> drop table iqmondummy

    New Fields in Buffer Manager relevan tto Cache Dbspace:

       MainCacheMatch   ----  # buffers found in the Main Cache which also  


      MainCacheMiss  ----- # misses for buffers NOT found in the Main Cache

       MainCacheMatch ---- # buffers found in the Main Cache which also match their counterparts in the main store

      MainCacheMismatch ---- # buffers found in the Main Cache which do NOT match their counterparts in the main store

      MainCacheReadTotal ----   # total physical reads

      MainCacheReadInError ---- # reads that encoutered errors

      MainCacheWriteTotal ----- # total physical writes

      MainCacheWriteInError ---- # writes that encoutered errors