Skip to Content
Technical Articles
Author's profile photo Mark Mumy

How to Capture Historical Database and DBSpace Sizes in SAP HANA Cloud, data lake and SAP IQ

Let us note, first, that this blog and code applies to both SAP HANA Cloud, data lake (cloud IQ) and SAP IQ (on-premise IQ).

 

For many SAP IQ use cases, customers have custom written scripts and various jobs to capture information about SAP IQ, include the database size and dbspace sizes over time.  In certain SAP application use cases like Near Line Storage for SAP BW and SAP BW4 DTO as well as Information Lifecycle Management for SAP ERP, we have relied upon front ends that have been replaced and that functionality has been lost.

 

It is also worth noting that SAP HANA Cloud, data lake does not yet have a feature to capture the database and dbspace size over time.

 

This event was written to handle both use cases.

 

I have written a rather simple event (code below) that will capture database information once a day, at midnight.  This is configurable to meet your business and reporting needs.  The event captures both the entire database size as well as the granular size of each main dbspace.

 

The overall database size is captured by using the SAP IQ procedure sp_iqspaceused (SAP IQ 16.1 SP05 manual).  This procedure outputs the sum total of all main dbspaces, include IQ_SYSTEM_MAIN.  This data is captured in a table stored in the catalog store (SYSTEM) called IQ_DBSize.

 

To capture the size of each dbspace, I have used the procedure sp_iqdbspace(SAP IQ 16.1 SP05 manual).  This procedure is a lightweight procedure that captures the size of each dbspace in the system.  The downside is that it does not report blocks in use, but rather a usage percentage and a human readable total size.  While not 100% accurate, I use these values to compute rough size for each dbspace.  See this example of the procedure output:

sp_iqdbspace
DBSpaceName    DBSpaceType Writable Online Usage TotalSize Reserve NumFiles NumRWFiles Stripingon StripeSize BlkTypes                OkToDrop lsname is_dbspace_preallocated
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IQ_SYSTEM_MAIN MAIN        T        T      21    9.76G     200M           1          1 T          1M         1H,255968F,32D,128M,36B N        (NULL) T
IQ_SYSTEM_TEMP TEMPORARY   T        T      1     9.76G     400M           2          2 T          1M         2H,96F,32A,16I          N        (NULL) T
user_main      MAIN        T        T      7     97.7G     400M           2          2 T          1M         2H,777474A              N        (NULL) T

(3 rows)

 

Notice that “user_main” has a size of 97.7G and a usage of 7 (7%).  The dbspace is roughly (97.7GB *1024 *1024) * 7% or 7,171,211.264 KB.  Not precise, but close enough for system planning.  The point is that I wanted a lightweight procedure and I wanted to avoid reverse engineering SAP IQ system procedures in the event.

 

First, let’s make sure that the event doesn’t exist and create the SYSTEM tables:

drop table if exists IQ_DBSpaceSize;
drop table if exists IQ_DBSize;

create table IQ_DBSpaceSize (
        capture_timestamp datetime
        , dbspace_name varchar(255)
        , dbspace_sizeKB unsigned bigint
) on SYSTEM;

create table IQ_DBSize (
        capture_timestamp datetime
        , database_sizeKB unsigned bigint
        , database_usedKB unsigned bigint
) on SYSTEM;

drop event if exists capture_size;

 

 

Now we can create the event:

 

create event capture_size
    SCHEDULE size_schedule
        START TIME '00:00 AM' EVERY 24 HOURS
    HANDLER
begin
  declare _ServerType char(1);
  declare _ServerCnt unsigned bigint;
  declare _ServerCoordCnt unsigned bigint;

  declare _dbsize unsigned bigint;
  declare _dbused unsigned bigint;

  -- use sp_iqmpxinfo (works in IQ and HDL) to know if this is simplex or the coordination on MPX
  select count(*) into _ServerCnt from sp_iqmpxinfo();
  select count(*) into _ServerCoordCnt from sp_iqmpxinfo()
    where server_name = @@servername and role = 'coordinator';

  if _ServerCnt = 0 then
      set _ServerType = 's';
  elseif _ServerCoordCnt = 1 then
      set _ServerType = 'c';
  else
      set _ServerType = 'o';
  end if;


  if lcase(_ServerType) = 's' or lcase(_ServerType) = 'c'
  then
      call dbo.sp_iqlogtoiqmsg( 'DBSIZE: can run on this server type: '|| _ServerType );

        -- capture dbspace usage
        -- the size is a rough number since it is a reverse of the usage, a percentage,
        -- and the size, which was converted to human readable format.
        -- this was easier than reverse engineering all of sp_iqdbspace.
      insert into IQ_DBSpaceSize
        select
          getdate()
          , dbspacename
          , ( usage / 100 ) * case lower ( right( totalsize, 1 ) )
          when 'k' then replace( lower( totalsize ), 'k','')
          when 'm' then replace( lower( totalsize ), 'm','') * 1024
          when 'g' then replace( lower( totalsize ), 'g','') * 1024 * 1024
          when 't' then replace( lower( totalsize ), 't','') * 1024 * 1024 * 1024
          when 'p' then replace( lower( totalsize ), 'p','') * 1024 * 1024 * 1024 * 1024
          end
          from sp_iqdbspace() where lower( dbspacetype ) = 'main'
            and dbspacename not in (  'hotsql_dbspace' );
          -- exclude any dbspaces you don't want to see in the above line
          -- hotsql_dbspace is an HDL reserved dbspace and shouldn't be captured.

        -- capture overall MAIN STORE (user and system) usage and store that
      call sp_iqspaceused ( _dbsize, _dbused, null, null, null, null, null, null, null, null );
      call dbo.sp_iqlogtoiqmsg( 'DBSIZE: '|| getdate()||' '|| _dbsize||' '|| _dbused );
      insert into IQ_DBSize values( getdate(), _dbsize, _dbused );
      commit;
    return
  else
      call dbo.sp_iqlogtoiqmsg( 'DBSIZE: cannot run on this server of type: '|| _ServerType );
  end if
