Skip to Content
SAP IQ

Cache Dbspace for Direct-Attached Storage

Tags:

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

         Syntax:

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

       File specification:

                    File <logical-file-name>

                    ‘File-Path’

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

         Requires MANAGE ANY DBSPACE system privilege.

  • To drop cache dbspace from a simplex or multiplex node

         Syntax:

         drop dbspace <cache-dbspace-name>

         Requires MANAGE ANY DBSPACE system privilege.

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

        Syntax:

        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

       Syntax:

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

      Requires MANAGE ANY DBSPACE system privilege.

  • To disable cache dbspace on simplex or multiplex

        Syntax:

        alter dbspace <cache-dbspace-name> OFFLINE

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

       Syntax:

       alter dbspace <cache-dbspace-name> ONLINE

      Requires MANAGE ANY DBSPACE system prvilege.

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

     Syntax:

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

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

     Syntax:

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

  •   To make cache dbspace readonly


          Syntax:

         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%'

Name                                                                                                                                                                                                                                                
          Value                                                                                                      

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Number of Cache Dbspace Files:                                                                                                
                                                                                                                               2                                                                                                          

Cache Dbspace Blocks: 1-25600                                                                                                                                                                              
                                                  iqcachedsp1                                                                                                

Cache Dbspace Blocks: 1045440-1071039                                                                                                                                                                                                                        
iqcachedsp2                                                                                                 

Cache Dbspace IQ Blocks Used:                                                                        
                                                                                                                                                        0
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                                                                       
                                                                                                                                                                               OkToDrop
lsname                                                                
                                             

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


cachedsp                                                                                                                       
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'


        DBSpaceName                                                                                   
                                 DBFileName                                                                                                                     
Path                                                                                        
                                                                                                                                                                   SegmentType  RWMode Online Usage DBFileSize Reserve
StripeSize BlkTypes                                                                                                                                                                                                                                                                    FirstBlk              LastBlk OkToDrop servername                                                                                                                     
mirrorLogicalFileName                                                                  


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

cachedsp                                                                                                                         iqcachedsp2                                                                                                                    
/work/demo0809/iqcachedsp2.iq                                                                                                                                                                                                                                    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.

         Syntax:

        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