end;

 

I changed the event timer to run “EVERY 1 MINUTES” so that I could show how the procedure will output data to the tables.

 

In SAP IQ 15.1 SP05 after 3 iterations, this event output looks like this:

select * from IQ_DBSize;
capture_timestamp          database_sizeKB database_usedKB
----------------------------------------------------------
2021-11-19 14:29:50.099509       112640000         8269128
2021-11-19 14:30:00.016558       112640000         8269128
2021-11-19 14:31:00.01503        112640000         8269128

(3 rows)

select * from IQ_DBSpaceSize;
capture_timestamp          dbspace_name   dbspace_sizeKB
--------------------------------------------------------
2021-11-19 14:29:50.087938 IQ_SYSTEM_MAIN        2149161
2021-11-19 14:29:50.087938 user_main             7171211
2021-11-19 14:30:00.007595 IQ_SYSTEM_MAIN        2149161
2021-11-19 14:30:00.007595 user_main             7171211
2021-11-19 14:31:00.005074 IQ_SYSTEM_MAIN        2149161
2021-11-19 14:31:00.005074 user_main             7171211

(6 rows)

 

That same event in SAP HANA Cloud, data lake, the output looks like this:

select * from IQ_DBSize;
capture_timestamp          database_sizeKB database_usedKB
----------------------------------------------------------
2021-11-19 14:30:00.54169      96503070720      1971885120
2021-11-19 14:31:00.237194     96503070720      1971879456
2021-11-19 14:32:00.236421     96503070720      1971886752

(3 rows)

select * from IQ_DBSpaceSize;
capture_timestamp          dbspace_name   dbspace_sizeKB
--------------------------------------------------------
2021-11-19 14:30:00.039703 IQ_SYSTEM_MAIN      252544077
2021-11-19 14:30:00.039703 user_main          1906965479
2021-11-19 14:31:00.030404 IQ_SYSTEM_MAIN      252544077
2021-11-19 14:31:00.030404 user_main          1906965479
2021-11-19 14:32:00.032755 IQ_SYSTEM_MAIN      252544077
2021-11-19 14:32:00.032755 user_main          1906965479

(6 rows)

 

Please comment if there are features of this that you want or if you find any issues with it.

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Roland Kramer
      Roland Kramer

      Hello Mark
      Nice Article.

      I found the usage of sp_iqsysmon also quite use and powerful, especially time depended analysis.

      Note 2517920 - About iqmt server parameter - SAP IQ
      sp_iqsysmon '00:00:05', 'threads';

      Note 2633038 - How to monitor Main/Temp cache allocation status -- SAP IQ
      sp_iqsysmon '00:00:05', 'mbufpool';

      Note 2809829 - How to monitor memory IQLM (IQ Large Memory) - SAP IQ
      sp_iqsysmon '00:00:05', 'memory, lma';

      SAP IQ Memory Details
      sp_iqsysmon '00:00:05', 'mbufalloc, tbufalloc';
      sp_iqsysmon '00:00:05', 'mbufman, mbufpool, mbufalloc';

      Best Regards Roland

      Author's profile photo Mark Mumy
      Mark Mumy
      Blog Post Author

      Yes, I completely agree.  Many customers use sp_iqsysmon, and other utilities, and extract relevant information either to the catalog or to external stores so that it can be used for historical analysis.

      This event could be augmented to include that information as well, with just a little tweaking and SQL coding.

       

      Mark

      Author's profile photo Mark Mumy
      Mark Mumy
      Blog Post Author

      I would be remiss if I didn't mention the newly added monitoring views.  Check out this link to the SAP IQ 16.1 SP05 views.  The monitoring views were added to SAP IQ 16.1 SP04.  They give quite a lot of the same information that you can get from sp_iqsysmon.

       

      Mark

      Author's profile photo Roland Kramer
      Roland Kramer

      True.

      Beside new Monitoring Views with SP04, we got a new Data Space Type: IQ_SYSTEM_CACHE

      CREATE DBSPACE IQ_SYSTEM_CACHE 
      USING FILE IQ_SYSTEM_CACHE 
      '../cache/IQ_SYSTEM_CACHE_0001.iqcache' SIZE 8192 IQ CACHE STORE

      It might not be the place of the Discussion here, but in Terms of new undiscovered feature definitely.

      Never the less, SAP Customers will gain from the new IQ Database Extension for tx. DBACOCKPIT, which bring new monitor capabilities far beyond SAP IQ Cockpit, SQL based Monitoring  and tx. DBACOCKPIT until now.

      for SAP BW/4 2021 with SAP Basis 7.56 SP01
      for SAP BW/4 2.0 with SAP Basis 7.53 SP10
      for SAP BW/4 1.0 and BW/4 1.0 with SAP Basis 7.50 SP23

      New Features include a SQL Interface, DB Startup Analysis, several file access options and event triggering for IQ Backup.